Open Physical Standby For Read Write Testing and Flashback

Open the Standby database in read write mode for any reporting or testing and then move it back to standby database using the flashback technology.

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.

Step 1 - In Standby database

A) Set up a flash recovery area. 

If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';

B) Cancel Redo Apply and create a guaranteed restore point.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE; 

To Confirm the details of restore point and its scn and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point; 

NAME                          SCN              TIME 

-------------------------     -------------    ------------------------------ 

STANDBY_FLASHBACK_TESTING     22607810         12-APR-09 01.10.21.000000000 P

Step 2 - In Primary Database

A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database. When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

B) Defer log archive destinations pointing to the standby that will be activated.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 - In Standby database

A) Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Once its done you can check the cotnrolfile status will be changed from Stnadby to Current

SQL> select CONTROLFILE_TYPE from v$database; 

CONTROL 

------- 

CURRENT

B) Then open the database.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 

SQL> ALTER DATABASE OPEN;

Step 4 - In Standby database

Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database

NOTE—

Any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

For example :

SQL> create table testing ( col1 varchar2 (100)); 

Table created. 

SQL> insert into testing values ( 'testing for flashback on standby database'); 

1 row created. 

SQL> commit; 

Commit complete.

Revert the active standby database back to Physical standby database 

Step 1 - In standby database

A1. Mount the database.

A2. Flashback the database to restore point.

SQL> STARTUP MOUNT FORCE;

ORACLE instance started. 

Total System Global Area  289406976 bytes 

Fixed Size                  1290208 bytes 

Variable Size             159383584 bytes 

Database Buffers          125829120 bytes 

Redo Buffers                2904064 bytes 

Database mounted. 

SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;

You can confirm the same by checking the controlfile status. It will be now backup controlfile

SQL> select controlfile_type from v$database; 

CONTROL 

-------------- 

BACKUP

B) Convert to Standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;  

SQL> STARTUP MOUNT FORCE;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> select controlfile_type from v$database;

CONTROL 

-------------- 

STANDBY

Step 2 - In standby database 

A) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap. 

Step 3 - In Primary database 

A) Re-enable archiving to the physical standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

Step 4 - In Standby database 

A) Open the database in Read only mode and ensure that all the transaction done in active mode are no more

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> ALTER DATABASE OPEN READ ONLY;  

SQL> select * from testing;  

select * from testing  

*  

ERROR at line 1:  

ORA-00942: table or view does not exist 

B) Drop the restore point

SQL> STARTUP FORCE MOUNT;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;  

SQL> DROP RESTORE POINT Standby_flashback_testing ; 

Caution: 

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss. 

Read more

Disable a table Constraints

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause :
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution :
Two solutions exist for this problem.

1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint            Child Constraint
----------------------------   --------------------------     -------------------------    ---------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK               SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK               TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.


2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Read more

DATABASE HEALTH CHECK

One of the daily task for oracle DBA to measure the health of databases.

This includes the database status,instance name, database open  mode, start up time,DR status,Archive log generation  etc.

The following script can be used to generate the oracle Database health Report. Below script is very basic script written to check   database status, additions can be made to script as per the requirements.

This report generates the following basic checks.

1. DATABASE STATUS

2. DATABASE NAME AND MODE

3. DR DATABASE STATUS

4. ARCHIVE LOG GENERATION LAST WEEK

5. DB PHYSICAL SIZE, DB ACTUAL SIZE

6. TABLESPACE USAGES

7. NO of USER/SESSIONS CONNECTED

8. DICTIONARY/LIBRARY HIT RATIO

9  BLOCKING QUERIES,BLOCKER AND WAITER

10. INVALID OBJECT COUNT LIST

Scripts Start.

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

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

set feedback off

set serverout on

set wrap off

set pages 300

set lines 200

PROMPT ================================================================

PROMPT DATABASE HEALTH CHECK REPORT

PROMPT ================================================================

PROMPT

PROMPT

PROMPT DATABASE STATUS

PROMPT =================

select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;

PROMPT

PROMPT

PROMPT DATABASE NAME AND MODE

PROMPT ========================

select name, open_mode, log_mode from v$database;

PROMPT

PROMPT

PROMPT DR DATABASE STATUS

PROMPT ===========================================================

Col APPLIED_TIME format a20

Col destination format a20

Col Status format a20

SELECT DB_NAME,destination,  APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,

(

  CASE

    WHEN ((APPLIED_TIME            IS NOT NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)

    OR (APPLIED_TIME               IS NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)

    OR ((LOG_ARCHIVED-LOG_APPLIED)  > 1))

    THEN 'Error! Log Gap is '

    ELSE 'OK!'

  END) Status,

   LOG_ARCHIVED-LOG_APPLIED LOG_GAP

FROM

  ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),

  (SELECT MAX(SEQUENCE#) LOG_ARCHIVED   FROM V$ARCHIVED_LOG    WHERE DEST_ID=1 ),

   (select applied_seq# as LOG_APPLIED,destination as destination  from v$archive_dest_status WHERE DEST_ID=3 ),

  (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME  FROM V$ARCHIVED_LOG  WHERE DEST_ID=1 );

PROMPT

PROMPT

PROMPT ARCHIVE LOG GENERATION LAST WEEK

PROMPT ===========================================================

SELECT A.*,  ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb

FROM

  (SELECT TO_CHAR (First_Time,'YYYY-MM-DD') DAY,COUNT (1) Count#, MIN (RECID) Min#,MAX (RECID) Max#,MIN(sequence#),MAX(sequence#)  

   FROM v$log_history  GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD')  ORDER BY 1 DESC ) A,

  (SELECT AVG (BYTES) AVG#,COUNT (1) Count#,MAX (BYTES) Max_Bytes,MIN (BYTES) Min_Bytes  

   FROM v$log ) B   where rownum < 8;

PROMPT

PROMPT

PROMPT DB PHYSICAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

PROMPT

PROMPT

PROMPT DB ACTUAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

PROMPT

PROMPT

PROMPT TABLESPACE USAGES

PROMPT ===========================================================

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (Mb)",

       SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

PROMPT

PROMPT

PROMPT NO of USER CONNECTED

PROMPT ===========================================================

select count(distinct username) "No. of users Connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT NO of SESSIONS CONNECTED

PROMPT ===========================================================

Select count(*) AS "No of Sessions connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT DISTINCT USERNAME CONNECTED

PROMPT ===========================================================

Select distinct(username) AS "USERNAME" from v$session;

PROMPT

PROMPT

PROMPT DICTIONARY HIT RATIO. THIS VALUE SHOULD BE GREATER 85%

PROMPT ==========================================================

select   (  1 - ( sum (decode (name, 'physical reads', value, 0)) / (  sum (decode (name, 'db block gets',value, 0)) + sum (decode (name, 'consistent gets', value, 0))))) * 100 "Buffer Hit Ratio" from v$sysstat;

PROMPT

PROMPT

PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 90%

PROMPT ===========================================================

select (sum(pins)/(sum(pins)+sum(reloads))) * 100 "Library Cache Hit Ratio" from v$librarycache;

PROMPT

PROMPT

PROMPT BLOCKING QUERY

PROMPT ===========================================================

select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;

PROMPT

PROMPT

PROMPT BLOCKER AND WAITER

PROMPT ===========================================================

Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;

PROMPT

PROMPT

PROMPT INVALID OBJECT COUNT LIST

PROMPT ===========================================================

select count(*),owner ,  object_type from dba_objects where status='INVALID' group by owner ,  object_type order by owner;

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

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

Script End.

Read more