In this example I am trying to create a 11g dataguard standby database (pc01sby) from my primary database (pc01prmy).
I have installed Oracle 11g R1 on both the nodes.
1. On the primary database pc01prmy enable force logging:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SELECT force_logging FROM v$database;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT force_logging FROM v$database;
2. Enable Archiving in the primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;
3. Create a static entry for the primary database (pc01prmy) in the listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pc01prmy)
(ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
(SID_NAME = pc01prmy)
)
)
4. Create a service for the standby database (pc01sby):
C:\> oradim -new -sid pc01sby
5. Create a static entry for the standby database in listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pc01sby)
(ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
(SID_NAME = pc01sby)
)
)
If you are creating the standby database in a remote system ensure not to make any static entry for the primary database. Just add the static entry show above in step 5.
6. Create a service named after your standby database (pc01sby) using the netmanager or adding the following entry in tnsnames.ora of the primary node:
PC01SBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lab40)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pc01sby)
)
)
7. Add a few standby logfiles for your primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc01prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc02prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc03prmy\srl01.log' size 52428800;
8. Set proper value for log_archive_config and log_archive_dest_1
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(pc01prmy, pc01sby)';
SQL> ALTER SYSTEM SET log_archive_dest_2 = 'service=pc01sby async valid_for=(online_logfile, primary_role) db_unique_name = pc01sby';
9. Still connected to your primary database increase the size of FRA:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=5G;
10. Copy the password file from your primary database node (eg: PWDpc01prmy.ora) in 'c:\app\Administrator\product\11.1.0\db_1\database' to the same location in your standby database node and rename it for your standby database (eg: PWDpc01sby.ora)
11. Create a pfile in the standby node for the standby database in 'c:\app\Administrator\product\11.1.0\db_1\database' and name it initpc01stby.ora
12. Edit the initpc01sby.ora and make the following entry:
DB_NAME=pc01sby
If you have an option of creating the dataguard configuration between two separate nodes then remember to keep the same db_name in the initialization parameter file of both the nodes but different names of the db_unique_name. Just a convention but not mandatory.
13. Create the following folders for the standby database in the standby node:
C:\> md C:\app\Administrator\admin\pc01sby
C:\> md C:\app\Administrator\admin\pc01sby\adump
C:\> md C:\app\Administrator\oradata\pc01sby
14. Startup the database in nomount state using the pfile that you created in step 12:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> startup nomount pfile = C:\app\Administrator\product\11.1.0\db_1\database\initpc01sby.ora
15. Restart the listener on both the nodes:
C:\> lsnrctl
LSNRCTL> stop
LSNRCTL> start
LSNRCTL> status
16. In the primary database node use rman to connect to the primary database and standby database:
C:\> rman
RMAN> CONNECT TARGET sys/oracle@pc01prmy
RMAN> CONNECT AUXILIARY sys/oracle@pc01sby
17. Still connected to the RMAN execute the following script:
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'pc01prmy','pc01sby'
set db_unique_name='pc01sby'
set db_file_name_convert='\pc01prmy\','\pc01sby\'
set log_file_name_convert='\pc01prmy\','\pc01sby\'
set control_files='C:\app\Administrator\oradata\pc01sby\pc01sby.ctl'
set log_archive_max_processes='5'
set fal_client='pc01sby'
set fal_server='pc01prmy'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(pc01prmy,pc01sby)'
set log_archive_dest_2 = 'service=pc01prmy ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pc01prmy'
;
}
18. Exit from RMAN and connect to you primary database and perform a logswitch:
RMAN> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE SWITCH LOGFILE;
19. Open a separate command prompt and connect to your standby database and test if you can recover the standby using the newly created logfile:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
20. On the standby verify that archive logs were received by executing a few log switch on the primary and verifying them on standby:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time FROM v$archived_log ORDER BY sequence#;
SQL> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> exit
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time from v$archived_log order by sequence#;
SQL> exit
21. You can verify if a log was applied or not by executing:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#;
SQL> exit
For more details visit:
http://download.oracle.com/
I have installed Oracle 11g R1 on both the nodes.
1. On the primary database pc01prmy enable force logging:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SELECT force_logging FROM v$database;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT force_logging FROM v$database;
2. Enable Archiving in the primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;
3. Create a static entry for the primary database (pc01prmy) in the listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pc01prmy)
(ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
(SID_NAME = pc01prmy)
)
)
4. Create a service for the standby database (pc01sby):
C:\> oradim -new -sid pc01sby
5. Create a static entry for the standby database in listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pc01sby)
(ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
(SID_NAME = pc01sby)
)
)
If you are creating the standby database in a remote system ensure not to make any static entry for the primary database. Just add the static entry show above in step 5.
6. Create a service named after your standby database (pc01sby) using the netmanager or adding the following entry in tnsnames.ora of the primary node:
PC01SBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lab40)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pc01sby)
)
)
7. Add a few standby logfiles for your primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc01prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc02prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc03prmy\srl01.log' size 52428800;
8. Set proper value for log_archive_config and log_archive_dest_1
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(pc01prmy, pc01sby)';
SQL> ALTER SYSTEM SET log_archive_dest_2 = 'service=pc01sby async valid_for=(online_logfile, primary_role) db_unique_name = pc01sby';
9. Still connected to your primary database increase the size of FRA:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=5G;
10. Copy the password file from your primary database node (eg: PWDpc01prmy.ora) in 'c:\app\Administrator\product\11.1.0\db_1\database' to the same location in your standby database node and rename it for your standby database (eg: PWDpc01sby.ora)
11. Create a pfile in the standby node for the standby database in 'c:\app\Administrator\product\11.1.0\db_1\database' and name it initpc01stby.ora
12. Edit the initpc01sby.ora and make the following entry:
DB_NAME=pc01sby
If you have an option of creating the dataguard configuration between two separate nodes then remember to keep the same db_name in the initialization parameter file of both the nodes but different names of the db_unique_name. Just a convention but not mandatory.
13. Create the following folders for the standby database in the standby node:
C:\> md C:\app\Administrator\admin\pc01sby
C:\> md C:\app\Administrator\admin\pc01sby\adump
C:\> md C:\app\Administrator\oradata\pc01sby
14. Startup the database in nomount state using the pfile that you created in step 12:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> startup nomount pfile = C:\app\Administrator\product\11.1.0\db_1\database\initpc01sby.ora
15. Restart the listener on both the nodes:
C:\> lsnrctl
LSNRCTL> stop
LSNRCTL> start
LSNRCTL> status
16. In the primary database node use rman to connect to the primary database and standby database:
C:\> rman
RMAN> CONNECT TARGET sys/oracle@pc01prmy
RMAN> CONNECT AUXILIARY sys/oracle@pc01sby
Note: If you didn't copy the exact passwordfile from the primary to the standby it will give you the following error:
RMAN> connect auxiliary sys/qfund@pc01sby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password;17. Still connected to the RMAN execute the following script:
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'pc01prmy','pc01sby'
set db_unique_name='pc01sby'
set db_file_name_convert='\pc01prmy\','\pc01sby\'
set log_file_name_convert='\pc01prmy\','\pc01sby\'
set control_files='C:\app\Administrator\oradata\pc01sby\pc01sby.ctl'
set log_archive_max_processes='5'
set fal_client='pc01sby'
set fal_server='pc01prmy'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(pc01prmy,pc01sby)'
set log_archive_dest_2 = 'service=pc01prmy ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pc01prmy'
;
}
18. Exit from RMAN and connect to you primary database and perform a logswitch:
RMAN> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE SWITCH LOGFILE;
19. Open a separate command prompt and connect to your standby database and test if you can recover the standby using the newly created logfile:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
20. On the standby verify that archive logs were received by executing a few log switch on the primary and verifying them on standby:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time FROM v$archived_log ORDER BY sequence#;
SQL> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> exit
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time from v$archived_log order by sequence#;
SQL> exit
21. You can verify if a log was applied or not by executing:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#;
SQL> exit
For more details visit:
http://download.oracle.com/
