DR Switch over : Related

Step 1 Check if Primary and DR are in sync

select thread#, max(sequence#) from gv$log_history group by thread#;

Step 2 Query swtichover status on primary and standby

SELECT SWITCHOVER_STATUS FROM V$DATABASE; ( SHows Sessions active – this is ok)

Step 3 Check the primary database.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:

DGMGRL> SHOW DATABASE VERBOSE ‘——-pr’;

Step 4 Check the standby database that is the target of the switchover.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the standby database that is the target of the switchover. For example:

DGMGRL> SHOW DATABASE VERBOSE ‘——-dr’;

Step 5 Shutdown all instances on primary except one – So all archivelogs are synched up (Donot shut down the DR instances first)
Shutdown services using srvctl
srvctl stop service -d —ppr

Step 6 Shutdown all secondary instances on standby

Step 7 Select * from v$active_instances on primary and DR to check only one instance is up

Step 8 Issue the switchover command from current primary

DGMGRL> SWITCHOVER TO “——-pdr”;

Step 8 Show the configuration.

Step 9 Shutdown the databases on primary

Step 10 srvctl modify database -d —-ppr -r physical_standby -s mount -y automatic
srvctl modify database -d —-pdr -r primary -y automatic

STep 11. Start databases on each side
srvctl start database -d —–ppr
srvctl start database -d —–pdr
STEP 12 Enable the services on the DR (which ever is the current DR) and start them on each instance
Issue the SHOW CONFIGURATION command to verify that the switchover was successful.

DGMGRL> SHOW CONFIGURATION;

Step 9 Check Services using crs_stat.sh

===============================================================
Step 1: Login to DR site (aoramab00001q12)

$. setdb_dba01sqa
$srvctl status database -d dba01sqa
Instance dba01sqa1 is running on node aorarib00001q12
Instance dba01sqa2 is running on node aorarib00001q13
Instance dba01sqa3 is running on node aorarib00001q14

SQL> select open_mode from v$database;

OPEN_MODE
———-
MOUNTED
Step 2. Login to Primary Site aoramab00001q04

$. setdb_dba01pqa

$srvctl status database -d dba01pqa

Instance dba01pqa1 is running on node aoramab00001q04
Instance dba01pqa2 is running on node aoramab00001q05
Instance dba01pqa3 is running on node aoramab00001q06

$sqlplus / as sysdba

sqL> select open_mode from v$database;

OPEN_MODE
———-
READ WRITE

$dgmgrl sys@dba01pqa
password:

DGMGRL> show configuration

Configuration
Name: dba01pqa
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
dba01pqa – Primary database
dba01sqa – Physical standby database

Current status for “dba01pqa”:
SUCCESS

DGMRL> switchover to ‘dba01sqa’

Performing switchover NOW, please wait…

Operation requires shutdown of instance “dba01pqa2” on database “dba01pqa”

Shutting down instance “dba01pqa2″…

You must start instance “dba01pqa2” manually

Operation requires startup of instance “dba01sqa1” on database “dba01sqa”

You must start instance “dba01sqa1” manually

Switchover succeeded, new primary is “dba01sqa

DGMGRL> exit

Step 3: Login to server aorib00001q12 ( This depends on the instance name returned in the DGMGRL switchover Command)

We should start converted primary database instance first. Do not use srvctl to start the instance. start the instance individually.

$ . setdb_dba01sqa

$sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2156344 bytes
Variable Size 440404168 bytes
Database Buffers 176160768 bytes
Redo Buffers 7606272 bytes
Database mounted.
Database opened.
SQL>

Once the instance is up, wait for the other instances to come up. Other instances will be up automatically.

$srvctl status database -d dba01sqa

sqL> select open_mode from v$database;

OPEN_MODE
———-
READ WRITE

Step 4: Login to the new DR side (aoramab00001q05)

$. setdb_dba01pqa

$sqlplus / as sysdba

SQL>startup mount
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2156344 bytes
Variable Size 440404168 bytes
Database Buffers 176160768 bytes
Redo Buffers 7606272 bytes
Database mounted.
SQL>exit

Once the DR database is mounted, wait for other instances to come up. Other instances will be up automatically.

$srvctl status database -d dba01pqa

sqL> select open_mode from v$database;

OPEN_MODE
———-
MOUNTED

Step 5: Login to aoramab00001q04

$. setdb_dba01pqa

DGMGRL> show configuration
Configuration

Name: dba01pqa

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

dba01pqa – Physical standby database

dba01sqa – Primary database

Step 6: Switch logfile on the Primary side and check the sync on both the primary and Standby side.
If there are any services registered, please make sure the services are up. Not in dba01pqa database.

The converted DR database will be made primary during the next switch test.

DETAILED STEPS :

Switchover

I. Prerequisites/Preparation

Verify if GLOBAL_DBNAME in listener.ora is set correctly to <<db_unique_name>>_DGMGRL.<<db_domain>>

Example listener.ora:
SID_LIST_LISTENER_<NodeName> =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = <Instance_Name>)
(GLOBAL_DBNAME = <db_unique_name>_DGMGRL)
(ORACLE_HOME = /oracle/product/10.2.0)
)
)
Note: db_domain is null.

For OLBPQA

aorarib00001q08:/oracle $ lsnrctl status|grep -i dgmg

Service “olbpqa_DGMGRL” has 1 instance(s).

aorarib00001q09:/oracle $ lsnrctl status|grep -i dgmg

Service “olbpqa_DGMGRL” has 1 instance(s).

aorarib00001q10:/oracle/product/asm/11.1.0/network/admin

Service “olbpqa_DGMGRL” has 1 instance(s).

For OLBSQA

aoramab00001q01:/oracle $ lsnrctl status|grep -i dgmg

Service “olbsqa_DGMGRL” has 1 instance(s).

aoramab00001q02:/oracle $ lsnrctl status|grep -i dgmg

Service “olbsqa_DGMGRL” has 1 instance(s).

aoramab00001q03:/oracle $ lsnrctl status|grep -i dgmg

Service “olbsqa_DGMGRL” has 1 instance(s).

II. Pre-Switchover Checks

Verify there are no large gaps

Primary

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:32:07 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

THREAD# SEQUENCE#

———- ———-

1 87

2 69

3 76

Standby

[User:oracle Sid:olbsqa3]

aoramab00001q03:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:34:01 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = ‘YES’

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = ‘CURRENT’)

GROUP BY THREAD# 2 3 4 5 6

