Flashback RAC Database to Guarantee Restore Point ( GRP )

It is a good practice to create a guarantee restore point ( GRP ), before upgrading or patching databases.

Here is an example of how to create a guarantee  restore point ( GRP ), and how to flashback the RAC database to this guarantee  restore point ( GRP ).

1)Check and make sure database is running at archivelog mode.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
      from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN FLASHBACK_ON
---------- ------------- ----------- ------------
ARCHIVELOG YES           6622166     NO

2) Create the guarantee restore point.

SQL> select * from v$restore_point;
no rows selected

SQL> create table before_patching ( id number, name varchar2(30));
Table created.

SQL> create restore point BEFORE_PATCHING guarantee flashback database;
Restore point created.

SQL> create table after_GRP ( id number, name varchar2(30));
Table created.

SQL> select * from v$restore_point;

SCN       DT GUA STORAGE_SIZE TIME      REST NAME           CON_ID
-------- --- --- ------------ --------- ---- -------------- -------
 6623316 1   YES 4294967296   29-NOV-16 YES BEFORE_PATCHING 0

3) Applying the patches unsuccessfully.

 The rollback is required to flashback the database to guarantee restore point ( GRP ).

4) Shutdown RAC database,  and then start up current instance only in mount mode.

$ srvctl stop database -d RACTEST
$ srvctl start instance -d RACTEST -i RACTEST1 -o mount;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> flashback database to restore point BEFORE_PATCHING;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
       from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN  FLASHBACK_ON
---------- ------------  ----------  -----------------
ARCHIVELOG YES           6625602     RESTORE POINT ONLY

SQL> select count(*) from before_patching;

 COUNT(*)
----------
 0

SQL> select count(*) from after_GRP;  <---DB was flasged back to the correct point.
 select count(*) from after_GRP
 *
ERROR at line 1:
ORA-00942: table or view does not exist

5) Start up all the instances.

$ srvctl stop database -d RACTEST
$ srvctl start database -d RACTEST
$ srvctl status database -d RACTEST
Instance RACTEST1 is running on node racnode1
Instance RACTEST2 is running on node racnode2
Instance RACTEST3 is running on node racnode3
Instance RACTEST4 is running on node racnode4

6) Drop the guarantee  restore point ( GRP ).

SQL> drop restore point BEFORE_PATCHING;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

Transportable Tablespace on Physical Standby Database

SITUATION

We need refresh one schema with data about 300 GB from PROD database to TEST database for testing purpose. The production database is 7×24, but there is a physical standby database. so we use physical standby database for transportable tablespace activities without touching and impacting on 7×24 production environment. The physical standby database and test database are sitting on the same grid infrastructure. The schema has only one tablespace.

It will take up to 10 hours by using normal datapump export and import. Instead, it will only take half an hour by using transportable tablespace on physical standby database without impacting on primary  database.

ENVIRONMENT

Operating System : RedHat Linux 6
Oracle Grid Infrastructure : 4 nodes 12.1.0.1 Clustware
Oracle RAC PROD database : 11.2.0.4
Oracle RAC TEST database : 11.2.0.4
Schema & Tablespace Name : TRANSPORT

STEPS

  • Create a new schema if you want to keep the original name, otherwise system will generate a new one automatically by using parameter “REMAP_SCHEMA” when importing.
  • Export schema metadata excluding tables and indexes on source database.
$expdp system/password  directory=EXP_DIR dumpfile=transport_meta_no_table_index.dmp content=metadata_only schemas=transport  exclude=table exclude=index
  • Check and make sure the standby is in SYNC with primary

Query data dictionary view V$ARCHIVE_GAP on standby database.

SQL>select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from V$ARCHIVE_GAP;

Note: there are lots of bugs and performance issues for this view on some versions like 11.2.0.4.

If you have set up Data Guard for this database, it should be easy to check:

DGMGRL> show database verbose "STNBYDB";

Database - STNBYDB

Enterprise Manager Name: STNBYDB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 9.41 MByte/s
Real Time Query: OFF
Instance(s):
STNBYDB1
STNBYDB2
STNBYDB3
STNBYDB4 (apply instance)
  • Check and set the value of db_recovery_file_dest_size on standby.

SQL> show parameter recovery

NAME                       TYPE        VALUE
-------------------------- ----------- ------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4000G
  • Cancel the MRP(Managed recover process).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.
  • Create guaranteed restore points(GRP) on standby.
