RMAN on Multitenant DB – Awareness of the Backup Optimization Behavior

Recovery Manager (RMAN) is one of the most popular Oracle databases components with unique Backup/Recovery features. It is fully integrated with the Multitenant Architecture allowing to implement Manage Many-Databases-as-One strategy.

RMAN permits to customize and save several database parameters used during the backup and recovery operations. Such parameters define for example the backup retention policy, the default device type,  how many archivelog copy should be stored, if the backup-sets should be compressed and/or encrypted and so on…

 

Below an example of RMAN setup with the highlight of the parameter CONFIGURE BACKUP OPTIMIZATION ON discussed on the next sections.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name CEFUPRD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/Databases/CEFUPRD/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/BACKUP/Databases/CEFUPRD/%d_%T_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 10 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/fast_recovery_area/rcocefuprd/cefuprd/snapcf_cefuprd.f';

RMAN>

 

 

Effects of RMAN Backup Optimization ON/OFF

In a Multitenant environment is more important than ever to understand the effects of the parameter CONFIGURE BACKUP OPTIMIZATION which can be set to ON or OFF.

Behavion when set ON

If RMAN determines that a file is identical and it has been backed up, then it is a candidate to be skipped. RMAN must do further checking to determine whether to skip the file, however, because both the retention policy and the backup duplexing feature are factors in the algorithm that determines whether RMAN has sufficient backups on the specified device type. (Definition from Oracle Backup Recovery User’s Guide).

Behavion when set OFF

The RMAN backup always includes all files no matter if they are identical and already backed up within the backup retention window.

 

 

What happens by migrating from Non-CDB to PDB?

Assuming that we have just migrated a non-CDB database to PDB and our pluggable database has 4 tablespaces all open read/write.  The container uses the same RMAN setup included on the top of this page, with CONFIGURE BACKUP OPTIMIZATION ON.

Dispite having a FULL database backup every night, only 1 backup every 8 days will be complete and consistent, because the RMAN backup optimization algorithm will detect the SEED PDB datafiles unchanged and it will skip those files. Therefore if we restore the CDB using the backup-sets generated by one FULL database backup, with no access to the rest of backup-sets inside the retention window, there are great probabilities that the restore will fail.

 

Extract of the CDB backup log which shows that the PDB$SEED datafiles have been skipped because already backed up 1 time during the last 8 days.

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES; 

Starting backup at May 15 2018 00:35:07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 instance=clgbprd1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=168 instance=clgbprd1 device type=DISK
skipping archived logs of thread 1 from sequence 39516 to 39931; already backed up
skipping archived logs of thread 2 from sequence 34457 to 34749; already backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=34774 RECID=148645 STAMP=976088413
input archived log thread=2 sequence=34775 RECID=148649 STAMP=976088467
input archived log thread=1 sequence=39944 RECID=148655 STAMP=976088552
input archived log thread=2 sequence=34776 RECID=148651 STAMP=976088509
input archived log thread=2 sequence=34777 RECID=148653 STAMP=976088551
input archived log thread=2 sequence=34778 RECID=148657 STAMP=976088700
input archived log thread=1 sequence=39945 RECID=148662 STAMP=976088937
input archived log thread=2 sequence=34779 RECID=148659 STAMP=976088838

...
Starting backup at May 15 2018 00:50:02
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 2; already backed up 1 time(s)
skipping datafile 4; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
...

 

Using only the backup-sets above to restore the CDB means that Oracle has to recreate the two skipped datafiles (number 2 and 4) applying the archived logs generated during the initial CDB provisioning.

To note that the full backup starts including archived log from the following sequence:

  • For the Thread 1 – Sequence  39944
  • For the Thread 2 – Sequence  34774

But when Oracle initiates the Media Recovery, it complains because the archived log Thread 1 – Sequence 1 is unavailable:

