jump to navigation

Oracle Backup and Recovery for a VLDB (Very Large Database) February 10, 2011

Posted by tamnau in Database, General, RMAN.
Tags: , , , , , , , , , , , , ,
4 comments

There is an ongoing focus for businesses to minimise downtime and increase operational continuity.  With such challenges, IT staff are constantly under pressure to meet such demands.   For DBAs, backup and recovery is one of the key areas which can be reviewed to ensure that a faster approach to recovery can be achieved to reduce downtime.

An RMAN feature called Incremental Merge (also referred to as Incremental Updated)  Backups can significantly reduce recovery time if configured correctly.  Perhaps not as widely used but has been around since 10g,  it is the ideal backup methodology for a VLDB (Very Large Database).  How it works is that the image copies of the data files are created and  incrementals are then applied rolling forward the image copies after each backup operation.   It is also important to enable fast incremental backups with BCT (Block Change Tracking). For more detailed information on BCT, please refer to the Pythian Whitepaper written by Alexander Gorbachev.

The table below lists the configuration that will be used for the following demonstration:

Description Value
Database Instance ORACLE_SID=VLDB
Host lnx01.earth.com
RMAN Catalog DB (TNS Name) RMANCAT
RMAN Catalog User rman


SECTION 1 – CONFIGURE BLOCK CHANGE TRACKING

1. To create block change tracking file, run the command in sqlplus as sysdba:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '<path_to_block_change_file_name>';

For example:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u02/oradata/VLDB/bct_VLDB.ctf';

IMPORTANT NOTE: If running in a RAC configuration, the following file needs to be placed on the cluster shared file system.

2. Login to database and verify the location of data files.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u02/oradata/VLDB/o1_mf_system_7o8kj2gt_.dbf
2 /u02/oradata/VLDB/o1_mf_sysux_2o3mt9jl_.dbf
3 /u02/oradata/VLDB/o1_mf_undotbs_1c5gb8nh_.dbf
4 /u02/oradata/VLDB/o1_mf_users_5v5db3vv_.dbf


SECTION 2 – CONFIGURE RMAN PARAMETERS

1. Register database with RMAN Catalog if using Catalog mode. See example here.

2. Connect to RMAN in catalog (or no catalog) mode.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] rman target / catalog rman/@RMANCAT

3. Configure the preferred RMAN Recovery Window. In the example, the recovery window will be 3 days.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

4. It is also recommended that you enable autobackup of controlfile in RMAN.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

SECTION 3 – Performing the Incremental Merge (Updated) Backup

1. Perform the backup of the database in RMAN, and this will create an image copy on the initial run otherwise it will apply the incrementals and rolls forward the image copies of data files.
RMAN> run
{
sql 'alter system archive log current';
recover device type disk
copy of database with tag 'MIB_UPDATE';
backup incremental level 1
for recover of copy with tag 'MIB_UPDATE' database;
sql 'alter system archive log current';
}
list copy of database tag 'MIB_UPDATE';
list backup tag 'MIB_UPDATE';

2. Repeat the previous step for any future or subsequent backups.

SECTION 4 – Performing the Recovery from Incremental Merge (Updated) Backup

An unexpected outage has occurred and now a recovery is required to be performed to bring the database back up quickly as possible.

1. To perform the database recovery ensure that the database in MOUNT mode.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown abort
SQL> startup mount

2. In RMAN, switch database to use image copy and then perform recovery.
RMAN> set echo on;
switch database to copy;
run {
recover database;
alter database open;
}

3. Login to database and verify the location of data files are pointing to the recovered image copies.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u03/orafra/VLDB/datafile/o1_mf_system_6o7mj1gt_.dbf
2 /u03/orafra/VLDB/datafile/o1_mf_sysaux_6o7mj4j9_.dbf
3 /u03/orafra/VLDB/datafile/o1_mf_undotbs1_6o7ml4yx_.dbf
4 /u03/orafra/VLDB/datafile/o1_mf_users_6o7mlhl4_.dbf

SECTION 5 – Switching database files back to original data file location

As the database is continuing to run from where the recovered image copies are located, there may be a desire to restore it back to its original location. This is optional, however it is recommended if you are running image copies from FRA (Fast Recovery Area).

1. Backup the database in RMAN
RMAN> run
{
ALLOCATE CHANNEL d1 TYPE DISK FORMAT '/u02/oradata/VLDB/%U.dbf';
recover
copy of database with tag 'MIB_RELOCATE';
backup incremental level 1
for recover of copy with tag 'MIB_RELOCATE' database;
release channel d1;
}
list copy of database tag 'MIB_RELOCATE';
list backup tag 'MIB_RELOCATE';

NOTE: If this is the first time of running the backup to new location, it will create the image copies of the datafiles.

2. Repeat the previous step for any future or subsequent backups until a time can be scheduled to switch back.

3. When a planned time has been scheduled and switch back can be performed, shutdown database and startup in MOUNT mode.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount

4. Switch database to use image copy and then perform recovery.  The database will now be started using image copies which is now configure in the original data file location.
RMAN> set echo on;
switch database to copy;
run {
recover database;
alter database open;
}

5. Login to database and verify the data files are pointing to the recovered image copies back in the original location (or directory).
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-SYSTEM_FNO-1_cim4edhc.dbf
2 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-SYSAUX_FNO-2_cjm4edig.dbf
3 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-UNDOTBS1_FNO-3_ckm4edjj.dbf
4 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-USERS_FNO-4_clm4edk2.dbf

CONSIDERATIONS
In conclusion, it is worth noting that when using RMAN Incremental Merge (Updated) Backup strategy, it is important to consider the following:

  • More storage is required to store the copy of database in addition to the traditional incremental backups.
  • For Point-In-Time recovery, continue to at least perform one full backup of database and include archive logs.  This can be can be performed less frequently (ie weekly, fortnightly, monthly).
  • Validate image copies and backups to detect if there is corruption or missing files which may compromise recovery.
  • Image copies of database should be on same storage tier as online data files to ensure performance it is not impacted when switching to database copy for recovery.
  • Image copies do not have to be stored in FRA, but should be stored on disk.

Cloning a RAC Database May 20, 2010

Posted by tamnau in General, RAC, RMAN.
Tags: , , , , ,
7 comments

Cloning a RAC Database may appear somewhat challenging, but its actually cloning a database as if it was a single instance and then enabling it as a cluster post to the duplication.

In the below scenario, I will demonstrate how this can be achieved by cloning a RAC database and have it manage by the same cluster. Please note that the following was tested for version 11.1 RAC with ASM.

The RAC configuration used for this example:

example_conifig_clone_rac

NOTE*:  Control file and online redo log copies, archived redo log files can be stored in the Flash Recovery Area (FRA) Disk Group.

1. Log onto source RAC database:

[oracle@lnx01] export ORACLE_SID=SALT1; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> create pfile='/app/oracle/scripts/clone/initPEPPER.ora';

2. Edit /tmp/initPEPPER.ora and change the appropriate values.
Remove the following entries:

<SOURCE_INSTANCE1>.__db_cache_size=7650410496
<SOURCE_INSTANCE2>.__db_cache_size=7583301632
<SOURCE_INSTANCE1>.__java_pool_size=67108864
<SOURCE_INSTANCE2>.__java_pool_size=67108864
<SOURCE_INSTANCE1>.__large_pool_size=67108864
<SOURCE_INSTANCE2>.__large_pool_size=67108864
<SOURCE_INSTANCE1>.__oracle_base='/app/oracle'
<SOURCE_INSTANCE2>.__oracle_base='/app/oracle'
<SOURCE_INSTANCE1>.__pga_aggregate_target=6643777536
<SOURCE_INSTANCE2>.__pga_aggregate_target=6643777536
<SOURCE_INSTANCE1>.__sga_target=9462349824
<SOURCE_INSTANCE2>.__sga_target=9462349824
<SOURCE_INSTANCE1>.__shared_io_pool_size=0
<SOURCE_INSTANCE2>.__shared_io_pool_size=0
<SOURCE_INSTANCE1>.__shared_pool_size=1409286144
<SOURCE_INSTANCE2>.__shared_pool_size=1476395008
<SOURCE_INSTANCE1>.__streams_pool_size=134217728
<SOURCE_INSTANCE2>.__streams_pool_size=134217728

3. Adjust Memory Target accordingly for your environment

*.memory_target=2G

4. Set spfile parameter:

*.spfile='+DG_DATA/PEPPER/spfilePEPPER.ora'

5. Disable the cluster_database parameter:

*.cluster_database=FALSE

6. Create init parameter under dbs:

[oracle@lnx01] export ORACLE_HOME=/app/oracle/product/11.1
[oracle@lnx01] cd $ORACLE_HOME/dbs
[oracle@lnx01] echo "SPFILE='+DG_DATA/PEPPER/spfilePEPPER.ora'" > $ORACLE_HOME/dbs/initPEPPER.ora

7. Edit initPEPPER.ora and add the following convert parameters:

*.log_file_name_convert=('+DG_DATA/SALT','+DG_DATA/PEPPER')
*.db_file_name_convert=('+DG_DATA/SALT','+DG_DATA/PEPPER')

8. Create the directories:

a) From one node only, create the ASM path for PEPPER database:

[oracle@lnx01] export ORACLE_SID=+ASM1; . oraenv
[oracle@lnx01] asmcmd mkdir +DG_DATA/PEPPER
[oracle@lnx01] asmcmd mkdir +DG_DATA/PEPPER/brsadata

b) From one node only, create the OS path for PEPPER admin directories:

[oracle@lnx01] mkdir -p /app/oracle/admin/PEPPER/adump
[oracle@lnx01] mkdir -p /app/oracle/diag/rdbms/PEPPER/PEPPER1/cdump
[oracle@lnx01] mkdir -p /app/oracle/diag/rdbms/PEPPER/PEPPER1/trace

c) Repeat step b on node 2 and other nodes in RAC:

[oracle@lnx02] mkdir -p /app/oracle/admin/PEPPER/adump
[oracle@lnx02] mkdir -p /app/oracle/diag/rdbms/PEPPER/PEPPER2/cdump
[oracle@lnx02] mkdir -p /app/oracle/diag/rdbms/PEPPER/PEPPER2/trace

9. Create a TNS entry for target auxiliary database and place in the tnsnames.ora. Add these entries on all nodes in cluster:

PEPPER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02-vip)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = PEPPER)
 )
 )

LISTENERS_PEPPER =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02-vip)(PORT = 1521))
 )

LISTENER_PEPPER1 =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01-vip)(PORT = 1521))

LISTENER_PEPPER2 =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02-vip)(PORT = 1521))

10. If no listener.ora exists, create one as follows

# Node 1
 LISTENER_lnx01 =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01-vip)(PORT = 1521)(IP = FIRST))
 (ADDRESS = (PROTOCOL = TCP)(HOST = 207.23.2.122)(PORT = 1521)(IP = FIRST))
 )
 )
# Node 2
 LISTENER_lnx02 =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02-vip)(PORT = 1521)(IP = FIRST))
 (ADDRESS = (PROTOCOL = TCP)(HOST = 207.23.2.126)(PORT = 1521)(IP = FIRST))
 )
 )

11. Create an SPFILE from the parameter file, and start the auxiliary instance:

[oracle@lnx01] export ORACLE_HOME=/app/oracle/product/11.1
[oracle@lnx01] export ORACLE_SID=PEPPER
SQL> CONNECT / as sysdba
SQL> CREATE SPFILE='+DG_DATA/PEPPER/spfilePEPPER.ora'
FROM PFILE='/app/oracle/scripts/clone/initPEPPER.ora';
SQL> STARTUP NOMOUNT;

12. Then run the following RMAN commands to duplicate the database:

[oracle@lnx01] rman target sysdba/<password>@SALT auxiliary /
RMAN> DUPLICATE TARGET DATABASE TO 'PEPPER';

14. Add the entry for oratab file (located under /etc or /var/opt/oracle) for target auxiliary db:

a) Add in For node 1 oratab file:

PEPPER1:/app/oracle/product/11.1:N

b) Add in For node 2 oratab file:

PEPPER2:/app/oracle/product/11.1:N

15. Enable the cluster parameter in the target auxiliary database from one node only

[oracle@lnx01] export ORACLE_HOME=/app/oracle/product/11.1
[oracle@lnx01] export ORACLE_SID=PEPPER
[oracle@lnx01] sqlplus / as sysdba
SQL> alter system set cluster_database=TRUE scope=spfile;
SQL> shutdown immediate;

16. Rename the init file and then copy the init file across to the

[oracle@lnx01] export ORACLE_SID=PEPPER1; . oraenv
[oracle@lnx01] mv $ORACLE_HOME/dbs/initPEPPER.ora $ORACLE_HOME/dbs/initPEPPER1.ora
[oracle@lnx01] scp $ORACLE_HOME/dbs/initPEPPER1.ora \
lnx02:/app/oracle/product/11.1/dbs/initPEPPER2.ora

17. Add the second thread of online redo logs and enable that thread;

SQL> startup

18. Start up the second instance:

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 '+DG_DATA' SIZE 250M reuse;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DG_REDO' TO GROUP 5 ;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6  '+DG_REDO' SIZE 250M reuse;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DG_DATA' TO GROUP 6;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+DG_REDO' SIZE 250M reuse;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DG_DATA' TO GROUP 7;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+DG_REDO' SIZE 250M reuse;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DG_DATA' TO GROUP 8;
SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

19. Stop all instances on each node for the target auxiliary database

a) From node 1 perform the following:

[oracle@lnx01] export ORACLE_SID=PEPPER1; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown immediate;

b) Then perform for other instances in the cluster:

[oracle@lnx01] export ORACLE_SID=PEPPER2; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown immediate;

20. To add into the cluster, add the new database to srvctl:

[oracle@lnx01] srvctl add database -d PEPPER -o /app/oracle/product/11.1
[oracle@lnx01] srvctl modify database -d PEPPER -p '+DG_DATA/PEPPER/spfilePEPPER.ora' -s open
[oracle@lnx01] srvctl add instance -d PEPPER -i PEPPER1 -n lnx01
[oracle@lnx01] srvctl add instance -d PEPPER -i PEPPER2 -n lnx02

20. Start the RAC database

[oracle@lnx01] srvctl start database -d PEPPER

21. Now verify that the RAC database has now registered with the cluster.

For pre-11.2 run:

crs_stat –t

Or for 11.2 run:

crsctl stat res -t