[DataGuard] Creating a Physical Standby Database
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
1 2 3 4 5 6 7 8 | 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
1 | SQL> alter database force logging; |
3. Update the parameter files
1 2 3 | 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...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | *.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.
1 | 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
1 2 3 4 | 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
1 | SQL> create pfile= 'd:\oracle\oradata\primary\initPrimary2.ora' from spfile; |
7. Create standby control file for standby db use
1 2 3 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | *.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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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
1 | oradim -new -sid standby |
Don't use the parameter "intpwd" parameter to create the password file at the same time, that's...
1 | oradim -new -sid standby -intpwd a |
Instead, use "orapwd" to create the passord file...
1 | 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...
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
1 2 3 | 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
1 2 | SQL> startup mount; SQL> alter database recover managed standby database disconnect from session; |
Verify the Setup
1. Check standby db
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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
1 2 3 | SQL> alter system switch logfile; System altered. |
4. Check standby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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...
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 | 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...
1 2 3 4 5 6 7 8 9 10 | 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
--------------------------------------
Regards,
FangwenYu
![](https://pic.cnblogs.com/avatar/93867/20191016181427.png)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步