Pages

Advertisement

Showing posts with label Dataguard. Show all posts
Showing posts with label Dataguard. Show all posts

Friday, September 6, 2019

Oracle Database: Process RSM0, PID = , will be killed

 
When you check status of database using dgmgrl and you get the error message in alert log - 
'Process RSM0, PID = 1524144984, will be killed'

 that means that dgmgrl is not able to get the response within stipulated amount of  time. 
This can be because of many reasons such has high CPU, Load Average , network etc. 
So, what you can do is increase the overall timeout period using 

 'EDIT CONFIGURATION SET PROPERTY OperationTimeout=90'

 another symptom of this is when you will check the database details, there will be no metrics which will be populated. 

Tuesday, June 11, 2019

Oracle Database: Flashback RAC Primary and Standby

 
In this blog I am going to discuss on how to flashback a RAC Primary and RAC Standby both and ensure Standby is in sync. 
These are for some maintenance activities you might want to perform on Primary and to have a rollback plan. 
You can re-order few steps if you ant
My primary Database name is PRIM and Standby is STDBY.

Step 1 and 2 are setup for the flashback. 

 Step 1  - Stop the apply on STDBY
dgmgrl /
edit datbase 'STDBY' set state 'APPLY-OFF'

 Step 2 - Create GRP (Guranteed Restore Point)
i. Switch logfile on Primary (alter system archive log current) 
ii. Create GRP on Standby (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iii. Create GRP on Primary  (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iv. take output of select * from v$restore_point and note the scn


 Now do your changes (patching etc), assuming your changes were not successful and you want to rollback.  

 Step 3 - Do the Flashback
i. Stop Database Primary (srvctl stop database -d PRIM)
ii. Start one instance in mount (startup mount exclusive)
iii. Flashback Database (flashback database to restore point BEFORE_RELOAD);
iv. alter database open reset logs

 Step 4 - Start Primary (all nodes)
i. srvctl start database -d PRIM


 Step 5 - Flashback Standby 
i. Check the current_scn (select current_scn from V$database)
ii. if current_scn of Standby is less than 2.iv, i.e scn of GRP on primary then you need not to do any flashback and can skip
iii. If current_scn is greater then you have 2 options
First - srvctl stop database -d STDBY and then startup mount exclusive for 1 instance
Then - 
    a. Flashback to scn of the GRP of primary (flashback database to scn <> )
    b. Flashback to scn of the GRP of standby (flashback database to restore point BEFORE_RELOAD). This assumes the scn of standby GRP is less than scn of primary GRP
iv. start DB (srvctl stop database -d CHMSTDBY, and srvctl start database -d CHMSTDBY)
v. Ensure apply is working fine. (edit database 'STDBY' set state='APPLY-ON')

 Step 6 - Finally Drop Restore Points
i. Primary - Drop restore point BEFORE_RELOAD
ii. Standby - for this you will have to have database is mount mode (in case your standby was read only) and then 'drop restore point BEFORE_RELOAD'

 References: How To Flashback Primary Database In Standby Configuration (Doc ID 728374.1)

Tuesday, April 16, 2019

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 2

 
This blog is in continuation with my previous blog to build Standby and DG broker. 
In this blog I do the setup of Dataguard and DG Broker.
Step 1 - Add Standby Redo log files (Optional and on both primary and Standby)
Standby redo logfiles' count is n+1 where n = total redo logfiles per thread
Syntax is 
alter database add standby logfile thread <num> group <num> ('+DATA','+FRA' ) size 1024M;

 Step 2 - Create Directories for DG Broker Configuration
[Node 1 - grid user - Primary Cluster]
asmcmd mkdir +FRA/MYDBPROD/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBPROD/DATAGUARDCONFIG

 [Node 1 - grid user - StandbyCluster ]
asmcmd mkdir +FRA/MYDBSTDBY/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBSTDBY/DATAGUARDCONFIG

Step 3 - Setup Dataguard configuration
[Node 1 - oracle user - Standby Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBPROD ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBPROD' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set fal_server=MYDBPROD scope=both sid='*';

 [Node 1 - oracle user - Primary Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBSTDBY ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBSTDBY' scope=both sid='*';
alter system set fal_server=MYDBSTDBY scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';

Step 4 - Setup DG Broker configuration
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
CREATE CONFIGURATION 'DG_Config' AS PRIMARY DATABASE IS 'MYDBPROD' CONNECT IDENTIFIER IS MYDBPROD;
show configuration;
ADD DATABASE 'MYDBSTDBY' AS CONNECT IDENTIFIER IS 'MYDBSTDBY';
show configuration;

Enable configuration;

Step 5 - Verify Configuration and Status
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
show configuration;
show database verbose 'MYDBPROD';
show database verbose 'MYDBSTDBY';
show instance verbose 'MYDBPR11' on database 'MYDBPROD';
show instance verbose 'MYDBPR12' on database 'MYDBPROD';
show instance verbose 'MYDBPR11' on database 'MYDBSTDBY';

show instance verbose 'MYDBPR12' on database 'MYDBSTDBY';

 This completes the setup of Dataguard and DG Broker

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 1

 
In this blog I show how to configure Dataguard Broker configuration for a 2-node RAC cluster to a 2-Node Standby.
This is a 2 blog series - you can find the second blog here - which only talks about configuration of Dataguard and Broker.

 Here is my configuration
Production DB
Unique Name - MYDBPROD
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Unqiue Name - MYDBSTDBY
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Step 1 - Create tnsnames.ora entries in all nodes
MYDBPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYPRODDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBPROD)
        (UR = A)
    )
  )

 MYDBSTDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYSTDBYDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBSTDBY)
        (UR = A)
    )
  )
