Guia Rapida Backups RMAN

1. Objetivos

Manual con ejemplos básicos de uso de RMAN.

1. Conectarse al catalogo de RMAN de una BBDD determinada

[ora11g@prueba ~]$ rman target / catalog rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 1 10:00:45 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBA11G (DBID=337560195)
recovery catalog database Password: *****
connected to recovery catalog database

2. Crear y Configurar un catálogo de recuperación.

2.1. Configurar el catálogo de recuperación de la Base de datos

Conectarse a la BBDD para crear un tablespace nuevo

$ sqlplus /nolog
SQL> connect /as sysdba  
Conectado.
Crear un tablespace
SQL> create tablespace nuevo datafile '/database/dba11g/nuevo.dbf' size 2M autoextend on;
Tablespace creado.              

2.2. Crear el propietario del catálogo de recuperación.

SQL> create user rman   
2  identified by rman   
3  default tablespace nuevo  
4  quota unlimited on nuevo;

SQL> grant connect to rman
SQL> grant recovery_catalog_owner to rman;

2.3. Crear el catálogo de recuperación

$ rman target / catalog rman@rman
rman> create catalog;

3. Sincronizar el catálogo de recuperación

3.1. Registrar una BBDD

$ rman target / catalog rman@rman
RMAN> register database;

3.2. Desregistrar una BBDD

$ Rman target / catalog rman@rman
RMAN> unregister database;

4. Borrar catalog de recuperación

RMAN> connect catalog rman/rman@rman
RMAN> drop catalog;
RMAN> drop catalog;

5. Actualización de versión del catálogo de recuperación.

RMAN> upgrade catalog;

6. Crear y usar un catálogo virtual privado

6.1. Crear el propietario del catálogo virtual privado.

$ sqlplus /nolog
SQL> connect /as sysdba
SQL> create user user_virtual  
2  identified by uservirtual  
3  default tablespace users  
4  quota unlimited on users;

6.2. Otorgar permisos al propietario del catálogo virtual privado.

SQL> grant recovery_catalog_owner to user_virtual;
SQL> grant catalog for database dba11g to user_virtual;
SQL> grant register database to user_virtual;

6.3 Crear un catálogo virtual privado.

$ rman target / catalog user_virtual/uservirtual@rman
RMAN> create virtual catalog;

Nota: Si la versión del cliente rman es anterior a Oracle Database 11g se debe ejecutar la siguiente sentencia:

RMAN> exe rman.dbms_rcvcat.create_virtual_catalog;

6.4 Usar el catálogo virtual privado.

Registramos una base de datos para este catálogo

$ rman target / catalog user_virtual/uservirtual@rman
RMAN> register database;

VIsualizamos las bases de datos registradas

$ sqlplus  user_virtual/user_virtual@rman
SQL> Select distict db_name from dbinc;

7. Backups ( ejemplo con base de datos dba11g )

Nos conectamos a la base de datos contra el catálogo

$ export ORACLE_SID=dba11g$ 
rman target / catalog rman/rman@rman
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 4 09:55:30 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBA11G (DBID=337560195)
connected to recovery catalog database

7.1 Backup completo de la BBDD (Whole Database Backup)

RMAN> backup as copy database spfile plus archivelog;

7.2 Full Backups

RMAN> backup database spfile plus archivelog;

7.3. Incremental Backups

Level 0 Incremental Backups

RMAN> backup incremental level 0 tablespace users; 

Differential Incremental Backups

RMAN> backup incremental level 1 tablespace users;

7.4 Cumulative Incremental Backups

RMAN> backup incremental level 1 cumulative tablespace users;

7.5 Comprimir backups

RMAN> backup as compressed backupset format ‘/ubicacion/ rman_%d_s%_t%_%p.bkupset’ tablespace users;

RMAN> backup as compressed backupset users;

7.6 Borrar todos los backups

RMAN> delete backup

7.7 Eliminar los backups obsoletos

RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 1 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
-----
Deleted 17 objects

7.8 Más tipos de backup con RMAN