RMAN> run {
allocate auxiliary channel dsk1 type disk ;
2> allocate auxiliary channel dsk2 type disk ;
allocate auxiliary channel dsk3 type disk ;
allocate auxiliary channel dsk4 type disk ;
3> allocate auxiliary channel dsk5 type disk ;
4> allocate auxiliary channel dsk6 type disk ;
5> duplicate database to 'CEFUAUX' noopen backup location '/BACKUP/Databases/CEFUPRD/backup_20180515_only' nofilenamecheck;
}6> 7> 8> 9>

allocated channel: dsk1
channel dsk1: SID=322 device type=DISK

allocated channel: dsk2
channel dsk2: SID=471 device type=DISK

allocated channel: dsk3
channel dsk3: SID=9 device type=DISK

allocated channel: dsk4
channel dsk4: SID=166 device type=DISK

allocated channel: dsk5
channel dsk5: SID=323 device type=DISK

allocated channel: dsk6
channel dsk6: SID=478 device type=DISK

Starting Duplicate Db at May 15 2018 09:29:15

....

contents of Memory Script:
{
 set until scn 2372623043;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at May 15 2018 11:24:39

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
Oracle instance started

 

I hope this example helped to understand that while migrating from non-CDB to Multitenant, many Administration tasks should be carefully reviewed due to major architecture changes.

 


 

Adding Pluggable Databases to an existing Multitenant Data Guard environment

We all know the benefits of the Oracle Multitenant cosolidation “Many-as-One”, one container (CDB) operation for wich many Pluggable Databases (PDBs) can take benefit; for example one CDB backup protects all PDBs stored inside the container itself.

While among the DBAs the setup of Oracle Data Guard is become more than a standandard routine,  described in thousands of Internet pages and blogs (one example available here), this post explains how to add new Pluggable Databases (PDBs) to an existing Multitenant environment protected by Data Guard.

 

How to create PDBs in Oracle Multitenant environment protected by Data Guard

There are multiple scenarios of PDB creation and they differently integrate within the Data Guard architecture. The easiest way to proceed consists in creating a new Pluggable Database using the SEED PDB:

  • PDB creation from SEED
    The creation of a brand new empty pluggable database is automatically replicated to each physical standby database. No additional action is required.

 

Unfortunately, this option is not always applicable because the new PDB should be a clone of an existing one. Therefore, it is important for the DBA to understand how integrating new non-empty Pluggable Database on a Multitenant and Data Guard environment without impacting the pre-existing setup.

 

  • PDB clone 

Cloning a PDB in a Data Guard environment requires few additional steps, which changes across the different Oracle versions and when Active Data Guard option is in use. While remote cloning the PDB the option STANDBYS=NONE should be used to defer the PDB replica to the Standby container. Then it is possible to replicate and protect the newly cloned PDB with Data Guard.

A full example of how to perform those tasks is reported below.

 

Cloning a PDB via DB Link using the STANDBYS=NONE option

create pluggable database PCJORD from PCLORD@ccls01_PCLORD tempfile reuse STANDBYS=NONE;

 

Open the newly cloned PDB

alter pluggable database PCJORD open instances=all;

 

On the Standby Container Restore PDB from Primary

run{
set newname for pluggable database PCJORD to new;
restore pluggable database PCJORD from service CMJP01;
switch datafile all;
}

 

Connect to the Standby container and STOP the Apply Process

dgmgrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-OFF';

 

If Active Data Guard is in use, re-start the Standby container in MOUNT

srvctl stop database -db CMJS01 

sqlplus / as sysdba
startup mount

 

Enable the PDB recovery on the Standby Container

alter session set container=PCJORD;
alter pluggable database enable recovery;

 

Connect to the Standby container and RE-START the Apply Process

dgmdrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-ON';

 

If Active Data Guard is in use Open the Container in Read Only

alter database open;

 

 


 

 

New to Oracle Multitenant?

Multitenant is the biggest architectural change of Oracle 12c and the enabler of many new database options in the years to come. Therefore I have decided to write over the time, few blog posts with basic examples of what should be done and not in a multitenant database environment.

 

Rule #1   – What should not be done

If you are a CDB DBA, always pay attention to which container you are connected to and remember that application data should be stored on Application PDB only!

Unfortunately this golden rule is not-enforced by the RDBMS, but it is left in our responsibility as shown on the example below:

oracle@lxoel7n01:~/ [CDB_TEST] sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 18:28:23 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

CDB$ROOT SQL>
CDB$ROOT SQL> show user
USER is "SYS"
CDB$ROOT SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

 

Once connected to the ROOT container let see if I can mistakenly create an application table:

CDB$ROOT SQL> CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB); 2 3 4 5 6

