Setting up Data Guard 11gR2 (RAC to RAC)

Purpose: 
Setting the Data Guard for RAC primary to RAC Standby.

Assumptions:
1- Primary site has 2 Node RAC 11gR2 installation on Oracle Linux 5.6. Primary database name is PROD
2- Standby site has Oracle GI 11gR2 with RDBMS software only installation on Linux 5.6 on 2 node Linux 5.6. Standby database name will be STAN.

******************************************************************************************

PRIMARY SITE (PROD) DETAILS 
******************************************************************************************


/etc/hosts file 

########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---------------------------------------------------------------------------------------------------------
--Below are few required parameters to know for Primary site (PROD)
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 ~]$
-- 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)
    )
  )

****************************************************************************************
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

-- Enable force logging
SQL> alter database force logging;
SQL> select force_logging from v$database;

-- Check the space usage of datafiles to get the space to accommodate the backup 
SQL> Select sum(bytes/1024/1024/1024) "GB" from dba_segments;
        GB
----------
 1.3404541

-- Check the ASM Usage:
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ 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;

NAME                             Total GB    Used Gb    Free GB
------------------------------ ---------- ---------- ----------
DATA                           39.9863281 5.05371094 34.9326172
FLASH                          39.9863281 .684570313 39.3017578

-- Check the logfiles details (prod1 instance)SQL> SELECT * FROM gv$logfile ORDER BY GROUP#;
    GROUP# STATUS  TYPE    SUBSTR(MEMBER,1,55)
---------- ------- ------- -------------------------------------------------------
         2         ONLINE  +DATA/prod/onlinelog/group_2.262.835716557
         2         ONLINE  +FLASH/prod/onlinelog/group_2.258.835716557
         1         ONLINE  +DATA/prod/onlinelog/group_1.261.835716555
         1         ONLINE  +FLASH/prod/onlinelog/group_1.257.835716555
         3         ONLINE  +DATA/prod/onlinelog/group_3.265.835716689
         3         ONLINE  +FLASH/prod/onlinelog/group_3.259.835716691
         4         ONLINE  +DATA/prod/onlinelog/group_4.266.835716691
         4         ONLINE  +FLASH/prod/onlinelog/group_4.260.835716691

We are working with 2 node RAC and for each node we have 1 thread and 2 group so totally we have 4 group for 2 threads. We will be adding  3 (recommended) standby logfile groups for each thread (Total 6 groups).

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 on Primary (PROD)
Below will add two members in each group of each threadSQL> 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#;
SQL> select group#,status,type,substr(member,1,55) from gv$logfile ORDER BY GROUP#;

    GROUP# STATUS  TYPE    SUBSTR(MEMBER,1,55)
---------- ------- ------- -------------------------------------------------------
         1         ONLINE  +DATA/prod/onlinelog/group_1.261.835716555
         1         ONLINE  +FLASH/prod/onlinelog/group_1.257.835716555
         2         ONLINE  +DATA/prod/onlinelog/group_2.262.835716557
         2         ONLINE  +FLASH/prod/onlinelog/group_2.258.835716557
         3         ONLINE  +DATA/prod/onlinelog/group_3.265.835716689
         3         ONLINE  +FLASH/prod/onlinelog/group_3.259.835716691
         4         ONLINE  +DATA/prod/onlinelog/group_4.266.835716691
         4         ONLINE  +FLASH/prod/onlinelog/group_4.260.835716691
         5         STANDBY +DATA/prod/onlinelog/group_5.279.836300695
         5         STANDBY +DATA/prod/onlinelog/group_5.278.836300703
         6         STANDBY +DATA/prod/onlinelog/group_6.277.836300711

    GROUP# STATUS  TYPE    SUBSTR(MEMBER,1,55)
---------- ------- ------- -------------------------------------------------------
         6         STANDBY +DATA/prod/onlinelog/group_6.276.836300717
         7         STANDBY +DATA/prod/onlinelog/group_7.275.836300723
         7         STANDBY +DATA/prod/onlinelog/group_7.274.836300729
         8         STANDBY +DATA/prod/onlinelog/group_8.271.836300735
         8         STANDBY +DATA/prod/onlinelog/group_8.270.836300741
         9         STANDBY +DATA/prod/onlinelog/group_9.269.836300747
         9         STANDBY +DATA/prod/onlinelog/group_9.267.836300749
        10         STANDBY +DATA/prod/onlinelog/group_10.273.836300755
        10         STANDBY +DATA/prod/onlinelog/group_10.272.836300765

20 rows selected.

-- Parameter Modification at the Primary Level:
To Stop the Archive log shipping

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER' SID='*'; 
After DR Setup is completed, this parameter will be enabled

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STAN)' SCOPE=BOTH SID='*';
==> PROD and STAN are TNS entries

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='*';
==> SERVICE=STAN is  TNS entry

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 parameters are necessary at the time of swithover
SQL> ALTER SYSTEM SET FAL_SERVER='STAN' SCOPE=BOTH SID='*';  -- PROD TNS entry
SQL> ALTER SYSTEM SET FAL_CLIENT='PROD' SCOPE=BOTH SID='*';   -- STAN TNS entry

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/Primary_bkp_for_stndby_%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