Backup de toda la base de datos

RMAN> backup database; 

Backup de un tablespace

RMAN> backup tablespace tbs_name;

Backup de un usuario

RMAN> backup user username; 

8. Recover/ Recuperación con RMAN

8.1 Recuperación total de una base de datos

Partimos de un ‘Whole Database Backup’ y se ha borrado toda la base de datos

 RMAN> backup as copy database spfile plus archivelog;

La recuperación se realiza de la siguiente forma:
La base de datos tiene que estar en modo nomount$sqlplus /nolog

SQL> connect /as sysdba
SQL> shutdown abort;  
SQL> startup nomount;

Recuperar la BBDD

RMAN> restore database;  
RMAN> recover database;

Montar la BBDD y abrir la base de datos con un resetlogs

SQL> alter database mount;
SQL> alter database open resetlogs;    

8.2 Recuperación de los controlfile

$ export ORACLE_SID=dba11g
$ rman target/ catalog  rman/rman@rman
RMAN> restore controlfile;

8.3 Comandos generales para recuperar la BBDD

RMAN> restore database;  
RMAN> recover database;

8.4 Comandos para la recuperación a tener en cuenta

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

9. Listar los backups

RMAN> list backup;

LIST BACKUP OF DATAFILE

Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE My Oracle Support

Step by step guide on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take a backup)
This is feature available in Oracle11g onwards.

In case you may want or need more about your current topic - please also access the Backup & Recover Community of Customers and Oracle Specialists directly via:
https://community.oracle.com/community/support/oracle_database/database_backup_and_recovery

Database Name :- chicago
Primary db_unique_name :- chicago
standby db_unique_name :- Boston

For duplicating a NON-Standby database, see :
   Note 452868.1 RMAN 'Duplicate From Active Database' Feature in 11G

Solution

1. Make the necessary changes to the primary database.
    a. Enable force logging.
    b. Creating the password file if one does not exist.
    c. Create standby redologs.
    d. Modify the parameter file suitable for Dataguard.
2. Ensure that the sql*net connectivity is working fine.
3. Create the standby database over the network using the active(primary) database files.
    a. Create the password file
    b. Create the initialization parameter file for the standby database (auxiliary database)
    c. Create the necessary mount points or the folders for the database files
    d. Run the standby creation ON STANDBY by connecting to primary as target database.

DUPLICATE TARGET DATABASE 
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
   PARAMETER_VALUE_CONVERT '', ''
   SET DB_FILE_NAME_CONVERT '', ''
   SET LOG_FILE_NAME_CONVERT '', ''
   SET SGA_MAX_SIZE 200M
   SET SGA_TARGET 125M;

4. Check the log shipping and apply.

PROCEDURE:

While creating the standby database we use the active database files i.e., this command will be useful in creating the physical standby database using active database files over the network.

1. Prepare the production database to be the primary database

a. Ensure that the database is in archivelog mode .

SQL> select log_mode from v$database;

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

b. Enable force logging

SQL> ALTER DATABASE FORCE LOGGING;

c. Create standby redologs

SQL> alter database add standby logfile '' size ;

d. Modify the primary initialization parameter for dataguard on primary,

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/chicago/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=boston;
System altered.

SQL> alter system set FAL_CLIENT=chicago;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/boston/data/','/u01/app/oracle/databases/chicago/data' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/boston/redo/','/u01/app/oracle/databases/chicago/redo' scope=spfile;
System altered.

2. Ensure that the sql*net connectivity is working fine.

Insert a static entry for Boston in the listener.ora file of the standby system.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = boston.us.oracle.com)
     (ORACLE_HOME = /u01/app/oracle/product/OraHome111)
     (SID_NAME = boston)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
  )

TNSNAMES.ORA for the Primary and Standby should have BOTH entries

CHICAGO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = chicago.us.oracle.com))
  )

BOSTON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = boston.us.oracle.com))
  )

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

% tnsping chicago
% tnsping boston

3. Create the standby database

a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

b. Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=chicago
DB_UNIQUE_NAME=boston
DB_BLOCK_SIZE=

c. Create the necessary directories in the standby location to place database files and trace files ($ADR_HOME).

d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

% export ORACLE_SID=boston
% sqlplus "/ as sysdba"
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcore1.ora

NOTE : Use either PFILE or SPFILE

# Addtl. comment
# If DUPLICATE without TARGET connection is used you cannot use SPFILE
# else getting

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

e. Verify if the connection 'AS SYSDBA' is working

% sqlplus /nolog
SQL> connect sys/SQL> connect sys/@chicago AS SYSDBA

f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

$ rman target sys/sys@chicago auxiliary sys/sys@boston

connected to target database: CHICAGO (DBID=761464750)
connected to auxiliary database: CHICAGO (not mounted)

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'chicago','boston'
  set db_unique_name='boston'
  set db_file_name_convert='/chicago/','/boston/'
  set log_file_name_convert='/chicago/','/boston/'
  set control_files='/u01/app/oracle/oradata/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='boston'
  set fal_server='chicago'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(chicago,boston)'
  set log_archive_dest_2='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'
;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=147 device type=DISK

allocated channel: prmy2
channel prmy2: SID=130 device type=DISK

allocated channel: prmy3
channel prmy3: SID=137 device type=DISK

allocated channel: prmy4
channel prmy4: SID=170 device type=DISK

allocated channel: stby
channel stby: SID=98 device type=DISK

Starting Duplicate Db at 19-MAY-08

contents of Memory Script:
{
backup as copy reuse
file '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore1'
file'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore.ora' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora' ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''";
}
executing Memory Script

Starting backup at 19-MAY-08
Finished backup at 19-MAY-08

sql statement: alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =''/u02/app/oracle/admin/boston/adump'' comment='''' scope=spfile";
sql clone "alter system set dispatchers =''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment='''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=boston'' comment='''' scope=spfile";
sql clone "alter system set db_unique_name =''boston'' comment='''' scope=spfile";
sql clone "alter system set db_file_name_convert =''/chicago/'', ''/boston/'' comment='''' scope=spfile";
sql clone "alter system set log_file_name_convert =''/chicago/'', ''/boston/'' comment='''' scope=spfile";
sql clone "alter system set control_files =''/u01/app/oracle/oradata/control01.ctl'' comment='''' scope=spfile";
sql clone "alter system set log_archive_max_processes =5 comment='''' scope=spfile";
sql clone "alter system set fal_client =''boston'' comment='''' scope=spfile";
sql clone "alter system set fal_server =''chicago'' comment='''' scope=spfile";
sql clone "alter system set standby_file_management =''AUTO'' comment='''' scope=spfile";
sql clone "alter system set log_archive_config =''dg_config=(chicago,boston)'' comment='''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =''service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'' comment='''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u02/app/oracle/admin/boston/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=boston'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''boston'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/chicago/'', ''/boston/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/chicago/'', ''/boston/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''boston'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''chicago'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(chicago,boston)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 845348864 bytes