Table created.

CDB$ROOT SQL> desc emp_1
 Name                                Null?    Type
 ----------------------------------- -------- ----------------------------
 EMP_ID                                        NUMBER
 EMP_NAME                                      VARCHAR2(25)
 START_DATE                                    DATE
 EMP_STATUS                                    VARCHAR2(10)
 RESUME                                        CLOB


CDB$ROOT SQL> insert into emp_1 values (1, 'Emiliano', sysdate, 'active', ' ');

1 row created.

CDB$ROOT SQL> commit;

Commit complete.


CDB$ROOT SQL> select * from emp_1;

EMP_ID     EMP_NAME                  START_DAT EMP_STATUS RESUME
---------- ------------------------- --------- ---------- ----------------
 1          Emiliano                  21-SEP-16 active

CDB$ROOT SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

The answer is “YES” and the consequences can be devastating…

 

Rule #2   – Overview of Local and Common Entities

Non-schema entities can be created as local or common.  Local entities exist only in one PDB similar to a non-CDB architecture, while Common entities exist in every current and future container.

List of possible Local / Common entities in a Multitenant database:

  • Users
  • Roles
  • Profiles
  • Audit Policies

All Local entities are created from the local PDB and all Common entities are created from the CDB$ROOT container.

Common-user-defined Users, Roles and Profiles require a standard database prefix, defined by the spfile parameter COMMON_USER_PREFIX:

SQL> show parameter common_user_prefix

NAME                              TYPE        VALUE
--------------------------------- ----------- -----------------
common_user_prefix                string      C##

 

Example of Common User creation:

SQL> CREATE USER C##CDB_DBA1 IDENTIFIED BY PWD CONTAINER=ALL;

User created.


SQL> SELECT con_id, username, user_id, common

  2  FROM cdb_users where username='C##CDB_DBA1'  ORDER BY con_id;

    CON_ID USERNAME                USER_ID COMMON
---------- -------------------- ---------- ------
         1 C##CDB_DBA1               102    YES
         2 C##CDB_DBA1               101    YES
         3 C##CDB_DBA1               107    YES
         4 C##CDB_DBA1               105    YES
         5 C##CDB_DBA1               109    YES
         ...

 

Example of Local user creation:

SQL> show con_name

CON_NAME
------------------------------
MYPDB

SQL> CREATE USER application IDENTIFIED BY pwd CONTAINER=CURRENT;

User created.

If we try to create a Local User from the CDB$ROOT container the following error occurs: ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> CREATE USER application IDENTIFIED BY pwd   CONTAINER=CURRENT;

CREATE USER application IDENTIFIED BY pwd   CONTAINER=CURRENT

                                      *

ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

 

 

Rule #3  – Application should connect through user-defined database services only

We have been avoiding to create user-defined database services for many years, sometimes even for RAC databases. But in Multitenet or Singletenant architecture the importance of user-defined database service is even greater. For each CDB and PDB Oracle is still automatically creating a default service, but as in the past the default services should never be exposed to the applications.

 

To create user-defined database service in stand-alone environment use the package DBMS_SERVICE while connected to the corresponding PDB:

BEGIN
 DBMS_SERVICE.CREATE_SERVICE(
     SERVICE_NAME     => 'mypdb_app.emilianofusaglia.net',
     NETWORK_NAME     => 'mypdb_app.emilianofusaglia.net',
     FAILOVER_METHOD  =>
     ...
      );
 DBMS_SERVICE.START_SERVICE('mypdp_app.emilianofusaglia.net ');
END;
/

The database services will not start automatically after opening a PDB!  Create a database trigger for this purpose.

 

To create user-defined database service in clustered environment use the srvctl utility from the corresponding RDBMS ORACLE_HOME:

