Advanced Oracle Data Guard Administration

Oracle Data Guard manual describes operations such as switchover, failover, backup/recovery quite well. In this blog, I will share the following advanced data guard operations that are either not described in the manual or need some more information:

• Leverage Flash Recovery Area for archive log management
• Rename Data File on the primary database server
• Rename Data File on the standby database server
• Manage TEMP files on the standby database server

My test environment is on Windows platform with Oracle 10g R2 10.2.0.4. Physical standby database is operating in maximum performance mode.

1. Leverage Flash Recovery Area for archive log management

Primary Database in Oracle Data Guard continuously ships redo vectors to the physical standby database to keep it in synch with the primary. During archive log switch operation, Oracle creates a new archive log file on the standby server. Use of Flash recovery area is definitely advantageous as you don't need to manage archive logs on the physical standby. We use flash recovery area to store and manage archive logs on physical standby. With Oracle flash recovery area, Oracle delete archive logs after applying them to standby database. Works pretty well. And simple setup as shown below:

SQL> connect / as sysdba

Connected.

SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;

alter system set DB_RECOVERY_FILE_DEST='e:\oraflash'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=5g ;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;

System altered.

SQL> alter system set  LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

Now configure archive log deletion policy.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

2. Rename Data files on Primary DB

Oracle documentation explains the procedure to rename a data file on the primary database participating in a data guard managed physical standby configuration. Per Oracle documentation, you rename the data file on primary server and then rename it on the standby database server as well.

A test, described below, shows that you don't need to repeat the rename step on physical standby server. 

Step - 1

Check the name of the data file on physical standby database:

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS11.ORA                            52428800

Step 2

Rename the data file on the primary database server

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGP\USERS11.ORA                             52428800

1 rows selected.

SQL> ALTER TABLESPACE USERS OFFLINE ;

Tablespace altered.

REM Rename USERS11.ORA to USERS21.ora on the primary database server at the OS level.

SQL> ALTER TABLESPACE USERS

  2  RENAME DATAFILE 'E:\ORADATA\DGP\USERS11.ORA'

  3  TO  'E:\ORADATA\DGP\USERS21.ORA' ;

Tablespace altered.

SQL> ALTER TABLESPACE USERS ONLINE ;

Tablespace altered.

REM Resize the file on primary to ensure propagation of resize operation

SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS21.ORA' RESIZE 250M ;

Database altered.

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGP\USERS21.ORA                          262144000

1 rows selected.

Step - 3

Verify the name of the data file on physical standby database after renaming it on the primary

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS11.ORA                          262144000

1 rows selected.

3. Rename Data File on Physical Standby

We recently ran out of disk space on the physical standby database server. In ideal world, you would like to have your physical standby database server mirror the primary database server in terms of processors, memory and disk space, but that wasn't the case in our environment. On primary database server, Oracle data files were on E drive which was 500GB in size. We also had E drive on the physical standby database server, but it was smaller in size.

E drive on the physical standby database server was completely full, so we wanted to move few files from E drive to F drive on the physical standby database server to make some space on the drive.

Like a good "DBA", before renaming a data file on standby, we performed a test in a test environment as shown below:

Step 1

Rename Data File on Physical Standby Database Server

SQL> alter database recover managed standby database cancel ;

Database altered.

After cancelling managed recovery on the standby server, we attempted to rename the database at the OS level, but it failed with "File locked" error message. So let's shutdown the database to release locks on data files.

SQL> shutdown immediate ;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

After shutting down the database, we successfully renamed the file at the OS level. A USERS01.ORA file was renamed to USERS31.ORA at the OS level.

SQL> STARTUP MOUNT ;

ORACLE instance started.

Total System Global Area  402653184 bytes

Fixed Size                  1296988 bytes

Variable Size             117441956 bytes

Database Buffers          281018368 bytes

Redo Buffers                2895872 bytes

Database mounted.

SQL> ALTER DATABASE

  2  RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'

  3* TO  'E:\ORADATA\DGS\USERS31.ORA' ;

ALTER DATABASE

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL>

SQL> ALTER DATABASE

  2  RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'

  3  TO  'E:\ORADATA\DGS\USERS31.ORA' ;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Step 2

Resize the corresponding file on the primary database server. The purpose of this step is to verify that file resize operation on this file is replicated to the standby database server.

SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS01.ORA' RESIZE 300M ;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

Step 3

Verify the file size on the physical standby database server. In addition, open the physical standby database in read only mode to verify.

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS31.ORA                          314572800

1 rows selected.

SQL> alter database recover managed standby database cancel  ;

Database altered.

SQL> alter database open ;

Database altered.

To summarize, renaming the data file on physical standby works.

4. Rename TEMP files on Standby

In data guard managed physical standby environment, Oracle manages TEMP files differently. During the initial standby database creation, RMAN creates TEMP files on the standby database server. From this point onwards, TEMP file management is on standby independent of the primary database. You should be able to perform the following on standby server independently:

• Resize TEMP files on standby
• Move TEMP files on standby
• Create additional TEMP files on standby

If you are using the physical standby database for reporting purposes and if you need additional TEMP space on standby, you should be able to perform these tasks independent of the primary database

Learn It Yourself: Oracle Flashback Features