Fixed Size 1303188 bytes
Variable Size 482348396 bytes
Database Buffers 356515840 bytes
Redo Buffers 5181440 bytes

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 19-MAY-08
channel prmy1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.1.0/db_1/dbs/snapcf_chicago.f tag=TAG20080519T173406 RECID=2 STAMP=655148053
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 19-MAY-08

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to"/u02/app/oracle/oradata/boston/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to "/u02/app/oracle/oradata/boston/system01.dbf";
set newname for datafile 2 to "/u02/app/oracle/oradata/boston/sysaux01.dbf";
set newname for datafile 3 to "/u02/app/oracle/oradata/boston/undotbs01.dbf";
set newname for datafile 4 to "/u02/app/oracle/oradata/boston/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format "/u02/app/oracle/oradata/boston/system01.dbf"
datafile 2 auxiliary format "/u02/app/oracle/oradata/boston/sysaux01.dbf"
datafile 3 auxiliary format "/u02/app/oracle/oradata/boston/undotbs01.dbf"
datafile 4 auxiliary format "/u02/app/oracle/oradata/boston/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/app/oracle/oradata/boston/temp01.dbf in control file

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 19-MAY-08
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u02/app/oracle/oradata/chicago/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u02/app/oracle/oradata/chicago/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u02/app/oracle/oradata/chicago/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u02/app/oracle/oradata/chicago/users01.dbf
output file name=/u02/app/oracle/oradata/boston/undotbs01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:00:24
output file name=/u02/app/oracle/oradata/boston/users01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:00:16
output file name=/u02/app/oracle/oradata/boston/system01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:02:32
output file name=/u02/app/oracle/oradata/boston/sysaux01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:02:32
Finished backup at 19-MAY-08

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=655148231 file name=/u02/app/oracle/oradata/boston/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=655148231 file name=/u02/app/oracle/oradata/boston/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=655148231 file name=/u02/app/oracle/oradata/boston/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=655148231 file name=/u02/app/oracle/oradata/boston/users01.dbf
Finished Duplicate Db at 19-MAY-08
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4

4. Start managed recovery

Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.

Example :

SQL> alter database recover managed standby database disconnect from session;

5. Open standby database in Read Only (active dataguard)

If you are licensed to use Active Dataguard (ADG) than open the Standby Database in READ ONLY and start the recovery.

SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;

Configuracion Dataguard 11g My Oracle Support

Pre-Requisitea) Database (PRIMARY) is up and running and the datafiles, controlfiles residing in ASM.
b) ASM instance and diskgroups configured in STANDBY server.
c) Network connectivity between PRIMARY and STANDBY server
PROCEDURE
1.Enable force logging in PRIMARY.
2.Create SRL(standby redo logs) in PRIMARY.
3.Backup the PRIMARY database.
4.Make proper changes in the parameter file of PRIMARY.
5.Create the parameter file for STANDBY
6.Copy the files (RMAN Backup, init.ora) to STANDBY
7.Establish the connectivity between PRIMARY and STANDBY.
8.Start the STANDBY instance and use RMAN duplicate to create standby database
9. Create SRL on standby.
10.Start the MRP process,
11. Verify whether the log are shipped and applied properly @the standby


Consider two databases of names PRIMARY= PRIMA  and STANDBY= MYSTD

1. Enable Forced Logging on PRIMARY

SQL> ALTER DATABASE FORCE LOGGING;

2. Configure Standby Redo Log on PRIMARY

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

a. Check the log files and sizes,

SQL>SELECT GROUP#,BYTES FROM V$LOG;

b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

NOTE: The SRL will be created in the ASM Disk group mentioned in init.ora parameter DB_CREATE_ONLINE_LOG_DEST_1

c. Verify the standby redo log file groups were created(do this after the creation of standby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Use RMAN to backup PRIMARY database with archivelog & controlfile.

RMAN> backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN> backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';

4. Make the necessary changes to PRIMARY  .

DB_NAME=PRIMA
DB_UNIQUE_NAME=PRIMA
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
LOG_ARCHIVE_DEST_2= 'SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYSTD'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=MYSTD
FAL_CLIENT=PRIMA
DB_FILE_NAME_CONVERT='MYSTD','PRIMA'
LOG_FILE_NAME_CONVERT='MYSTD','PRIMA'
STANDBY_FILE_MANAGEMENT=AUTO


5. Create the parameter file for standby,

a. CREATE PFILE='' from spfile; (@primary,)
b. Make the necessary changes, for example,

DB_NAME=PRIMA
DB_UNIQUE_NAME=MYSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
CONTROL_FILES='+DATA','+FRA'
DB_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMA
FAL_CLIENT=MYSTD

Note: If the ASM Disk groups names are different between PRIMARY and STANDBY do the necessary changes in STANDBY with the parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n

6. Copy the files (RMAN Backup, init.ora) to STANDBY
a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical location
Example:scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*
b) Copy the init.ora (step 5) to STANDBY
c) Recreate the password file in standby using orapwd utility