oracle@oel7n01:~/ [EFU1] srvctl add service -db EFU \
> -pdb MYPDB -service mypdb_app.emilianofusaglia.net \
> -failovertype SELECT -failovermethod BASIC \
> -failoverdelay 2 -failoverretry 90

 

List all CDB database services order by Container ID:

SQL> SELECT con_id, name, pdb FROM v$services ORDER BY con_id;

    CON_ID NAME                                     PDB
---------- --------------------------------------- -----------------

         1 EFUXDB                                   CDB$ROOT   <-- CDB Default Service 
         1 SYS$BACKGROUND                           CDB$ROOT   <-- CDB Default Service 
         1 SYS$USERS                                CDB$ROOT   <-- CDB Default Service 
         1 EFU.emilianofusaglia.net                 CDB$ROOT   <-- CDB Default Service 
         1 EFU_ADMIN.emilianofusaglia.net           CDB$ROOT   <-- CDB User-defined Service  
         3 mypdb.emilianofusaglia.net               MYPDB      <-- PDB Default Service 
         3 mypdb_app.emilianofusaglia.net           MYPDB      <-- PDB User-defined Service  

7 rows selected.

 

EZCONNECT to a PDB using the user-defined service:

sqlplus <username>/<password>@<host_name>:<local-listener-port>/<service-name>
sqlplus application/pwd@oel7c-scan:1522/mypdb_app.emilianofusaglia.net

 

 

Rule #4  –  Backup/Recovery strategy in Multitenant

As database administrator one of the first responsibility to fulfil is the “Backup/Recovery” strategy. The migration to multitenant database, due to the high level of consolidation density requires to review existing procedures. Few infrastructure operations, like creating a Data Guard or executing a backup, have been shifted from per-database to per-container consolidating the number of tasks.

RMAN in 12c covers all CDB, PDB backup/restore combinations, even though the best practice suggests to run the daily backup at CDB level, and in case of restore needed, the granularity goes down to the single block of one PDB.  Below are reported few basic backup/restore operations in Multitenant environment.

 

Backup a full CDB:

RMAN> connect target /;
RMAN> backup database plus archivelog;

 

Backup a list of PDBs:

RMAN> connect target /;
RMAN> backup pluggable database mypdb, hrpdb plus archivelog;

 

Backup one PDB directly connecting to it:

RMAN> connect target sys/manager@mypdb.emilianofusaglia.net;
RMAN> backup incremental level 0 database;

 

Backup a PDB tablespace:

RMAN> connect target /;
RMAN> backup tablespace mypdb:system;

 

Generate RMAN report:

RMAN> report need backup pluggable database mypdb;

 

Complete PDB Restore

RMAN> connect target /;
RMAN> alter pluggable database mypdb close;
RMAN> restore pluggable database mypdb;
RMAN> recover pluggable database mypdb;
RMAN> alter pluggable database mypdb open;

 

 

Rule #5  –  Before moving to Multitenant

Oracle Multitenant has introduced many architectural changes that force the DBA to evolve how databases are administered. My last golden rule suggests to thoroughly study the multitenant/singletenant architecture before starting any implementation.

During my experiences implementing multitenant/singletenant architectures, I found great dependencies with the following database areas:

  • Provisioning/Decommissioning
  • Patching and Upgrade
  • Backup/recovery
  • Capacity Planning and Management
  • Tuning
  • Separation of duties between CDB and PDB

 

 

Duplicate database from active database

— Source database     = TRAC
— Duplicate database = TDUP10

##############################
## PREREQUISITES for Cloning
##############################

## Create the diag structure
 mkdir -p <diag_directory_DB_Name>
## Generate the PFILE from the Original DB and copy to the target host:
 alter system create pfile'/tmp/pfile.ora' from spfile;
 scp /tmp/pfile.ora lclus01:/tmp
## Adjust the following pfile parameters:
 DB_NAME
 LOG_FILE_NAME_CONVERT
 DB_FILE_NAME_CONVERT
 DB_CREATE_FILE_DEST
##Statup nomount and create the spfile:
 alter system create spfile='+DATA/TDUP10/spfile_TDUP10.ora' from pfile'/tmp/pfile.ora';
