Cloning a Database with RMAN (DATA GUARD Part 2)

0
32

Backup-based Duplication

All RMAN needs here is the location of the backup pieces (previously executed backup) which is accessible and rman reads the backup pieces and restores the spfile,controlfile,datafiles and archivelog files to perform the duplicate operation.

Details about this example:
Primary hostname – PRIMARY_HOST
Standby hostname – SECONDARY_HOST

TNS alias for Primary – SEADEV TNS alias for standby – SEAREPL

Primary DB_NAME Parameter=SEADEV      Primary DB_UNIQUE_NAME Parameter= SEADEV Standby DB_NAME Parameter=SEADEV     Standby DB_UNIQUE_NAME Parameter= SEAREPL

This is very simple to perform:

1- Create a backup of the source DB (if you do not have one already):
$ rman target=/
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

1) Take a backup of the database as follows (if you do not have one already):
RMAN > backup database plus archivelog;

or

RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset format ‘/oracle_backup/%d_%u’ database plus archivelog
include current controlfile;
release channel c1;
release channel c2;
}

2) Make the backup pieces available for duplicate operation.
++ If the duplicate is going to happen on different server, move the backup pieces to a new server using commands like ftp,scp etc.
++ If the duplicate is going to happen on the same server as target, then you can either retain them in the same location where the backup was done or copy it to a required location.


3) Create a password file for the auxiliary instance.
For unix copy the password file from the target database to $ORACLE_HOME/dbs and rename it.
For windows copy the password file from the target database to %ORACLE_HOME/database and rename it.


4) Create a initialization parameter for the auxiliary instance with only one parameter DB_NAME.
Since we are duplicating the database onto a separate server with the same filesystem as the original, we don’t need to convert the file names.
In this case, the PFILE is called « initFGUARD.ora » and is placed in the « $ORACLE_HOME/dbs » directory.

echo DB_NAME=’SEAREPL’ > $ORACLE_HOME/dbs/initSEAREPL.ora

# Convert file names to allow for different directory structure if necessary
#DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/PROD/,/u01/app/oracle/oradata/SEAREPL/)
#LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/PROD/,/u02/app/oracle/oradata/SEAREPL/)


5) Setup the tnsnames.ora file
Add the appropriate entries into the « tnsnames.ora » file in the « $ORACLE_HOME/network/admin » directory to allow connections to the target database from the duplicate server on the Duplicated Server.
SEADEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )  

SEAREPL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SECONDARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SEAREPL)
    )
  )  


6) Create directories (if needed) on Auxiliary Server
On the auxiliary DB, create a directory with a name that matches your SID:
cd $ORACLE_BASE/admin
mkdir SEAREPL mkdir SEAREPL/adump
mkdir SEAREPL/bdump
mkdir SEAREPL/cdump
mkdir SEAREPL/udump
mkdir SEAREPL/dpdump
mkdir SEAREPL/pfile
mkdir SEAREPL/scripts
mkdir -p $ORACLE_BASE/oradata/SEAREPL
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/trace
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/cdump


7) Copy the backup files from the source database to the destination server(optional)
That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use something like:
scp -r oracle@destination_server:/u01/app/oracle/fast_recovery_area/FGUARD/archivelog /u01/app/oracle/fast_recovery_area/FGUARD


8) Now start the auxiliary instance to no mount mode.

Unix Example
Just set the environment variables and start the instance.
% export ORACLE_SID=SEAREPL % export ORACLE_HOME=/home/oracle/ora11g
% export PATH=$ORACLE_HOME/bin:$PATH
% sqlplus « /as sysdba »
SQL > startup nomount


Windows Example
Create a service and then set the necessary environment variables and start the instance.
% oradim -new -sid SEAREPL set ORACLE_SID=SEAREPL set ORACLE_HOME=D:\Orahome\Ora11gr2
set PATH=D:\Orahome\Ora11gr2\bin;%PATH%
% sqlplus « /as sysdba »
SQL > startup nomount


9) Connect to the auxiliary instance from RMAN and perform the rman duplicate as follows:

For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing, we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).
$ export ORACLE_SID=FGUARD
$ rman AUXILIARY /
$ rman TARGET sys/password@SEADEV AUXILIARY /
$ rman CATALOG rman/password@CATALOG AUXILIARY /
$ rman TARGET sys/password@SEADEV CATALOG rman/password@CATALOG AUXILIARY /

RMAN > DUPLICATE DATABASE TO SEAREPL UNTIL TIME « TO_DATE(’29-MAY-2010 14:16:42′,’DD-MON-YYYY HH24:MI:SS’) »
SPFILE
set control_files=’D:\SEAREPL\c1.ctl’
set db_file_name_convert=’D:\Database\Ora11g2\ora11g2′,’d:\SEAREPL’
set log_file_name_convert=’D:\Database\Ora11g2\ora11g2′,’d:\SEAREPL’
BACKUP LOCATION ‘D:\dup11r2’
;

Here use of the BACKUP LOCATION clause identifies the type of duplication as having no target connection, no recovery catalog and being backup-based.

More Examples:
# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET’s current state.
DUPLICATE TARGET DATABASE TO FGUARD SPFILE NOFILENAMECHECK;


# Duplicate database to TARGET’s state 4 days ago.
DUPLICATE TARGET DATABASE TO FGUARD UNTIL TIME ‘SYSDATE-4’ SPFILE NOFILENAMECHECK;


# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO FGUARD SPFILE BACKUP LOCATION ‘/source/app/oracle/fast_recovery_area/FGUARD’ NOFILENAMECHECK;

Check Status of DB’s

These are several scripts to monitor the status of the databases.
You will be executing them frequently to check that the databases are in sync and that there are no gaps between them.

On Primary Database:
select protection_mode, protection_level, database_role, switchover_status  from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY

On Secondary Database:
select protection_mode, protection_level, database_role, switchover_status  from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED



On Primary Database:
alter system switch logfile;
archive log list;

On Secondary Database:
SELECT sequence#, first_time, applied

  FROM v$archived_log

 where  first_time > sysdate – 2 and creator = ‘LGWR’ — standby_dest = ‘YES’

 ORDER BY sequence# asc;

 SEQUENCE# FIRST_TIME         NEXT_TIME          APPLIED

———- —————— —————— ———

     13477 24/AUG/18 11:56:33 24/AUG/18 12:09:46 IN-MEMORY

     13476 24/AUG/18 07:19:30 24/AUG/18 11:56:33 YES

     13475 23/AUG/18 22:17:59 24/AUG/18 07:19:30 YES

     13474 23/AUG/18 17:24:53 23/AUG/18 22:17:59 YES

     13473 23/AUG/18 16:38:42 23/AUG/18 17:24:53 YES



Startup Standby if DB is closed:
shutdown immediate;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Open Standby in Read-Only Mode:
alter database recover managed standby database cancel;
alter database open read only; 


From Read-Only to Standby
shutdown immediate;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Nice Script to check that both databases are in sync
CLEAR SCREEN
ARCHIVE LOG LIST;
col host_name for a10
col db_unique_name for a10
col name for a10
select NAME,DB_UNIQUE_NAME,HOST_NAME,OPEN_MODE,DATABASE_ROLE from v$database, v$instance ;




Verify that the Physical Standby Database is Performing Correctly
Once you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database. To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.
1. On the standby database, identify the existing archived redo log files by querying the V$ARCHIVED_LOG view.
select * from
(SELECT sequence#, first_time, next_time, applied
  FROM v$archived_log
  ORDER BY sequence# desc
  FETCH FIRST 10 ROWS ONLY)
order by sequence#  ;


2. On the primary database, issue a number of ALTER SYSTEM SWITCH LOGFILE statements to archive a number of redo log files.
alter system switch logfile;
alter system switch logfile;
archive log list;

3. On the standby database, re-query the V$ARCHIVED_LOG view to verify the redo data was received and applied on the standby database.
select * from
(SELECT sequence#, first_time, next_time, applied
  FROM v$archived_log
  ORDER BY sequence# desc
  FETCH FIRST 10 ROWS ONLY)
