This article describes the setup of the DataGuard and Observer, in Maxavailability mode and with Fail Over about to be activated.
If there’s going to be a Standby setup in a operational Data Guard environment then the standby logs need to be checked
For large databases it is recommended starting up before opening the backup settings.
Adding the following aliases to /home/oracle/.bash_profile makes the installation process easier.
|
1 2 3 4 5 6 7 8 9 10 11 |
alias oh='cd /u01/app/oracle/product/10g' alias admin='cd /u01/app/oracle/admin/' alias dbs='cd /u01/app/oracle/product/10g/dbs' alias sql='sqlplus "/ as sysdba"' alias network='cd /u01/app/oracle/product/10g/network/admin' alias alert='tail -200f /u01/app/oracle/admin/FSM/bdump/alert_FSM.log ' |
Step 1.
The Primary DB should be in Archive Log mode.
|
1 2 3 4 5 6 7 8 9 |
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM' SCOPE=spfile; alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile; shutdown immediate startup mount alter database archivelog |
Step 2.
Primary DB should have Flashback active.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
shutdown immediate; startup mount; alter database flashback on; alter database open; SELECT flashback_on, log_mode FROM v$database; SELECT flashback_on, log_mode FROM v$database; FLASHBACK_ON LOG_MODE ------------------ --------------------- YES ARCHIVELOG |
Step 3.
|
1 2 3 4 5 6 7 8 9 10 11 |
Force Logging should be active. ALTER DATABASE FORCE LOGGING; SELECT force_logging FROM v$database; FOR --- YES |
Step 4.
Standby DB.
Load only the RDBMS and patches. The standby db has the same rights and folder ownership as the Primary, however it hasn’t been created yet.
Primary
Oracle_Unique_Name FSM_DG1
Standby
Oracle_Unique_Name FSM_DG2
Step 5.
Create the Standby Redologs in the Primary. (Whatever has been added to the Primary should also be added to the 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 32 33 34 35 |
Drop StandBy Log ALTER DATABASE DROP STANDBY LOGFILE GROUP 5; ALTER DATABASE DROP STANDBY LOGFILE GROUP 6; ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; ALTER DATABASE DROP STANDBY LOGFILE GROUP 8; ALTER DATABASE DROP STANDBY LOGFILE GROUP 9; --- adding a single standby log ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log') SIZE 100M reuse; --- adding a multi-user standby log ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log','/u02/app/oracle/oradata/FSM/stdby_log01_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log','/u02/app/oracle/oradata/FSM/stdby_log02_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log','/u02/app/oracle/oradata/FSM/stdby_log03_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log','/u02/app/oracle/oradata/FSM/stdby_log04_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log','/u02/app/oracle/oradata/FSM/stdby_log05_2.log') SIZE 100M reuse; |
Step 6.
The following changes need to be made in the Primary DB’s pfile. These changes should then be copied to a spfile and the database opened with the spfile.
|
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 |
SALTER SYSTEM SET DB_UNIQUE_NAME='FSM_DG1' SCOPE=SPFILE; + ALTER SYSTEM SET FAL_CLIENT='FSM_DG1' SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER='FSM_DG2' SCOPE=SPFILE; ALTER SYSTEM SET Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' SCOPE=SPFILE; Alter system set Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG1' Alter system set Log_archive_dest_2='Service=FSM_DG2 lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG2' Alter system set Standby_File_Management='AUTO' scope=spfile; Alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile; shutdown immediate startup |
The same, but creating a new pfile instead.
|
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 |
*.db_name='FSM' *.db_unique_name='FSM_DG1' *.FAL_Client='FSM_DG1' ----->Primary DB unique name *.FAL_Server='FSM_DG2' ----->StandBY DB unique name *.Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' -----> (Primary DB unique name,StandBY DB unique name) *.Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG1' -----> (Primary DB unique name,StandBY DB unique name) *.Log_archive_dest_2='Service=FSM_DG2 lgwr async ----->(StandBY DB unique name) VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG2' ----->(StandBY DB unique name) *.Log_archive_dest_state_1='ENABLE' *.Log_archive_dest_state_2='ENABLE' *.Standby_File_Management='AUTO' *.LOG_ARCHIVE_MAX_PROCESSES=30 |
Step 7.
Make the necessary folders in the Standby DB. Any missing folders would result in this error message “ORA-07446: sdnfy: bad value ” for parameter”.
|
1 2 3 4 5 6 7 8 9 |
mkdir -p /u01/app/oracle/admin/FSM/adump mkdir -p /u01/app/oracle/admin/FSM/bdump mkdir -p /u01/app/oracle/admin/FSM/cdump mkdir -p /u01/app/oracle/admin/FSM/udump mkdir -p /u01/app/oracle/archive/FSM |
Step 8.
Creating the following pfile for the StandBy. Make it in the Primary and send it to $ORACLE_HOME/dbs in the Standby.
|
1 2 3 |
create pfile from spfile ; scp initFSM.ora sdb:/u01/app/oracle/product/10g/dbs/ |
Step 9.
Make the following changes in initFSM.ora in the Standby server.
|
1 2 3 4 5 6 7 |
*.db_name='FSM' *.db_unique_name='FSM_DG2' *.FAL_Client='FSM_DG2' ----->StandBY DB unique name *.FAL_Server='FSM_DG1' ----->Primary DB unique name |
|
1 2 3 4 5 6 7 |
*.Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' -----> (Primary DB unique name,StandBY DB unique name) *.Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG2' -----> (Primary DB unique name,StandBY DB unique name) |
|
1 2 3 4 5 |
*.Log_archive_dest_2='Service=FSM_DG1 lgwr async ----->StandBY DB unique name VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG1' ----->StandBY DB unique name |
|
1 2 3 4 5 6 7 |
*.Log_archive_dest_state_1='ENABLE' *.Log_archive_dest_state_2='ENABLE' *.Standby_File_Management='AUTO' *.LOG_ARCHIVE_MAX_PROCESSES=30 |
Step 10.
Opening the Standby DB in nomount mode.
|
1 2 3 4 5 |
$sqlplus / as sysdba create spfile from pfile; startup nomount |
Step 11.
Make a password file in the standby (the sys password should be the same as in the Primary).
|
1 |
/u01/app/oracle/product/10g/bin/orapwd file=$ORACLE_HOME/dbs/orapwFSM password=sys |
Step 12.
Making a backup of the Primary using RMAN. The RMAN backup should be sent to the Standby (in the same location as it is in the Primary).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$rman target / run { BACKUP CURRENT CONTROLFILE FOR STANDBY format '/u02/backup/standbyctl' ; ALLOCATE CHANNEL C1 TYPE DISK MAXPIECESIZE 15G; BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u02/BACKUP/DBF_%D_%U_%S_%P.RMAN' TAG='RMAN_FULLYEDEK_FOR_STANDBY'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL C1; } |
Alternatively
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
run { BACKUP CURRENT CONTROLFILE FOR STANDBY format '/u02/backup/standbyctl' ; ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 15G; BACKUP DATABASE FORMAT '/u02/BACKUP/DBF_%U_%D_%S_%P'TAG='RMAN_FULLYEDEK_FOR_STANDBY';; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL C1; } |
The backup should be copied to the /u02/backup path in the Standby. If copying the backup is going to take a long time, start the NFS (Network File System) service and then mount, this will save the trouble of copying.
|
1 |
mount -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp PrimaryDB_IP:/u02/backup /u02/backup |
Step 13.
The RMAN backup should be restored in the standby. To do this, in the Primary DB :
|
1 2 3 |
rman target / auxiliary sys/sys@FSM_DG2 DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; |
tail -f outputs the alertlog dump.
Step 14.
Creating the redo logs in the Standby (be careful with the file sizes).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select bytes from v$log; select bytes/1048576 as MB from v$log; select group#, member from v$logfile ; col member format a70 set lines 132 set trims on select group#,type,member from v$logfile order by type; |
Drop StandBy Log
|
1 2 3 4 5 6 7 8 9 |
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5; ALTER DATABASE DROP STANDBY LOGFILE GROUP 6; ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; ALTER DATABASE DROP STANDBY LOGFILE GROUP 8; ALTER DATABASE DROP STANDBY LOGFILE GROUP 9; |
— adding a single standby log
|
1 2 3 4 5 6 7 8 9 |
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log') SIZE 100M reuse; |
— adding a multi-user standby log
|
1 2 3 4 5 6 7 8 9 |
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log','/u02/app/oracle/oradata/FSM/stdby_log01_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log','/u02/app/oracle/oradata/FSM/stdby_log02_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log','/u02/app/oracle/oradata/FSM/stdby_log03_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log','/u02/app/oracle/oradata/FSM/stdby_log04_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log','/u02/app/oracle/oradata/FSM/stdby_log05_2.log') SIZE 100M reuse; |
Step 15.
To apply the redos, created in the Primary, to the Standby:
|
1 |
alter database recover managed standby database disconnect from session; |
To check, in the Primary:
|
1 |
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#; |
In the Standby:
|
1 |
select thread#,max(sequence#) from v$archived_log where applied ='YES' group by thread#; |
REDO APPLY
|
1 |
alter database recover managed standby database disconnect; |
REDO APPLY USING REAL-TIME APPLY
|
1 |
alter database recover managed standby database using current logfile disconnect; |
This can done manually without making an RMAN duplicate. Put the Primary DB into Mount mode, create a control file for the Standby and copy to the original paths using the same names.
The same procedure should be applied for both the datafiles and log files.
|
1 |
SELECT SWITCHOVER_STATUS,PROTECTION_MODE,DATABASE_ROLE from v$database; |
PRIMARY
|
1 |
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; |
STANDBY
|
1 |
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; |
|
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 32 33 |
column "First Time" format A40column applied format A10 column "Next Time" format A40 set linesize 120 set pagesize 1000 select thread#, sequence#, applied, to_char(first_time,'DD-MON-YY:HH24:MI:SS') "First Time", to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Next Time" from v$archived_log UNION select NULL,NULL,' ',NULL,null FROM DUAL UNION select null,null, db_unique_name, database_role, open_mode from v$database / |
Checking the Standby’s Archive destination from the Primary DB
|
1 2 3 |
Select Status, Errorfrom v$Archive_dest where dest_id=2; |
The Redo Switch frequency
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
COL DAY FORMAT a15;COL HOUR FORMAT a4; COL TOTAL FORMAT 999; SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(FIRST_TIME,'HH24') HOUR, COUNT(*) TOTAL FROM V$LOG_HISTORY GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24') ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24') ASC; |
If there are any idle archivelogs
|
1 |
select * from v$archive_gap; |
|
1 2 3 4 5 6 7 |
SELECT nameFROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN Low_Sequence and High_Sequence; |
Copy missing archivelogs from the Primary to the Standby
|
1 2 3 |
ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38792_5711453.log';ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38793_5711453.log'; ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38794_5711453.log'; |
Flashback needs to be active for MAXAVAILABILITY.
|
1 2 3 |
SELECT flashback_on, log_mode FROM v$database;whilst in mount mode alter database flashback on; |
Both in the Primary and the Standby run
|
1 |
Alter system set dg_broker_start=True scope=both; |
Check that machine is running the observer process
|
1 |
select FS_FAILOVER_OBSERVER_HOST from v$database; |
|
1 |
Check that machine is running the observer process |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
show configuration verboseSHOW DATABASE 'FSM_DG2'; SHOW DATABASE 'FSM_DG1'; SHOW DATABASE VERBOSE 'FSM_DG1'; SHOW DATABASE VERBOSE 'FSM_DG2'; SHOW INSTANCE FSM_DG1; SHOW INSTANCE VERBOSE FSM_DG1; SHOW INSTANCE FSM_DG1 'TopWaitEvents'; |
Primary
db_name————->FSM
db_unique_name——>FSM_DG1
StandBy
db_name————->FSM
db_unique_name——>FSM_DG2
log
tail -300f /u01/app/oracle/admin/FSM/bdump/drcFSM.log |more
Connecting to the Primary
|
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 |
connect sys/sys@FSM_DG1> show configuration verbose CREATE CONFIGURATION FSM_BROKER as PRIMARY DATABASE IS "FSM_DG1" CONNECT IDENTIFIER IS "FSM_DG1"; ADD DATABASE "FSM_DG2" AS CONNECT IDENTIFIER IS "FSM_DG2" MAINTAINED AS PHYSICAL ; show configuration EDIT DATABASE "FSM_DG1" SET PROPERTY 'LogXptMode'='SYNC'; EDIT DATABASE "FSM_DG2" SET PROPERTY 'LogXptMode'='SYNC'; set property FastStartFailoverThreshold = 15; EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; Succeeded. EDIT DATABASE 'FSM_DG1' SET PROPERTY FastStartFailoverTarget='FSM_DG2'; Property "faststartfailovertarget" updated EDIT DATABASE 'FSM_DG2' SET PROPERTY FastStartFailoverTarget='FSM_DG1'; |
NOTE: Enabling configuration shuts down and restarts the database!!!!
There should be a system alert when enabling configuration, however the DBMS doesnt give one.
|
1 2 3 4 5 6 7 |
********************SHUTSDOWN THE DATABASE !!!!!!********************;SHUTSDOWN THE DATABASE !!!!!! enable configuration; ********************SHUTSDOWN THE DATABASE !!!!!! ********************SHUTSDOWN THE DATABASE !!!!!! |
|
1 |
ENABLE FAST_START FAILOVER; |
In both databases
|
1 2 |
sqlplus sys/sys@FSM_DG1; sqlplus sys/sys@FSM_DG2 |
This should allow connection to both the databases, however in a failover it wouldn’t allow opening the database.
Alternatives:
In Standby
|
1 2 3 4 5 |
Alter System set Log_archive_dest_2='Service=FSMLGWR AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG1'; |
Putting the Primary in mount mode
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
|
1 2 3 4 5 |
To disable disable fast_start failover; disable fast_start failover force |
|
1 |
switchover to 'FSM_DG1' ; |
(Writes to the current Standby DB)
(Switches roles, primary to standby and standby to primary)
REINSTATE DATABASE ‘FSM_DG2’;
standby archive location
|
1 2 3 |
how database 'FSM_DG1' standbyarchivelocation ;show database 'FSM_DG2' standbyarchivelocation ; edit database FSM_DG2 set property StandbyArchiveLocation = '/u01/app/oracle/archive/FSM'; |
Closing and restarting the databases in the Data Guard environment.
Steps to close the databases
|
1 2 |
connect sys/sys@FSM_DG1; stop observer |
Primary
|
1 2 3 4 |
alter system set log_archive_dest_state_2=defer; alter system switch logfile ; shutdown immediate |
Standby
|
1 2 |
alter database recover managed standby database cancel; shutdown immediate; |
Steps to restart
Standby
|
1 2 3 4 |
startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session; |
Primary
|
1 2 3 4 |
startup; alter system set log_archive_dest_state_2=enable; alter system switch logfile; |
Using RMAN for...
12 March 2019