Environment details: Oracle 10gR2 running on RHEL5 64bit.
Dataguard final config: Physical standby, Maximum Performance, no broker. Primary DB A1AR running on server lnx-primary. Standby DB A2AR running on server lnx-standby.
1. Make sure that your primary DB is running in FORCE LOGGING mode.
Otherwise, on the primary DB issue:
ALTER DATABASE FORCE LOGGING;
2. Create a Password File on both systems (use the same sys password for both DBs), alternatively you can simply copy the primary password file to the standby server and rename it.
3. Clone the DB manually or using RMAN.
3a. Manual clone:
Shutdown the primary DB.
On the standby DB server, create the four dump dirs in the location you will speficy in the standby DB init file
mkdir bdump
mkdir udump
mkdir cdump
mkdir adump
Copy the redo logs and datafiles to the standby DB server using the locations you will speficy in the standby DB init file.
No need to copy control file or init file at this stage, just follow the steps below.
3b. If you prefer to use RMAN then check my post “Dataguard – Setting up Physical Standby part2”
4. Make sure that your primary is running in ARCHIVELOG mode. Otherwise:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
5. Create the control file for the standby database.
If the DB is not mounted or opened.
SQL> STARTUP MOUNT;
Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/A2AR.ctl';
SQL> ALTER DATABASE OPEN;
6. Set init parameters. First:
SQL> CREATE PFILE FROM SPFILE;
6a. Then modify the pfile so that the primary initialization parameters are:
[...]
*.db_domain='lnx-primary'
*.db_name='A1AR'
*.DB_UNIQUE_NAME='A1AR'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(A1AR,A2AR)'
*.LOG_ARCHIVE_DEST_1=
'LOCATION=/u11/oracle/oradata/arch_A1AR/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=A1AR'
*.LOG_ARCHIVE_DEST_2=
'SERVICE=A2AR LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=A2AR'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=A1AR
*.FAL_CLIENT=A2AR
*.DB_FILE_NAME_CONVERT='A2AR','A1AR','/u02/oracle/oradata/A2AR/','/u02/oracle/oradata/A1AR/',
'/u01/oracle/admin/product/A2AR/','/u01/oracle/product/admin/A1AR/',
'/u11/oracle/arch_A2AR/','/u11/oracle/arch_A1AR/','/u12/oracle/oradata/A1AR/users01.dbf','/u02/oracle/oradata/A2AR/users01.dbf'
*.LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/A2AR/','/u02/oracle/oradata/A1AR/',
'/u01/oracle/admin/product/A2AR/','/u01/oracle/product/admin/A1AR/',
'/u11/oracle/arch_A2AR/','/u11/oracle/arch_A1AR/','/u12/oracle/oradata/A1AR/users01.dbf','/u02/oracle/oradata/A2AR/users01.dbf'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=5
[...]
Delete/move the spfile and bounce the DB so that it uses the new pfile. After that recreate the spfile:
SQL> CREATE SPFILE FROM PFILE;
and bounce the DB to use the new spfile.
6b. After that, copy the PFILE to the standby server and modify it so that the standby Initialization Parameters are:
[...]
*.db_domain='lnx-standby'
*.db_name='A1AR'
*.DB_UNIQUE_NAME='A2AR'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(A1AR,A2AR)'
*.LOG_ARCHIVE_DEST_1=
'LOCATION=/u11/oracle/oradata/arch_A2AR/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=A2AR'
*.LOG_ARCHIVE_DEST_2=
'SERVICE=A1AR LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=A1AR'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=A1AR
*.FAL_CLIENT=A2AR
*.DB_FILE_NAME_CONVERT='A1AR','A2AR','/u02/oracle/oradata/A1AR/','/u02/oracle/oradata/A2AR/',
'/u11/oracle/arch_A1AR/','/u11/oracle/arch_A2AR/','/u12/oracle/oradata/A1AR/users01.dbf','/u02/oracle/oradata/A2AR/users01.dbf',
'/u02/oracle/oradata/A2AR/users01.dbf','/u12/oracle/oradata/A1AR/users01.dbf'
*.LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/A1AR/','/u02/oracle/oradata/A2AR/',
'/u11/oracle/arch_A1AR/','/u11/oracle/arch_A2AR/'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=5
[...]
NOTE:
Check again and again the following parameters:
– The LOG_ARCHIVE_DEST_2, all the SIDs in it, be aware of the difference between standby and primary.
– *.db_domain,*.db_name and *.DB_UNIQUE_NAME; if any of these is not correct you will get some kind of error in the alert log such as:
Fri Mar 9 21:23:41 2012
Error 12154 received logging on to the standby
Fri Mar 9 21:23:41 2012
Errors in file /u01/oracle/admin/A1AR/bdump/a1ar_arc0_31012.trc:
ORA-12154: Message 12154 not found; No message file for product=RDBMS, facility=ORA
PING[ARC0]: Heartbeat failed to connect to standby 'A1AR'. Error is 12154.
Note that the example assumes the use of the LGWR process to transmit redo data to both the local and remote destinations on the LOG_ARCHIVE_DEST_2 initialization parameter in ASYNC mode, which means that the log writer process writes to the local online redo log file, while the network server (LNSn) processes (one for each destination) asynchronously transmit the redo to remote destinations. The LGWR process continues processing the next request without waiting for the LNS network I/O to complete.If redo transport services transmit redo data to multiple remote destinations, the LNSn processes (one for each destination) initiate the network I/O to all of the destinations in parallel.
If you prefer SYNC mode (default for LGWR) just write LGWR, in this case Data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process.
For more info on LOG_ARCHIVE_DEST_n parameter attributes: http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm
In addition, ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, redo transport services may be unable to transmit redo data from the primary database to the standby databases. In a Data Guard configuration, COMPATIBLE must be set to a minimum of 9.2.0.1.0. However, if you want to take advantage of new Oracle Database 10g features, set the COMPATIBLE parameter to 10.2.0.0 or higher.
For more info on dataguard parameters: http://docs.oracle.com/cd/B19306_01/server.102/b14239/init_params.htm
7. Configure listeners and tnsnames files for the primary and standby database.
On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by redo transport services.
The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and service that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used.
Examples:
Primary database tnsnames.ora
A1AR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = A1AR.lnx-primary)
)
)
A2AR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = A2AR.lnx-standby)
)
)
Standby database tnsnames.ora
A1AR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = A1AR.lnx-primary)
)
)
A2AR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = A2AR.lnx-standby)
)
)
To test, from the primary server try first:
$ tnsping A2AR
and then
$ sqlplus 'sys/password@A2AR' as sysdba
In some cases, such as when you plan to use the broker or when you clone the DB with RMAN, you may have to edit the listener.ora file.
I recommend to create a separate listener for your dataguard setup if you have more than one service running on the same server.
8. Startup nomount the physical standby database using the pfile. The create spfile from pfile, shutdown and restart mount.
On the standby database, issue the following SQL statement to start and mount the database:
SQL> STARTUP NOMOUNT;
SQL> CREATE SPFILE FROM PFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
9. Start Redo Apply.
On the standby database, issue the following command to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or simply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.
With LGWR ASYNC set, the transmission of redo data to the remote standby location won’t occur until after a log switch. To test this, on the primary:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Even more than once. To verify, on the primary issue:
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
On the standby:
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
10.Sop Redo Apply:
On the standby database, issue the following command to stop Redo Apply:
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
This is needed when you want to access your standby DB in readonly, otherwise if you try:
alter database open read only;
when the redo apply is active you will get:
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
Reference:
http://docs.oracle.com/cd/B19306_01/server.102/b14239/title.htm
http://www.orafaq.com/wiki/Data_Guard_implementation_for_Oracle_10gR2
http://www.arabicjourney.com/ttrail/fajr.php?doc=wtrail&doid=12519592
Recommended readings:
Scenarios:
http://docs.oracle.com/cd/B19306_01/server.102/b14239/scenarios.htm
SQL Statements:
http://docs.oracle.com/cd/B19306_01/server.102/b14239/sql_stmts.htm
Views:
http://docs.oracle.com/cd/B19306_01/server.102/b14239/views.htm
DBA tales for rainy days 