Dataguard failover switchover and configuration example

Today, we demonstrated the switching process between switchover and failover on the virtual machine. The customer is concerned with failover. We directly turn off the virtual storage switch to simulate array faults. Failover steps are as follows:

1.

select NAME,RESETLOGS_TIME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,REMOTE_ARCHIVE, DATABASE_ROLE,SWITCHOVER_STATUS,FORCE_LOGGING from v$database;

2. Stop the log application

alter database recover managed standby database cancel;

3. Disable standby log Transmission

alter database recover managed standby database finish force;

4. Switch to primary

alter database commit to switchover to primary with session shutdown;

If a gap exists during this step, the ORA-16139: switchover: Media recovery required-standby not in limbo error is reported. During the test, this problem occurs if the master database is started and then the slave database is not waiting for the log transmission of the slave database to complete. Force Switch

alter database activate physical standby database;

5. Restart the database to the open state.

Certificate -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Attach the dataguard implementation process from a single machine to a single machine in Linux.

Prepare the environment

Primary: RHEL 4.7 + Oracle 10.2.0.4 single host + file system Host Name: zhbqdb1

Standby: RHEL 4.7 + Oracle 10.2.0.4 single host + file system Host Name: zhbqdb2

Create a database named zhdydb1 on the host and enable the archive mode. The backup machine is installed with data software, and the version is upgraded to the same as that of the host.

Configure/etc/hosts on the host and backup machine and create a data storage path

# Do not remove thefollowing line, or various programs
# That require Network functionality will fail.
127.0.0.1 localhost. localdomain localhost
172.17.42.29 zhbqdb1
172.17.42.30 zhbqdb2

 

 

Create a directory for storing data files and archives

 

mkdir /u01/flash_recovery_areamkdir /u01/oradatachown oracle:oinstall/u01/flash_recovery_areachown oracle:oinstall/u01/oradata

Host Configuration
Enable force Logging

SQL> alter database force logging;

 

Check whether the current database is under force logging:

Selectforce_logging from V $ database;

Create an archive directory

 

 mkdir /u01/flash_recovery_area/ZHDYDB1/arch mkdir/u01/flash_recovery_area/ZHDYDB2 mkdir/u01/flash_recovery_area/ZHDYDB2/arch mkdir/u01/flash_recovery_area/ZHDYDB2/archivelog

Configure tnsnames

On zhbqdb1

VI $ ORACLE_HOME/Network/admin/tnsnames. ora

 

Zhdydb1 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = zhbqdb1) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = zhdydb1)
)
)
 
Zhdydb2 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = zhbqdb2) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = zhdydb2)
)
)
 

Add standby redo log file