#############################################################
##############################
 Network Configuration
 ##############################
##Listener Static Entry:
 SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = /u01/oracle/product/11.2.0.1)
 (PROGRAM = extproc)
 )
 (SID_DESC =
 (global_dbname = TDUP10.emilianofusaglia.net)
 (ORACLE_HOME = /u01/oracle/product/11.2.0.1)
 (sid_name = TDUP11)
 )
 )
##TNS entry
 TDUP10.emilianofusaglia.net =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = loraclu-scan.emilianofusaglia.net)(PORT = 1526))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = TDUP10.emilianofusaglia.net)
 )
 )
#############################################################
##############################
 ##RMAN Duplicate
 ##############################
#rman
 connect target /
 connect auxiliary sys/xxxxx@TDUP10.emilianofusaglia.net
 duplicate target database
 to TDUP10
 from active database
 DB_FILE_NAME_CONVERT 'TRAC','TDUP10'
 skip tablespace POR_READONLY;
##Register the database into the Grid Infrastructure
 srvctl add database -d TDUP10 -o $ORACLE_HOME
 srvctl add instance -d TDUP10 -i TDUP11 -n lclus01
 srvctl add instance -d TDUP10 -i TDUP12 -n lclus02
##Create the password file on each node using the utility orapwd.
 #cd $ORACLE_HOME/dbs
 #orapwd file=<password_file_name> password=<sys_password> entries=n
## Add oratab entry
 <DB_NAME>:<ORACLE_HOME>:N

Oracle 11gR1 Data Guard Setup

############################################################
## DATA GUARD IMPLEMENTATION on 11g R1 RAC ##
############################################################

--Primary DB: USA10
--Standby DB: EURO10

############################
--From the Primary Database:
alter database force logging;

alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;

select * from v$standby_log;

 alter system set parallel_execution_message_size=8192 scope=spfile;
 alter system set fast_start_mttr_target=3600;
 
############################
--From the Standby Site:

--Dump the pfile and change the following parameters for the Standby:
*.control_files='+DATA1/EURO10/CONTROLFILE/CURRENT01.CTR','+FRA1/EURO10/CONTROLFILE/CURRENT02.CTR'
*.db_file_name_convert='/USA10/','/EURO10/'
*.log_file_name_convert='/USA10/','/EURO10/'
*.db_unique_name='EURO10'
--AS this is a single instance RAC
_disable_interface_checking = TRUE


LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01.emilianofusaglia.net)(PORT = 1526))
  )

alter system set local_listener='LISTENER' scope=spfile sid='EURO11';
---
mkdir -p /u01/oracle/admin/EURO10/adump
mkdir -p /u01/oracle/diag/rdbms/euro10/EURO11/cdump


######################################################################
 ################## #Network Congiguration ########################
######################################################################


##########################################
## Static Listener Entries:
##########################################

--Primary Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
  )

--Primary Cluster Node 2
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
  )

--Primary Cluster Node 3
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
  )

--Standby Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
  )


##########################################
##TNS Entries Primary & Standby Cluster
##########################################
EURO11.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
      (INSTANCE_NAME = EURO11)
    )
  )

EURO10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
    )
  )

USA10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan01-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan02-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan03-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = USA10.emilianofusaglia.net)
    )
  )


##########################################
## Standby Controlfile Setup ##
##########################################

alter database create standby controlfile as '/u01/oracle/emiliano/USA10.stby.ctl';

# scp  /u01/oracle/emiliano/USA10.stby.ctl lneuron01:/tmp


### Execute the following steps:
Startup nomount pfile='/u01/oracle/emiliano/PFILES/EURO10_for_GD.ora';
Create the spfile on ASM
Startup nomount exclusive;


## Do not RESTORE the Controlfile automatically DONE by the CLONE Procedure
## Which update also Contro_file parameter into SPFIE!!!!
rman target /
RMAN> restore controlfile from '/u01/oracle/emiliano/USA10.stby.ctl';

---
##########################################
## Duplicate the Database
##########################################

