Wednesday, 31 March 2021

1Z0-083:Oracle Database Administration II Training and dumps

Training. Please visit

Oracle 12 And 19c Multitenant Architecture – EasyReliable

For oracle database and cloud dumps

Oracle Database Administration II Exam Number: 1Z0-083| Oracle Database19c Dumps@easyreliable

EasyDumps – EasyReliable

(492) Oracle Database Administration II Exam Number: 1Z0-083| Oracle Database19c Dumps@easyreliable - YouTube

on No comments:

Thursday, 25 March 2021

Oracle RMAN Backup Compression : with Case study

 

Oracle 11gR2 RMAN Backup Compression : with Case study

RMAN Backup Compression : Oracle 11g Release 2 

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN  backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands:

Binary compression creates some performance overhead during backup and restore operations. Binary compression consumes CPU resources, so compressed backups should not be scheduled when CPU usage is already high. However, the following circumstances may warrant paying the performance penalty:

  • You are using disk-based backups when disk space in your flash recovery area or other disk-based backup destination is limited.
  • You are performing your backups to some device over a network when reduced network bandwidth is more important than CPU usage.
  • You are using some archival backup media such as CD or DVD, where reducing backup sizes saves on media costs and archival storage.


Commands to be used:

Step-1: Set RMAN configuraton for backup type:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