Here, there are 7 redo log groups in the primary library, corresponding to which you need to create 7 + 1 standby logfilegroup

 

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/oradata/ZHDYDB1/onlinelog/sdbylog8a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog8a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 9('/u01/oradata/ZHDYDB1/onlinelog/sdbylog9a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog9a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 10('/u01/oradata/ZHDYDB1/onlinelog/sdbylog10a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog10a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 11('/u01/oradata/ZHDYDB1/onlinelog/sdbylog11a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog11a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 12('/u01/oradata/ZHDYDB1/onlinelog/sdbylog12a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog12a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 13('/u01/oradata/ZHDYDB1/onlinelog/sdbylog13a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog13a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 14('/u01/oradata/ZHDYDB1/onlinelog/sdbylog14a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog14a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 15('/u01/oradata/ZHDYDB1/onlinelog/sdbylog15a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog15a.log') SIZE 100M;

 

View status

SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; columnmember  format a50selectgroup#,member from v$logfile order by group#;

 

Create password files and Initialization Files

Make sure that each of primary and standby has a path for RMAN backup:/bak/dgbak

 

If the password file does not exist in primary, create

orapwdfile=$ORACLE_HOME/dbs/orapwzhdydb1 password=aaa entries=5 cp$ORACLE_HOME/dbs/orapwzhdydb1 /bak/dgbakmv/bak/dgbak/orapwzhdydb1 /bak/dgbak/orapwzhdydb2

SQL> Create pfile = '/bak/dgbak/initzhdydb2.ora' from spfile;

 

Modify initialization parameters

alter system setdb_unique_name=zhdydb1 scope=spfile;alter system setLOG_ARCHIVE_CONFIG='DG_CONFIG=(zhdydb1,zhdydb2)'  scope=spfile; alter system setLOG_ARCHIVE_DEST_1='LOCATION=/u01/flash_recovery_area/ZHDYDB1/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=zhdydb1'scope=spfile; alter system setLOG_ARCHIVE_DEST_2='SERVICE=zhdydb2 LGWR SYNC AFFIRM  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=zhdydb2'  scope=spfile;alter system setLOG_ARCHIVE_DEST_STATE_1=ENABLE  scope=spfile;alter system setLOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile; alter system setLOG_ARCHIVE_MAX_PROCESSES=10 scope=spfile;alter system setlog_archive_format='%t_%s_%r.dbf' scope=spfile; alter system setSTANDBY_ARCHIVE_DEST='LOCATION=/u01/flash_recovery_area/ZHDYDB1/arch'  scope=spfile;  alter system setremote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set FAL_SERVER=zhdydb2  scope=spfile;alter system set FAL_CLIENT=zhdydb1  scope=spfile; alter system set db_file_name_convert='/u01/oradata/ZHDYDB1/','/u01/oradata/ZHDYDB2/'scope=spfile; alter system set log_file_name_convert='/u01/flash_recovery_area/ZHDYDB1/','/u01/flash_recovery_area/ZHDYDB2/'scope=spfile;  alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile;

After the instance is restarted to the Mount status

 

Alterdatabase set standby database to maximize availability; -- upgrade the protection level to maximum availability.

selectdatabase_role,protection_mode,protection_level,switchover_status fromv$database; 

Database_role protection_mode protection_level switchover_status

----------------------------------------------------------------------------

Primary maximum availability unprotected to standby

 

Restart Database

 

 

RMAN backup to/bak/dgbak and transfer relevant files to standby

rman target / <<ENDcrosscheck archivelog all;run{backup format '/bak/dgbak/db_%u_%d_%s'  database;}crosscheck archivelog all;run{ sql"alter system archive log current"; backupformat '/bak/dgbak/ar_%t_%s_%p' archivelog all;}run{backup as compressed backupsetformat='/bak/dgbak/standby.ctl'tag='standby'current controlfile for standby reuse;}crosscheck archivelog all;run{ sql"alter system archive log current"; backupformat '/bak/dgbak/ar_%t_%s_%p' archivelog all ;}exitEND

 

Bytes -----------------------------------------------------------------------------------------------

 

cd/bak/dgbakscp *zhbqdb2:/bak/dgbak scp$ORACLE_HOME/network/admin/tnsnames.ora zhbqdb2:$ORACLE_HOME/network/admin

Backup Server Configuration

Create necessary Directories

 

mkdir /u01/oradata/ZHDYDB2mkdir /u01/flash_recovery_area/ZHDYDB2mkdir /u01/oradata/ZHDYDB2/onlinelogmkdir/u01/flash_recovery_area/ZHDYDB2/onlinelog mkdir/u01/flash_recovery_area/ZHDYDB2/arch mkdir/u01/flash_recovery_area/ZHDYDB2/archivelog mkdir/u01/flash_recovery_area/ZHDYDB1 mkdir/u01/flash_recovery_area/ZHDYDB1/arch mkdir/u01/flash_recovery_area/ZHDYDB1/archivelog mkdir -p /opt/app/adminmkdir -p/opt/app/admin/zhdydb1cd/opt/app/admin/zhdydb1mkdir adump  bdump cdump  dpdump  pfile udump

Move the relevant file to the corresponding location

CD/bak/dgbak/

MV initzhdydb2.ora orapwzhdydb2 $ ORACLE_HOME/dbs/

 

Edit initialization parameters

VI $ ORACLE_HOME/dbs/initzhdydb2.ora

Remove parameters:

*. Remote_listener = 'listeners _ oradb'

*. Local_listener = 'listeners _ oradb'

 

Modify the following parameters

*. Cluster_database = false

 

Make sure that the oracle_sid in the standby environment variable is zhdydb2.

 

Sqlplus/assysdba

Startup nomount

 

create spfilefrom pfile;shutdownimmediatestartup nomount altersystem set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zhdydb1,zhdydb2)'  scope=spfile; altersystem set db_unique_name=zhdydb2 scope=spfile;altersystem setLOG_ARCHIVE_DEST_1='LOCATION=/u01/flash_recovery_area/ZHDYDB2/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=zhdydb2'scope=spfile;  altersystem setLOG_ARCHIVE_DEST_2='SERVICE=zhdydb1LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zhdydb1' scope=spfile; altersystem setSTANDBY_ARCHIVE_DEST='LOCATION=/u01/flash_recovery_area/ZHDYDB2/arch'  scope=spfile sid='*'; alter system set db_file_name_convert='/u01/oradata/ZHDYDB2/','/u01/oradata/ZHDYDB1/'scope=spfile; alter system set log_file_name_convert='/u01/flash_recovery_area/ZHDYDB2/','/u01/flash_recovery_area/ZHDYDB1/'scope=spfile; altersystem set FAL_SERVER=zhdydb1 scope=spfile;altersystem set FAL_CLIENT=zhdydb2 scope=spfile;alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile;

Shutdown immediate

Clone Database

You have copied tnsnames. ora in zhdydb1 to standby. Then, create a soft connection to the client:

ln -s$ORACLE_HOME/network/admin/tnsnames.ora $MY_CLIENT/network/admin/tnsnames.orals -l$MY_CLIENT/network/admin/

 

SQL> startup nomount;

 

rman target sys/aaa@zhdydb1 auxiliary / <<EOF CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/bak/dgbak/%F'; DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK  DORECOVER; EOF

 

Rebuilding standby's redo logfile Group

alter database drop standby logfile group 8/alter database drop standby logfile group 9/alter database drop standby logfile group 10/alter database drop standby logfile group 11/alter database drop standby logfile group 12/alter database drop standby logfile group 13/alter database drop standby logfile group 14/alter database drop standby logfile group 15/

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/oradata/ZHDYDB2/onlinelog/sdbylog8a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog8a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/oradata/ZHDYDB2/onlinelog/sdbylog9a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog9a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 10('/u01/oradata/ZHDYDB2/onlinelog/sdbylog10a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog10a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11('/u01/oradata/ZHDYDB2/onlinelog/sdbylog11a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog11a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 12('/u01/oradata/ZHDYDB2/onlinelog/sdbylog12a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog12a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 13('/u01/oradata/ZHDYDB2/onlinelog/sdbylog13a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog13a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 14('/u01/oradata/ZHDYDB2/onlinelog/sdbylog14a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog14a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 15('/u01/oradata/ZHDYDB2/onlinelog/sdbylog15a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog15a.log') SIZE 100M;

 

View status

SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; columnmember  format a50selectgroup#,member from v$logfile order by group#;

Start standby

Add a listener and register the database name zhdydb1 and Instance name zhdydb2 to the listener.

Netca

Netmgr

 

 

Start listening

Restart the standby Database

SQL> startup nomount;

SQL> alter database Mount standby database;

ALTERDATABASE RECOVER MANAGED STANDBY DATABASE [ using current logfile ] DISCONNECT FROMSESSION;

 

Disable standby

Alterdatabase recover managed standby database cancel;

Shutdownimmediate;

 

LSNRCTL stop

 

Check the synchronization of DG

Run the following SQL statement in primary and standby respectively.

SELECT THREAD#, MAX(SEQUENCE#) AS"LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

Query all archives received from primary (standby side)
SQL> select Registrar, creator, thread #, sequence #, first_change #, next_change # from V $ archived_log;

 

View alert logs

Tail-F $ oracle_base/admin/zhdydb1/bdump/alert_zhdydb1.log

Tail-F $ oracle_base/admin/zhdydb1/bdump/alert_zhdydb2.log

 

Gap Processing

1) check whether there is a log gap:

SQL> select unique thread #, max (sequence #) over (partition by thread #) last from V $ archived_log;

SQL> selectthread #, low_sequence #, high_sequence # from V $ archive_gap;

2) If yes, copy it.

3) manually register these logs:

SQL> alterdatabase register logfile 'path ';

Switchover master database cut into slave database:

1. query the current status:

SQL> selectswitchover_status from V $ database;

If the status is sessions active, the session connected to the master database is closed. Or add the with sessionshutdown clause to disconnect these sessions.

 

2. Switch to physical standby

SQL> alterdatabase commit to switchover to physical standby with Session shutdown;

 

3. Shut down the database

SQL> shutdownimmediate

 

4. Start the database

SQL> startupnomount;

SQL> alterdatabase Mount standby database;

SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;

 

Switch slave database to master database

1. After completing the above steps, switch the original slave database to the master database.

SQL> alterdatabase commit to switchover to primary;

 

2. Start the database to the open state.

SQL> alterdatabase open;

Failover

When the primary database encounters a fault, we need to implement a Failover (Failover) to switch the slave database to the top of the master database for application.

Follow these steps ):

 

1. Stop the apply process.

SQL> alter database recover managed standby database finish force;

 

2. Switch to the master database

SQL> alter database commit to switchover to primary;

 

3. Start to open

SQL> alter database open;