7. Establish the connectivity between primary and standby.

In PRIMARY (TNSNAMES.ORA)

MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)

In STANDBY (TNSNAMES.ORA)
PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)

NOTE: Use TNSPING to check the connectivity between PRIMARY and STANDBY

8. Start the STANDBY instance and use RMAN duplicate to create standby database

NOTE: Make sure the ASM instance also running.
$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’’
SQL>startup nomount 

NOTE: Connect to catalog if your primary database has catalog database.

$RMAN target sys/@primary catalog RMAN/RMAN@RMAN auxiliary sys/
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}
9. Create SRL(Standby Redo logs) on standby,

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

For Example,

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

10. Start the MRP process in PRIMARY

In PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In STANDBY
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Verify whether the log are shipped and applied properly in the standby

a. execute on PRIMARY database

SQL> ALTER SYSTEM SWITCH LOGFILE;

b. execute on STANDBY database

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

c. Issue more log switches at PRIMARY

SQL> ALTER SYSTEM SWITCH LOGFILE;

d. Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

RMAN> catalog start with '/u01/backups/rman/2016-01-21/' noprompt

This article is an update of a previous Oracle 9i article.

• Introduction
• Backup-Based Duplication
• Active Database Duplication

Related articles.

• Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2)

Introduction

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.
The article assumes the duplicate database is being created on a separate server, using the same SID (DB11G) and the same file structure as the source database. Explanations of several other scenarios are available here.

Backup-Based Duplication

Create a backup of the source database, if a suitable one doesn't already exist.

$ rman target=/

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG

All subsequent actions occur on the server running the duplicate database.
Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10

Add the appropriate entries into the "tnsnames.ora" file in the "$ORACLE_HOME/network/admin" directory to allow connections to the target database from the duplicate server.

# Added to the tnsnames.ora
DB11G-SOURCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

Create a PFILE for the duplicate database. Since we are duplicating the database onto a separate server with the same filesystem as the original, we don't need to convert the file names. In this case, the PFILE is called "initDB11G.ora" and is placed in the "$ORACLE_HOME/dbs" directory.

# Minimum Requirement.
DB_NAME=DB11G

# Convert file names to allow for different directory structure if necessary.
#DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u01/app/oracle/oradata/NEWSID/'
#LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u02/app/oracle/oradata/NEWSID/'

We don't need all the other parameters as the clone will copy the SPFILE from the primary database. If you are duplicating a database on the same machine you will probably need to create a PFFILE/SPFILE manually from the primary database SPFILE, manually change the values and avoid the SPFILE clause in the duplicate command.
Create any directories necessary for start the duplicate database.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Make the backup files from the source database available to the destination server. That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use the following type of commands.

$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/autobackup /u01/app/oracle/fast_recovery_area/DB11G

Connect to the duplicate instance.

$ ORACLE_SID=DB11G; export ORACLE_SID
$ sqlplus / as sysdba

Start the database in NOMOUNT mode.

SQL> STARTUP NOMOUNT;

With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).

$ ORACLE_SID=DB11G; export ORACLE_SID

# No target or catalog. Metadata comes from backups.
$ rman AUXILIARY /

# Target, but no catalog. Metadata comes from target database controlfile.
$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY /

# Catalog, but no target. Metadata comes from the RMAN catalog.
$ rman CATALOG rman/password@rman-catalog AUXILIARY /

# Target and catalog. Metadata can come from the target controlfile or the catalog.
$ rman TARGET sys/password@DB11G-SOURCE CATALOG rman/password@rman-catalog AUXILIARY /

We can then duplicate the database using one of the following commands. Remember, remove the SPFILE clause if you have manually created a full PFILE or SPFILE.

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DB11G
  SPFILE
  NOFILENAMECHECK;

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

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

# Do a point-in-time clone.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK
  UNTIL TIME "TO_DATE('2014-11-17 07:00:00', 'YYYY-MM-DD HH24:MI:SS')";