7 /

THREAD# MAX(SEQUENCE#)

———- ————–

1 86

2 68

3 75

Note : On the standby the following query should be within 1 or 2 of the primary query result.

Verify Primary and Standby tempfiles match

Primary

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:37:14 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> set linesize 100

column FILENAME format a50

column BYTES format 9999999999

column TABLESPACE format a15

SELECT TMP.NAME FILENAME, BYTES/1024/1024 “Size inMB” , TS.NAME TABLESPACE

FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#

SQL> SQL> SQL> SQL> 2 3 SQL> /

FILENAME Size inMB TABLESPACE

————————————————– ———- —————

+DATA1/olbpqa/tempfile/temp.481.759444521 500 TEMP

+DATA1/olbpqa/tempfile/temp.480.759444521 500 TEMP

+DATA1/olbpqa/tempfile/temp.479.759444521 500 TEMP

+DATA1/olbpqa/tempfile/temp.478.759444523 32767 TEMP

Standby

[User:oracle Sid:olbsqa3]

aoramab00001q03:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:38:38 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> set linesize 100

column FILENAME format a50

column BYTES format 9999999999

column TABLESPACE format a15

SELECT TMP.NAME FILENAME, BYTES/1024/1024 “Size inMB” , TS.NAME TABLESPACE

FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#

SQL> SQL> SQL> SQL> 2 3 /

FILENAME Size inMB TABLESPACE

————————————————– ———- —————

+DATA1/olbsqa/tempfile/temp.360.759347217 32767 TEMP

+DATA1/olbsqa/tempfile/temp.376.759347217 500 TEMP

+DATA1/olbsqa/tempfile/temp.377.759347215 500 TEMP

+DATA1/olbsqa/tempfile/temp.378.759347215 500 TEMP

Verify all datafiles necessary for role transition are ONLINE

Primary

[User:oracle Sid:olbpqa3]

aoramab00001q03:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:41:04 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;

no rows selected

Standby

[User:oracle Sid:olbsqa3]

aoramab00001q03:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 12:41:04 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;

no rows selected

Verify if START_OPTIONS ,DB_ROLE in the OCR are all set

Primary

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ srvctl config database -d olbpqa -a

aorarib00001q08 olbpqa1 /oracle/product/10.2.0

aorarib00001q09 olbpqa2 /oracle/product/10.2.0

aorarib00001q10 olbpqa3 /oracle/product/10.2.0

DB_NAME: null

ORACLE_HOME: /oracle/product/10.2.0

SPFILE: null

DOMAIN: null

DB_ROLE: PRIMARY

START_OPTIONS: mount

POLICY: AUTOMATIC

ENABLE FLAG: DB ENABLED

Standby

[User:oracle Sid:olbsqa3]

aoramab00001q03:/oracle $ srvctl config database -d olbsqa -a

aoramab00001q01 olbsqa1 /oracle/product/10.2.0

aoramab00001q02 olbsqa2 /oracle/product/10.2.0

aoramab00001q03 olbsqa3 /oracle/product/10.2.0

DB_NAME: null

ORACLE_HOME: /oracle/product/10.2.0

SPFILE: null

DOMAIN: null

DB_ROLE: PHYSICAL_STANDBY

START_OPTIONS: mount

POLICY: automatic

ENABLE FLAG: DB ENABLED

Any mismatch in OCR Setting should be corrected using

> srvctl modify database -d <db_unique_name> -<option> <option>

Note: You can see the entire options with «srvctl modify database -h»

Broker configuration is enabled and state of all members as intended is ONLINE

Primary

aorarib00001q10:/oracle $ dgmgrl sys/<pwd>@olbpqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> show configuration

Configuration

Name: olbpqa

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

olbpqa – Primary database

olbsqa – Physical standby database

Current status for “olbpqa”:

SUCCESS

remote_login_passwordfile is set to ‘EXCLUSIVE’

Primary ( Preferably Instance1)

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

———————————— ———– ————————

remote_login_passwordfile string EXCLUSIVE

Standby (Preferably Apply Node)

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

———————————— ———– ————————

remote_login_passwordfile string EXCLUSIVE

Verify TNSPING is successful between Primary and Standby and can connect to each other.
Ensure <<db_unique_name>>_DGMGRL is part of service names otherwise post switchover, instances may have to be manually started.

III. Switchover

If primary available, check application is not connected:

sql> select username, count(*) from gv$session group by username;

Stop the Services on the Primary

> srvctl stop service –d olbpqa

Issue global log switch from the primary

[User:oracle Sid:olbpqa1]

aorarib00001q08:/oracle $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Oct 17 13:04:16 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> alter system archive log current;

System altered.

Shutdown all instances except the instance1 on Primary.

Check the Status

aorarib00001q10:/oracle $ srvctl status database -d olbpqa

Instance olbpqa1 is running on node aorarib00001q08

Instance olbpqa2 is running on node aorarib00001q09

Instance olbpqa3 is running on node aorarib00001q10

Shutdown all instances except Instance1

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ srvctl stop instance -d olbpqa -i olbpqa3

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ srvctl stop instance -d olbpqa -i olbpqa2

Check the Status

aorarib00001q10:/oracle $ srvctl status database -d olbpqa

Instance olbpqa1 is running on node aorarib00001q08

Instance olbpqa2 is not running on node aorarib00001q09

Instance olbpqa3 is not running on node aorarib00001q10

Shutdown all instance except the apply instance on Standby.

Check the apply Instance

[User:oracle Sid:olbpqa3]

aorarib00001q10:/oracle $ dgmgrl sys/<pwd>@olbpqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> show database olbsqa

Database

Name: olbsqa

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

olbsqa1 (apply instance)

olbsqa2

olbsqa3

Current status for “olbsqa”:

SUCCESS

Check the status

aoramab00001q03:/oracle $ srvctl status database -d olbsqa

Instance olbsqa1 is running on node aoramab00001q01

Instance olbsqa2 is running on node aoramab00001q02

Instance olbsqa3 is running on node aoramab00001q03

Shutdown all instance except the apply instance

[User:oracle Sid:olbsqa3]

aorarib00001q12:/oracle $ srvctl stop instance -d olbsqa -i olbsqa2

PRODUCTION

[User:oracle Sid:olbsqa3]

aorarib00001q12:/oracle $ srvctl stop instance -d olbsqa -i olbsqa3

PRODUCTION

Check the status