Step 2 - Static Listener Entries for DG broker operations
[Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

lsnrctl stop 
lsnrcl start

 [Node 1- Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 2 - Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

 lsnrctl stop
lsnrctl start


Step 3 - Reset sys and system password and copy password file from Node 1 of Primary to other 3 nodes (in $ORACLE_HOME/dbs), also change the name as per the name of the instance

 Step 4 -  Add Database and Instance in OCR registry 
[Node 1 - oracle user - Standby Cluster]
srvctl add database -d MYDBSTDBY -o /opt/oracle/product/112/db -c RAC \
-p +DATA/MYDBSTDBY/spfileMYDBSTDBY.ora -r PHYSICAL_STANDBY -s MOUNT -t IMMEDIATE -n <DB_NAME> -a DATA,REDO1,FRA

 srvctl add instance -d MYDBSTDBY -i MYDBPR11 -n Node1
srvctl add instance -d MYDBSTDBY -i MYDBPR12 -n Node2

Step 5 - Create init files
[Node 1 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR11.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'

 [Node 2 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR12.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'
Step 6 - Create Audit Directories
[Node 1 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump

 [Node 2 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump
Step 7 - Start the instance, mount it and recover standby database. 
You can use any method to recover/restore.
One of the methods which I recommend is to use active database duplication. It's generally faster. 

 rman << EOF
connect TARGET sys/<pwd>@MYDBPROD ;
connect AUXILIARY sys/<pwd>@MYDBSTDBY;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;


In the next blog I discuss on DG Broker Build

Oracle Database: How to Know if Real Time Apply is Happening or Not (11g,12c,18c,19c)

 
In this blog I tell how to know if your Managed Standby is using Real time apply or not. 
Note  - for Real time apply - you must have standby redo logs configured (n + 1 standby logs per thread)
Case 1 - Idle  When no Recovery is Happening 
On Production 

 select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2

    DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------

         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES IDLE

Case 2 - Recovery (no Real time apply) 
On Production 
 select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2 ;

   DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES MANAGED

3. Case 3 - Real Time Apply 
On Production 
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2

   DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES MANAGED REAL TIME APPLY

I have highlighted the changes. Make note of your dest_id, i have shown here for 2, as my standby is configured using dest id 2. 

You can start a real time apply using. 
alter database recover managed standby database using current logfile disconnect;

Friday, April 5, 2019

Oracle Database: Archive Log Repository

 
Archive log repository is one of the seldom used and known concepts in Oracle Database. 

 An Oracle Database can be configured to send archive logs to remote destination without database being present in that site. 

 To explain
1. Database Instance is running
2. Database Control File is present
3. The CF has to be standby CF (ensure this)
4. Database is in mount state
5. No Datafiles present 

 If you configure your source database similar to a dataguard configuration, your primary will start sending archive logs to the Archive log repostiory site. 

 What are the use case? 
1. Backup of archive logs on remote site 
2. Remote Site can be used for tape backup etc
3. Setup during dataguard setup - the time spent to backup and transfer the archivelogs can be used by sending the logs using the archive log repo. 
So when you have your database restored, you will have your archives already there. 

 For More Information : https://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#SBYDB4745

Popular Posts

© Copyright 2019 DBA to DA. . Powered by Blogger.