# Alter some SPFILE parameters during the clone.
DUPLICATE DATABASE TO MYCLONE
  SPFILE
    parameter_value_convert ('DB11G','MYCLONE')
    set db_file_name_convert='/u01/oradata/db11g/','/u01/oradata/myclone/'
    set log_file_name_convert='/u01/oradata/db11g/','/u01/oradata/myclone/'
    set control_files='/u01/oradata/myclone/control01.ctl','/u01/oradata/myclone//control02.ctl','/u01/oradata/myclone/control03.ctl'
    set db_name='MYCLONE'
    set log_archive_dest_1='location=/u01/oradata/myclone/arch'
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;

The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.
First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.
The passwords in the password files must match for both servers, so remember to set the correct password when creating the password file on the destination server.
Both the source and destination database servers require a "tnsnames.ora" entry for the destination database. In this case I added the following to each server. The destination server still requires the source entry shown in the previous section.

# Added to the tnsnames.ora on source and destination server.
DB11G-DESTINATION =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

The destination server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration. Remember to restart or reload the listener.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY sys/password@DB11G-DESTINATION

Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.

DUPLICATE DATABASE TO DB11G
  FROM ACTIVE DATABASE
  SPFILE
  NOFILENAMECHECK;

How to solve ORA-04044: procedure, function, package, Script .sh

How to solve ORA-04044: procedure, function, package, or type is not allowed here

While creating shell script some time we face error

ORA-04044: procedure, function, package, or type is not allowed here

NOTE:

this is because we are using tables containing '$' SYMBOL and when ever we use tables like v$session,v$log,v$logfile etc shell script take '$' SYMBOL as shell variables and then while reading script it gives error

ORA-04044: procedure, function, package, or type is not allowed here

SOLUTION:-

for overcome this error always use escape '\' SYMBOL before '$' SYMBOL in table name

like we are use

select * from v$session;

select * from v$log;

in shell script we have to use

select * from v\$session;

select * from v\$log;

and this will solve our error and sucessfully run our shell script.

Control_file_record_keep_time

RMAN and control_file_record_keep_time
If the control_file_record_keep_time is less than the retention policy then it may overwrite reusable records prior to obsoleting them in the RMAN metadata. Therefore it is recommended that the control_file_record_keep_time should set to a higher value than the RMAN retention policy.  Hence:

control_file_record_keep_time = retention period + level 0 backup interval + 1

For example, a level 0 backup once a week with retention policy of a recovery windows of 14 days would have a control_file_record_keep_time of 22 ( 14+7+1).

 

SQL> alter system set control_file_record_keep_time=30 scope=both;

System altered.

SQL> show parameter control;

NAME      TYPE VALUE

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

control_file_record_keep_time      integer 30

control_files      string +DATA/ACME/CONTROLFILE/current

.261.848598131, +FRA/ACME/CONT

ROLFILE/current.256.848598131

control_management_pack_access      string DIAGNOSTIC+TUNING

SQL> 

Data Encryption - DBMS_OBFUSCATION_TOOLKIT Security

Create the Package

First we create the package header containing the two conversion functions.

CREATE OR REPLACE PACKAGE toolkit AS

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW;
  
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2;
  
END toolkit;
/

Create the Package Body

All VARCHAR2 inputs are padded to multiples of 8 charaters, with the encryption key also being a multiple of 8 charaters. The encryption key and padding characters can be altered to suit.

CREATE OR REPLACE PACKAGE BODY toolkit AS

  g_key     RAW(32767)  := UTL_RAW.cast_to_raw('12345678');
  g_pad_chr VARCHAR2(1) := '~';

  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);


  -- --------------------------------------------------
  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW IS
  -- --------------------------------------------------
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
  BEGIN
    padstring(l_text);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),
                                        key            => g_key,
                                        encrypted_data => l_encrypted);
    RETURN l_encrypted;
  END;
  -- --------------------------------------------------



  -- --------------------------------------------------
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2 IS
  -- --------------------------------------------------
    l_decrypted  VARCHAR2(32767);
  BEGIN
    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key   => g_key,
                                        decrypted_data => l_decrypted);
                                        
    RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
  END;
  -- --------------------------------------------------


  -- --------------------------------------------------
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2) IS
  -- --------------------------------------------------
    l_units  NUMBER;
  BEGIN
    IF LENGTH(p_text) MOD 8 > 0 THEN
      l_units := TRUNC(LENGTH(p_text)/8) + 1;
      p_text  := RPAD(p_text, l_units * 8, g_pad_chr);
    END IF;
  END;
  -- --------------------------------------------------