aoramab00001q03:/oracle $ srvctl status database -d olbsqa

Instance olbsqa1 is running on node aoramab00001q01

Instance olbsqa2 is not running on node aoramab00001q02

Instance olbsqa3 is not running on node aoramab00001q03

Tail Broker and Alert Logs (optional) on Primary and Standby

SQL> SHOW PARAMETER background_dump_dest

Tail the broker logs

> tail -f <background_dump_dest location>/dr*

Tail the alert logs

> tail -f <background_dump_dest location>/alert*

Create Guaranteed Restore Points on Standby

Check the apply Instance. (Refer 5.a for details)
Stop the apply process on apply instance

SQL> CONNECT SYS/password@standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Create a guaranteed restore point with SQL*Plus

SQL> CREATE RESTORE POINT SWITCHOVER_START GUARANTEE FLASHBACK DATABASE;

Start the apply process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Create Guaranteed Restore Points on Primary.

Create a guaranteed restore point with SQL*Plus

SQL> CONNECT SYS/password@primary
SQL> CREATE RESTORE POINT SWITCHOVER_START GUARANTEE FLASHBACK DATABASE;

Perform the Switchover

Connect to Broker on Primary using SYS Credentials of Primary Database

aorarib00001q08:/oracle $ dgmgrl sys/<pwd>@olbpqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL>

Issue Switchover

aorarib00001q08:/oracle $ dgmgrl sys/<pwd>@olbpqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> show configuration

Configuration

Name: olbpqa

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

olbpqa – Primary database

olbsqa – Physical standby database

Current status for “olbpqa”:

SUCCESS

DGMGRL> switchover to olbsqa

Performing switchover NOW, please wait…

Operation requires shutdown of instance “olbpqa1” on database “olbpqa”

Shutting down instance “olbpqa1″…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “olbsqa3” on database “olbsqa”

Shutting down instance “olbsqa3″…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “olbpqa1” on database “olbpqa”

Starting instance “olbpqa1″…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “olbsqa3” on database “olbsqa”

Starting instance “olbsqa3″…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “olbsqa”

DGMGRL>

Post-Switchover Steps
Ensure the Datagaurd Broker Status on New Primary/Standby to be “SUCCESS”

Primary

[User:oracle Sid:olbpqa1]

aoramab00001q08:/oracle $ dgmgrl sys/<pwd>@olbpqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> show configuration

Configuration

Name: olbpqa

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

olbpqa – Physical standby database

olbsqa – Primary database

Current status for “olbpqa”:

SUCCESS

Standby

[User:oracle Sid:olbsqa1]

aoramab00001q01:/oracle $ dgmgrl sys/<pwd>@olbsqa

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> show configuration

Configuration

Name: olbpqa

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

olbpqa – Physical standby database

olbsqa – Primary database

Current status for “olbpqa”:

SUCCESS

Check all Instances are up and running on New Primary/Standby

Primary

aoramab00001q01:/oracle $ srvctl status database -d olbsqa

Instance olbsqa1 is running on node aoramab00001q01

Instance olbsqa2 is running on node aoramab00001q02

Instance olbsqa3 is running on node aoramab00001q03

Standby

aorarib00001q08:/oracle $ srvctl status database -d olbpqa

Instance olbpqa1 is running on node aorarib00001q08

Instance olbpqa2 is running on node aorarib00001q09

Instance olbpqa3 is running on node aorarib00001q10

Start the appropriate instances/database based on the status above

> srvctl start instance –d <db_unique_name> -i <Instance_Name>

Repeat the step 2c until all the Instances of New Primary/Standby are up and running.

Verify if START_OPTIONS ,DB_ROLE in the OCR are all set.

Primary

[User:oracle Sid:olbsqa1]

aoramab00001q01:/oracle $ srvctl config database -d olbsqa -a

aoramab00001q01 olbsqa1 /oracle/product/10.2.0

aoramab00001q02 olbsqa2 /oracle/product/10.2.0

aoramab00001q03 olbsqa3 /oracle/product/10.2.0

DB_NAME: null

ORACLE_HOME: /oracle/product/10.2.0

SPFILE: null

DOMAIN: null

DB_ROLE: PRIMARY

START_OPTIONS: mount

POLICY: automatic

ENABLE FLAG: DB ENABLED

Standby

[User:oracle Sid:olbpqa1]

aorarib00001q08:/oracle $ srvctl config database -d olbpqa -a

aorarib00001q08 olbpqa1 /oracle/product/10.2.0

aorarib00001q09 olbpqa2 /oracle/product/10.2.0

aorarib00001q10 olbpqa3 /oracle/product/10.2.0

DB_NAME: null

ORACLE_HOME: /oracle/product/10.2.0

SPFILE: null

DOMAIN: null

DB_ROLE: PHYSICAL_STANDBY

START_OPTIONS: mount

POLICY: AUTOMATIC

ENABLE FLAG: DB ENABLED

Check the Services on New Primary

aorarib00001q01:/oracle $ srvctl status service -d olbsqa

Service srv_olbpqa is running on instance(s) olbsqa1, olbsqa2, olbsqa3

Service svc_olbpqa_olb is running on instance(s) olbsqa1, olbsqa3

Service svc_olbpqa_olbreporting is running on instance(s) olbsqa1, olbsqa3

Service svc_olbpqa_webtool is running on instance(s) olbsqa1, olbsqa2, olbsqa3

Service svc_olbpqa_olb1 is running on instance(s) olbsqa1, olbsqa3

Start the Services on New Primary(if required)

aorarib00001q01:/oracle $ srvctl start service -d olbsqa

Confirm the Health Check of Application is Success with application users
Drop any Switchover Guaranteed Restore Points.

SQL> DROP RESTORE POINT SWITCHOVER_START;

DR Related :

Please find the updated one.

1. OEM–>Targets

(Loginto OEM and Click on the Targets tab)

2. DR_dataguard?Members

(Click on DR_Dataguard and then Members)

3. Check the Apply Lag and Transport Lag tab. If there is any correct symbol without number then that may be the indication that there is some lag.
Transport lag and Apply lag should be less than 60 sec

4. Click on the database in the same tab.

5. In the High Availability Tab – Click on Physical Standby.

Check the Last Received Log and Last applied Log (Multiple threads). If both are same then its in sync.

If still found any doubts loginto the individual server and execute the below query. Both the sequence Numbers should be same.

PRIMARY:

Last Primary Seq Generated from Primary
————————————————————-
Primary: SQL> select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
STANDBY:

Last Standby Seq Received at Standby site
—————————————————————-
PhyStdby:SQL> select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Last Standby Seq Applied at Standby site
————————————————————–
PhyStdby:SQL>select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied=’YES’
group by thread# order by 1;

to check actual lag time

select a.db_unique_name, b.lagtime from (select db_unique_name from v$database) a, (select nvl(round(trunc(mod(sysdate – max(timestamp),1)*86400)/60),-1) lagtime from gv$recovery_progress where ITEM = ‘Last Applied Redo’) b;
Actions to be taken if there is Lag:
Primary Database:
ALTER SYSTEM SWITCH LOGFILE ; It needs to be done all primary nodes

Standby Database: For huge lag:
ps –ef |grep mrp (to confirm which host receiving standby activity)

Make the Broker false:
alter system set dg_broker_start = FALSE scope=both;

Cancel Media Recovery on Standby db:
Alter database recover managed standby database cancel;

Start manual recovery standby db:
Alter database recover managed standby database disconnect;

Stop manual recovery standby db:
Alter database recover managed standby database cancel;

Restart the Broker:
Alter system set dg_broker_start = TRUE scope=both;
=============================================================================

rc2o1pdr DR database was not in sync with production because the archived logs was not transfered to DR side.

Tried restoring the missed logfiles on primary side, even then it was not not transfered by FAL.

Following Steps followed to resolve this issue.:

1)take the backup of those missed archived logs at primary server side.

[User:oracle Sid:rc2o1ppr3]
aorarib00001005:/oracle $ rman target / catalog rman/L#ani_4@dmdb

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Nov 26 05:55:13 2010

connected to target database: RC2O1PPR (DBID=1639653565)
recovery catalog database Password:
RMAN> rman backup archivelog low logseq 512345 high logseq 512350 thread 3 format=’/oracle/trash/thread_3-%u’;
2)scp those backup pieces to DR server side.

loramab00001001:/oracle/trash$ scp aorarib00001005:/oracle/trash/thread_3% .
loramab00001001:/oracle/trash$ scp thread_3% aoramab00035004:/oracle/trash/rc2o1pdr_lag
to check the status of the log in standby
select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

3)Register these pieces with rman at DR database.

User:oracle Sid:rc2o1pdr3]
aoramab00035004:/oracle $ rman target / catalog rman@dmdb

rman> catalog start with ‘/oracle/trash/rc2o1pdr_lag’

we have to find the log sequence — logseq will get it from the standby last log applied .
4)Then restore these archive logs at DR database

rman> restore archivelog low logseq 512345 high logseq 512350 thread 3;

5) after completion of this delete the rman pieces which you took backup from all the servers to clear the sapce in /oracle.
select thread#, max(sequence#) “Last Standby Seq Applied” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied=’YES’ group by thread# order by 1;

======================================================Lock down ID steps
Please find the missing log file from the standby server .

copy the missing log file from ASM to local directory

RMAN> list archivelog low logseq 401419 high logseq 401419 thread 2 all;

ep
. setdb_+ASM

ASMCMD>

ASMCMD>cp +FRA/o103ppr/archivelog/2011_07_10/thread_1_seq_42715.17655.756123173 /oracle/trash/n014038

then SCP to DR side

first scp to jump server and then from jump server to Standby location

scp thread* aoramab00001009:/oracle/trash/o103/
change the permission of file to 777
And login to Dr database and register the file .giving the full path of the file where you have copied

alter database register logfile ‘location’;

alter database register logfile ‘/oracle/trash/o103/thread_3_seq_41767.41525.756123175’;

MRP will start applying the log

PRIMARY

RMAN> list archivelog low logseq 401419 high logseq 401419 thread 2 all;

aorarib00001051:/oracle $ asmcmd

ASMCMD> cp +FRA/d303ppr/archivelog/2012_03_13/thread_2_seq_401419.80527.777794641 /net/mnt/backup

copying +FRA/d303ppr/archivelog/2012_03_13/thread_2_seq_401419.80527.777794641 -> /net/mnt/backup/thread_2_seq_401419.80527.777794641

STANDBY:

SQL> alter database register or replace logfile ‘/net/mnt/dump/thread_2_seq_401419.80527.777794641’;

NO NEED TO RESTORE – as its already registered it will get start applying

RMAN>catalog start with ‘/oracle/trash/rc2o1pdr_lag’

rman> restore archivelog low logseq 512345 high logseq 512350 thread 3;

=================================/////////////////////\\\\\\\\\\\\\\\\\\\\\\\\=========
method 3

if oracle password is there and production backup of archivelog is avalaiable

then we can restore the achivelog in production and start manually recovery in DR side

Standby side

stop broker in dr side

Make the Broker false:
alter system set dg_broker_start = FALSE scope=both;

Cancel Media Recovery on Standby db:
Alter database recover managed standby database cancel;

Start manual recovery standby db:
Alter database recover managed standby database disconnect;

In Production

rman>restore (archivelog low logseq 35224 high logseq 35309 thread 1 all);

need to monitor PRODUCTION FRA space .

and DR alertlog to find the gap and restore the log for backupiece

eg
FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 35224-35226
DBID 3377405836 branch 741025240
FAL[client]: All defined FAL servers have been attempted.
————————————————————-
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
————————————————————-
in production

rman taget /

rman> restore (archivelog low logseq 35224 high logseq 35226 thread 1 all);

alter database recover managed standby database disconnect from session;

===================================================================================================================================

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> alter system set dg_broker_start = FALSE scope=both;

System altered.

SQL> Alter database recover managed standby database disconnect;
Alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> Alter database recover managed standby database cancel;
^CAlter database recover managed standby database cancel
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
====================================================