order by sequence#  ;

 SEQUENCE# FIRST_TIME         NEXT_TIME          APPLIED
———- —————— —————— ———
     13473 23/AUG/18 16:38:42 23/AUG/18 17:24:53 YES
     13474 23/AUG/18 17:24:53 23/AUG/18 22:17:59 YES
     13475 23/AUG/18 22:17:59 24/AUG/18 07:19:30 YES
     13476 24/AUG/18 07:19:30 24/AUG/18 11:56:33 YES
     13477 24/AUG/18 11:56:33 24/AUG/18 12:09:46 IN-MEMORY



Enable Flashback on Both Servers

This operation will allow us to perform switchover and switchback extremely simple. This feature allows you to view the state of your database at a specified prior point in time. Oracle does this by keeping copies of all modified data blocks in flashback logs.
The Flashback logs are written in the Flash Recovery Area; a directory specified by a new parameter db_recovery_file_dest.
Suppose you deleted/modified the configuration information for your application. Instead of performing a recovery operation on this database (and having the end users screaming while the application is offline), you can just ask the database to “put the table back the way it was 5 minutes ago”.  
Oracle automatically creates and manages Flashback Logs within the Flash Recovery Area.

On The Primary Database:
alter system set db_flashback_retention_target=60 scope=both;
shutdown immediate;

On The Standby Database:
alter system set db_flashback_retention_target=60 scope=both;
shutdown immediate;
startup mount;
alter database flashback on;
select name,database_role,flashback_on from v$database;

NAME       DATABASE_ROLE    FLASHBACK_ON
———- —————- ——————
SEADEV        PHYSICAL STANDBY YES


On The Primary Database:
startup mount
alter database flashback on;
alter database open;
select name,database_role,flashback_on from v$database;

NAME       DATABASE_ROLE    FLASHBACK_ON
———- —————- ——————
SEADEV        PRIMARY          YES

This operation will allow us to perform switchover and switchback extremely simple. This feature allows you to view the state of your database at a specified prior point in time. Oracle does this by keeping copies of all modified data blocks in flashback logs.
The Flashback logs are written in the Flash Recovery Area; a directory specified by a new parameter db_recovery_file_dest.
Suppose you deleted/modified the configuration information for your application. Instead of performing a recovery operation on this database (and having the end users screaming while the application is offline), you can just ask the database to “put the table back the way it was 5 minutes ago”.  
Oracle automatically creates and manages Flashback Logs within the Flash Recovery Area.

On The Primary Database:
alter system set db_flashback_retention_target=60 scope=both;
shutdown immediate;

On The Standby Database:
alter system set db_flashback_retention_target=60 scope=both;
shutdown immediate;
startup mount;
alter database flashback on;
select name,database_role,flashback_on from v$database;

NAME       DATABASE_ROLE    FLASHBACK_ON
———- —————- ——————
SEADEV        PHYSICAL STANDBY YES


On The Primary Database:
startup mount
alter database flashback on;
alter database open;
select name,database_role,flashback_on from v$database;

NAME       DATABASE_ROLE    FLASHBACK_ON
———- —————- ——————
SEADEV        PRIMARY          YES

Set up the Data Guard Broker

The first step is to configure the LISTENER.ORA.
The broker uses the Oracle Net Services to make connections to the databases, setup both redo transport and archive gap resolution and perform role transitions. We need to create a special static entry in the listener.ora file for each database in the broker configuration, this entry makes it possible for the broker to connect to an idle instance using a remote SYSDBA connection and perform the necessary startup.

GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>> in listener.ora on all instances of both primary and standby.
This is important otherwise you’ll have TNS-12154 error during switchover operation.

In DEV Machine:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = DCMV-SWDB1D)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = SEADEV_DGMGRL)

      (ORACLE_HOME = E:\app\ORACLE_SVC\product\12.1.0\dbhome_1)

      (SID_NAME = SEADEV)

    )

  )