END toolkit;
/

Test It

We can test the basic functionality using the following code.

DECLARE
  l_value VARCHAR2(16) := 'ORACLE-BASE';
  l_raw   RAW(16);
BEGIN
  DBMS_OUTPUT.put_line('l_value: ' || l_value);
  l_raw := toolkit.encrypt(l_value);
  DBMS_OUTPUT.put_line('l_raw: ' || l_raw);
  DBMS_OUTPUT.put_line('Original Value : ' || toolkit.decrypt(l_raw));
END;
/

Remember that the length of the output from the encryption routine will be rounded up to the next multiple of 8 characters. If the results are to be stored as RAW datatypes in the database you must make sure enough room is allocated. SQL*Plus displays the contents of RAW variable in HEX so it appears to be twice as long as it actually is.

Encrypt Table Data

The following code provides a simple example of how data in a table might be encrypted using a trigger. First we must create a test table with an appropriate trigger.

CREATE TABLE encrypted_data (
  username  VARCHAR2(20),
  data      RAW(16)
);

CREATE OR REPLACE TRIGGER encrypted_data_biur_trg 
BEFORE INSERT OR UPDATE ON encrypted_data
FOR EACH ROW
DECLARE
BEGIN
  :new.data := toolkit.encrypt(UTL_RAW.cast_to_varchar2(:new.data));
END;
/

Next we test the trigger using some simple insert, update and query statements.

SQL> INSERT INTO encrypted_data (username, data)
  2  VALUES ('tim_hall', UTL_RAW.cast_to_raw('My Secret Data'));

1 row created.

SQL> SELECT * FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             FA57C55510D258C73DE93059E3DC49EC

1 row selected.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My Secret Data

1 row selected.

SQL> UPDATE encrypted_data
  2  SET    data     = UTL_RAW.cast_to_raw('My NEW Secret')
  3  WHERE  username = 'tim_hall';

1 row updated.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My NEW Secret

1 row selected.

SQL>

show snapshot controlfile name

RMAN> show snapshot controlfile name;

Configure it to the shared disk group as below:

[oracle@db2 bin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 29 01:30:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PDB (DBID=518531946)

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/snapcf_PROD021.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';
new RMAN configuration parameters are successfully stored

.PROFILE GRID

#
# Simple profile places /usr/bin at front, followed by /usr/sbin.
#
# Use less(1) or more(1) as the default pager for the man(1) command.
#
export PATH=/usr/bin:/usr/sbin
if [ -f /usr/bin/less ]; then
    export PAGER="/usr/bin/less -ins"
elif [ -f /usr/bin/more ]; then
    export PAGER="/usr/bin/more -s"
fi
#
# Define default prompt to @:<"($|#) ">
# and print '#' for user "root" and '$' for normal users.
#
# Currently this is only done for bash/pfbash(1).
#
case ${SHELL} in
*bash)
    typeset +x PS1="\u@\h:\w\\$ "
    ;;
esac
export AWT_TOOLKIT=XToolkit
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid_1
export ORACLE_SID=+ASM1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/usr/sbin:/usr/X11/bin:/usr/dt/bin:/usr/openwin/bin:/usr/sfw/bin:/usr/sfw/sbin:/usr/ccs/bin:/usr/local/bin:/usr/local/sbin:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:$ORACLE_HOME/oracm/lib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export TEMP=/tmp
export TMPDIR=/tmp
ulimit -n 65536

# Alias
alias sql='sqlplus / as sysasm'