Wed Nov 19 08:53:18 2014
Errors in file /oracle/diag/rdbms/agt01ppr/agt01ppr1/trace/agt01ppr1_ora_52887896.trc:
ORA-15032: not all alterations performed
ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation
ERROR: error ORA-15032 caught in ASM I/O path
Wed Nov 19 08:53:18 2014
Wed Nov 19 08:53:33 2014
Errors in file /oracle/diag/rdbms/agt01ppr/agt01ppr1/trace/agt01ppr1_ora_52887896.trc (incident=125420):
ORA-00600: internal error code, arguments: [kfmdSlvJoinWrt1], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/agt01ppr/agt01ppr1/incident/incdir_125420/agt01ppr1_ora_52887896_i125420.trc
Wed Nov 19 08:53:53 2014
Trace dumping is performing id=[cdmp_20141119085353]
Wed Nov 19 08:53:53 2014
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 52887896
Wed Nov 19 08:53:54 2014
/oracle/diag/rdbms/agt01ppr/agt01ppr1/trace/agt01ppr1_ora_52887896.trc
ORA-15032: not all alterations performed
ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation
ERROR: error ORA-15032 caught in ASM I/O path
DDE: Problem Key ‘ORA 1110’ was flood controlled (0x1) (no incident)
ORA-01110: data file 7: ‘+DATA1/agt01ppr/datafile/agent_desktop_data.635.769381737’
ORA-15081: failed to submit an I/O operation to a disk
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect /
Connected.
DGMGRL> show database ‘dz102pdr’

Database
Name: dz102pdr
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
dz102pdr3
dz102pdr4
dz102pdr1 (apply instance)
dz102pdr2

Current status for “dz102pdr”:
Warning: ORA-16610: command “Broker automatic health check” in progress
[User:oracle Sid:dz102pdr4]
loramab00035006:/oracle $ olsnodes
loramab00035003
loramab00035004
loramab00035005
loramab00035006
[User:oracle Sid:dz102pdr4]
loramab00035006:/oracle $ ssh loramab00035003
This is a proprietary system requiring authorized access. Any unauthorized acce
ss and/or use of this system is not permitted. Any authorized use is subject to
compliance with applicable law and internal policies as may be amended from tim
e to time. Accordingly this system may be monitored and the results recorded an
d reviewed. By using or accessing this system you expressly acknowledge that yo
u are an authorized user and are not entitled to any privacy rights with respect
to your use of this system.
[User:oracle Sid:+ASM1]
loramab00035003:/oracle $ ps -ef| grep mrp
oracle 9329 9225 0 08:59 pts/0 00:00:00 grep mrp

RESTORE V1:

RMAN Restore : (prod to lower region scenario)

Scp /copy the full backup and incr backup (if needed) from production to lower region.
Based on 10g database or 11g database. On lower region server copy setdb_<dbname> for ex : we will take one database which is in 10g database.
Environment creation for new database. In our example lets take 10g database.

bash-3.00$ cat *vs01pdv*
unset PATH;export PATH=.:/usr/bin:/usr/local/bin
# Environment specific to each DB
# Version 12/4/2006
# Modify the following variables based on the environment
export ORACLE_SID=vs01pdv1 (Please note here we need to change our db name)
export ORACLE_DB=vs01pdv (Please note here we need to change our db name)
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/10.2.0
export FUNCT=dev
# Load the Common Environment
. ${ORACLE_BASE}/setdb_common
#END OF FILE

Create password for new database which we are going to create in lower region if required.
Create pfile for new database.
startup nomount using pfile.
rman target /
RMAN> restore controlfile from <path>’;
RMAN> alter database mount;
SQL> alter database flashback off;
SQL> alter database disable block change tracking;
RMAN>catalog start with <path where backup pieces are present>;

It will ask for cataloging all files … give

Yes

RMAN> Restore database;
RMAN> recover database; (Most of the if all backup pieces and archivelogs are present it will recover database properly). Some times where you don’t have full archivelogs. In that case you have do incomplete recovery using backup controlfile…

SQL> recover database using backup controlfile until cancel;

Media recovery will ask for options like

Auto |cancel

Issue cancel here…

Media recovery cancelled.

SQL> Alter database open resetlogs;

SQL> show parameter controlfile

Note that controlfile location and need to include this controlfile path in our pfile.

SQL> shut immediate

SQL> startup pfile=’path’ open;

SQL> create spfile from pfile=’pfile path where we stored our pfile’;

SQL> shut immediate;

SQL>startup(This time it will start using spfile).

Note : where prompt is RMAN here, Need to use RMAN prompt wherever SQL used, Pls use SQL prompt.

One Performance Issue Scenario ***

Performance Issue:

We had performance issue in GPS03PPR database last week(6/24) where the Application Team were complaining for Long running jobs.

We had examined the top activity and found the top sql from OEM.

Below is the Top Query and the Execution Plan:

SQL ID : bkq3b73wm34fn
SQL Text : SELECT WIREDEST, SRVRTID, CUSTOMERID, WIREDEST, LOGID, AMOUNT,
 USERID, AGENTID, AGENTTYPE, ORIGCURRENCY, EXCHANGERATE,
 PROCESSEDBY, ACCTDEBITID FROM BPW_WIREINFO WHERE
 SRVRTID LIKE :1 AND CONFIRMNUM = :2
 
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 137 | 60172 (1)| 00:12:03 |
|* 1 | TABLE ACCESS FULL| BPW_WIREINFO | 1 | 137 | 60172 (1)| 00:12:03 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CONFIRMNUM"=:2 AND "SRVRTID" LIKE :1)

Cause:
 The Table is around 1.7 GB in size and it is having around 3.4 million records.
 
 The above query returns only one row per execution. This was found in OEM and the SQL Execution History.
 
 For each and every execution, it need to perform Full Table Scan on this table and in-turn more User I/O.

Solution:

As per the Execution plan Predicate, a composite Index on CONFIRMNUM and SRVRTID columns would yield a better performance.
 
 But creating a new index will slow down the DML operations as there were many indexes which were already present on the table.
 
 The SQL Profile had suggested to use an existing index, IDXBPWWIRE0037 for a better runtime.
 
 This was a composite index present with the leading column as SRVRTID, STATUS, PROCESSEDBY.
 
 We had accepted the SQL Profile to fix the User I/O issues which fixed the Performance problem.
 
 
Below is the execution plan after the SQL Profile in place:

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 137 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| BPW_WIREINFO | 1 | 137 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDXBPWWIRE0037 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CONFIRMNUM"=:2)
 2 - access("SRVRTID" LIKE :1)
 filter("SRVRTID" LIKE :1)
 
 
 The Optimizer was not able to pickup this Index at first place because there are 2 other columns STATUS and PROCESSEDBY, which need to be filtered out from the Index Access and then it need to filter out CONFIRMNUM column from the Resultset.

:::::: A nice DR approach ::::::

Environment Details :

PROD: 4 node RAC
DR : 4 Node RAC

Dataguard Broker :Enabled

Monitoring Tool : OEM (Grid Control)

Prepare the physical standby database to be activated.
=============================================================

