- 2 NODE ORACLE 11GR2 RAC CLUSTER INSTALLED ON ORACLE LINUX 5.6
- PREPARATION OF 2 NODE ORACLE 11GR2 STANDBY CLUSTER DATABASE
******************************************************************************************
PRIMARY SITE DETAILS******************************************************************************************
/etc/hosts file content
########Public ##############
132.35.21.177 rac1.localdomain rac1
132.35.21.178 rac2.localdomain rac2
132.35.21.137 dr-rac1.localdomain dr-rac1
132.35.21.138 dr-rac2.localdomain dr-rac2
########Private ##############
10.10.10.11 rac1-priv.localdomain rac1-priv
10.10.10.12 rac2-priv.localdomain rac2-priv
10.10.10.5 dr-rac1-priv.localdomain dr-rac1-priv
10.10.10.6 dr-rac2-priv.localdomain dr-rac2-priv
########Virtual ##############
132.35.21.187 rac1-vip.localdomain rac1-vip
132.35.21.188 rac2-vip.localdomain rac2-vip
132.35.21.147 dr-rac1-vip.localdomain dr-rac1-vip
132.35.21.148 dr-rac2-vip.localdomain dr-rac2-vip
########SCAN ##############
132.35.21.198 racscan.localdomain racscan
132.35.21.199 dr-racscan.localdomain dr-racscan
---------------------------------------------------------------------------------------------------------
DB_NAME=PROD
DB_UNIQUE_NAME=PROD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
----------------------------------------------------------------------------------------------------------
[oracle@rac1 ~]$ srvctl config database -d prod
Database unique name: prod
Database name: prod
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: prod
Database instances: prod1,prod2
Disk Groups: DATA,FLASH
Services:
Database is administrator managed
[oracle@rac1 ~]$
****************************************************************************************
SETTING UP PRIMARY SITE
****************************************************************************************
Check Archive log mode on primary database
export ORACLE_SID=prod1
sqlplus / as sysdba
SQL> select log_mode from v$database;
LOG_MODE
------------
ACHIVELOG
if database is not running in archivelog mode you must shutdown and mount and change it to archivelog mode
-----------------------------------------------------------------------------------------------------------------------
Enable force logging
SQL> select force_logging from v$database;
FORCE_LOGGING
NO
SQL> alter database force logging;
-----------------------------------------------------------------------------------------------------------------------
Check the space usage of datafiles to get the space to accomadate the backup
SQL> Select sum(bytes/1024/1024/1024) "Size in GB" from dba_segments;
-----------------------------------------------------------------------------------------------------------------------
Check the ASM Usage:
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/11.2.0/grid
sqlplus / as sysasm
SQL> select NAME,TOTAL_MB/1024 "Total GB",(TOTAL_MB/1024-FREE_MB/1024) "Used Gb",FREE_MB/1024 "Free GB" from v$asm_diskgroup;
-----------------------------------------------------------------------------------------------------------------------
Check the logfiles details
SQL> SELECT * FROM gv$logfile ORDER BY GROUP#;
-----------------------------------------------------------------------------------------------------------------------
NOTE: we are working with 2 node rac and each thread we have 2 group so we have 4 groups in total . We are going to add 3 standby logfile groups for each thread (TOTAL 6).
Before adding the standby logfile we need to change the standby file management to manual. After standby log files are created we will again change it to auto
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SCOPE=BOTH SID='*';
-----------------------------------------------------------------------------------------------------------------------
Add standby redo redo logs
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+DATA','+DATA') SIZE 250M,GROUP 6 ('+DATA','+DATA') SIZE 250M,GROUP 7 ('+DATA','+DATA') SIZE 250M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 ('+DATA','+DATA') SIZE 250M,GROUP 9('+DATA','+DATA') SIZE 250M, GROUP 10 ('+DATA','+DATA') SIZE 250M;
-----------------------------------------------------------------------------------------------------------------------
Change standby file management setting to auto
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
-----------------------------------------------------------------------------------------------------------------------
Check the log files details.
SQL> SELECT * FROM gv$logfile ORDER BY GROUP#;
-----------------------------------------------------------------------------------------------------------------------
Parameter Modification at the Primary Level:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STAN)' SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN' scope=both sid='*';
STAN is TNS enty name which will be used to connect to sandby database
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='PROD_%t_%s_%r.ARC' SCOPE=SPFILE SID='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE SID='*';
The below parameter is necessary at the time of swithover
SQL> ALTER SYSTEM SET FAL_SERVER='STAN' SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET FAL_CLIENT='PROD' SCOPE=BOTH SID='*';
To Stop the Archive log shipping
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER' SID='*';
(When Dr Setup Will Complete We Will Enable this parameter)
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA/STAN','+DATA/PROD' SCOPE=SPFILE SID='*';
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA/STAN','+DATA/PROD','+FLASH/STAN','+FLASH/PROD' SCOPE=SPFILE SID='*';
-----------------------------------------------------------------------------------------------------------------------
Create the Stage Area:
mkdir -p /home/oracle/backup
-----------------------------------------------------------------------------------------------------------------------
Create pfile for standby from spfile of PRIMARY database:
SQL> create pfile='/home/oracle/backup/pfile.ora ' from spfile;
-----------------------------------------------------------------------------------------------------------------------
Take Rman Backup
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup current controlfile for standby format '/home/oracle/backup/Primary_bkp_for_stndby_%U';
BACKUP AS COMPRESSED BACKUPSET DATABASE format '/home/oracle/backup/%d_%U.bckp' PLUS ARCHIVELOG format '/home/oracle/backup/%d_%U.bckp';
backup current controlfile for standby format '/home/oracle/backup/TEST_STBY_AFT_%U';
release channel c1;
release channel c2;
}
-----------------------------------------------------------------------------------------------------------------------
Move backup from Primary to Standby:
scp -r /home/oracle/backup dr-rac1:/home/oracle/backup
scp /home/oracle/backup/pfile.ora dr-rac1:/home/oracle/backup/pfile.ora
-----------------------------------------------------------------------------------------------------------------------
TNS file of Primary servers
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racscan.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
prod1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
prod2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
*****************************************************************************************************
Configure the password file and copy to all prodary and standby node
scp $ORACLE_HOME/dbs/orapwprod1 dr-rac1:$ORACLE_HOME/dbs/orapwSTAN1
scp $ORACLE_HOME/dbs/orapwprod2 dr-rac2:$ORACLE_HOME/dbs/orapwSTAN2
*****************************************************************************************************
STANDBY SITE PREPARATION
*****************************************************************************************************
Standby pfile file creation
remote_listener='dr-racscan.localdomain:1521'
audit_file_dest='/u01/app/oracle/admin/STAN/adump'
audit_trail='db'
cluster_database=false
compatible='11.2.0.0.0'
control_files='+DATA/STAN/CONTROLFILE/current.346.798618115','+FLASH/STAN/CONTROLFILE/current.3587.798618115' #Restore Controlfile
db_block_size=8192
db_create_file_dest='+DATA'
db_file_name_convert='+DATA/PROD','+DATA/STAN'
db_name='PROD'
db_recovery_file_dest='+FLASH'
db_recovery_file_dest_size=26214400000
db_unique_name='STAN'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
fal_client='STAN'
fal_server='PROD'
stan2.instance_number=2
stan1.instance_number=1
log_archive_config='DG_CONFIG=(PROD,STAN)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
log_archive_dest_2='SERVICE=PROD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='defer'
log_archive_format='STAN_%t_%s_%r.arc'
log_file_name_convert='+DATA/PROD','+DATA/STAN','+FLASH/PROD','+FLASH/STAN'
open_cursors=300
pga_aggregate_target=536870912
processes=515
remote_listener='dr-racscan.localdomain:1521'
remote_login_passwordfile='exclusive'
sessions=800
sga_target=1610612736
stan12.thread=2
stan1.thread=1
stan1.undo_tablespace='UNDOTBS1'
stan2.undo_tablespace='UNDOTBS2'
standby_file_management='AUTO'
service_names='stan.localdomain'
-----------------------------------------------------------------------------------------------------------------------
Create Audit Directory on both standby nodes
[oracle@dr-rac1 ~]$ mkdir -p /u01/app/oracle/admin/PROD/adump
[oracle@dr-rac2 ~]$ mkdir -p /u01/app/oracle/admin/PROD/adump
-----------------------------------------------------------------------------------------------------------------------
create directories on ASM
[oracle@dr-rac1 ~]$ asmcmd -p
ASMCMD> cd DATA
ASMCMD [+DATA] mkdir STAN
ASMCMD [+DATA] cd STAN
ASMCMD [+DATA/STAN] mkdir CONTROLFILE DATAFILE ONLINELOG
ASMCMD [+DATA/STAN] > cd +FLASH
ASMCMD [+FLASH] > mkdir STAN
ASMCMD [+FLASH] > cd STAN
ASMCMD [+FLASH/STAN] > mkdir ARCHIVELOG CONTROLFILE ONLINELOG
-----------------------------------------------------------------------------------------------------------------------
Update tns entries on both primary and standby database nodes (TNS on both nodes should have entries of cluster nodes of each cluster)
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racscan.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
prod1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
prod2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.localdomain)
)
)
STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dr-rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stan.localdomain)
)
)
STAN2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dr-rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stan.localdomain)
)
)
STANSCAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dr-racscan.localdomai)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stan.localdomain)
)
)
-----------------------------------------------------------------------------------------------------------------------
Note Currently standby database is running as single instance so STAN entry is pointing to that specific node (dr-rac1-vip.localdomain) in this case. Remember you should name this entry as STAN so log shipping can be done using this entry. after conversion to cluster we will GIVE this name (STAN) to dr-racscan.
-----------------------------------------------------------------------------------------------------------------------
Set the environment on standby node (dr-rac1):
[oracle@dr-rac2 ~]$ export ORACLE_SID=stan1
[oracle@dr-rac2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@dr-rac2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
-----------------------------------------------------------------------------------------------------------------------
Startup the instance in nomount state using parameter file we already created
[oracle@dr-rac2 ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/backup/pfile.ora'
-----------------------------------------------------------------------------------------------------------------------
Create standby database using rman
[oracle@dr-rac2 ~]$ rman target sys/oracle@prod auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jan 7 12:14:18 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=247554883)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database for standby;
-----------------------------------------------------------------------------------------------------------------------
Enable parameter LOG_ARCHIVE_DEST_STATE_2 on primary database to enable log shipping
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE' SID='*';
-----------------------------------------------------------------------------------------------------------------------
Enable recovery on the standby site
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
-----------------------------------------------------------------------------------------------------------------------
Check archive log gap:
SQL> SELECT * FROM V$ARCHIVE_GAP;
If there is no gap we can open the database in read only mode.
-----------------------------------------------------------------------------------------------------------------------
Cancel recovery process and open database as read only
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
-----------------------------------------------------------------------------------------------------------------------
Enable recovery again
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> SELECT NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
PROD STAN PHYSICAL STANDBY READ ONLY WITH APPLY
-----------------------------------------------------------------------------------------------------------------------
Convert Single instance to RAC
Create directory on asm for parameterfile
[oracle@dr-rac1 ~]$ asmcmd -p
ASMCMD [+] > cd DATA/STAN
ASMCMD [+DATA/STAN] > mkdir PARAMETERFILE
-----------------------------------------------------------------------------------------------------------------------
Create spfile on shared location
SQL> create spfile='+DATA' from file='/home/oracle/backup/pfile.ora';
-----------------------------------------------------------------------------------------------------------------------
Shutdown instance and startup nomount with spfile
SQL> shutdown immediate;
SQL> startup nomount;
-----------------------------------------------------------------------------------------------------------------------
Enable the RAC Parameter:
SQL> alter system set cluster_database=TRUE scope=spfile sid='*';
System altered.
-----------------------------------------------------------------------------------------------------------------------
Note: Change names of Controlfiles (because of OMF controlfile names are auto generated by OMF during standby db creation so we need to change them accordingly check names of controlfiles on asm and update parameter)
SQL> alter system set control_files='+data/stan/controlfile/current.262.836308255','+flash/stan/controlfile/current.268.836308257' scope=spfile sid='*';
SQL> Shutdown instance:
SQL> shutdown immediate;
-----------------------------------------------------------------------------------------------------------------------
Add this database to OCR
[oracle@dr-rac1 ~]$ srvctl add database -d STAN -n PROD -o /u01/app/oracle/product/11.2.0/dbhome_1 -m localdomain -p +DATA/STAN/PARAMETERFILE/spfile.259.836320345 -r physical_standby -a DATA,FLASH
-----------------------------------------------------------------------------------------------------------------------
Add the Instances to OCR
[oracle@dr-rac1 ~]$ srvctl add instance -d STAN -i stan1 -n dr-rac1
[oracle@dr-rac1 ~]$ srvctl add instance -d STAN -i stan2 -n dr-rac2
-----------------------------------------------------------------------------------------------------------------------
shutdown the database:
SQLPLUS> shutdown immediate;
-----------------------------------------------------------------------------------------------------------------------
Startup the cluster standby database as read only
[oracle@dr-rac1 ~]$ srvctl start database -d STAN
-----------------------------------------------------------------------------------------------------------------------
Check the final status of the database with the cluster
set the asm env
[oracle@dr-rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@dr-rac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@dr-rac1 ~]$ export ORACLE_SID=+ASM1
su - oracle
[oracle@dr-rac1 ~]$ srvctl status database -d STAN
Instance stan1 is running on node dr-rac1
Instance stan2 is running on node dr-rac2
-----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.stan.db
1 ONLINE ONLINE dr-rac1 Open,Readonly
2 ONLINE ONLINE dr-rac2 Open,Readonly
[oracle@dr-rac1 ~]$
[oracle@dr-rac1 ~]$ srvctl config database -d stan
Database unique name: STAN
Database name: PROD
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STAN/PARAMETERFILE/spfile.259.836320345
Domain: localdomain
Start options: open
Stop options: immediate
Database role: physical_standby
Management policy: AUTOMATIC
Server pools: STAN
Database instances: stan1,stan2
Disk Groups: DATA,FLASH
Services:
Database is administrator managed
Now DR is fully operation
******************************************************************************* STANBY MONITORING AND MANAGEMENT ******************************************************************************* SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; ======================================================================= SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE PROD READ WRITE PRIMARY -------------------------------------------------------- SQL> select name,open_mode,database_role from v$database NAME OPEN_MODE DATABASE_ROLE PROD MOUNTED PHYSICAL STANDBY ---------------------------------------------------------- SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_STANDBY --------------------------------------------------- SQL> alter database recover managed STANDBY database disconnect from session; -------------------------------------------------- 1) Make sure our destinations are all valid on both the primary and the STANDBY: SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2 DEST_ID STATUS DESTINATION ERROR 1 VALID USE_DB_RECOVERY_FILE_DEST 2 VALID STAN -------------------------------------------------- SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2 DEST_ID STATUS DESTINATION ERROR 1 VALID USE_DB_RECOVERY_FILE_DEST 2 VALID PROD ------------------------------------------------- 2) see if the redo is actually being applied, From the primary run: SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = 'STAN' order by FIRST_TIME ---------------------------------------------------------------- 3) If you notice that logs aren’t applying, it is possible you might have a gap in your redo, in which case the STANDBY cannot apply. If you have the FAL_SERVER parameter set correctly, this shouldn’t be a problem. You can check to see if there are any gaps in the redo, by running the following query on the primary: SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2 STATUS GAP_STATUS VALID NO GAP ------------------------------------------------------------- 4) The V$DATAGUARD_STATUS view is very useful for looking for errors or just seeing what has happened. You can query it on the primary and STANDBY to see status for that database. SQL> select * from V$DATAGUARD_STATUS order by TIMESTAMP ------------------------------------------------------------------- 5) Sometimes you want to really know the data is there. A more reassuring way to verify is to actually check the STANDBY and verify that the new data is there. You can do this by changing the STANDBY’s role to readonly. First you’ll need to stop managed recovery and then open databaase: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN READ ONLY; ------------------------------------------------------------------- You can now run your query to see that the changes have come across. When you’re done, do not forget to take your database back to MOUNT and restart recovery. 6) If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the STANDBY server using the following commands. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION; Database altered. To stop recovery process SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ------------------------------------------------------------ To start immediate redo apply and open database as read only First stop recovery SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; open database as read only SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SQL> SELECT NAME, OPEN_MODE,DATABASE_ROLE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROD READ ONLY WITH APPLY PHYSICAL STANDBY ---------------------------------------------------------- 5. DATABASE SWITCHOVER A database can be in one of two mutually exclusive modes (primary or STANDBY). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements. -- Convert primary database to STANDBY CONNECT / AS SYSDBA SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; NOTE: ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected If you get this error you have connected sessions that need to be shutdown. Re-issue the command with the WITH SESSION SHUTDOWN clause. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; -- 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; ================================================ Failover In case of crash of primary database login on STANDBY database and use these command to make it primary ================================================= SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. SQL> select name,open_mode,database_role,db_unique_name from v$database; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ PROD MOUNTED PRIMARY PROD alter database open; Database altered. SQL> select name,open_mode,database_role,db_unique_name from v$database; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ PROD READ WRITE PRIMARY PROD ------------------------------------------------------------------------------ 6. RECOVER CRASHED / DESTROYED PRIMARY DATABASE USING FLASHBACK Flashback on Primary database must be enabled if you want to save database primary database and recover in case of any crash SQL> ALTER DATABASE FLASHBACK ON; Database altered. ------------------------------------------------------------------------------------ Now crash primary database SQL> SHUTDOWN ABORT; ORACLE instance shut down. ------------------------------------------------------------------------------------- Login to STANDBY database Convert STAN database to Primary Finish Log Apply process using this command SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. Activate STANDBY database (STAN) as primary SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. Check database status SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ PROD MOUNTED PRIMARY PROD As above query shows in result that STAN is converted to primary database so now we will open it for read/write operations. SQL> ALTER DATABASE OPEN; Database altered. Check database status SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ PROD READ WRITE PRIMARY PROD ----------------------------------------------------------------------------- FLASHBACK (PROD) TO A BEFORE CRASH RESTORE POINT Flashback crashed/destroyed Primary database and open it as STANDBY (Because we already converted STANDBY (STAN) to primary so now we will recover old primary (PROD) as STANDBY on new primary (STAN) check scn when STAN database was coverted into primary SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) 1393488 ---------------------------------------------------------------------- Now mount old primary (PROD) and flashback it to scn 1393488 SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2259312 bytes Variable Size 268437136 bytes Database Buffers 507510784 bytes Redo Buffers 6791168 bytes Database mounted. SQL> FLASHBACK DATABASE TO SCN 1393488; Flashback complete. Convert (PROD) to STANDBY SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,FLASHBACK_ON FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ FLASHBACK_ON ------------------ PROD MOUNTED PHYSICAL STANDBY PROD YES --------------------------------------------------------------- 7. CHANGING PROTECTION MODES SQL> SELECT PROTECTION_MODE FROM V$DATABASE; PROTECTION_MODE MAXIMUM PERFORMANCE The mode can be switched using the following commands. Note the alterations in the redo transport attributes. -- Maximum Availability. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; -- Maximum Performance. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; -- Maximum Protection. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; SQL> ALTER DATABASE OPEN;
No comments:
Post a Comment