ADR_BASE_LISTENER = E:\app\ORACLE_SVC\product

In SEAREPL Machine:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = CLDV-SWREP1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = SEAREPL_DGMGRL)

      (ORACLE_HOME = E:\app\ORACLE_SVC\product\12.1.0\dbhome_1)

      (SID_NAME = SEAREPL)

    )   )

ADR_BASE_LISTENER = E:\app\ORACLE_SVC\product

And Re-start the Listener on all instances


If you want to use the Data Guard Broker, you will need first to start the Data Guard Monitor process (DMON) on both sites:

connect sys@SEADEV as sysdba
alter system set DG_BROKER_START=false scope=both;
!mkdir /home/oracle/DG
alter system set DG_BROKER_CONFIG_FILE1=’/home/oracle/DG/Broker_DG1.dat’ scope=both;
alter system set DG_BROKER_CONFIG_FILE2=’/home/oracle/DG/Broker_DG2.dat’ scope=both;

connect sys@SEAREPL as sysdba
alter system set DG_BROKER_START=false scope=both;
!mkdir /home/oracle/DG
alter system set DG_BROKER_CONFIG_FILE1=’/home/oracle/DG/Broker_DG1.dat’ scope=both;
alter system set DG_BROKER_CONFIG_FILE2=’/home/oracle/DG/Broker_DG2.dat’ scope=both;


As you can see, we defined the location of the DG Broker Files.
If you need to change the location of the files the steps are below
1- stop the broker using the DG_BROKER_START parameter by setting this to false (on all nodes in the cluster)
2- change the DG file destination parameters
3- copy the files to the new location
4- then re-start the broker, again set the DG_BROKER_START parameter to true (on all nodes in the cluster)

Now we are ready to start the Broker, so you need to perform the following step in both sites:
connect sys@SEADEV as sysdba
alter system set DG_BROKER_START=true scope=both;

connect sys@SEAREPL as sysdba
alter system set DG_BROKER_START=true scope=both;



Now we can enter data guard broker (dgmgrl) on any of the servers.
Connect as user sys using appropriate password. Sqlplus style ‘/ as sysdba’ cannot used with broker.
At any time, you can use help command in dgmgrl to get command list
$ dgmgrl
DGMGRL> connect sys

Now we create the configuration file:
DGMGRL> create configuration orcl_dg as primary database is SEADEV connect identifier is SEADEV;

Here you could get a:

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

This will happen if you are creating a configuration in Oracle 12c. You will not receive this error for earlier versions. According to the documentation, “any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared”.

So on both the primary and the standby databases, I need to clear this archive destination as follows:

SQL> alter system set log_archive_dest_2= » scope=both;

Then try again:

DGMGRL> create configuration dg_config as primary database is SEADEV connect identifier is SEADEV;


Two configuration files appear under $ORACLE_HOME/dbs, they are actually multiplexed copies of each other.
This gives an option of redundancy in production environments.
Creation place can be controlled via parameter DG_BROKER_CONFIG_FILE.

Now add physical standby instance SEAREPL
DGMGRL> add database SEAREPL as connect identifier is SEAREPL maintained as physical;

Let’s check its configuration:
DGMGRL> show configuration
Configuration – orcl_dg

  Protection Mode: MaxPerformance
  Databases:
    SEADEV  – Primary database
    SEAREPL – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


Now we can enable the newly created configuration
DGMGRL> enable configuration
DGMGRL> show configuration
Configuration – orcl_dg

  Protection Mode: MaxPerformance
  Databases:
    SEADEV  – Primary database
    SEAREPL – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Performing actions with the Data Guard Broker Manager

There are a number of commands that you can use to change the state of the database

Turn off/on the redo transport service for all standby databases   
On Primary
    DGMGRL> edit database SEADEV set state=transport-off;
    DGMGRL> edit database SEADEV set state=transport-on;

Turn off/on the apply state   
On Standby
    DGMGRL> edit database SEAREPL set state=apply-off;
    DGMGRL> edit database SEAREPL set state=apply-on;

