My latest experience with Data Guard was manually creating a Physical Standby Database in a Disaster Recovery DR project. I am maintaining it daily and it works well. I would like to share my experience with the other DBAs.
In this example the database version is 10.2.0.1. The Primary database and Standby database are located on different machines at different sites. The Primary database is called CELL and the Standby database is called STAN. I use Flash Recovery Area, and OMF.
I. Before you get started:
1. Make sure the operating system and platform architecture on the primary and standby systems are the same.
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
3. Test the Standby Database creation on a test environment first before working on the Production database.
II. On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
i) To check if a password file already exists, run the following command:
SQL> SELECT * FROM V$PWFILE_USERS;
ii) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%
$orapwd file=pwdCELL.ora password=***** force=y
(Note: Replace
- On UNIX:
$cd $ORACLE_HOME/dbs
$Orapwd file=pwdCELL.ora password=***** force=y
(Note: Replace ***** with your actual password for the SYS user.)
3. Configure a Standby Redo log.
i) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes/1024/1024 MB from v$log;
MB
----
50
50
50
ii) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
iii) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
IF OMF (Oracle Managed Files)
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
If non OMF (Manually managed files)
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\oracle\product\10.2.0\oradata\cell\REDO04.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\oracle\product\10.2.0\oradata\cell\REDO05.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\oracle\product\10.2.0\oradata\cell\REDO06.LOG') SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log; (or)
SQL> SELECT GROUP#,DBID,BYTES/1024/1024 MB FROM V$STANDBY_LOG;
GROUP# DBID MB
---------- ---------------------------
4 UNASSIGNED 50
5 UNASSIGNED 50
6 UNASSIGNED 50
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\oracle\product\10.2.0\flash_recovery_area\CELL\ARCHIVELOG
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
i) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’c:\pfileCELL.ora’ from spfile;
- On UNIX:
SQL>create pfile=’
ii) Edit this pfile to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
cell.__db_cache_size=490733568
cell.__java_pool_size=4194304
cell.__large_pool_size=4194304
cell.__shared_pool_size=104857600
cell.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\cell\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\cell\bdump'
*.compatible='10.2.0.1.0'
*.control_files='c:\oracle\product\10.2.0\oradata\cell\control01.ctl','c:\oracle\product\10.2.0\oradata\cell\control02.ctl','c:\oracle\product\10.2.0\oradata\cell\control03.ctl'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\cell\cdump'*.db_block_size=8192
*.db_cache_size=490733568
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\stan','c:\oracle\product\10.2.0\oradata\cell'
*.db_name='cell'
*.db_recovery_file_dest='c:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='cell'
*.FAL_CLIENT='cell'
*.FAL_SERVER='stan'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cell,stan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\product\10.2.0\flash_recovery_area\CELL\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cell'
*.LOG_ARCHIVE_DEST_2='SERVICE=stan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\stan','c:\oracle\product\10.2.0\oradata\cell'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_pool_size=104857600
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\oracle\product\10.2.0/admin/cell/udump'
(Note: For DB_FILE_NAME_CONVERT -Specify the location of the standby DB datafiles followed by the primary location;
For LOG_FILE_NAME_CONVERT - Specify the location of the standby DB online redo log files followed by the primary location.)
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’c:
SQL>create spfile from pfile=’c:\pfileCELL.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
- On Unix:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/u01/app/oraclepfileCELL.ora’;
SQL>create spfile from pfile=’
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘
On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
i) Create directory for data files, for example, on windows,
c:\oracle\product\10.2.0\oradata\stan
ii) Copy the data files and temp files over.
iii) Create directory for online logs, for example, on Windows, c:\oracle\product\10.2.0\oradata\stan
iv) Copy the online logs over.
2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
File will be created in %ORACLE_HOME%\database directory.
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
i) Copy pfileCELL.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
ii) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
stan.__db_cache_size=490733568
stan.__java_pool_size=4194304
stan.__large_pool_size=4194304
stan.__shared_pool_size=104857600
stan.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\stan\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\stan\bdump'
*.compatible='10.2.0.1.0'
*.control_files='c:\oracle\product\10.2.0\oradata\stan\stan.ctl','c:\oracle\stan.ctl'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\stan\cdump'
*.db_block_size=8192
*.db_cache_size=490733568
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\cell','c:\oracle\product\10.2.0\oradata\stan'
*.db_name='cell'
*.db_recovery_file_dest='c:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='stan'
*.FAL_CLIENT='stan'
*.FAL_SERVER='cell'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cell,stan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\product\10.2.0\flash_recovery_area\stan\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_2='SERVICE=cell LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cell'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\cell','c:\oracle\product\10.2.0\oradata\stan'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_pool_size=104857600
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\oracle\product\10.2.0\admin\stan\udump'
4) On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
'c:\oracle\product\10.2.0\admin\stan\adump'
'c:\oracle\product\10.2.0\admin\stan\bdump'
'c:\oracle\product\10.2.0\admin\stan\cdump'
'c:\oracle\product\10.2.0\admin\stan\udump'
c:\oracle\product\10.2.0\flash_recovery_area\stan\ARCHIVELOG
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations
c:\oracle\STAN.CTL
c:\oracle\product\10.2.0\oradata\stan\STAN.CTL
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to %ORACLE_HOME%
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
i) On Primary system:
use Oracle Net Manager to configure a listener for PRIM and STAN services.
Then restart the listener.
$lsnrctl stop
$lsnrctl start
Content of LISTENER.ORA file on PRIMARY Server is,
# listener.ora Network Configuration File: c:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = AbdulsamiPC.qic.local)(PORT = 1521))
)
)
ii) On Standby server: use Net Manager to configure a listener for PRIM and STAN services. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
i) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN.
Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
ii) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN.
Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
Content of TNSNAMES.ORA File on both PRIMARY and STANDBY Server is,
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
CELL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = abdulsamipc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cell)
)
)
STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = asamipc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stan)
)
)
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’c:\pfileSTAN.ora’;
SQL>create spfile from pfile=’c:\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
- On Unix:
SQL>startup nomount pfile=’$ORACLE_HOME
SQL>create spfile from pfile=’$ORACLE_HOME
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘
12. Start Redo apply
i) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
i) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
ii) On Primary, force a logfile switch:
SQL>alter system switch logfile;
iii) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target 'sys/*****
Rman>backup archivelog all delete input;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server. If the password files are not in sync then update/recreate password file for the Standby database.
Reference:
Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04
For any queries please don't hesitate to contact me, Sami Malik (samiora@gmail.com)
[Senior Oracle Applicaitons DBA at Government Sector in Middle East]
Cell: 0097433164959