Step-2: Set RMAN configuraton for compresion algorithim type:

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Step-3: Chnage your bacup script :
rman > backup as compressed backupset incremental level 0 database;
OR
connect target `/'
run
{
allocate channel ch1 device type disk;
backup as compressed backupset incremental level 0 database;
release channel ch1;
}

Example:

RMAN> configure device type disk backup type to compressed backupset;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
configure device type disk backup type to compressed backupset parallelism 1;
new RMAN configuration parameters are successfully stored

RMAN> show all;
configure device type disk backup type to compressed backupset parallelism 1;

RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

It is possible to compress archived redo log and incremental backupsas follows:

RMAN> backup as compressed backupset archivelog all;
RMAN> backup as compressed backupset incremental level 0 database;
RMAN> backup as compressed backupset incremental level 1 database;
RMAN> backup as compressed backupset incremental level 1 cumulative database;

To de-configure:

By changing this parameter, all backups will be compressed.  To return to the default non-compressed configuration, clear the configuration to make future backups be non-compressed:

RMAN> configure device type disk clear;
old RMAN configuration parameters:
configure device type disk backup type to compressed backupset parallelism 1;
RMAN configuration parameters are successfully reset to default value
RMAN> show all;
configure device type disk parallelism 1 backup type to backupset; # default

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of

LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size: 276.55M
time: 00:00:50

To summaries we can conclude:

LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

Note : Binary compression can be CPU-intensive. You can choose which compression algorithm that RMAN uses for backups. By default, RMAN uses BZIP2, which has a very good compression ratio. ZLIB compression, which requires a COMPATIBLE setting of 11.0.0 or higher, is very fast but has a lower compression ratio than other algorithms.

As shown, the compressed backup is smaller in size than the uncompressed backup. To identify which files were compressed, query v$backup_files as follows:

sql> column fname format a90;
sql> set lines 180;
sql> set pages 500;
SQL> select fname, compressed from v$backup_files; 

FNAME                     COMPRESSED
-----------               -----------
C:\uncompressed_sysaux.bkp       NO
C:\copmressed_sysaux.bkp           YES

I have tested in one of my production database environment with following configurations:
-----------------------------------------------------------------------------------------------
-- Before compression method ( When CPU% was <10%)
a) Backup size :  20.68G
b) Elapsed Time : 6 min ( max)
-- Applied following commands and see the statistics
1) RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
2) CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
3) backup as compressed backupset incremental level 0 database;

-- After applied compression method ( When CPU% was <10%)
a) Backup size :  4.03G ( around 80% reduced)
b) Elapsed Time : 8 min ( around 30% time consumption increased)

So based on calculation and test scenarios, you decide what type of compression method you will follow. But It is best practice when CPU utilization is very very less for longer duration in off peak hours in OLTP database environments, you can go for any compression method.
on No comments:

DBCA NEW FEATURES ORACLE 19C

 In Oracle 19c a number of new features have been added to DBCA in silent mode.

In Oracle 18c, we could clone a PDB both via the GUI DBCA as well as using the dbca -silent -createPluggableDatabase command.

New in Oracle 19c is the ability to create a remote clone of a PDB as well as perform the relocate of a PDB using the DBCA silent mode.

We can use the createFromRemotePDB parameter of the DBCA command createPluggableDatabase to create a PDB by cloning a remote PDB as well as use the relocatePDB option to relocate a PDB from one container database to another.

Let us have a look at a worked example.

Here is the 19c environment:

a) host01: CDB1 (PDB1,PDB2)
b) host02: CDB2 (PDB2)

We will remote clone PDB1 from CDB1 and create the pluggable database in the container database CDB2.

We will then create another pluggable database PDB3 in CDB1 by cloning PDB1 using the dbca silent method.

Then we will relocate PDB3 from CDB1 to CDB2.

For this we need to create a common user in CDB1 and use this use to create a database link from CDB2 to CDB1.
 

***Create common user in CDB1***

SQL> create user c##link_user identified by oracle container=all;

User created.

SQL> grant sysoper,sysdba to c##link_user container=all;

Grant succeeded.

SQL> grant create pluggable database to c##link_user container=all;

Grant succeeded.

SQL> grant create session to c##link_user container=all;

Grant succeeded.


***Add TNS entry to connect to CDB1 from CDB2***

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1.localdomain)
    )
  )

***Create database link from CDB2 to CDB1***

SQL> create database link cdb1_link connect to c##link_user identified by oracle
    using 'cdb1';

Database link created.


SQL>  select * from dual@cdb1_link;

D
-
X


***Note the PDB's currently in CDB2***

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB2 			  READ WRITE NO

 

Remote Pluggable Clone

 
Create the Pluggable Database using the dbca -silent -createPluggableDatabase command with the -createFromRemotePDB clause.
 

[oracle@host02 ~]$ dbca -silent -createPluggableDatabase -pdbname pdb1 -sourceDB cdb2 -createFromRemotePDB -remotePDBName pdb1 -dbLinkUsername c##link_user -remoteDBConnString "host01:1521/cdb1.localdomain" -remoteDBSYSDBAUserName SYS -dbLinkUserPassword oracle -remoteDBSYSDBAUserPassword welcome1 -sysDBAUserName SYS -sysDBAPassword welcome1 -pdbDatafileDestination '/u01/app/oracle/oradata/CDB2/pdb1/' 

Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "pdb1" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/pdb1/cdb2.log" for further details.


SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB2 			  READ WRITE NO
	 4 PDB1 			  READ WRITE NO

 

Relocate Pluggable Database

 

First we create PDB3 in CDB1 by cloning from PDB1 using the dbca -silent -createPluggableDatabase command.
 

[oracle@host01 ~]$ dbca -silent -createPluggableDatabase -sourceDB cdb1 -pdbName pdb3 -createAsClone FALSE -createPDBFrom PDB -sourcePDB pdb1 -fileNameConvert '/u01/app/oracle/oradata/CDB1/pdb1/','/u01/app/oracle/oradata/CDB1/pdb3/'
Prepare for db operation
13% complete
Creating Pluggable Database
15% complete
19% complete
23% complete
31% complete
53% complete
Completing Pluggable Database Creation
60% complete
Executing Post Configuration Actions
100% complete
Pluggable database "pdb3" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb3/cdb1.log" for further details.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
 	 5 PDB3				  READ WRITE NO

 
Relocate PDB3 from CDB1 to CDB2 using the dbca -silent -relocatePDB command.
 

[oracle@host02 ~]$ dbca -silent  -relocatePDB -pdbname pdb3 -sourceDB cdb2 -remotePDBName pdb3 -dbLinkUsername c##link_user -remoteDBConnString "host02:1521/cdb1.localdomain" -remoteDBSYSDBAUserName SYS -dbLinkUserPassword oracle -remoteDBSYSDBAUserPassword G#vin2407 -sysDBAUserName SYS -sysDBAPassword G#vin2407 -pdbDatafileDestination '/u01/app/oracle/oradata/CDB2/pdb3/' 
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "pdb3" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/pdb3/cdb2.log" for further details.


****Note the PDB's now in CDB2****

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB2 			  READ WRITE NO
	 4 PDB1 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO

****Note the PDB's now in CDB1****

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
on No comments:

Multitenant : DBCA PDB Remote Clone in Oracle Database 19c

 

Multitenant : DBCA PDB Remote Clone in Oracle Database 19c

Oracle Database 12c Release 2 (12.2) introduced the ability to perform a hot clone of a remote pluggable database (PDB) using the CREATE PLUGGABLE DATABASE command. In Oracle 19c it's possible to perform a remote clone of a pluggable database (PDB) using the Database Configuration Assistant (DBCA).

Multitenant : Remote Clone

Prerequisites

Connect to the remote CDB and prepare it for cloning.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a user in the remote database for use with the database link. In this case, we will use a common user in the remote PDB.

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

Unlike a regular remote clone, we don't need to create a database link. We just need to supply the credentials we would use to create the link. The DBCA does the rest.

Check the remote CDB is in local undo mode and archivelog mode.

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Because the remote CDB is in local undo mode and archivelog mode, we don't need to turn the remote database into read-only mode.

Connect to the local CDB and prepare it for cloning.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Check the local CDB is in local undo mode and archivelog mode.

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Remote Clone a PDB with the DBCA

In 19c the DBCA -createPluggableDatabase command has a new parameter called -createFromRemotePDB, allowing us to create a new PDB by remote cloning an existing PDB.

   [-createFromRemotePDB <Create a pluggable database from Remote PDB clone operation.>]
      -remotePDBName <Name of the pluggable database to clone/relocate>
      -dbLinkUsername <Common user of a remote CDB, used by database link to connect to remote CDB.>
      -remoteDBConnString <EZCONNECT string to connect to Source database for example "host:port/servicename">
      [-remoteDBSYSDBAUserName <User name with SYSDBA privileges of remote database>]
      [-dbLinkUserPassword <Common user password of a remote CDB, used by database link to connect to remote CDB.>]
      [-remoteDBSYSDBAUserPassword <Password for remoteDBSYSDBAUserName user of remote database.>]
      [-sysDBAUserName <User name  with SYSDBA privileges>]
      [-sysDBAPassword <Password for sysDBAUserName user name>]

You can see the full syntax for the -createPluggableDatabase command here, although at the time of writing the documentation doesn't match the utility usage presented by the dbca -createPluggableDatabase -help command.

Make sure the environment is set up to point to the local instance "cdb1" and create a new PDB called "pdb5new" as a clone of the remote PDB called "pdb5" in the "cdb3" instance.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -createPluggableDatabase \
    -pdbName pdb5new \
    -sourceDB cdb1 \
  -createFromRemotePDB \
    -remotePDBName pdb5 \
    -remoteDBConnString localhost:1521/pdb5 \
    -remoteDBSYSDBAUserName sys \
    -remoteDBSYSDBAUserPassword SysPassword1 \
    -dbLinkUsername c##remote_clone_user \
    -dbLinkUserPassword remote_clone_user 
Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "pdb5new" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5new/cdb1.log" for further details.
$

Connect to the local root container and check the status of the new PDB.

COLUMN name FORMAT A30

SELECT con_id, name, open_mode
FROM   v$pdbs
ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB5NEW                        READ WRITE

SQL>

We can clean up by deleting the new pluggable database using the command below.

dbca -silent \
     -deletePluggableDatabase \
     -sourceDB cdb1 \
     -pdbName pdb5new
Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
85% complete
92% complete
100% complete
Pluggable database "pdb5new" deleted successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5new/cdb14.log" for further details.
$

Appendix

The instances and pluggable databases used in these examples are created using the following commands.

# Local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

 

# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1

The databases have Oracle Managed Files (OMF) enabled and are switched to archivelog mode.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF


export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

EXIT;
EOF
on No comments:

Wednesday, 24 March 2021

Duplicating a Database in 12c

 

Duplicating a Database

Overview of RMAN Database Duplication

Database duplication is the use of the DUPLICATE command to copy all or a subset of the data in a source database. The duplicate database (the copied database) functions entirely independently from the source database (the database being copied).

This section contains the following topics:

Purpose of Database Duplication

A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

  • Test backup and recovery procedures

  • Test an upgrade to a new release of Oracle Database

  • Test the effect of applications on database performance

  • Create a standby database

  • Generate reports

For example, you can duplicate the production database on host1 to host2, and then use the duplicate database on host2 to practice restoring and recovering this database while the production database on host1 operates as usual.

If you copy a database with operating system utilities rather than the DUPLICATE command, then the DBID of the copied database remains the same as the original database. To register the copied database in the same recovery catalog with the original, you must change the DBID with the DBNEWID utility (see Oracle Database Utilities). In contrast, the DUPLICATE command automatically assigns the duplicate database a different DBID so that it can be registered in the same recovery catalog as the source database.

The DUPLICATE command can create a fully functional copy of your database or create a physical standby database, which serves a very different purpose. A standby database is a copy of the primary database that you update continually with archived redo log files from the primary database. If the primary database is inaccessible, then you can fail over to the standby database, which becomes the new primary database. A database copy, however, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.

See Also:

Oracle Data Guard Concepts and Administration to learn how to create a standby database with the DUPLICATE command

Basic Concepts of Database Duplication

The source host is the computer that hosts the source database. The source database instance is the instance associated with the source database.

The destination host is the computer that hosts the duplicate database. The source host and destination host can be the same or different computers. For the duplication process, the database instance associated with the duplicate database is called the auxiliary instance.

Note:

Performing simultaneous database duplication operations using the same source database is not supported.

Techniques for Duplicating a Database

RMAN supports two basic types of duplication: active database duplication and backup-based duplication. RMAN can perform backup-based duplication with or without either of the following connections:

  • Target

  • Recovery catalog

A connection to both is required for active database duplication.

Figure 25-1 shows the decision tree for the two duplication techniques.

Figure 25-1 Duplication Techniques

Description of Figure 25-1 follows
Description of "Figure 25-1 Duplication Techniques"
About Active Database Duplication with RMAN

In active database duplication, RMAN connects as TARGET to the source database instance and as AUXILIARY to the auxiliary instance. RMAN then manages the copying of the database to the auxiliary instance over the network. No backups of the source database are required.

RMAN can transfer the files required for active database duplication as image copies or backup sets.

Note:

Performing active database duplication using backup sets is available starting with Oracle Database 12c Release 1 (12.1).

Figure 25-2 illustrates active database duplication.

Figure 25-2 Active Database Duplication

Description of Figure 25-2 follows
Description of "Figure 25-2 Active Database Duplication"

When active database duplication is performed using image copies, after RMAN establishes a connection with the source database, the source database transfers the required database files to the auxiliary database. Using image copies may require additional resources on the source database. This method is referred to as the push-based method of active database duplication.

When RMAN performs active database duplication using backup sets, a connection is established with the source database and the auxiliary database. The auxiliary database then connects to the source database through Oracle Net Services and retrieves the required database files from the source database. This method of active database duplication is also to as the pull-based method.

Using backup sets for active database duplication provides certain advantages. RMAN can employ unused block compression while creating backups, thus reducing the size of backups that are transported over the network. Backup sets can be created in parallel on the source database by using multisection backups. You can also encrypt backup sets created on the source database.

See Also:

Factors That Determine Whether Backup Sets or Image Copies Are Used for Active Database Duplication

RMAN only uses image copies to perform active database duplication when no auxiliary channels are allocated or when the number of auxiliary channels allocated is less than the number of target channels.

RMAN uses backup sets to perform active database duplication when the connection to the target database is established using a net service name and any one of the following conditions is satisfied:

  • The DUPLICATE ... FROM ACTIVE DATABASE command contains either the USING BACKUPSET, USING COMPRESSED BACKUPSET, or SECTION SIZE clause.

  • The number of auxiliary channels allocated is equal to or greater than the number of target channels allocated.

Note:

Oracle recommends that you use backup sets to perform active database duplication.

About Backup-Based Duplication with RMAN

In backup-based duplication, RMAN creates the duplicate database by using preexisting RMAN backups and copies. This technique of duplication uses one of the following mutually exclusive methods:

  • Duplication without a target database connection. RMAN obtains metadata about backups from a recovery catalog.

  • Duplication without a target database connection and without a recovery catalog. RMAN obtains metadata about where backups and copies reside from the BACKUP LOCATION clause specification of the DUPLICATE command.

  • Duplication with a target database connection. RMAN obtains metadata about backups from the target database control file or from the recovery catalog.

Figure 25-3 illustrates backup-based duplication without a target connection. RMAN connects to a recovery catalog database instance and the auxiliary instance. The destination host must have access to the RMAN backups required to create the duplicate database.

Figure 25-3 Backup-Based Duplication Without a Target Connection

Description of Figure 25-3 follows
Description of "Figure 25-3 Backup-Based Duplication Without a Target Connection"

Figure 25-4 illustrates backup-based duplication without connections to the target or to the recovery catalog database instance. RMAN connects to the auxiliary instance of the duplicate database on the destination host. A disk backup location containing all the backups or copies for duplication must be available to the destination host.

Figure 25-4 Backup-Based Duplication Without a Target Connection or Recovery Catalog Connection

Description of Figure 25-4 follows
Description of "Figure 25-4 Backup-Based Duplication Without a Target Connection or Recovery Catalog Connection"

Figure 25-5 illustrates backup-based duplication with a target connection. RMAN connects to the source database instance and the auxiliary instance. Optionally, RMAN can connect to a recovery catalog database (not shown in the figure). The destination host must have access to the RMAN backups required to create the duplicate database.

Figure 25-5 Backup-Based Duplication with a Target Connection

Description of Figure 25-5 follows
Description of "Figure 25-5 Backup-Based Duplication with a Target Connection"

Contents of a Duplicate Database

A duplicate database can include the same contents as the source database or only a subset of the tablespaces in the source database. For example, you can use the TABLESPACE option of the DUPLICATE command to duplicate only specified tablespaces, or the SKIP READONLY option to exclude read-only tablespaces from the duplicate database.

How RMAN Duplicates a Database

For backup-based duplication, the principal work of the duplication is performed by the auxiliary channels. These channels correspond to a server session on the auxiliary instance on the destination host. For active database duplication, the type of duplication technique used determines which channel performs the principal work. When active database duplication is performed using backup sets, the principal work of duplication is performed by the auxiliary channels. When image copies are used, the primary work is performed by the target channels.

RMAN must perform database point-in-time recovery, even when no explicit point in time is provided for duplication. Point-in-time recovery is required because the online redo log files in the source database are not backed up and cannot be applied to the duplicate database. The farthest point of recovery of the duplicate database is the most recent redo log file archived by the source database.

As part of the duplicating operation, RMAN automates the following steps:

  1. Creates a default server parameter file for the auxiliary instance if the following conditions are true:

    • Duplication does not involve a standby database.

    • Server parameter files are not being duplicated.

    • The auxiliary instance was not started with a server parameter file.

  2. Restores from backup or copies from the active database the latest control file that satisfies the UNTIL clause requirements.

  3. Mounts the restored or copied backup control file from the active database.

    Note:

    If the control file in the source database contains disabled threads, then these threads will also be disabled in the duplicate database.

  4. Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance. This step applies to backup-based duplication.

  5. Copies the duplicate data files and restores them with incremental backups and archived redo log files to a noncurrent point in time.

  6. Shuts down and restarts the auxiliary database instance on the destination host in NOMOUNT mode.

  7. Creates a new control file, which then creates and stores the new DBID in the data files.

  8. Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database.

    If you do not want to open the duplicate database, use the NOOPEN clause in the DUPLICATE statement as described in "Specifying the State of the Duplicate Database".

See Also:

The DUPLICATE entry in Oracle Database Backup and Recovery Reference for a complete list of which files are copied to the duplicate database

Basic Steps of Database Duplication

To duplicate a database:

This section describes the basic steps of database duplication. Follow the link in each step for further instructions.

  1. Prepare for database duplication.

    See "Preparing to Duplicate a Database".

    While duplicating an Oracle Real Application Clusters (Oracle RAC) database, set the CLUSTER_DATABASE initialization parameter on the auxiliary database to FALSE. This parameter can be reset to TRUE after the duplication completes.

  2. Start RMAN and connect to the necessary database instances.
  3. For active database duplication, decide if you want to encrypt or compress the backup sets that are used to transfer files from the source database to the duplicate database. Additionally, decide if you want to create backup sets on the source database in parallel by using multisection backups.
  4. Place the source database in the proper state (if necessary).
  5. Configure RMAN channels (if necessary).
  6. Perform the duplication.

See Also:

Duplicating CDBs

Duplicating a whole multitenant container database (CDB) or one or more pluggable databases (PDBs) requires only minimal adjustments to the procedure for duplicating a non-CDB as described in "Basic Steps of Database Duplication".

When duplicating a whole CDB or one more PDBs:

  • You must create the auxiliary instance as a CDB. To do so, start the instance with the following declaration in the initialization parameter file:

    enable_pluggable_database=TRUE

  • When instructed to connect to the necessary instances, connect to the root as a user with SYSDBA or SYSBACKUP privilege. On both instances, the password for the user performing the duplication must be the same.

  • When instructed to create an initialization parameter file for the auxiliary instance, you must copy the file from the source database. This ensures that the auxiliary instance is also a CDB. After copying, you modify DB_NAME and the various destination/location parameters.

When transferring backup files from the source to the destination host:

This example transfers the backup files for the entire CDB:

BACKUP COPY OF DATABASE;

The next example transfers only the backup files for the PDB pdb3:

BACKUP COPY OF PLUGGABLE DATABASE pdb3;

Duplicating PDBs

RMAN enables you to duplicate PDBs with the DUPLICATE command. You can duplicate a single PDB, a set of PDBs, or a set of tablespaces within a PDB.

You must log in to the root of the CDB as a user who is granted the SYSDBA or SYSBACKUP privilege. To perform backup-based duplication of PDBs, you must back up the root and the seed database (PDB$SEED) of the CDB that contains the listed PDBs.

To duplicate PDBs, you must create the auxiliary instance as a CDB. To do so, start the instance with the declaration enable_pluggable_database=TRUE in the initialization parameter file. When you duplicate one or more PDBs, RMAN also duplicates the root (CDB$ROOT) and the seed database (PDB$SEED). The resulting duplicate database is a fully functional CDB that contains the root, the seed database, and the duplicated PDBs.Table 25-1 describes the options that you can use to duplicate PDBs or tablespaces within PDBs.

Table 25-1 Options to Duplicate PDBs

DUPLICATE OptionExplanation

PLUGGABLE DATABASE pdb_name

Duplicates the specified PDBs in the CDB. Use a comma-delimited list to duplicate multiple PDBs.

SKIP PLUGGABLE DATABASE pdb_name

Duplicates all the PDBs in the CDB, except the PDBs specified by pdb_name. Use a comma-delimited list to specify multiple PDBs that must be excluded.

TABLESPACE pdb_name:tablespace_name

Duplicates specified tablespaces within a PDB. The tablespace name must be prefixed with the name of the PDB that contains the tablespace. If you omit the name of the PDB, root is taken as the default.

SKIP TABLESPACE pdb_name:tablespace_name

Duplicates all tablespaces in the CDB except the specified tablespaces in the specified PDB.

To duplicate PDBs:

  1. Ensure that you have completed Steps 1 to 4 in "Basic Steps of Database Duplication".

  2. Run the DUPLICATE command with either the PLUGGABLE DATABASE or SKIP PLUGGABLE DATABASE option described in Table 25-1.

    The following are some examples of duplicating PDBs:

    • To duplicate the PDB pdb1 to the CDB cdb1, use the following command:

      DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1;

    • To duplicate the PDBs pdb1, pdb3, and pdb4 to the database cdb1, use the following command:

      DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1,pdb3,pdb4;

    • To duplicate all the databases in the CDB, except the PDB pdb3, use the following command:

      DUPLICATE DATABASE TO cdb1 SKIP PLUGGABLE DATABASE pdb3;

To duplicate tablespaces within a PDB:

  1. Ensure that you have completed Steps 1 to 4 in "Basic Steps of Database Duplication".
  2. Run the DUPLICATE command with the TABLESPACE option described in Table 25-1.

    The following are some examples of duplicating tablespaces contained in PDBs:

    • To duplicate the users tablespace that is part of PDB pdb1, use the following command:

      DUPLICATE DATABASE TO cdb1 TABLESPACE pdb1:users;

    • To duplicate the PDB pdb1 and the users tablespace in PDB pdb2, use the following command:

      DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1 TABLESPACE pdb2:users;

Preparing to Duplicate a Database

Step 1: Choosing a Duplication Technique

Your business requirements and the database environment determine which duplication technique is best for your situation. Consider the following questions:

  • Are you familiar with the prerequisites for each duplication technique?

    Review the Prerequisites section of the DUPLICATE command description in Oracle Database Backup and Recovery Reference for a complete list.

    Some prerequisites are specific and depend on the duplication technique. For example, active duplication requires that the source and auxiliary instances use the same password as the source database, whereas backup-based duplication without connections to the target database and recovery catalog requires only that all backups and database copies reside in a single location.

  • Do backups of the source database exist?

    The principal advantage of active database duplication is that it does not require source database backups. Active duplication copies mounted or online database files over a network to the auxiliary instance. One disadvantage of this technique is the negative performance effect on the network. Another disadvantage is that the source database is running processes required to transfer the files to the auxiliary host, thereby affecting the source database and production workload.

    If the source database backups exist, and if the effect on the network is unacceptable, then backup-based duplication may be a better option. You can copy backups to temporary storage and transfer them manually to the destination host. If duplication is made with a connection to the target or the recovery catalog, then the backup files on the destination host must have the same file specification as they had on the source host. Otherwise, this is not a requirement.

  • Is a recovery catalog available?

    If a recovery catalog exists, then you can perform backup-based duplication without connecting RMAN as TARGET to the source database. This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication.

  • How much disk space is available on the destination host?

    The disk space on the destination host can be an issue when you perform duplication using disk backups. For example, if the source database is 1 terabyte (TB), and if you duplicate the database from disk backups without using shared disk or network file system (NFS), then you must have at least 2 terabytes (TB) of space available on the destination host. In some environments, manual transfer of backups is necessary because NFS performance is a bottleneck.

  • Are the source and destination hosts connected by a LAN or a WAN?

    Performance of active database duplication is probably slower on a wide area network (WAN) than on a local area network (LAN). If the performance degradation on a WAN is unacceptable, then backup-based duplication may be the only viable option.

  • When do you plan to duplicate the database?

    If you must duplicate the database during a period of high user activity, then the loss of network throughput caused by active duplication may be a problem, making backup-based duplication a better choice. Also, in active database duplication, the RMAN channels required for copying files to the auxiliary host can affect performance.

Specifying Encryption for Active Database Duplication

Starting with Oracle Database 12c Release 1 (12.1), RMAN can use backup sets to transfer the source database files that need to be duplicated. The backup sets are transferred over the network to the auxiliary database. Backup sets can be encrypted for additional security. Specify the encryption algorithm by using the SET ENCRYPTION ALGORITHM command before the DUPLICATE command.

Before you perform active database duplication, use one of the following techniques to ensure that the encryption is successful:

  • If the source database uses transparent encryption, then you must share the Oracle keystore that contains the encryption key between the source database and the auxiliary database as described in "Step 6: Copying the Oracle Keystore to the Destination Host".

  • If the source database uses password encryption, then you must specify the password used to encrypt backups.

    The following command sets the encryption password to encr_pwd:

    SET ENCRYPTION ON IDENTIFIED BY encr_pwd;

The following example connects to the target database and an auxiliary database using net service names and performs active database duplication. The keystore that contains the encryption key is shared between the target and the auxiliary database. sbu is a user who is granted the SYSBACKUP privilege.

CONNECT TARGET "sbu@prod AS SYSBACKUP";
CONNECT AUXILIARY "sbu@dup_db AS SYSBACKUP";
SET ENCRYPTION ALGORITHM 'AES128';
DUPLICATE TARGET DATABASE TO dup_db
     FROM ACTIVE DATABASE
     PASSWORD FILE;

Compressing Backup Sets Used to Perform Active Database Duplication

When you use backup sets to perform active database duplication, you can compress the backup sets created to transfer files from the source database to the destination database. Using compressed backup sets minimizes the size of the backup sets and thus provides better performance for the database duplication. Compressing backup sets used for active database duplication is supported starting with Oracle Database 12c Release 1 (12.1).

Use the USING COMPRESSED BACKUPSET clause of the DUPLICATE command to compress the backup sets containing data required to perform active database duplication. The following command performs active database duplication using compressed backup sets. Assume that the connection to the target database and auxiliary instance has been made using net service names.

DUPLICATE TARGET DATABASE TO dup_db
     FROM ACTIVE DATABASE
     PASSWORD FILE
     USING COMPRESSED BACKUPSET;

Creating Backup Sets in Parallel During Active Database Duplication

RMAN multisection backups provide faster backup performance by backing up very large data files in parallel. Multiple backup pieces are created, with a separate channel writing to each backup piece. Starting with Oracle Database 12c Release 1 (12.1), you can use multisection backup sets to transfer the source files required to perform active database duplication.

Use the SECTION SIZE clause in the DUPLICATE command to create multisection backup sets that can be used for active database duplication. The following command creates multisection backup sets, with the size of each backup piece being 400MB. Assume that the connection to the target database and auxiliary instance has been made using net service names.

DUPLICATE TARGET DATABASE TO dup_db
     FROM ACTIVE DATABASE
     PASSWORD FILE          
     SECTION SIZE 400M;

Specifying the State of the Duplicate Database

When you use the RMAN DUPLICATE command, the duplicate database is created and then opened in RESETLOGS mode. You can specify that the duplicate database must not be opened by using the NOOPEN clause in the DUPLICATE command.

You may not want to open the duplicate database immediately after creation in the following situations:

  • Opening the duplicate database may cause errors.

  • You need to modify the initialization parameters of the duplicate database.

    For example, you need to modify flashback database settings, configure fast incremental backups, or modify block change tracking.

  • You are creating a new database as part of an upgrade procedure.

    During an upgrade you cannot open the database with RESETLOGS option. The NOOPEN clause enables you to duplicate the database and then leave it in a state ready for opening in upgrade mode and subsequent execution of upgrade scripts.

The following command creates a duplicate database called dupdb. Because the NOOPEN clause is used, RMAN creates the duplicate database but does not open it.

DUPLICATE TARGET DATABASE 
TO dupdb
FROM ACTIVE DATABASE
NOOPEN;

Step 2: Choosing a Strategy for Naming Duplicate Files

When duplicating a database, RMAN generates names for the duplicate control files, data files, temp files, and online redo log files. Therefore, you must decide on a naming strategy for these files.

The simplest duplication strategy is to configure the duplicate database to use the same names as the source database. Using the same names means that your environment meets the following requirements:

  • If the source database uses ASM disk groups, then the duplicate database must use ASM disk groups with the same names.

  • If the source database files are Oracle Managed Files, then the auxiliary instance must set DB_CREATE_FILE_DEST to the same directory location as the source database. Although the directories are the same on the source and destination hosts, Oracle Database chooses the relative names for the duplicate files.

  • If the names of the database files in the source database contain a path, then this path name must be the same in the duplicate database.

    Note:

    If the destination and source hosts are the same, then use one of several methods available to modify the database file paths in the duplicate database. See "Specifying Alternative Names for Duplicate Database Files".

When you configure your environment as suggested, no additional configuration is required to name the duplicate files.

It is recommended that you use different names for the ASM disk groups in the source and duplicate database. Use one of the following techniques to specify different names for database files in the duplicate database:

  • Set the DB_CREATE_FILE_DEST and LOG_FILE_CREATE_DEST initialization parameters in the auxiliary instance. This is recommended when you use ASM and OMF.

  • Use the SET NEWNAME command.

  • Set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters in the auxiliary instance.

    If DB_FILE_NAME_CONVERT is applied for ASM names, only disk group name changes must be performed.

See Also:

Specifying Alternative Names for Duplicate Database Files for information about specifying different file names for files in the duplicate database

Step 3: Installing the Oracle Database Software on the Destination Host

If the source and destination hosts are different, then you must install the Oracle Database software on the destination host so that you can create the auxiliary instance. You can install the software in one of two ways:

  • Perform a normal installation with Oracle Universal Installer (OUI).

    Install an Oracle Database that is the same release as the source database. Do not create a database; install the software only. Apply any required patches.

  • Clone the source Oracle home.

    Use OUI to clone the source Oracle home. This ensures that all patches applied to the source database are present in the duplicate database.

See Also:

Oracle Universal Installer and OPatch User's Guide for information about cloning an Oracle home

Step 4: Making Backups Accessible to the Duplicate Instance

Note:

If you are performing active database duplication, then skip this section and proceed to "Step 5: Preparing Remote Access to Databases".

When duplicating with a target and recovery catalog or just a target connection, RMAN uses metadata in the RMAN repository to locate backups and archived redo log files needed for duplication. If RMAN is connected to a recovery catalog, then RMAN obtains the backup metadata from the catalog. If RMAN is not connected to a catalog, as may be the case when performing backup-based duplication with a target connection, then RMAN obtains metadata from the control file.

Unless you are duplicating without a connection to the target and to the recovery catalog, the names of the backups must be available with the same names recorded in the RMAN repository. Ensure that auxiliary channels on the destination host can access all data file backups and archived redo log files (required to restore and recover the duplicate database to the desired point in time). If not, duplication fails. The archived redo log files can be available either as image copies or backup sets.

Note:

The database backup need not have been generated with BACKUP DATABASE. You can mix full and incremental backups of individual data files, but a full backup of every data file is required.

This section contains the following topics:

Making SBT Backups Accessible to the Auxiliary Instance

The steps in this task are specific to your media manager configuration.

To make SBT backups accessible to the auxiliary instance:

  1. If necessary, install media management software on the destination host.
  2. Make the tapes with the backups accessible to the destination host. Typically, you do one of the following:

    • Physically move the tapes to a drive attached to the remote host.

    • Use a network-accessible tape server.

  3. If necessary, inform the remote media management software about the existence of the tapes.

Making Disk Backups Accessible to the Auxiliary Instance

When you make disk backups accessible to the auxiliary instance, your strategy depends on whether or not you duplicate the database while connected to the target or recovery catalog. If you do not connect to the target or recovery catalog, then you must designate a backup location for the duplication by using the BACKUP LOCATION clause.

When you use a backup location, the backups and copies can reside in a shared location or can be moved to the location on the destination host. In the latter case, you do not need to preserve the name or the original path of the backup or copy. The location specified in the BACKUP LOCATION option must contain sufficient backup sets, image copies, and archived logs to restore all of the files being duplicated, and recover them to the desired point in time.

It is not required that all of the backups be from the same point in time, or that they all be backup sets, or all image copies. Data file backups can be supplied as either image copies or backup sets. Archived logs can be supplied either in their normal format or as backup sets of archived logs.

When you use backups from different points in time, the backup location must contain archived logs covering the time from the start of the oldest backup until the desired recovery point.

If the backup location contains backup files from multiple databases, then the DATABASE clause must specify the name of the database that is to be duplicated. If the backup location contains backup files from multiple databases having the same name, then the DATABASE clause must specify both the name and DBID of the database that is to be duplicated.

The source database's Fast Recovery Area is particularly well suited for use as a backup location because it almost always contains all of the files needed for the duplication. To use a Fast Recovery Area as a backup location, you can either remotely access it from the destination system, or copy its contents to the destination system.

When you are not using a backup location, your strategy depends on the following mutually exclusive scenarios:

  • Identical file systems for source and destination hosts

    This scenario is the simplest and Oracle recommends it. For example, assume that the backups of the source database are stored in /dsk1/bkp. In this case, you can make disk backups accessible to the destination host in either of these ways:

    • Manually transfer backups from the source host to an identical path in the destination host. For example, if the backups are in /dsk1/bkp on the source host, then use FTP to transfer them to /dsk1/bkp on the destination host.

    • Use NFS or shared disks and ensure that the same path is accessible in the destination host. For example, assuming that the source host can access /dsk1/bkp, use NFS to mount /dsk1/bkp on the destination host and use /dsk1/bkp as the mount point name.

  • Different file systems for source and destination hosts

    In this case you cannot use the same directory name on the destination host as you use on the source host. You have the following options:

Assume that you have two hosts, srchost and dsthost, and access to NFS or shared disk. The database on srchost is called srcdb. The backups of srcdb reside in /dsk1/bkp on host srchost. The directory /dsk1/bkp is in use on the destination host, but the directory /dsk2/dup is not in use on either host.

To transfer the backups from the source host to the destination host:

  1. Create a backup storage directory in either the source or destination host.

    For this example, create backup directory /dsk2/dup on the destination host.

  2. Mount the directory created in the previous step on the other host, ensuring that the directory and the mount point names are the same.

    For example, if you created /dsk2/dup on the destination host, then use NFS to mount this directory as /dsk2/dup on the source host.

  3. Make the backups available in the new location on the destination host. You can use either of the following techniques:

    • Connect RMAN to the source database as TARGET and use the BACKUP command to back up the backups, as explained in "Backing Up RMAN Backups". For example, use the BACKUP COPY OF DATABASE command to copy the backups in /dsk1/bkp on the source host to /dsk2/dup on the source host. In this case, RMAN automatically catalogs the backups in the new location.

      If you are duplicating a PDB, then use the PLUGGABLE DATABASE syntax of the BACKUP COPY OF command to copy only the backups of the PDB.

    • Use an operating system utility to transfer the backups to the new location. For example, use FTP to transfer the backups from /dsk1/bkp on the source host to /dsk2/dup on the destination host, or use the cp command to copy the backups from /dsk1/bkp on the source host to /dsk2/dup on the source host. Afterward, connect RMAN to the source database as TARGET and use the CATALOG command to update the RMAN repository with the location of the manually transferred backups.

Step 5: Preparing Remote Access to Databases

When a database must be accessed from another host, you must set up a password file and Oracle Net connectivity. Be aware of the potential security consequences of this type of setup.

This section contains the following topics:

Establishing Database Connectivity

You can establish database connectivity by manually creating a password file or using Oracle Net.

To create a password file manually:

Follow the instructions in Oracle Database Administrator's Guide to create a password file.

The types of file names allowed for password files and the location of the password files are both platform-specific and operating system-specific.

See Also:

To establish Oracle Net connectivity and set up a static listener:

Follow the instructions in Oracle Database Net Services Administrator's Guide to configure a client for connection to a database and add static service information for the listener.

Creating a Password File for the Auxiliary Instance

You have the following options for creating a password file for the auxiliary instance on the destination host:

  • Copy the source database password file to the destination host and rename it to match the auxiliary instance name. This is applicable only if the source and destination hosts are on the same platform.

  • Create the password file manually.

  • Create the password file with the ORAPWD command. The SYSBACKUP option creates a SYSBACKUP entry in the new password file.

  • Specify the PASSWORD FILE option on the DUPLICATE... FROM ACTIVE DATABASE command.

    In this case, RMAN copies the source database password file to the destination host and overwrites any existing password file for the auxiliary instance. This technique is useful if the source database password file has multiple passwords to make available on the duplicate database.

    There are additional requirements for the case of DUPLICATE ... FROM ACTIVE DATABASE. The password file must contain at least two passwords, for the SYS user and the user with SYSBACKUP privilege, and the passwords must match the passwords of the source database. You can start the auxiliary instance and enable the source database to connect to it.

See Also:

Oracle Database Administrator's Guide for information about creating a password file

Note:

If you create a standby database with the FROM ACTIVE DATABASE option, then RMAN always copies the password file to the standby host.

Step 6: Copying the Oracle Keystore to the Destination Host

If transparent encryption is configured on the source database, then you must ensure that the Oracle software keystore from the source database is available to the auxiliary instance. Manually copy the source database keystore to the destination host.

The following are requirements for the keystore at the duplicate database:

  • The keystore must be in the default location, or in the location indicated by the sqlnet.ora file.

  • Permissions on the Oracle keystore file must be set so that the database can access the file.

  • The keystore must be an auto login keystore.

  • During duplication, the auxiliary instance is restarted thereby causing the Oracle software keystore to become unavailable. To ensure that the auxiliary instance has access to the keystore, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file such that it points to the keystore location.

  • With Oracle Real Application Clusters (Oracle RAC), register the auxiliary instance statically with an Oracle Grid Infrastructure listener and use the ENVS parameter in the sqlnet.ora file of the Oracle Grid home to specify environment variables that set the keystore location and the unique name of the database.

    The following example sets the ENVS parameter in sqlnet.ora to specify the keystore location and unique database name:

    (ENVS="ORACLE_UNQNAME=cdbrptl, ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/ORACLE/WALLETS/cdbrpt1)))")

  • If the source database uses a password-based software keystore (not an auto-login software keystore), then you must provide the keystore password before you begin the database duplication.

    Use the SET command with the DECRYPTION WALLET OPEN IDENTIFIED BY clause to specify the password that must be used to open the keystore.

    The following command sets specifies that the password used to open the keystore is my_pswd:

    SET DECRYPTION WALLET OPEN IDENTIFIED BY my_pswd;

See Also:

Step 7: Creating an Initialization Parameter File and Starting the Auxiliary Instance

The location and content of the initialization parameter file depend on your choice in "Step 2: Choosing a Strategy for Naming Duplicate Files". This section makes the following assumptions:

  • You choose the recommended technique of using the same naming strategy for the source and destination hosts. For Oracle Real Application Clusters environments, you therefore use the same ORACLE_SID for source and destination hosts.

  • You create a text-based initialization parameter file (PFILE) for the auxiliary instance. See Table 26-5.

  • The initialization parameter file is located in the operating system-specific default location of the host on which SQL*Plus runs.

    For example, on Linux and UNIX the default initialization parameter file name is ORACLE_HOME/dbs/initORACLE_SID.ora and on Windows the file name is ORACLE_HOME\database\initORACLE_SID.ora.

  • You plan to specify the SPFILE clause on the DUPLICATE command.

    The DUPLICATE ... SPFILE technique is easiest because during duplication RMAN automatically copies the server parameter file from the source database to the auxiliary instance or restores it from backup.

If you cannot meet the preceding requirements, then see "Duplicating a Database When No Server Parameter File Exists".

To create an initialization parameter file and start the auxiliary instance:

  1. Do one of the following:

    • Copy the initialization parameter file from the source host to the destination host, placing it in the operating system-specific default location, and then modify DB_NAME, DB_DOMAIN, and the various destination/location parameters such as CONTROL_FILES and DB_RECOVERY_FILE_DEST.

      If you are duplicating a CDB, ensure that the ENABLE_PLUGGABLE_DATABASE parameter is present and set to TRUE.

    • Complete these steps:

      1. Using a text editor, create an empty file for use as a text-based initialization parameter file, and save it in the operating system-specific default location.

      2. In the parameter file, set DB_NAME and DB_DOMAIN. These are the only required parameters.

        Setting DB_DOMAIN enables you to connect to the default database service when connecting with a net service name.

      3. If the auxiliary instance is to be a CDB, then set the following parameter:

        ENABLE_PLUGGABLE_DATABASE=TRUE

  2. If necessary, set other initialization parameters like those needed for Oracle Real Application Clusters.

  3. Set the required environment variables, such as ORACLE_HOME and ORACLE_SID.

  4. Start SQL*Plus and connect to the auxiliary instance as a user with SYSDBA or SYSBACKUP privileges. Start the auxiliary instance in NOMOUNT mode. No PFILE parameter on the STARTUP command is necessary if the file is in the default location.

    SQL> STARTUP NOMOUNT;

Note:

For this scenario, ensure that the auxiliary instance is started with a text-based initialization parameter file and not a server parameter file. Do not create a control file or try to mount or open the auxiliary instance.

Step 8: Placing the Source Database in the Proper State

Note:

If you are performing backup-based duplication without a target connection, then skip to "Configuring RMAN Channels for Use in Duplication".

If RMAN is connected to the source database as TARGET, then the source database must be in the proper state for the duplication.

To ensure that the source database is in the proper state:

  1. If the source database instance is not mounted or open, then mount or open it.
  2. If you are performing active database duplication, then ensure that the following additional requirements are met:

    • If the source database is open, then archiving must be enabled.

    • If the source database is not open, then the database does not require instance recovery.

Starting RMAN and Connecting to Databases

In this task, you must start the RMAN client and connect to the database instances required by the duplication technique chosen in "Step 1: Choosing a Duplication Technique". The RMAN client can be located on any host so long as it can connect to the necessary databases over the network.

To start RMAN and connect to the target and auxiliary instances:

  1. Start the RMAN client on any host that can connect to the necessary database instances.

    For example, enter the following command at the operating system prompt on the destination host:

    % rman
  2. At the RMAN prompt, run CONNECT commands for the database instances required for your duplication technique.

    Note:

    When duplicating a whole CDB or one or more PDBs, connect to the root of both instances.

    • For active database duplication using image copies (push-based method), you must connect to the source database as TARGET and to the auxiliary instance as AUXILIARY. You must supply the net service name to connect to the AUXILIARY instance. A recovery catalog connection is optional. On both instances, the password for the user performing the duplication must be the same. Any user with a SYSDBA or SYSBACKUP privilege can perform duplication.

    • For active database duplication using backup sets (pull-based method), you must connect to the source database as TARGET using a net service name. The auxiliary instance uses this net service name to connect to the source database and retrieve the backup sets required for the duplication. Connect to the auxiliary instance as AUXILIARY. If you are connecting to the auxiliary instance remotely or intend to use the PASSWORD FILE option of the DUPLICATE command, then connect to the auxiliary instance with a net service name. On both instances, the password for the user performing the duplication must be the same. Any user with a SYSDBA or SYSBACKUP privilege can perform duplication. A recovery catalog connection is optional.

    • For backup-based duplication without a target connection, you must connect to the auxiliary instance as AUXILIARY and the recovery catalog as CATALOG.

    • For backup-based duplication with a target connection, you must connect to the source database as TARGET and the auxiliary instance as AUXILIARY. A recovery catalog is optional.

    • For backup-based duplication without target and recovery catalog connections, you must connect to the auxiliary instance as AUXILIARY.

    In the following example of active database duplication, a connection is established to three database instances using net service names. RMAN connects to the target database and auxiliary instance as a user with the SYSBACKUP privilege. The connection to the catalog is using the rco user (the catalog owner). RMAN prompts for the passwords for these users.

    CONNECT TARGET "sbu@prod AS SYSBACKUP";      # source database

CONNECT AUXILIARY "sbu@dupdb AS SYSBACKUP";  # duplicate database instance

CONNECT CATALOG rco@catdb;    # recovery catalog database

Configuring RMAN Channels for Use in Duplication

Configuring Channels for Backup-Based Duplication

The channel on the auxiliary instance, not the source database instance, restores RMAN backups in backup-based duplication. The channel configuration depends on your duplication technique.

RMAN can use the same channel configurations on the source database for duplication on the destination host. RMAN can use these configurations even if the source database channels do not specify the AUXILIARY option.

Note the following additional considerations:

  • The channel type (DISK or sbt) of the auxiliary channel must match the backup media. In general, the more channels you allocate for disk backups, the faster the duplication. You cannot increase the speed of duplication after the disks reach their maximum read/write rate. For tape backups, limit the number of channels to the number of devices available.

  • If the auxiliary channels need special parameters (for example, to point to a different media manager), then you can configure an automatic channel with the AUXILIARY option of the CONFIGURE command.

  • When you perform duplication without a target connection and without a recovery catalog, only disk channels can be used. If no user-allocated channels are used, then only one channel initially restores the control file. After the control file is mounted, the number of allocated channels depends on the configuration in the restored control file.

  • If you omit the USING BACKUPSET clause from the DUPLICATE command and the number of allocated auxiliary channels is greater than or equal to the number of target channels, then RMAN still uses active database duplication with backup sets.

Configuring Channels for Active Database Duplication

In active database duplication, you do not have to change your source database channel configuration or configure AUXILIARY channels.

To perform active database duplication using backup sets, you must allocate additional auxiliary channels. The number of auxiliary channels must be greater than or equal to the number of target channels. Using backup sets for active duplication also enables parallelism which can improve the speed of the duplication process.

See Also:

Oracle Database Backup and Recovery Reference for information about the CONFIGURE command

Duplicating a Database

This section describes the most basic procedure to duplicate a database. This section makes the following assumptions:

  • You are duplicating the database to a remote host. The duplicate database files use the same names as the source database files.

    Note:

    When running the DUPLICATE command in this configuration, you must specify the NOFILENAMECHECK option on the DUPLICATE command. If you duplicate a database on the same host as the source database, then verify that NOFILENAMECHECK is not specified.

  • You are duplicating the entire database or one or more PDBs. For other scenarios, see "Duplicating a Subset of the Source Database Tablespaces".

Note:

For more information about duplicating an Oracle RAC database, refer to My Oracle Support Note 1617946.1 at https://support.oracle.com/rs?type=doc&id=1617946.1.

To duplicate a database to a remote host with the same directory structure:

  1. Ensure that you have completed Steps 1 through 4 in "Basic Steps of Database Duplication".
  2. Run the DUPLICATE command.

Example 25-1 Duplicating to a Different Host with the Same Directory Structure (Active)

This example illustrates how to perform active duplication. RMAN uses the pull method (using backup sets) by default.DUPLICATE requires theNOFILENAMECHECK option because the source database files have the same names as the duplicate database files.

The PASSWORD FILE option specifies that RMAN copies the password file to the destination host.

RMAN automatically copies the server parameter file to the destination host, restarts the auxiliary instance with the server parameter file, copies all necessary database files and archived redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with theRESETLOGS option to create the online redo log.dupdb is theDB_NAME of the duplicate database

DUPLICATE TARGET DATABASE TO dupdb
  FROM ACTIVE DATABASE
  PASSWORD FILE
  SPFILE
  NOFILENAMECHECK;

Example 25-2 Duplicating a PDB

This example adds the PLUGGABLE DATABASE clause to the previous example to duplicate a single PDB. The root and seed database are automatically included in the duplication. The auxiliary instance must have been started with an initialization parameter file that includes the declaration enable_pluggable_database=TRUE.

DUPLICATE TARGET DATABASE TO cdbdupe
  PLUGGABLE DATABASE sales
  FROM ACTIVE DATABASE
  PASSWORD FILE
  SPFILE
  NOFILENAMECHECK;

Backup-Based Duplication Without a Target Connection: Example

In this variation of Example 25-1, RMAN does not use a TARGET connection to the source database. Example 25-3 creates a duplicate of the source database prod as it appeared in 2013 in a previous database incarnation. RMAN is not connected to the source database but must be connected to a recovery catalog because no BACKUP LOCATION clause is provided.

Example 25-3 Duplicating a Database to a Past Point in Time (Backup-Based)

DUPLICATE DATABASE prod DBID 8675309 TO dupdb
  UNTIL TIME "TO_DATE('11/01/2013', 'MM/DD/YYYY')"
  SPFILE
  NOFILENAMECHECK;

Note the following characteristics of Example 25-3:

  • The FROM ACTIVE DATABASE clause is not specified. By not specifying this clause, you instruct RMAN to perform backup-based duplication.

  • The DBID of the source database is specified because the source database name prod is not unique in the recovery catalog.

  • The NOFILENAMECHECK check is specified because it is necessary when the duplicate database files use the same names as the source database files.

Assume a variation in which you want to restore an archival backup, which is all-inclusive in the sense that every file needed to restore and recover the database is included. The recommended technique for restoring an archival backup for testing is to create a temporary instance and use the DUPLICATE command. In this way, you avoid interfering with the source database.

In the DUPLICATE command you must specify the restore point that was created with the archival backup. You can only specify TO RESTORE POINT if RMAN is connected to a catalog, or to the source database when the restore point exists in the control file.Example 25-4 specifies restore point TESTDB103107.

Example 25-4 Using an Archival Backup for Backup-Based Duplication

DUPLICATE DATABASE prod DBID 8675309 TO dupdb
  TO RESTORE POINT TESTDB103107
  SPFILE
  NOFILENAMECHECK;

Backup-Based Duplication with a Target Connection: Example

Assume a backup-based variation of Example 25-1 in which RMAN is connected as TARGET to the source database. Example 25-5 recovers the duplicate database to 1 week ago to view the data in the source database as it appeared then.

Example 25-5 Duplicating a Database to a Past Point in Time (Backup-Based)

DUPLICATE TARGET DATABASE TO dupdb
  SPFILE
  NOFILENAMECHECK
  UNTIL TIME 'SYSDATE-7';

Note the following characteristics of Example 25-5:

  • The FROM ACTIVE DATABASE clause is not specified. By not specifying this clause, you instruct RMAN to perform backup-based duplication.

  • The NOFILENAMECHECK option is specified because it is necessary when the duplicate database files use the same names as the source database files.

Backup-Based Duplication Without a Target or Recovery Catalog Connection: Example

In the variation of Example 25-1 shown in Example 25-6, RMAN does not use a TARGET connection to the source database or a CATALOG connection to a recovery catalog. All backup and copies necessary for duplication until November 1 of 2013 at 2:00 PM, including a control file backup or copy, have been placed under /prod_backups.

Example 25-6 Duplicating a Database Without a Target and Recovery Catalog Connection (Backup-Based)

DUPLICATE DATABASE TO dupdb
  UNTIL TIME "TO_DATE('11/01/2013 14:00:00', 'MM/DD/YYYY HH24:MI:SS')"
  SPFILE
  BACKUP LOCATION '/prod_backups'
  NOFILENAMECHECK;

Note the following characteristics of Example 25-6 :

  • The database name is not specified. By not specifying a database name with the DATABASE keyword, DUPLICATE obtains the database name and DBID from the backups. An error is displayed if backups for multiple databases were found in the BACKUP LOCATION.

  • Use of the BACKUP LOCATION clause identifies the type of duplication as backup-based with neither a target connection nor recovery catalog.

  • The UNTIL TIME option is specified. It is the only UNTIL subclause permitted with the BACKUP LOCATION clause.

  • The NOFILENAMECHECK option is specified because it is necessary when the duplicate database files use the same names as the source database files.

Example 25-7 Duplicating a Database to a Past Point in Time (Backup-Based)

This example performs backup-based duplication to a point-in-time in the past.

DUPLICATE TARGET DATABASE TO dupdb
  SPFILE
  NOFILENAMECHECK
  UNTIL TIME 'SYSDATE-7';

Note the following characteristics of Example 25-7:

  • The FROM ACTIVE DATABASE clause is not specified. Omitting this clause instructs RMAN to perform backup-based duplication.

  • The NOFILENAMECHECK option check is specified because it is necessary when the duplicate database files use the same names as the source database files.

Example: Script to Duplicate a Database Using Backup-based Duplication

This example shows how to use a script to automate the process of duplicating a target database.

This example assumes the following:

  • backups of the target database are available to the auxiliary instance

  • connection to the RMAN recovery catalog that contains metadata for the target database is available (connection to the target database is not required)

  • both source and duplicate database use Oracle Managed Files (OMF)

  • operating system used is Linux or UNIX

  • audit directory is created on the auxiliary database host

  • prerequisites for backup-based duplication are met

The script provided in this example performs the following tasks:

  • drops the auxiliary database

  • backs up the target database

  • creates a dummy auxiliary instance and opens it in NOMOUNT mode

  • duplicates the target database using the target database backups and metadata in the RMAN recovery catalog

    The duplicate database control file is stored as +REDO/ORACLE_SID/CONTROLFILE/cf3.ctl and the data files are stored in the +DATA directory.

  • verifies that the required objects are created in the duplicate database

To duplicate a target database using backup-based duplication without a target connection:

  1. Create a parameter file (pfile) for the auxiliary instance. The pfile contains only the DB_NAME initialization parameter which is set to the SID of the duplicate database.

    The following pfile, called init_dup.ora and located in the /home/oracle directory, sets the DB_NAME parameter. Replace dup_db with the SID of your duplicate database:

    *.db_name = 'dup_db'
  2. Use a text editor and create a Shell script (called dup_db.sh in this example) with the contents shown below and with the following modifications:

    • Replace the value of the ORACLE_HOME variable with the Oracle home directory of your auxiliary instance.

    • Replace the value of the logdir variable with the directory in which you want to store log files.

    • Replace the following placeholders (shown in Italics) with values appropriate to your duplication scenario:

      dup_db: system identifier (SID) and service name of the auxiliary instance

      tgt_db: SID and service name of the target database

      sys_pswd: Password for the SYS user of the target database

      rman_cat_user: Name of the RMAN catalog user

      cat_user_pswd: Password for the RMAN catalog user rman_cat_user

      rman_catalog_db: SID of the RMAN catalog database

      system_pswd: Password for the SYSTEM user in the target database

    • If you want to store the duplicate database control file using a name and location that is different from +REDO/ORACLE_SID/CONTROLFILE/cf3.ctl, then replace the value of control_files in the dup_aux_db function with a value that is appropriate for your duplication scenario.

    • If you want to store the duplicate data files in a directory that is different from +DATA, then replace the value of db_create_file_dest in the dup_aux_db function with a value that is appropriate for your duplication scenario.

    #!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_2
export ORACLE_BASE=/uo1/app/oracle
export ORACLE_SID=dup_db
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/Opatch
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
export logdir=/home/oracle/log
export dt='date +%y%m%d%H%M%S'
export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'

function drop_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
shutdown immediate;
startup mount exclusive restrict;
drop database;
exit;
EOF2
}

echo "Backup the target database"
function backup_source_db {
$ORACLE_HOME/bin/rman target sys/sys_pswd@tgt_db catalog
rman_cat_user/cat_user_pswd@rman_catalog_db <<EOF
RUN {
backup as backupset cumulative incremental level 1 database include current
controlfile plus archivelog not backed up delete input;}
exit;
EOF
}

sleep 120

echo "List the backup of the target database"
function check_source_db_backup {
$ORACLE_HOME/bin/rman target sys/sys_pswd@tgt_db catalog
rman_cat_user/cat_user_pswd@rman_catalog_db <<EOF
LIST BACKUP OF DATABASE COMPLETED AFTER '(SYSDATE-1/24)';
EOF
}

echo "Start the auxiliary database in FORCE NOMOUNT mode"
function nomount_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/rman target / <<EOF2
startup force nomount pfile='/home/oracle/init_dup.ora';
exit;
EOF2
}

echo "Duplicate the target database"
function dup_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/rman catalog rman_cat_user/cat_user_pswd@rman_catalog_db AUXILIARY /
<<EOF
duplicate database tgt_db to dup_db spfile
set control_files '+REDO/${ORACLE_SID}/CONTROLFILE/cf3.ctl'
set db_create_file_dest '+DATA/' ;
exit;
EOF
}

echo "Check schema objects on the target"
function check_source_db {
$ORACLE_HOME/bin/sqlplus -s system/system_pswd@tgt_db <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
select table_name, num_rows from dba_tables where owner='SOE';
exit;
EOF2
}

echo "Check schema objects on the auxiliary"
function check_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
select table_name, num_rows from dba_tables where owner='SOE';
exit;
EOF2
}

drop_aux_db
backup_source_db
check_source_db_backup
nomount_aux_db
dup_aux_db
check_source_db
check_aux_db
  3. Set execute permissions for the script dup_db.sh using the chmod command.
    $ chmod +x dup_db.sh
  4. On the duplicate host (that hosts the duplicate database), run the dup_db.sh script.

    The following command runs the dup_db.sh script that is stored in the /home/my_scripts/duplication directory:

    $./home/my_scripts/duplication/dup_db.sh

Restarting DUPLICATE After a Failure

RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which data files were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with or without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.

To restart a DUPLICATE operation:

  1. Exit RMAN.
  2. Start SQL*Plus and connect to the auxiliary instance with SYSDBA or SYSBACKUP privilege. Start the auxiliary instance in NOMOUNT mode with the same SPFILE or PFILE specification that you used initially. If you omitted this specification initially, then omit it again here.

    This example starts the auxiliary instance using the parameters in /home/my_pfile.ora:

    STARTUP FORCE PFILE=/home/my_pfile.ora
  3. Exit SQL*Plus and start RMAN.
  4. Connect to the same databases as initially.
  5. Repeat the DUPLICATE command.

The second DUPLICATE operation:

  • Locates the data files that were successfully duplicated by the initial DUPLICATE command.

  • Displays a message similar to the following for each data file that it does not need to duplicate again:

    RMAN-05560: Using previous duplicated file /oradata/new/data01.f for datafile 1 with checkpoint SCN of 1654665

  • Restores only the missing or incomplete data files, thereby avoiding recopying and restoring all the data files.

If you do not want RMAN to automatically recover from a failed DUPLICATE operation, specify the keyword NORESUME to disable the functionality. Using the keyword NORESUME in the first invocation of DUPLICATE prevents a subsequent DUPLICATE command for the new database from using this automatic optimization.

on No comments:
Subscribe to: Posts (Atom)