##From Primary DB Restore the DB to the Standby Side:
rman
connect target sys/xxxxxxx@USA10                             ----PS No domain for Target
connect auxiliary sys/xxxxxxx@EURO10.emilianofusaglia.net      ----PS Use domain for Auxiliary

run {
   allocate channel p1 type disk;
   allocate auxiliary channel s1 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

--or

run {
   allocate channel p1 type disk;
   allocate channel p2 type disk;
   allocate channel p3 type disk;
   allocate channel p4 type disk;
   allocate auxiliary channel s1 type disk;
   allocate auxiliary channel s2 type disk;
   allocate auxiliary channel s3 type disk;
   allocate auxiliary channel s4 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

##########################################
## Register the Standby Database to CRS
##########################################

srvctl add database -d EURO10 -o $ORACLE_HOME
srvctl add instance -d EURO10 -i EURO11 -n lneuron01

##########################################
## Data Guard Broker Configuration
##########################################

--Primary
alter system set dg_broker_config_file1 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig02.dat';

--Standby
alter system set dg_broker_config_file1 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig02.dat';

--on both databases
alter system set dg_broker_start=true;

--From a primary node connect to the broker and create the configuration

#dgmgrl

DGMGRL> connect sys/xxxxxxxx@USA10.emilianofusaglia.net
Connected.

create configuration 'CONFDG10' as primary database is 'USA10' connect identifier is USA10.emilianofusaglia.net;
   
add database 'EURO10' as connect identifier is EURO10.emilianofusaglia.net;

edit database 'USA10' set property 'LogXptMode' = 'SYNC';
edit database 'EURO10' set property 'LogXptMode' = 'SYNC';

edit database 'USA10' set property 'LogXptMode' = 'ASYNC';
edit database 'EURO10' set property 'LogXptMode' = 'ASYNC';

--edit configuration set protection mode as maxavailability;
edit configuration set protection mode as maxprotection;
--edit configuration set protection mode as maxavailability;
--edit configuration set protection mode as maxperformance;
enable configuration;

edit database 'USA10' set property 'NetTimeout' = '20';
edit database 'EURO10' set property 'NetTimeout' = '20';

exit;

DGMGRL> SWITCHOVER to "EURO10";

--Stop Recovery
edit database 'EURO10' set state = 'APPLY-OFF';
--Start Recovery
edit database 'EURO10' set state = 'APPLY-ON';

--Enabling ArchiveLog Tracing on Primary and Standby Good for Troubleshooting!!
edit instance 'USA11' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA12' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA13' on database 'USA10' set property 'LogArchiveTrace' = '1';    

edit instance 'EURO11' on database 'EURO10' set property 'LogArchiveTrace' = '6345';    

##############################################################################

RMAN Backup Recovery Test

#######################################################
##                RMAN Setup, Backup and Recovery Test                     ##
#######################################################

–Basic tips for Disk & Tape Backup/Recovery strategy.

--To be able to efficiently perform incremental backup check if "Change Block Tracking" is enable:
 SELECT * FROM V$BLOCK_CHANGE_TRACKING;

–If it doesn’t exist create it:
alter database enable block change tracking using file ‘+FRA1’;

###########################
# RMAN Format Description
###########################

%a Current database activation id
%A Zero-filled activation ID
%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256
%d Database name
%D Current day of the month from the Gregorian calendar in format DD
%e Archived log sequence number
%f Absolute file number
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h Archived redo log thread number
%I DBID
%M Month in the Gregorian calendar in the format MM
%n Database name, padded on the right with x characters to a total length of eight characters
%N Tablespace name. Only valid when backing up datafiles as image copies.
%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1
 for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT
 string either explicitly or implicitly within %U.
%r Resetlogs ID
%s Backup set number. This number is a counter in the control file that is incremented for each backup set.
 The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup
 control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.
%S Zero-filled sequence number
%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time.
 The combination of %s and %t can be used to form a unique name for the backup set.
%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD
%u An 8-character name constituted by compressed representations of the backup set or image copy number and the
 time the backup set or image copy was created
%U A system-generated unique filename (default). %U is different for image copies and backup pieces.
 For a backup piece, %U is a shorthend for %u_%p_%c and guarantees uniqueness in generated backup filenames.
 For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u

%Y Year in this format: YYYY

%% Percent (%) character. For example, %%Y translates to the string %Y

################################################################

#####################
## RMAN STEUP
#####################

connect target /
 connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net
REGISTER DATABASE;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
 CONFIGURE BACKUP OPTIMIZATION ON;
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA1/%F';
 CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' RATE 200M PARMS 'ENV=(NB_ORA_POLICY=ora_monthly)' FORMAT 'backup_%d_%T_%U';
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 200M FORMAT '+BACKUP/%d_%T_%U';
 CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA1/snapcf_dbrm07.cf';
--Full Database backup
 BACKUP INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;
--Incremental Database backup
 BACKUP INCREMENTAL LEVEL = 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;
--RMAN Catalog Maintenance and Backup Validation
 SHOW ALL;
 run{
 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
 allocate channel tape1 type 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLIENT=lxrm01.emilianofusaglia.net)';
 crosscheck backup completed before "sysdate-30";
 delete noprompt expired backup;
 delete noprompt obsolete;
 }
--Rman basic Backup Report
 SHOW all;
 list backup summary;

################################################################
##                                        RESTORE TESTS
################################################################

–Restore Datafile from TAPE

SQL> startup mount
 ORACLE instance started.
Total System Global Area 4277059584 bytes
 Fixed Size                  2154936 bytes
 Variable Size            2499812936 bytes
 Database Buffers         1728053248 bytes
 Redo Buffers               47038464 bytes
 Database mounted.
 SQL>
 SQL>
 SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_  ERROR                       CHANGE#    TIME
 ---------- ------- -------  -------------------------- ----------  --------
 9 ONLINE  ONLINE   FILE NOT FOUND                     0
RMAN> connect target /
connected to target database: DBRM07 (DBID=3653795552, not open)
RMAN> connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net
connected to recovery catalog database
RMAN> restore datafile 9;
Starting restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=1393 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: SID=1422 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_3
 channel ORA_DISK_3: SID=1451 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_4
 channel ORA_DISK_4: SID=1480 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_5
 channel ORA_DISK_5: SID=1509 instance=DBRM071 device type=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: SID=1538 instance=DBRM071 device type=SBT_TAPE
 channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010120208)