-- Configure the password file and copy to all primary 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

Remember db_name is required to be the same across Data Guard configuration, only db_unique_name will be differnt for primary and standby databases. Changes are in bold in the below parameter file as per the standby node.

initSTAN.ora
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'

-- Create Audit Directory on both standby nodes
[oracle@dr-rac1 ~]$ mkdir -p /u01/app/oracle/admin/STAN/adump
[oracle@dr-rac2 ~]$ mkdir -p /u01/app/oracle/admin/STAN/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)
    )
  )

STAN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-rac1-vip.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stan.localdomain)
     )
  )

Please note Currently standby database is running as single instance so STAN entry is pointing to that specific node  address (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 change the address to (SCAN) dr-racscan.

-- Set the environment on standby node (dr-rac1):
export ORACLE_SID=stan1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
-- Start up the instance in nomount state using parameter file we already created
[oracle@dr-rac1 ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/backup/pfile.ora'

-- Create standby database using rman
[oracle@dr-rac1 ~]$ 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)   -- ==> PROD is db_name not db_unique_name
RMAN> duplicate target database for standby;

-- Enable log shipping on Pirmary (PROD)
Enable parameter LOG_ARCHIVE_DEST_STATE_2 on primary database to enable log shipping, we already set it DEFER in previous steps.
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 (STAN)
-- Create spfile on shared location of standby 
SQL> create spfile='+DATA' from file='/home/oracle/backup/pfile.ora';

-- Shutdown instance and startup nomount with spfile
shutdown immediate;
startup nomount;

-- Enable the RAC Parameter:
SQL> alter system set cluster_database=TRUE scope=spfile sid='*';
System altered.

Please note that you need to change the names of controlfiles in spfile because 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.

alter system set control_files='+data/stan/controlfile/current.262.836308255','+flash/stan/controlfile/current.268.836308257' scope=spfile sid='*';

-- Shutdown instance
shutdown immediate;

-- Add this database to cluster
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-d ==> DB_UNIQUE_NAME
-n ==> DB_NAME
-r  ==> DB ROLE
-a ==> DISK GROUPS

-- Add the Instances
srvctl add instance -d STAN -i stan1 -n dr-rac1
srvctl add instance -d STAN -i stan2 -n dr-rac2-n ==> NODE NAME
-- shutdown the database:
shutdown immediate;

-- Startup the cluster standby database as read only
srvctl start database -d STAN

--Check the final status of the database with the cluster
-- set the ASM environment
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1

su - oracle

[oracle@dr-rac2 ~]$ srvctl status database -d STAN
Instance stan1 is running on node dr-rac1
Instance stan2 is running on node dr-rac2

[oracle@dr-rac1 ~]$ crsctl status res ora.stan.db -t
--------------------------------------------------------------------------------
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 ~]$ 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 Queries *******************************************************************************
-- DB role info
select name,open_mode,database_role from v$database;
-- Managed Standby info
select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_STANDBY -- Start recovery process alter database recover managed STANDBY database disconnect from session; -- Error info select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2 -- see if the redo is actually being applied, From the primary run: select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = 'STAN' order by FIRST_TIME

-- If you notice that logs aren't being applied, it is possible you might have a gap in your redo. 

You can check to see if there are any gaps in the redo, by running the following query on the primary:

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2

-- 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.

select * from V$DATAGUARD_STATUS order by TIMESTAMP

-- 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:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 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.

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.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION; --To stop recovery process ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ------------------------------------------------------------ --To start immediate redo apply and open database as read only, First stop recovery ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE OPEN; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SELECT NAME, OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;

-- 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 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. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; 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 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE;

select name,open_mode,database_role,db_unique_name from v$database;

alter database open;

select name,open_mode,database_role,db_unique_name from v$database;

-- 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 ALTER DATABASE FLASHBACK ON; --Now crash primary database

SHUTDOWN ABORT;

--Login to STANDBY database Convert STAN database to Primary, Finish Log Apply process using this command ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; --Activate STANDBY database (STAN) as primary ALTER DATABASE ACTIVATE STANDBY DATABASE; --Check database status SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE; As above query shows in result that STAN is converted to primary database so now we will open it for read/write operations. ALTER DATABASE OPEN; -- Check database status SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE; -- 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 SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; -- Now mount old primary (PROD) and flashback it to scn 1393488 STARTUP MOUNT FLASHBACK DATABASE TO SCN 1393488; --Convert (PROD) to STANDBY ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,FLASHBACK_ON FROM V$DATABASE;

-- CHANGING PROTECTION MODES SELECT PROTECTION_MODE FROM V$DATABASE; The mode can be switched using the following commands. Note the alterations in the redo transport attributes. -- Maximum Availability. 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; -- Maximum Performance. ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; -- Maximum Protection. ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAN AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; ALTER DATABASE OPEN;