DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

Pages

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Thursday, January 09, 2014

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

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.

-- Parameter Modification at the Primary Level:To Stop the Archive log shippingSQL> 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_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 swithoverSQL>ALTER SYSTEM SET FAL_SERVER='STAN' SCOPE=BOTH SID='*'; -- PROD TNS entrySQL>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='*';

-- Configure the password file and copy to all primary and standby nodescp $ORACLE_HOME/dbs/orapwprod1 dr-rac1:$ORACLE_HOME/dbs/orapwSTAN1scp $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.

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=stan1export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export PATH=$ORACLE_HOME/bin:$PATH-- Start up the instance in nomount state using parameter file we already created[oracle@dr-rac1 ~]$ sqlplus / as sysdbaSQL>startup nomount pfile='/home/oracle/backup/pfile.ora'

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='*';

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

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.

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;

--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 primaryALTER DATABASE ACTIVATE STANDBY DATABASE;--Check database statusSELECT 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 statusSELECT 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 MOUNTFLASHBACK DATABASE TO SCN 1393488;--Convert (PROD) to STANDBYALTER DATABASE CONVERT TO PHYSICAL STANDBY;SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,FLASHBACK_ON FROM V$DATABASE;

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