SQL> CREATE RESTORE POINT refresh_transport GUARANTEE FLASHBACK DATABASE;

Restore point created.
  • On primary defer the log_archive_dest_2, which is sending logs to standby
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.
  • Check the protection mode if it is MAX_AVAILABILITY AND MAX_PROTECTION then downgrade it to MAX_PERFORMANCE by:
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAX_PROTECTION MAX_PROTECTION

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.
  • Activate and open the standby database.
SQL> select open_Mode from v$database;

OPEN_MODE
-------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.
  • Check that the tablespace will be self-contained.
SQL>execute sys.dbms_tts.transport_set_check('TRANSPORT', true);
SQL> select * from sys.transport_set_violations;

==>Any violations must be resolved before the tablespaces can be transported

  • Place the tablespace in read only mode.
SQL> alter tablespace TRANSPORT read only;

Tablespace altered.
  • Do the Metadata export, and copy files.
$ expdp system/password dumpfile=transport_refresh.dmp        \
directory=EXP_DIR transport_tablespaces = TRANSPORT \
transport_full_check=y

Note: The parameters are different for different versions, like for 10g: transport_tablespace=y  tablespaces=TRANSPORT

RMAN> copy datafile 20 to '+DG2'; ( where DG2 is the diskgroup for TEST database )
  • Check the endianness of the target database and convert, if necessary ( NOT applicable to this situation ).
SQL>select * from v$transportable_platform order by platform_id;

If source and target endianness are the same , File conversion is NOT needed ( please refer to oracle doc for other requirements ).

If source and target Endianness are different , the file need to be converted by using RMAN on source server:

RMAN> convert tablespace TRANSPORT to platform='new platform name' ...

On target server:

RMAN> CONVERT DATAFILE .. to PLATFORM=' target platform name' from platform=' source platform name'
  • Import TTS metadata.
$ impdp system/password  DUMPFILE=transport_refresh.dmp DIRECTORY=EXP_DIR TRANSPORT_DATAFILES='+dg2/TEST/DATAFILE/TRANSPORT.588.860329689'

It’s not possible to import when tablespace already exists or when target schema is not created. If users don’t exist, DataPump provides an alternative by using remap_schema (for import utility we can create the schema) ie:

REMAP_SCHEMA=<source_user>:<target_user>

If tablespace already exists in target, we can use remap_tablespace parameter on impdp (there is no option in import but rename tablespace at source or the existing
one at target).

REMAP_TABLESPACE=(<source_tbs1>:<target_tbs1>,<source_tbs2>:<target_tbs2>,…)

  • Place the standby database in mount mode again.
SQL> STARTUP MOUNT;
  • Flashback to GRP, then shutdown and starup the standby database.
SQL> FLASHBACK DATABASE TO RESTORE POINT refresh_transport;
Flashback completed.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.
  • Start up the MRP again if not yet.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT USING CURRENT LOGFILE;

Database altered.
  • Enable the log_archive_dest_2 on primary , and check the standby database SYNC status as per previous step.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.
  • Drop guaranteed restore points(GRP):
RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
6836263465366 GUARANTEED 2014-10-07 11:55:19 REFRESH_TRANSPORT

SQL> DROP RESTORE POINT TRANSPORT_REFRESH;

Restore point dropped.
  • Import schema meta objects like packages, procedures and functions,etc.
SQL> select object_type,status,count(*) from dba_objects where owner='TRANSPORT' group by object_type,status;

OBJECT_TYPE STATUS COUNT(*)
------------ ------- -------
TRIGGER INVALID 6
TABLE VALID 175
INDEX VALID 383
LOB VALID 10


$impdp system/password  directory=EXP_DIR dumpfile=transport_meta_no_table_index.dmp content=metadata_only schemas=transport  exclude=table exclude=index

SQL> select object_type,status,count(*)
from dba_objects
where owner='TRANSPORT'
group by object_type,status;

OBJECT_TYPE STATUS COUNT(*)
------------ ------- -------
FUNCTION VALID 1
SYNONYM VALID 15
PACKAGE VALID 20
SEQUENCE VALID 6
TRIGGER VALID 6
TABLE VALID 175
PACKAGE BODY VALID 18
INDEX VALID 383
LOB VALID 10
VIEW VALID 11

10 rows selected.