Put a database into a real-time query mode   
On Standby
    DGMGRL> edit database SEAREPL set state=apply-off;
    sql> alter database open read only;
    DGMGRL> edit database SEAREPL set state=apply-on;

Change the protection mode   
On Primary
# Choose what level of protection you require
sql> alter database set standby to maximize performance;
sql> alter database set standby to maximize availability;
sql> alter database set standby to maximize protection;

Check the current configuration mode:
DGMGRL> show database SEADEV LogXptMode


Maximum Protection with Logical Standby Database
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxprotection;
show configuration;


Change of Protection Level and Transport Method
dgmgrl> connect sys@SEADEV
dgmgrl> show configuration verbose;
dgmgrl> show database verbose SEAREPL ;

Maximum Protection; here 2 Standby Databases are recommended. The changes are always done on Primary and Standby in case of a later SWITCHOVER.
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxprotection;
dgmgrl> show configuration;

Maximum Availability
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxavailability;
dgmgrl> show configuration;

Maximum Performance with LGWR-Transport
If there was a higher Protection Level beforehand, it must be lowered to Maximum Performance now
dgmgrl> edit configuration set protection mode as maxperformance;
dgmgrl> edit database SEADEV set property LogXptMode=ASYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=ASYNC;
dgmgrl> show configuration;

Maximum Performance with ARCH-Transport 
dgmgrl> edit configuration set protection mode as maxperformance;
dgmgrl> edit database SEADEV set property LogXptMode=ARCH;
dgmgrl> edit database SEAREPL set property LogXptMode=ARCH;
dgmgrl> show configuration;



Quick List of Equivalent Broker Commands to ‘ALTER SYSTEM’ :
SQL> alter database recover managed SEAREPL database cancel;
DGMGRL> edit database ‘SEAREPL’ set state=’LOG-APPLY-OFF’;

SQL> alter database recover managed SEAREPL database disconnect;
DGMGRL> edit database ‘SEAREPL’ set state=’ONLINE’;

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database ‘SEADEV’ set property ‘LogArchiveMaxProcesses’=4;

SQL> alter system set log_archive_dest_state_2=’enable’ scope=both;
DGMGRL> edit database ‘SEAREPL’ set property ‘LogShipping’=’ON’;

SQL> alter system set log_archive_dest_state_2=’defer’ scope=both;
DGMGRL> edit database ‘SEAREPL’ set property ‘LogShipping’=’OFF’;
DGMGRL> edit database ‘SEADEV’ set state=’LOG-TRANSPORT-OFF‘;


This will defer all standby databases



Add the Logical Standby to the Broker Configuration if that has been configured:

dgmgrl
dgmgrl> connect sys@SEADEV
dgmgrl> show configuration;
dgmgrl> add database RECO as connect identifier is RECO maintained as logical;
dgmgrl> enable database RECO;
dgmgrl> show configuration;



Switchover
Switchover: Primary and Standby exchange their roles
dgmgrl> connect sys@SEADEV
dgmgrl> switchover to SEAREPL;

After it completes, issue startup mount at old primary site, which is now new standby.
DGMGRL> show configuration

Configuration – orcl_dg
Protection Mode: MaxAvailability
Databases:

SEAREPL  – Primary database
SEADEV  – Physical standby database

Fast-Start Failover: DISABLED
Configuration Status
SUCCESS

You can switch roles back by issuing a new switchover command from dgmgrl
DGMGRL> switchover to PROD;
And issue again startup mount on previous primary after it has been brought down if neccessary.


Failover
Primary is lost, unaccessible or some other type of fault and we need to brind former standby open as new primary.
If old primary will come alive after failover, it will need either flashed back to the scn when the failover took place, or completely re-built from the new primary by duplication.
dgmgrl>failover to SEAREPL;

Performing failover NOW, please wait…
Failover succeeded, new primary is « SEAREPL »

DGMGRL> show configuration
Configuration – orcl_dg

Protection Mode: MaxAvailability
Databases:

SEAREPL – Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode

SEADEV – Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED
Configuration Status:
WARNING