Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm
Env:
OS：Windows Server 2003
Oracle: 11.2.0.3
Primary Database: primary (db_unique_name)
Standby Database: standby (db_unique_name)
For convenience, the primary and standby database sit on the same machine. And using cold backup method to back up the primary database.
Primary DB Setup
1. Enable Archivelog mode
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination D:\oracle\ARCHIVED_LOG\primary\ Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 SQL>
2. Force Logging
SQL> alter database force logging;
3. Update the parameter files
SQL> create pfile='d:\oracle\oradata\primary\initPrimary.ora' from spfile; File created.
Open the file "initPrimary.ora", add the following entries in the file...
*.db_name='primary' *.db_unique_name='primary' *.log_archive_config='DG_CONFIG=(primary, standby)' *.log_archive_dest_1='LOCATION=D:\oracle\ARCHIVED_LOG\primary\ VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=primary' *.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=standby' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=30 *.remote_login_passwordfile='EXCLUSIVE' -- Specify the follwing parameters for primary/standby switchover *.LOG_FILE_NAME_CONVERT='D:\oracle\oradata\standby\','D:\oracle\oradata\primary\' *.DB_FILE_NAME_CONVERT='D:\oracle\oradata\standby\','D:\oracle\oradata\primary\' *.STANDBY_FILE_MANAGEMENT='AUTO' *.FAL_CLIENT='primary' *.FAL_SERVER='standby'
Pay sprecial attention to the parameter "LOG_FILE_NAME_CONVERT" and "DB_FILE_NAME_CONVERT". These parameters are for "standby" db only, so put the primary database log(data) file ahead of standby database log(data) file. For example, in this case, the *primary* will be acted as standby db if switched over. Thus, you need to put "D:\oracle\oradata\standby\" before "D:\oracle\oradata\primary\". Besides, don't forget the "\" at the end of the file path. Otherwise, the Oracle will not recognise the log(db) file correctly.
Then we need to convert the pfile to spfile.
SQL> create spfile from pfile='d:\oracle\oradata\primary\initPrimary.ora';
Note: you may need to shutdown the instance before running the above command.
4. Add the standby log group
alter database add standby logfile ('D:\oracle\oradata\primary\standylog01.rdo') size 50M;
alter database add standby logfile ('D:\oracle\oradata\primary\standylog02.rdo') size 50M;
alter database add standby logfile ('D:\oracle\oradata\primary\standylog03.rdo') size 50M;
alter database add standby logfile ('D:\oracle\oradata\primary\standylog04.rdo') size 50M;
5. Full databse cold backup
Shutdown database and copy all the datafiles to the standby datafile folder -- "d:\oracle\oradata\standby\"
6. Backup the pfile for standby database usage
SQL> create pfile='d:\oracle\oradata\primary\initPrimary2.ora' from spfile;
7. Create standby control file for standby db use
SQL> STARTUP MOUNT; SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'd:\oracle\oradata\primary\standby.ctl'; SQL> ALTER DATABASE OPEN;
Standby DB Setup
1. Create relevant folder for standby database.
For example:
d:\oracle\oradata\standby -- put all the primary backup data files in this folder
d:\oracle\archived_log\standby
D:\oracle\fast_recovery_area\standby
D:\oracle\admin\standby\adump
2. Modify the parameter file generated from primary database
*.audit_file_dest='D:\oracle\admin\standby\adump' *.control_files='D:\oracle\oradata\standby\control01.ctl','D:\oracle\fast_recovery_area\standby\control02.ctl' *.db_name='primary' *.db_unique_name='standby' *.diagnostic_dest='D:\oracle' *.FAL_CLIENT='standby' *.FAL_SERVER='primary' *.log_archive_config='DG_CONFIG=(primary, standby)' *.log_archive_dest_1='LOCATION=D:\oracle\ARCHIVED_LOG\standby\ VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=standby' *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=primary' *.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='D:\oracle\oradata\primary\','D:\oracle\oradata\standby\' *.DB_FILE_NAME_CONVERT='D:\oracle\oradata\primary\','D:\oracle\oradata\standby\' *.remote_login_passwordfile='EXCLUSIVE' *.STANDBY_FILE_MANAGEMENT='AUTO'
Please note that the db_name is still "primary" (standby should have the same db_name as primary db), but the db_unique_name is "standby". Please also note the value set the parameter "LOG_ARCHIVE_DEST_1", "LOG_ARCHIVE_DEST_2", "LOG_FILE_NAME_CONVERT", "DB_FILE_NAME_CONVERT" (this time, put the *pimary* ahead of *standby*)
3. Update the tnsname.ora to add service names - primary and standby
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev.analytics.moodys.net)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMARY)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev.analytics.moodys.net)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STANDBY)
)
)
4. Create "standby" Oracle service and password file
oradim -new -sid standby
Don't use the parameter "intpwd" parameter to create the password file at the same time, that's...
oradim -new -sid standby -intpwd a
Instead, use "orapwd" to create the passord file...
orapwd file=D:\oracle\product\11.2.0\dbhome_2\database\PWDstandby.ORA entries=2 force=y ignorecase=y password=a
Remember to set the "ingorecase" to "y". Otherwise, the following error may occur...
MESSAGE -------------------------------------------------------------------------------- PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031. Error 1031 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031. Error 1031 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031. Error 1031 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031. Error 1031 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031. Error 1031 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 1031.
5. Restore the backup data files and standby control files
Put the backup data files of primary database to the relevant directory -- d:\oracle\oradata\standby
Put the standby control file (standby.ctl) of the priamry database to the relevant directory -- d:\oracle\oradata\standby; d:\oracle\fast_recovery_area\standby and rename the control files to control01.ctl and control02.ctl respectively.
6. Startup standby instance
C:\set oracle_sid=standby C:\sqlplus sys/a as sysdba SQL>create spfile from pfile='D:\oracle\oradata\standby\initStandby.ora';
7. Restore standby db and start redo apply
SQL> startup mount; SQL> alter database recover managed standby database disconnect from session;
Verify the Setup
1. Check standby db
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
standby
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
7 rows selected.
SQL>
2. Check the primary db
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
primary
SQL> select sequence#, thread#, fal, status, applied from v$archived_log where sequence# > 32 order by sequence#;
SEQUENCE# THREAD# FAL S APPLIED
---------- ---------- --- - ---------
33 1 NO A NO
33 1 YES A YES
34 1 NO A NO
34 1 YES A YES
35 1 NO A NO
35 1 YES A YES
36 1 NO A NO
36 1 YES A YES
37 1 NO A NO
37 1 YES A YES
38 1 NO A YES
38 1 NO A NO
39 1 NO A YES
39 1 NO A NO
14 rows selected.
3. Switch log file in primary
SQL> alter system switch logfile; System altered.
4. Check standby
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
40 IN-MEMORY
8 rows selected.
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
40 YES
8 rows selected.
SQL>
Please note there is one more record appeared -- sequence# 40
5. Check the primary
SQL> select sequence#, thread#, fal, status, applied from v$archived_log where sequence# > 32 order by sequence#;
SEQUENCE# THREAD# FAL S APPLIED
---------- ---------- --- - ---------
33 1 YES A YES
33 1 NO A NO
34 1 NO A NO
34 1 YES A YES
35 1 NO A NO
35 1 YES A YES
36 1 NO A NO
36 1 YES A YES
37 1 NO A NO
37 1 YES A YES
38 1 NO A YES
38 1 NO A NO
39 1 NO A YES
39 1 NO A NO
40 1 NO A YES
40 1 NO A NO
16 rows selected.
6. Simple Test
Create a new tablespace in primary...
SQL> create tablespace test_ts 2 datafile 'D:\oracle\oradata\primary\test_ts01.dbf' 3 size 1M; Tablespace created.
Because the log sync is async mode, the tablespace test_ts won't be created in standby immediately. But we can switch logfile in primary to force the log apply in standby.
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- primary SQL> alter system switch logfile; System altered. SQL>
Check the alert file in standby, and will find the following messages...
Thu Apr 26 09:34:34 2012 RFS[5]: Selected log 4 for thread 1 sequence 54 dbid 1618933582 branch 781450898 Thu Apr 26 09:34:35 2012 Archived Log entry 21 added for thread 1 sequence 53 ID 0x607ee34e dest 1: Thu Apr 26 09:34:37 2012 Media Recovery Log D:\ORACLE\ARCHIVED_LOG\STANDBY\1_53_781450898.ARC Recovery created file D:\ORACLE\ORADATA\STANDBY\TEST_TS01.DBF Successfully added datafile 5 to media recovery Datafile #5: 'D:\ORACLE\ORADATA\STANDBY\TEST_TS01.DBF' Media Recovery Waiting for thread 1 sequence 54 (in transit)
The db file is generated in standby database.
7. Monitor the log apply reference -- http://docs.oracle.com/cd/B19306_01/server.102/b14239/manage_ps.htm#i1035175