1.Black out primary and standby targets in Grid Control

2.Stop DataGuard Brokers on both the primary and standby sides.

The Broker will undo any changes made required during this process.
 
 PRIMARY AND STANDBY:
 
 SQL>alter system set dg_broker_start=false scope=both;

Since this is RAC cluster we need to run the above command only in once node.

3.Set up a flash recovery area if already not set (only on physical standby database that needs to be activated R/W mode ).

On the physical standby database that will be activated for read/write access, 
 We should set the following initialization parameters to ensure a guaranteed restore point can be created. 
 
 This scenario sets up the flash recovery area in the +FRA location
 
 SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
 SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA' scope=both sid='*';
 
 SQL> select flashback_on from v$database;
 
 FLASHBACK_ON
 ------------------
 NO
 
 SQL>alter database flashback ON;
 
 Database altered.
 
 SQL> select flashback_on from v$database;
 
 FLASHBACK_ON
 ------------------
 YES 
 
 Since this is a physical standby database it would be already in mount mode. 
 If it would be in readonly mode you need to shutdown and startup in mount mode.then enable the flashback .
 
 This step-3 is optional (required only if standby database flashback mode not enabled)
 
 If already set then 
 
 Check you have enough space available in the recovery area:
 
 select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
 
 Sample o/p:
 ==========
 
 NAME FREE_MB TOTAL_MB PERCENTAGE
 ------------------------------ ---------- ---------- ----------
 CRS 13203 14385 91.7831074
 DATA1 408055 11877030 3.43566531
 FRA 408231 2623995 15.5576135
 DATA2 117436 138105 85.0338511
 FRA2 137925 138105 99.8696644

 SELECT 
 file_type, 
 sum(percent_space_used) used_space,
 100 - sum(percent_space_used) free_space,
 sum(percent_space_reclaimable) reclaimable_space, 
 (100 - sum(percent_space_used)) + sum(percent_space_reclaimable) available_space
 FROM v$flash_recovery_area_usage
 group by file_type;
 
 
 Sample o/p:
 ===========
 
 FILE_TYPE USED_SPACE FREE_SPACE RECLAIMABLE_SPACE AVAILABLE_SPACE
 ------------ ---------- ---------- ----------------- ---------------
 ARCHIVELOG  2.77        97.23        0                 97.23
 BACKUPPIECE 0             100         0                100
 CONTROLFILE 0             100        0                 100
 FLASHBACKLOG 0           100        0                  100
 IMAGECOPY 0            100         0                   100
 ONLINELOG 0              100       0                   100
 
 6 rows selected.

 
 if not increase the size:
 SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=30G;

4.Cancel Redo Apply and create a guaranteed restore point.

On the physical standby database, stop Redo Apply and create a restore point.

Since this is a RAC cluster, determine the apply node of the DR standby and perform this step on that node:

 standby:
 ========
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 SQL> CREATE RESTORE POINT BEFORE_DR_TEST GUARANTEE FLASHBACK DATABASE;
 
 When you create a guaranteed restore point, 
 you associate an easy-to-remember name with a timestamp or SCN so that you can later flash back the database to a name instead of specifying an exact SCN or time.
 
 
 Select scn, time, name from v$restore_point where name=’BEFORE_DR_TEST’;
 
 
 Example in our Environment :
 ===========================
 
 aoramab00001009:/oracle $ dgmgrl /
 DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - 64bit Production
 
 Copyright (c) 2000, 2005, Oracle. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 Connected.
 DGMGRL> show database o201pdr
 
 Database
 Name: o201pdr
 Role: PHYSICAL STANDBY
 Enabled: YES
 Intended State: ONLINE
 Instance(s):
 o201pdr1 (apply instance)
 o201pdr3
 o201pdr2
 o201pdr4

SUCCESS
 
 So here the MRP is running on 1st node .
 
 Let's go to the 1st node and cancel the media recovery .
 
 aoramab00001008:/oracle $ ps -ef|grep o201pdr|grep mrp
 oracle 2818284 1 1 Jun 10 - 15:28 ora_mrp0_o201pdr1
 
 standby:
 
 SQL> select instance_name from v$instance;
 
 INSTANCE_NAME
 ----------------
 o203pdr1
 
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
 Database altered.

SQL> CREATE RESTORE POINT BEFORE_DR_TEST GUARANTEE FLASHBACK DATABASE;

Restore point created .

5. Prepare the primary database to have the physical standby be diverged.
==============================================================

1.Archive the current log file (On RAC systems make sure to perform this on EACH INSTANCE).
 When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.
 
 Example:
 Primary (each RAC instance)

 Instance: o201ppr1
 SQL> select instance_name from v$instance;
 INSTANCE_NAME
 ----------------
 o201ppr1
 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
 Instance: o201ppr2
 Same has to be followed for the other 2 :

6.Defer log archive destinations pointing to the standby that will be activated.
==============================================================

Find the respective LOG_ARCHIVE_DEST and make the state to defer.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

7. Stop the Primary Database .
===============================

In our RAC environment Every applications has been attached to one Service . So Application wise DR test are being carried out regularly .So in this case we used to stop only respective service .

srvctl stop service -d o201ppr -s svc_o201ppr_fco_filter(on Primary)
8. Activate the physical standby database.
==========================================

On the physical standby database, perform the following steps:

8.1: Stop the Physical standby Database:

you’ll need to use srvctl stop database -d <databasename> to shut down the database
Then, you’ll need to use SQLPLUS to start the database instance (one instance required for DR testing).:

Then Follow this to activate the standby for Activation.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

SQL> STARTUP MOUNT FORCE;

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> ALTER DATABASE OPEN;

In our RAC Environment we have to Start the same service only that we had stopped on Primary .

Srvctl start service -d o203pdr -s svc_o203ppr_firco_filter

9. Now the DR Database is ready for testing .
==========================================

10.Revert the activated database back to a physical standby database.
==============================================================

aoramab00001008:/oracle $ srvctl stop database -d o201pdr

SQL> STARTUP MOUNT FORCE

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
o201pdr1

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_DR_TEST; *** (only one instance, i.e o201pdr1 )

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> STARTUP MOUNT FORCE;

In case of our environment which is RAC, we need to follow few extra step .

SQL> SHUTDOWN IMMEDIATE;

SQL> EXIT

aoramab00001008:/oracle $ srvctl start database -d o203pdr -o mount

(To find out the RESTORE POINT, you can query ‘select name from v$restore_point;)