The script below is the one I had used to learn more about Oracle Flashback features and then to teach development teams how to use flashabck features. I have embeded some comments within the script to explain few points. Just copy and run the script to get better understanding of Oracle Flashback features. Enjoy!

REM *****************************************************************
REM Name: OraFlash.sql
REM Author: Jay Mehta
REM Description: A script to demonstrate Oracle flashback features
REM *****************************************************************
REM
REM **** drop Scripts
DROP TABLE TEST PURGE;
DROP SEQUENCE TEST_SEQ;
DROP RESTORE POINT before_insert ;
DROP RESTORE POINT before_upper_update ;
DROP RESTORE POINT before_lower_update ;
DROP RESTORE POINT before_delete ;
REM *****************************************************************
REM
REM *****************************************************************
ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY:HH24:MI:SS';
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
REM
REM **** Create a test table
REM
REM This table will be used in test cases
CREATE TABLE TEST
(   ID NUMBER(9),
    NAME VARCHAR2(30),
    TIME DATE ) ;
REM
REM **** You must enable row movement to perform FLASHBACK .. TO TIMESTAMP/SCN/RESTORE POINT
REM **** FLASHBACK ..... BEFORE DROP doesn't require Row Movement Enabled.
REN
ALTER TABLE TEST ENABLE ROW MOVEMENT;
REM To show that index and trigger are also flashed back by FLASHBACK ..... BEFORE DROP   
CREATE UNIQUE INDEX TEST_PK
ON TEST (ID);
ALTER TABLE TEST
ADD CONSTRAINT TEST_PK
PRIMARY KEY(ID);
CREATE SEQUENCE TEST_SEQ;
REM **** Create a trigger on Test Table
CREATE OR REPLACE TRIGGER TEST_BRI_TRG
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
     SELECT TEST_SEQ.NEXTVAL
     INTO :new.id
     FROM dual;
     IF SQL%notfound THEN
       raise_application_error(-20031,
           'The sequence generator IS NOT wo;king');
     END  IF;
  END IF;
END;
/

REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_insert ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
REM **** Insert Data
INSERT INTO TEST(ID,NAME,TIME)
VALUES(1,'Jay',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(2,'Jim',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(3,'John',SYSDATE);
COMMIT ;
SELECT * FROM TEST ;
REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_upper_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
UPDATE TEST
SET NAME = UPPER(NAME);
COMMIT;
SELECT * FROM TEST ;
REM
CREATE RESTORE POINT before_lower_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;

Prompt ******* Update to Lower Case
UPDATE TEST
SET NAME = LOWER(NAME);
COMMIT;
SELECT * FROM TEST ;

CREATE RESTORE POINT before_delete ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
Prompt ******* Delete Data
DELETE FROM TEST ;
COMMIT;
Prompt ***** Data in Test Table at this moment
SELECT *
FROM TEST ;
Prompt ************  Update Timestamp as appropriate
REM Flashback Querty Examples with TIMESTAMP clause
REM ***** Data in Test Table 2 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 2/84600) ;
Prompt ***** Data in Test Table 3 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 3/84600) ;
Prompt ***** Data in Test Table 4 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 4/84600) ;
Prompt ***** Data in Test Table 5 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 5/84600) ;
Prompt ***** Data in Test Table 6 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 6/84600) ;
Prompt ***** Data in Test Table 7 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 7/84600) ;
Prompt ***** Data in Test Table 1 hour ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 1/24) ;
SELECT *
FROM V$RESTORE_POINT ;
REM
REM Flashback Row History - Retrieve Version History
REM Update Timestamp appropeiately
REM
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME,
       DECODE( VERSIONS_OPERATION
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation" ,
             ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_XID;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP TO_DATE('09/08/2010:10:30:20','MM/DD/YYYY:HH24:MI:SS')  AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1/1440 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;

REM ***** Flashback Row History - Undo Mistakes
REM
-- Get the UNDO Statement to undelete row
SELECT start_timestamp, commit_timestamp, operation, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid in ( SELECT  hextoraw(VERSIONS_XID)
        FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE )
order by start_scn;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
TABLE_NAME='TEST'
ORDER BY START_SCN ;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
XID = '0009002F0001CB51'
-- XID = HEXTORAW('0009002F0001CB51')
ORDER BY START_SCN ;

REM Flashback Table - Undo Mistakes
REM

SELECT * FROM TEST ;
REM Flashback Before Delete
REM User SCN, TIMESTAMP or RESTORE POINT
REM
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:08', 'MM/DD/YYYY:HH24:MI:SS' )) ;
REM FLASHBACK TABLE TEST TO SCN 2342345234 ;
FLASHBACK TABLE TEST TO RESTORE POINT before_delete;
SELECT * FROM TEST ;

-- Flashback Before Last Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:05', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_upper_update;
SELECT * FROM TEST ;

-- Flashback Before First Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/09/2010:07:00:00', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_lower_update;
SELECT * FROM TEST ;
REM
REM Flashback Drop - Recover Dropped Tables
REM
DROP TABLE TEST;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;
FLASHBACK TABLE TEST TO BEFORE DROP  RENAME TO TEST1 ;
SELECT * FROM RECYCLEBIN ;
SELECT TABLE_NAME FROM USER_TABLES ORDER BY 1;
SELECT * FROM TEST;

-- permanent drop
DROP TABLE TEST PURGE ;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;