Creation of standby database
Step 1:
Create an rman script, using vi, in the Primary DB, called /home/oracle/CreateStandbyDB.rcv and add the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET DB_UNIQUE_NAME='erpsby' SET LOG_ARCHIVE_DEST_2='SERVICE=erpprm LGWR SYNC AFFIRM REGISTER VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' Set STANDBY_FILE_MANAGEMENT='AUTO' SET FAL_SERVER='erpprm' SET FAL_CLIENT='erpsby' SET CONTROL_FILES=’/Oracle/oradata/erp/control01.ctl’,’ /Oracle/fast_recovery_area/erp/control02.ctl’ NOFILENAMECHECK; |
Step 2:
Open the Standby DB in nomount mode.
|
1 2 3 |
sqlplus / as sysdba startup nomount |
Step 3:
Make a duplicate of the standby db in the primary db using rman.
rman target / auxiliary sys/sys@erp_sby cmdfile=/home/oracle/CreateStandbyDB.rcv log=/home/oracle/Log_CreateStandbyDB.log
To view the operations as they execute: tail -f /home/oracle/Log_CreateStandbyDB.log
If an error appears, check the connection to the database using the tnsping command: tnsping erpsby (the oracle_uniqname of the standby db)
If the rman command has to be used again, this means that the standby db was created in /Oracle/11G/dbs/. It may be necessary to delete spfileerp.ora.
Step 4:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
shutdown immediate; startup alter database add standby logfile group 4 size 100m; alter database add standby logfile group 5 size 100m; alter database add standby logfile group 6 size 100m; alter database add standby logfile group 7 size 100m; alter database recover managed standby database using current logfile disconnect; |
Control
|
1 2 3 4 5 |
!ps -ef | grep mrp oracle 471268 1 0 10:51:16 - 0:02 ora_mrp0_erp oracle 475150 270568 1 11:02:47 pts/0 0:00 grep mrp |
Views to check the status
Step 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
alter database open ; select OPEN_MODE from V$DATABASE; select SWITCHOVER_STATUS,PROTECTION_MODE,DATABASE_ROLE from V$DATABASE; select STATUS, ERROR from V$ARCHIVE_DEST ; select PROTECTION_MODE from V$DATABASE; select SWITCHOVER_STATUS,OPEN_MODE from v$database ; select name, value, datum_time, time_COMPUTED from V$DATAGUARD_STATS where name like 'APPLY LAG' --the difference should be less than 30 seconds; |
Step2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select LOGGROUP.GROUP#, LOGGROUP.SEQUENCE#, LOGGROUP.ARCHIVED, LOGGROUP.STATUS, LOGDETAIL.TYPE from v$log loggroup join v$logfile logdetail on loggroup.GROUP# = logdetail.GROUP#; -- select loggroup.GROUP#, loggroup.SEQUENCE#, loggroup.archived, loggroup.status, logdetail.type from v$standby_log loggroup join v$logfile logdetail on loggroup.GROUP# = logdetail.GROUP#; |
Step 3:
In the Standby DB
|
1 |
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; |
In the Primary DB
|
1 |
ALTER SYSTEM SWITCH LOGFILE; |
Step 4:
In the Standby DB
|
1 2 3 |
select SEQUENCE#, FIRST_TIME, NEXT_TIME from V$ARCHIVED_LOG order by SEQUENCE#; select SEQUENCE#,APPLIED from V$ARCHIVED_LOG order by SEQUENCE#; |
It’s necessary to run the following command after opening the Standby DB.
|
1 |
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; |
Conclusion
This article will help you to setup the physical standby database successfully and make sure the high availability criteria of database are taken care of. We have explained the scenarios of Switchover in a subsequent article.
Useful resources for further reading:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642.pdf
http://www.troug.org/?page_id=42 (Uğur İnal)
Create Standby Database using 11g DUPLICATE FROM ACTIVE DATABASE
Note: Only Open Source software was used to write this article (free open source software)
The operating system on the Host Machine: Ubuntu 11.10 X64
Virtual Machine application: VirtualBox 4.1.2
Design Software: Dia (https://wiki.gnome.org/Apps/Dia)
Text Editor: Gedit and Open Office
Using RMAN for...
12 March 2019