ON Primary:
===============
One node only

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
o203ppr1

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable(enabling the log shipping)

Enable the DG broker in both PRIMARY and STANDBY

alter system set dg_broker_start=true scope=both sid=’*’;

MAKE SURE THE PRIMARY AND STANDBY in SYNC.*****************

***************** END*******************

HOPE THIS ARTICLE MAKE SENSE:

Cheers,
Satyaprakash

ORA-01000: maximum open cursors exceeded.

Sometime in the real world the application jobs while running may face the error “ORA-01000: maximum open cursors exceeded” .

They will immediately point to DBA to increase the open_cursors value , but that’s not the solution always . We need to identify what exactly causing this . It might be due to various reason –

Let’s try to understand what really this cursor is !!!!!!

Any application that uses Oracle Database as back end repository, Oracle Identity Manager runs several SQL statements.
For every SQL statement execution in Oracle Database, certain area in the memory is allocated.

Oracle PL/SQL allows you to name this area. This private SQL area is called context area or cursor.

These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache.

To keep a defective session from filling up the library cache or clogging the CPU with millions of parse requests, the OPEN_CURSORS database parameter must be set to
limit the cursors.

Here what has happened :

Apps team complained one of there job is getting failed .

So from the DBA prospective since it was a critical job we had to increase the open_cursor value for the immediate fix , but we had to identify the root cause to provide a permanent solution .

Step to increase the open_cusor Limit:

1.SQL> show parameter open_cursor

NAME TYPE VALUE
———————————— ———– ——————————
open_cursors integer 2300

2. alter system set open_cursor=2500 scope=both sid=’*’;

system altered.

Once we incresed the open_cursor value we asked them to re-run there job again to monitor which is the defective Query on the code causing this .

Once the Job started we found few Queries which were reaching near to the open_cursor limit .

Query to find the Details :
============================

select a.value, s.username, s.sql_id, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#
and s.sid=a.sid and b.name = ‘opened cursors current’ and s.username is not null order by 1;

It will be more helpful If we know the DB username who has kicked off the JOB .
Here the DB username was AUTOLOS_ETL_USER

SQL> select a.value, s.username, s.sql_id, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = ‘opened cursors current’ and
s.username=’AUTOLOS_ETL_USER’ order by 1

SQL> /

VALUE USERNAME SQL_ID SID SERIAL#
---------- ------------------------------ ------------- ---------- ----------
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 2648 1181
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 3095 41991
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 2809 52001
 96 AUTOLOS_ETL_USER ab58yscqkptra 2711 43134

In next 5 mins It crossed the earlier Limit 2300
Here is the Problem really exist .
SQL> /

VALUE USERNAME SQL_ID SID SERIAL#
---------- ------------------------------ ------------- ---------- ----------
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 2648 1181
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 3095 41991
 10 AUTOLOS_ETL_USER az2uqz8kug7wy 2809 52001
 2401 AUTOLOS_ETL_USER ab58yscqkptra 2711 43134

Once we Identified the defective Query

"ab58yscqkptra"

it was informed informed to the Application team to go for further investigation of the code .

Hope this will give some idea on resolving the cursor issue .
Cheers,
Satyaprakash

Dataguard Related Script

To find when the last applied and received Archive log
=================================================
select ‘Last applied : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

Sample o/p:
================
LOGS TIME
—————- ——————
Last applied : 16-JUL-09:09:24:16
Last received : 16-JUL-09:09:28:36

==============================================================
Check Redo Apply and Redo Transport status on physical standby
==============================================================
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY
/

+++++++++++++++++++++++++++

Finding the Lag :Run the below Script Accordingly

+++++++++++++++++++++++++++

Need to be run @PRODUCTION
===========================
select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Need to be run @Standby Site
===========================
Last Standby Seq Received at Standby site
—————————————————————-
select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Last Standby Seq Applied at Standby site
————————————————————–
select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied=’YES’
group by thread# order by 1;

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence
Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Show archived log destination status & if any issues with the ARCH destination +++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

— on primary databaes
select dest_id,dest_name,status,database_mode, error from v$archive_dest_status;

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
select message, timestamp from v$dataguard_status where dest_id = &enter_the_dest(for example 1,2 or 3) ;

Run this on Standby to find the details of the ARCHIVE Log details which are not applied
==============================================================================
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG
where applied=’NO’;

The below script will give you the details of last 20 Archive logs status :
=========================================================================
select SEQUENCE# ,REGISTRAR,ARCHIVED,APPLIED ,status from v$archived_log
where sequence#>(select max(sequence#)-20 from v$log_history);

=================================================
To monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:
================================================

select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time,
item, round(sofar/1024,2) “MB/Sec”
from v$recovery_progress
where (item=’Active Apply Rate’ or item=’Average Apply Rate’);

Sample o/p:
START_TIME ITEM MB/SEC
–––––- –––––––––––––––- ––––
27-MAR-14 15:49:44 Active Apply Rate 8.5
27-MAR-14 15:49:44 Average Apply Rate 6.30

How to drop a RAC database manually

1) Stop the database

srvctl stop database -d <databasename>

2) Change the parameter CLUSTER_DATABASE to false

alter system set cluster_database=false scope=spfile;

3) Restart the database in restrict mode

shutdown immediate;
startup mount restricted exclusive;

4) Drop the database .Finally Gone :) 
drop database;

Adding / Dropping Disks From a ASM Disk Group

Identify Candidate Disks

The current disk group configuration, 
(TESTDB_DATA1 and candidate disks not assigned to any disk group)
 has the following configuration:


$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"

SELECT
    NVL(a.name, '[CANDIDATE]')      disk_group_name
  , b.path                          disk_file_path
  , b.name                          disk_file_name
  , b.failgroup                     disk_file_fail_group
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name;

Disk Group Name Path            File Name            Fail Group     
--------------- --------------- -------------------- ---------------
TESTDB_DATA1    /dev/oradisks/data1   DATA1_0000    CONTROLLER1
                /dev/oradisks/data3   DATA1_0001    CONTROLLER1
                /dev/oradisks/data6   DATA1_0002    CONTROLLER2
                /dev/oradisks/data8   DATA1_0003    CONTROLLER2

[CANDIDATE]     /dev/oradisks/data10
                /dev/oradisks/data20
                /dev/oradisks/data24

In this example, I will be adding two new disks
 (/dev/oradisks/data10 and /dev/oradisks/data20) to the current disk group.