channel ORA_SBT_TAPE_1: starting datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 channel ORA_SBT_TAPE_1: restoring datafile 00009 to +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 13ld31mb_1_2
 channel ORA_SBT_TAPE_1: piece handle=13ld30mb_1_2 tag=TAG20110406T121258
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:16
 Finished restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete
RMAN> recover datafile 9;
Starting recover at 07.04.11
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 using channel ORA_DISK_3
 using channel ORA_DISK_4
 using channel ORA_DISK_5
 using channel ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 destination for restore of datafile 00009: +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 1eld30qo_1_2
 channel ORA_SBT_TAPE_1: piece handle=1eld30qo_1_2 tag=TAG20110406T121520
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:25
starting media recovery
archived log for thread 1 with sequence 162 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_162.16655.718385527
 archived log for thread 1 with sequence 163 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_163.8452.718385527
 archived log for thread 2 with sequence 141 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_141.4677.718385531
 archived log for thread 3 with sequence 132 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_132.5466.718385525
 archived log for thread 3 with sequence 133 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_133.1479.718385525
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=3 sequence=131
 channel ORA_SBT_TAPE_1: reading from backup piece 1hld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1hld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 thread=3 sequence=131
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=1 sequence=161
 channel ORA_SBT_TAPE_1: reading from backup piece 1ild30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1ild30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:58
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 thread=1 sequence=161
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=2 sequence=140
 channel ORA_SBT_TAPE_1: reading from backup piece 1jld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1jld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 thread=2 sequence=140
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 RECID=824 STAMP=718387211
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 RECID=825 STAMP=718387319
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 RECID=823 STAMP=718387086
 media recovery complete, elapsed time: 00:00:02
 Finished recover at 07.04.11