A standby database is a DRP (Disaster Recovery Plan) solution, and it is nothing but a copy of the primary database, which can be automatically synchronized with Dataguard. Either using Dataguard or shell scripts for automatic or manual synchronization, the primary database must operate in archive mode.
You can check this with the v$databse view:
SQL> SELECT log_mode FROM v$database;
If the result is “ARCHIVELOG” we are ok, but if the database is in “NOARCHIVELOG” you should follow these steps:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
You should also enable the force Logging option in order to assure every transaction in the database is archived.
SQL> ALTER DATABASE FORCE LOGGING;
It is important to check the DB_NAME and DB_UNIQUE_NAME parameters:
SQL> show parameter db_name
NAME TYPE VALUE ----------------------- -------- ---------------------- db_name string orcl SQL> show parameter db_unique_name NAME TYPE VALUE ----------------------- -------- ---------------------- db_unique_name string orcl
The DB_NAME will be “orcl” in both servers (primary and standby), but the DB_UNIQUE_NAME must be different. I will use “orcl_stby” for the standby database.
The following parameters must me configured in the primary database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_stby)'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_stby'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; SQL> ALTER SYSTEM SET FAL_SERVER=orcl_stby; SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Some of these parameters require the instance to be restarted.
In this case, I´m using the fast recovery area for the archives location. I will also use the same filename and path structures for the primary and standby servers.
You should adjust the *_CONVERT parameters if the filename or path structures differs.
The tnsnames.ora should be the same in both servers (primary and standby):
orcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary-server)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) orcl_stby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby-server)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )
Now we can backup the primary database to create the standby:
$ rman target=/ RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
We also need to generate the controlfile for the standby:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl_stby.ctl';
and the parameter file:
SQL> CREATE PFILE='/tmp/initorcl_stby.ora' FROM SPFILE;
remember to edit the following lines in the initorcl_stby.ora
*.db_unique_name='orcl_stby' *.fal_server='orcl' *.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
Now you should create the following directories in the standby server:
$ mkdir -p /u01/app/oracle/oradata/orcl $ mkdir -p /u01/app/oracle/fast_recovery_area/orcl $ mkdir -p /u01/app/oracle/admin/orcl/adump
and copy the files from the primary to the standby server:
$ scp /tmp/orcl_stby.ctl oracle@/u01/app/oracle/oradata/orcl/control01.ctl $ scp /tmp/orcl_stby.ctl oracle@/u01/app/oracle/fast_recovery_area/orcl/control02.ctl $ scp -r /u01/app/oracle/fast_recovery_area/orcl/archivelog oracle@/u01/app/oracle/fast_recovery_area/orcl $ scp -r /u01/app/oracle/fast_recovery_area/orcl/backupset oracle@/u01/app/oracle/fast_recovery_area/orcl $ scp /tmp/initorcl_stby.ora /tmp/initorcl_stby.ora $ scp $ORACLE_HOME/dbs/orapworcl oracle@$ORACLE_HOME/dbs
The password file must be the same in the primary and standby database.
If you change the sysdba password in the primary database, you should copy the new password file to the standby server.
Remember to start the listener on the standby server:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby-server)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle
We need to create the spifle for the standby database:
$ export ORACLE_SID=orcl $ sqlplus / as sysdba SQL> CREATE SPFILE FROM PFILE='/tmp/initorcl_stby.ora';
Now you should restore the backup on the standby server:
$ rman target=/
RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE;
and create the online redo logs as they exist in the primary database:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 100M;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
The standby redo logs must exist on both the primary and standby database in case of switchovers, this is when the standby database becomes the primary database and the primary becomes the standby.
The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs.
For example:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby_redo01.log') SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby_redo02.log') SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby_redo03.log') SIZE 100M;
To start the apply process on the standby server:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you want to check the synchronization between the primary and standby database, on the primary database:
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; SQL> SELECT sequence#, first_time, next_time FROM v$archived_logORDER BY sequence#;
now, on the standby database:
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; and check the sequence#.
Remember there are 3 protection modes:
Maximum Availability: This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database. (ref1)
Maximum Performance: This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode. (ref1)
Maximum Protection: This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down. (ref1)
To check the protection mode:
SQL> SELECT protection_mode FROM v$database; PROTECTION_MODE ------------------- MAXIMUM PERFORMANCE
To change the protection mode:
— Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_stby'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
— Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_stby'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
— Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_stby'; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; ALTER DATABASE OPEN;
SWITCHOVER:
In order to exchange the roles between the primary and standby database at runtime without loss of data:
— On the primary server
— Convert primary database to standby
CONNECT / AS SYSDBA SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
— Shutdown primary database
SQL> SHUTDOWN IMMEDIATE;
— Mount old primary database as standby database
SQL> STARTUP NOMOUNT; SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
— On the standby server
— Convert standby database to primary
CONNECT / AS SYSDBA SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
— Shutdown standby database
SQL> SHUTDOWN IMMEDIATE;
-- Open old standby database as primary SQL> STARTUP;
If you repeat these steps, you may be restoring the original roles to the databases, this is a switchback.
When the primary database is not available you need to activate the standby as the new primary database. We call this a failover:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; READ-ONLY STANDBY vs ACTIVE DATAGUARD
You can open the standby database in read-only mode:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE OPEN READ ONLY;
the archive logs keep being copied to the standby server but they are not applied. To resume managed recovery:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Active Data Guard allows the standby to be open in read-only mode without interrupting the synchronization process.
There are licensing implications for this feature.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE OPEN READ ONLY; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
References:
ref1 : https://docs.oracle.com : Data Guard Concepts and Administration, Oracle
(https://docs.oracle.com/cd/B28359_01/server.111/b28294/protection.htm#CHDEDGIF)
ref2 : https://oracle-base.com : Data Guard Physical Standby Setup in Oracle Database 11g Release 2, Tim Hall
(https://oracle-base.com/articles/11g/data-guard-setup-11gr2)