Add Disks to a Disk Group

Finally, let's add the two new disks to the disk group. 
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysasm"

SQL> ALTER DISKGROUP testdb_data1 ADD
  2  FAILGROUP controller1 DISK '/dev/oradisks/data10'
  3  FAILGROUP controller2 DISK '/dev/oradisks/data20' REBALANCE  POWER 11;

Diskgroup altered.
This needs to be done within the ASM instance and connected as a user 
with SYSASM privileges:

Query to check the status of the rebalance operation .

SQL> SELECT group_number, operation, state, power, est_minutes 
FROM v$asm_operation;

GROUP_NUMBER OPERATION STATE   POWER EST_MINUTES
------------ --------- ------ ------ -----------
           1 REBAL     RUN        11           9

Monitor until the output says norows selected ,
 that indicates rebalancing has been completed.
After adding the new disks, this is a new view of the disk group configuration:

Status of the diskgroup :

Disk Group Name Path            File Name            Fail Group     
--------------- --------------- -------------------- ---------------
TESTDB_DATA1    /dev/oradisks/data1   DATA1_0000    CONTROLLER1
                /dev/oradisks/data3   DATA1_0001    CONTROLLER1
                /dev/oradisks/data6   DATA1_0002    CONTROLLER2
                /dev/oradisks/data8   DATA1_0003    CONTROLLER2
                /dev/oradisks/data10  DATA1_0004    CONTROLLER1
                /dev/oradisks/data20  DATA1_0005    CONTROLLER2


[CANDIDATE]     /dev/oradisks/data24


Drop Disks from a Disk Group

Now, let's drop the same two new disks from the disk group. This needs to be done within the ASM instance and connected as a user with SYSASM privileges:


$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysasm"

SQL> ALTER DISKGROUP data1 DROP
  2  DISK data1_0004, data1_0005  REBALANCE POWER 11;

Diskgroup altered.
The current disk group configuration, (DATA1 and candidate disks not assigned to any disk group) now has the following configuration:


Disk Group Name Path            File Name            Fail Group     
--------------- --------------- -------------------- ---------------
TESTDB_DATA1    /dev/oradisks/data1   DATA1_0000    CONTROLLER1
                /dev/oradisks/data3   DATA1_0001    CONTROLLER1
                /dev/oradisks/data6   DATA1_0002    CONTROLLER2
                /dev/oradisks/data8   DATA1_0003    CONTROLLER2

[CANDIDATE]     /dev/oradisks/data10
                /dev/oradisks/data20
                /dev/oradisks/data24

How to check the progress of EXPDP/IMPDP

APPLIES TO: Version 10.1.0.2 and later

1.The status of the job:

select JOB_NAME,OPERATION,STATE from dba_datapump_jobs;

2.The percentage of work done :

SELECT b.username, a.sid, b.opname, b.target,
            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
     FROM v$session_longops b, v$session a
     WHERE a.sid = b.sid      ORDER BY 6;

3.The percentage of work done and the current status of the Datapump job

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
     FROM v$session_longops sl, v$datapump_job dp
     WHERE sl.opname = dp.job_name
     AND sl.sofar != sl.totalwork;

Switchover

I. Prerequisites/Preparation

II. Pre-Switchover Checks

1.SQL> show parameter open_cursor

NAME TYPE VALUE ———————————— ———– —————————— open_cursors integer 2300

2. alter system set open_cursor=2500 scope=both sid=’*’;

system altered.

Sample o/p: ================ LOGS TIME —————- —————— Last applied : 16-JUL-09:09:24:16 Last received : 16-JUL-09:09:28:36

============================================================== Check Redo Apply and Redo Transport status on physical standby ============================================================== SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY /

+++++++++++++++++++++++++++

Finding the Lag :Run the below Script Accordingly

+++++++++++++++++++++++++++

Need to be run @PRODUCTION =========================== select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Show archived log destination status & if any issues with the ARCH destination +++ +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

— on primary databaes select dest_id,dest_name,status,database_mode, error from v$archive_dest_status;

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select message, timestamp from v$dataguard_status where dest_id = &enter_the_dest(for example 1,2 or 3) ;

Run this on Standby to find the details of the ARCHIVE Log details which are not applied ============================================================================== SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where applied=’NO’;

The below script will give you the details of last 20 Archive logs status : ========================================================================= select SEQUENCE# ,REGISTRAR,ARCHIVED,APPLIED ,status from v$archived_log where sequence#>(select max(sequence#)-20 from v$log_history);

================================================= To monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress: ================================================

select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time, item, round(sofar/1024,2) “MB/Sec” from v$recovery_progress where (item=’Active Apply Rate’ or item=’Average Apply Rate’);

Sample o/p: START_TIME ITEM MB/SEC –––––- –––––––––––––––- –––– 27-MAR-14 15:49:44 Active Apply Rate 8.5 27-MAR-14 15:49:44 Average Apply Rate 6.30

APPLIES TO: Version 10.1.0.2 and later

NAME TYPE VALUE
———————————— ———– ——————————
open_cursors integer 2300

Sample o/p:
================
LOGS TIME
—————- ——————
Last applied : 16-JUL-09:09:24:16
Last received : 16-JUL-09:09:28:36

==============================================================
Check Redo Apply and Redo Transport status on physical standby
==============================================================
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY
/

Need to be run @PRODUCTION
===========================
select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Show archived log destination status & if any issues with the ARCH destination +++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

— on primary databaes
select dest_id,dest_name,status,database_mode, error from v$archive_dest_status;

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
select message, timestamp from v$dataguard_status where dest_id = &enter_the_dest(for example 1,2 or 3) ;

Run this on Standby to find the details of the ARCHIVE Log details which are not applied
==============================================================================
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG
where applied=’NO’;

The below script will give you the details of last 20 Archive logs status :
=========================================================================
select SEQUENCE# ,REGISTRAR,ARCHIVED,APPLIED ,status from v$archived_log
where sequence#>(select max(sequence#)-20 from v$log_history);

=================================================
To monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:
================================================

select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time,
item, round(sofar/1024,2) “MB/Sec”
from v$recovery_progress
where (item=’Active Apply Rate’ or item=’Average Apply Rate’);

Sample o/p:
START_TIME ITEM MB/SEC
–––––- –––––––––––––––- ––––
27-MAR-14 15:49:44 Active Apply Rate 8.5
27-MAR-14 15:49:44 Average Apply Rate 6.30