Configuring RMAN settings with Enterprise Manager
Click Maintenance link to go to maintenance screen, and then click Configure Backup Settings link.
Database Recovery Using Enterprise Manager
Recovering the database using EM performs the identical process of performing recovery with the RMAN command-line interface (CLI), except it is done through EM web interface.
EM Main Screen -> Maintenance tab -> Select Perform Recovery under Backup/Recovery
When you start Performance Recovery wizard, it will ask for host credentials username and password. This account needs to have administrative privilege for Windows and Oracle user privilege in Unix. You also need to enter database credentials.
Showing posts with label Backup and Recovery. Show all posts
Showing posts with label Backup and Recovery. Show all posts
Friday, January 29, 2010
Performing User-Managed Incomplete Recovery
User-managed incomplete recovery can be performed by time, change, and cancelling. The methods of performing incomplete recovery are using the RECOVER DATABASE command with UNTIL TIME, CHANGE, or CANCEL clauses.
Ex: time-based recovery
Make sure that NLS_DATE_FORMAT is set to a value that you can reproduce in SQL*Plus.
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
Remove USERS01.dbf and restore it from backup using copy command to simulate the recovery situation:
C:\oracle\ora101t\> delete USERS01.dbf
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\USERS01.dbf
SQL> startup mount
SQL> recover database until time ’06-SEP-2004 15:15:00’;
SQL> alter database open resetlogs;
Ex: cancel-based recovery
Copy online backup of all the datafiles that makeup ora101t
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\*.dbf
SQL> connect / as sysdba
SQL> @backup_control_resetlogs.sql #create control file by using backup control to trace
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;
Ex: time-based recovery
Make sure that NLS_DATE_FORMAT is set to a value that you can reproduce in SQL*Plus.
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
Remove USERS01.dbf and restore it from backup using copy command to simulate the recovery situation:
C:\oracle\ora101t\> delete USERS01.dbf
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\USERS01.dbf
SQL> startup mount
SQL> recover database until time ’06-SEP-2004 15:15:00’;
SQL> alter database open resetlogs;
Ex: cancel-based recovery
Copy online backup of all the datafiles that makeup ora101t
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\*.dbf
SQL> connect / as sysdba
SQL> @backup_control_resetlogs.sql #create control file by using backup control to trace
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;
Performing Incomplete Recovery
Incomplete recovery is a recovery that stops before the failure that forced the recovery. Another way of looking at incomplete recovery is that not all the transactions in archived redo logs get applied to the database to make the database complete. With incomplete recovery, after the recovery process has ended, the database is still missing transactions that were in the database before failure.
Incomplete recovery is sometimes called database point-in-time recovery (DBPITR), because this recovery is to a determined point-in-time.
RMAN incomplete recovery is performed using SET UNTIL TIME and SET UNTIL SEQUENCE clauses prior to RECOVER command. These clauses direct the recovery process to stop at a designated time, a redo log sequence, or a system change number (SCN) before full recovery is completed.
User managed incomplete recovery is performed by using RECOVER DATABASE command in conjunction with the UNTIL TIME, UNTIL CHANGE, or UNTIL CANCEL clauses. The UNTIL CANCEL clause is designed to just stop the recovery process at a random point.
NOTE: RMAN based incomplete recovery doesn’t have a CANCEL-based option; however, RMAN-based incomplete recovery has SCN and SEQUENCE methods.
RMAN Incomplete Recovery
RMAN incomplete recovery can be performed by time, redo log sequence, or SCN. To perform an incomplete recovery, use the RECOVER DATABASE command with the UNTIL TIME, SCN, or SEQUENCE clause or use SET UNTIL clause prior to the RECOVER DATABASE command.
SQL> startup mount
Make sure NLS_DATE_FORMAT is set to a value that you can reproduce in RMAN:
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
RMAN> run
{
set until time ’06-SEP-2004 11:25:00’;
restore database;
recover database;
}
RMAN> alter database open resetlogs;
Example using sequence based recovery, which uses a redo log sequence number to terminate the recovery process. There are some steps required to identify the redo log sequence number that require accessing V$REDO_LOG_HISTORY dynamic view.
Get the sequence number and thread information using V$LOG_HISTORY.
SQL> select * from log_history;
SQL> startup mount
RMAN> run
{
set until sequence 3 thread 1;
restore database;
recover database;
}
RMAN> alter database open resetlogs;
Incomplete recovery is sometimes called database point-in-time recovery (DBPITR), because this recovery is to a determined point-in-time.
RMAN incomplete recovery is performed using SET UNTIL TIME and SET UNTIL SEQUENCE clauses prior to RECOVER command. These clauses direct the recovery process to stop at a designated time, a redo log sequence, or a system change number (SCN) before full recovery is completed.
User managed incomplete recovery is performed by using RECOVER DATABASE command in conjunction with the UNTIL TIME, UNTIL CHANGE, or UNTIL CANCEL clauses. The UNTIL CANCEL clause is designed to just stop the recovery process at a random point.
NOTE: RMAN based incomplete recovery doesn’t have a CANCEL-based option; however, RMAN-based incomplete recovery has SCN and SEQUENCE methods.
RMAN Incomplete Recovery
RMAN incomplete recovery can be performed by time, redo log sequence, or SCN. To perform an incomplete recovery, use the RECOVER DATABASE command with the UNTIL TIME, SCN, or SEQUENCE clause or use SET UNTIL clause prior to the RECOVER DATABASE command.
SQL> startup mount
Make sure NLS_DATE_FORMAT is set to a value that you can reproduce in RMAN:
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
RMAN> run
{
set until time ’06-SEP-2004 11:25:00’;
restore database;
recover database;
}
RMAN> alter database open resetlogs;
Example using sequence based recovery, which uses a redo log sequence number to terminate the recovery process. There are some steps required to identify the redo log sequence number that require accessing V$REDO_LOG_HISTORY dynamic view.
Get the sequence number and thread information using V$LOG_HISTORY.
SQL> select * from log_history;
SQL> startup mount
RMAN> run
{
set until sequence 3 thread 1;
restore database;
recover database;
}
RMAN> alter database open resetlogs;
Recovering from Control File loss
Control files contain RMAN metadata information and the required repository information, if you are not going to use Recovery Manager Catalog. In 10g, RMAN has introduced control file autobackup, which allows you to configure RMAN to automatically backup the control file with other backups directly to the flash recovery area. This assures that you have control file for recovery purposes.
Recovering a Control File Autobackup
First configure RMAN settings to perform a control file autobackup, which consists of enabling a configuration parameter. The control file autobackup configures all backups to automatically backup the control file. If you are not using recovery catalog, you need to specify the database identifier (DBID) after connecting to the target database when performing control file recovery operation.
Ex:
RMAN> connect target //note the DBID of database
RMAN> configure controlfile autobackup on;
RMAN> run
{
backup database;
backup (archivelog all);
}
Next, you simulate the missing control files by deleting all control files. The database will need to be shutdown to perform this simulation)
C:\oracle\>delete *.ctl
Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
SQL> startup nomount
RMAN> connect target /
RMAN> set dbid 2738992764;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
Re-creating Control File
The control file contains physical map of an Oracle database. In other words, control file has all the locations of the physical files, including datafiles, redo logs, and control files. Control file has also the information about whether the database is in ARCHIVELOG mode, as well as RMAN metadata information.
The control file create script can be created with the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
SQL> alter database backup control file to trace;
The trace file will be present in UDUMP location. The trace file has two options: RESETLOGS and NORESETLOGS. In this case we will go with NORESETLOGS. Save the file as backup_script_noresetlogs.sql.
Simulate the loss of control file by deleting control files for the database. This is performed with the database shutdown.
C:\oracle\> delete *.ctl
SQL> startup nomount
SQL> @backup_script_noresetlogs.sql
Control file will be created.
Recovering a Control File Autobackup
First configure RMAN settings to perform a control file autobackup, which consists of enabling a configuration parameter. The control file autobackup configures all backups to automatically backup the control file. If you are not using recovery catalog, you need to specify the database identifier (DBID) after connecting to the target database when performing control file recovery operation.
Ex:
RMAN> connect target //note the DBID of database
RMAN> configure controlfile autobackup on;
RMAN> run
{
backup database;
backup (archivelog all);
}
Next, you simulate the missing control files by deleting all control files. The database will need to be shutdown to perform this simulation)
C:\oracle\>delete *.ctl
Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
SQL> startup nomount
RMAN> connect target /
RMAN> set dbid 2738992764;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
Re-creating Control File
The control file contains physical map of an Oracle database. In other words, control file has all the locations of the physical files, including datafiles, redo logs, and control files. Control file has also the information about whether the database is in ARCHIVELOG mode, as well as RMAN metadata information.
The control file create script can be created with the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
SQL> alter database backup control file to trace;
The trace file will be present in UDUMP location. The trace file has two options: RESETLOGS and NORESETLOGS. In this case we will go with NORESETLOGS. Save the file as backup_script_noresetlogs.sql.
Simulate the loss of control file by deleting control files for the database. This is performed with the database shutdown.
C:\oracle\> delete *.ctl
SQL> startup nomount
SQL> @backup_script_noresetlogs.sql
Control file will be created.
Recovery Walk Through and User Managed Recovery
Walk through using RESTORE and RECOVER command to restore database from backup.
First, target db must be in a mounted state to perform a full database recovery. The database can be open if you are performing online tablespace recovery or something less than full database recovery.
SQL> startup mount
Run RESTORE and RECOVER command
RMAN> run
{
allocate channel c1 type disk;
restore database;
recover database;
alter database open;
}
User Managed Recovery
It is the traditional recovery method where you directly manage the database files required to recover the database using operating system commands.
Ex:
SQL> startup
You get an error saying that USERS01.dbf file is missing.
Restore the USERS01.dbf file, using cp command
SQL> startup mount
SQL> recover database;
SQL> alter database open;
First, target db must be in a mounted state to perform a full database recovery. The database can be open if you are performing online tablespace recovery or something less than full database recovery.
SQL> startup mount
Run RESTORE and RECOVER command
RMAN> run
{
allocate channel c1 type disk;
restore database;
recover database;
alter database open;
}
User Managed Recovery
It is the traditional recovery method where you directly manage the database files required to recover the database using operating system commands.
Ex:
SQL> startup
You get an error saying that USERS01.dbf file is missing.
Restore the USERS01.dbf file, using cp command
SQL> startup mount
SQL> recover database;
SQL> alter database open;
Recovering from Non-critical files
An Overview of Non-critical files
The recovery of non-critical files is an important matter that you should be familiar with so you can resolve them in an efficient manner. Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised.
Creating a New Temporary Tablespace
A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because temporary tablespace does not have any permanent objects stored within it, there is no change in the SCN from the checkpoint process in the control file or file header.
SQL> create temporary tablespace temp2 tempfile
‘C:\oracle\ora101t\temp2_01.dbf’ size 100M
Extent management local uniform size 1M;
Starting Database with a Missing Tempfile
startup mount
drop tablespace temp including contents;
create temporary tablespace temp tempfile
‘C:\oracle\ora101t\temp01.dbf’ size 100M
extent management local uniform size 1M;
Altering Default Temporary Tablespace
alter database default temporary tablespace temp2;
Re-creating Redo Log Files
Redo logs contain all the transactions committed or uncommitted. An important standard for creating Oracle database is to have mirrored redo logs, also called multiplexed redo logs. If a redo log member is lost or deleted and the mirrored log member still exists, then redo log member can be easily rebuilt. The command ALTER DATABASE ADD LOGFILE MEMBER will create a log member if one has been lost or deleted.
SQL> alter database drop logfile member ‘C:\oracle\redo01.log’;
SQL> alter database add logfile member ‘C:\oracle\redo01.log’ to group 1;
NOTE: Make sure that the database is in restrict mode if you do not have many redo logs.
Recovering an Index Tablespace
Recovering the database with a missing index tablespace is another non-critical recovery. An index tablespace should contain only indexes. Indexes are objects that can be created from the underlying database tables. Rebuild index scripts can be rerun to build the indexes in the index tablespace.
startup mount
drop tablespace indexes including contents;
create tablespace indexes datafile ‘C:\oracle\ora101t\index01.dbf’ size 20M;
Re-creating Indexes:
Re-creating indexes is required after rebuilding the index tablespace. As long as you have the create index scripts, this is a non-critical recovery process.
Recovering Read-Only Tablespaces
A read-only tablespace is a tablespace that contains static information. This means that in most cases, no media recovery is needed.
Re-creating the Password File
There are multiple methods for a DBA to authenticate to an Oracle database. The standard method is to log in directly to the operating system of the server, connect directly to the database with Inter-Process Control (IPC), and establish local connection on the database, which does not need to use SQL*Net. This method requires the operating system’s account to require the password for validation. Once in the secure OS account, you can connect as SYSDBA or SYSOPER. SYSOPER has partial database administration privilege, which is good for operational support.
A second primary method is to connect remotely using SQL*Net and authenticate with a password file. The password file is required for all remote database administrative connections to an Oracle database using SYSDBA or SYSOPER. ORAPWD is an Oracle utility that generates a password file for remote connections. ORAPWD should be run when the database is shutdown. When using ORAPWD, one should use appropriate naming convention, which includes orapw$ORACLE_SID. The password file must be located in $ORACLE_HOME/dbs in UNIX and in $ORACLE_HOME\database in Windows. The init.ora file must also contain REMOTE_LOGIN_PASSWORDFILE parameter, set to SHARED or EXCLUSIVE.
orapwd file=orapwdORA101T password=syspass entries=20
The entries option determines how many users can be stored in password file. To see what users are utilizing the password file,
SQL> select * from v$pwfile_users;
NOTE:
Redo log member cannot be added to current or active online redo log group, because the log group is actively recording transactions
The recovery of non-critical files is an important matter that you should be familiar with so you can resolve them in an efficient manner. Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised.
- Temporary Tablespaces: Can be recovered without impacting database operations. All database users need a temporary tablespace of some kind to perform database operations. They essentially provide sorting operations.
- Redo log files: Non-current redo log files are also considered non-critical database files. A lost redo group can be much more severe and does not come into the category of a non-critical recovery
- Index tablespaces: Index tablespaces contain only indexes and can be re-created or rebuilt.
- Read-only tablespaces: These tablespaces are static. This allows recovery to be fairly straightforward process under most circumstances.
- Password files: Password files contain the passwords for privileged administrative users such as SYSDBA and SYSOPER. This allows you to connect remotely to a database instance and perform administrative functions. The password file can be deleted and re-created if necessary.
Creating a New Temporary Tablespace
A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because temporary tablespace does not have any permanent objects stored within it, there is no change in the SCN from the checkpoint process in the control file or file header.
SQL> create temporary tablespace temp2 tempfile
‘C:\oracle\ora101t\temp2_01.dbf’ size 100M
Extent management local uniform size 1M;
Starting Database with a Missing Tempfile
startup mount
drop tablespace temp including contents;
create temporary tablespace temp tempfile
‘C:\oracle\ora101t\temp01.dbf’ size 100M
extent management local uniform size 1M;
Altering Default Temporary Tablespace
alter database default temporary tablespace temp2;
Re-creating Redo Log Files
Redo logs contain all the transactions committed or uncommitted. An important standard for creating Oracle database is to have mirrored redo logs, also called multiplexed redo logs. If a redo log member is lost or deleted and the mirrored log member still exists, then redo log member can be easily rebuilt. The command ALTER DATABASE ADD LOGFILE MEMBER will create a log member if one has been lost or deleted.
SQL> alter database drop logfile member ‘C:\oracle\redo01.log’;
SQL> alter database add logfile member ‘C:\oracle\redo01.log’ to group 1;
NOTE: Make sure that the database is in restrict mode if you do not have many redo logs.
Recovering an Index Tablespace
Recovering the database with a missing index tablespace is another non-critical recovery. An index tablespace should contain only indexes. Indexes are objects that can be created from the underlying database tables. Rebuild index scripts can be rerun to build the indexes in the index tablespace.
startup mount
drop tablespace indexes including contents;
create tablespace indexes datafile ‘C:\oracle\ora101t\index01.dbf’ size 20M;
Re-creating Indexes:
Re-creating indexes is required after rebuilding the index tablespace. As long as you have the create index scripts, this is a non-critical recovery process.
Recovering Read-Only Tablespaces
A read-only tablespace is a tablespace that contains static information. This means that in most cases, no media recovery is needed.
Re-creating the Password File
There are multiple methods for a DBA to authenticate to an Oracle database. The standard method is to log in directly to the operating system of the server, connect directly to the database with Inter-Process Control (IPC), and establish local connection on the database, which does not need to use SQL*Net. This method requires the operating system’s account to require the password for validation. Once in the secure OS account, you can connect as SYSDBA or SYSOPER. SYSOPER has partial database administration privilege, which is good for operational support.
A second primary method is to connect remotely using SQL*Net and authenticate with a password file. The password file is required for all remote database administrative connections to an Oracle database using SYSDBA or SYSOPER. ORAPWD is an Oracle utility that generates a password file for remote connections. ORAPWD should be run when the database is shutdown. When using ORAPWD, one should use appropriate naming convention, which includes orapw$ORACLE_SID. The password file must be located in $ORACLE_HOME/dbs in UNIX and in $ORACLE_HOME\database in Windows. The init.ora file must also contain REMOTE_LOGIN_PASSWORDFILE parameter, set to SHARED or EXCLUSIVE.
orapwd file=orapwdORA101T password=syspass entries=20
The entries option determines how many users can be stored in password file. To see what users are utilizing the password file,
SQL> select * from v$pwfile_users;
NOTE:
Redo log member cannot be added to current or active online redo log group, because the log group is actively recording transactions
Monitoring RMAN Backups / List and Report commands
Monitoring RMAN Backups
Monitoring actual sessions during RMAN backups or recoveries can be performed utilizing RMAN dynamic views.
V$RMAN_OUTPUT Displays messages reported by an RMAN job in progress
V$RMAN_STATUS Displays the success or failure of all completed RMAN jobs
V$RECOVER_FILE Shows the datafiles that require recovery
An incarnation is a unique backup of the target database that is identified by a unique DB_KEY value. A new incarnation is generated each time a database is opened with RESETLOGS or BACKUP CONTROLFILE.
SQL> select dbid from V$database;
SQL> conn rman_user/rman_user@ora101rc
SQL> select db_key from rc_database where dbid=72737292;
Using LIST Commands
Is used to query the RMAN repository and get the data regarding the BACKUP command, COPY command, and database incarnations. The output of LIST commands displays the files that the CHANGE, CROSSCHECK, and DELETE commands have used.
LIST command displays backup information by using the BY BACKUP and BY FILE options. There are also SUMMARY and VERBOSE options to condense or expand the output.
RMAN> list backupset by backup summary;
RMAN> list backupset by file;
Using REPORT commands
Used to query RMAN repository and get the data regarding which need a backup, unneeded backups, database physical schema, and whether or not unrecoverable operations were performed on files. The output of REPORT commands will generate more detailed information from RMAN repository.
NOTE: The RMAN repository must synchronize with the controlfile. In addition, the CHANGE, UNCATALOG, and CROSSCHECK commands should have been recently executed for the report to be completely accurate.
The REPORT command options include REPORT NEED BACKUP, REPORT OBSOLETE, and REPORT SCHEMA.
RMAN> report obsolete;
RMAN> report schema;
Monitoring actual sessions during RMAN backups or recoveries can be performed utilizing RMAN dynamic views.
V$RMAN_OUTPUT Displays messages reported by an RMAN job in progress
V$RMAN_STATUS Displays the success or failure of all completed RMAN jobs
V$RECOVER_FILE Shows the datafiles that require recovery
An incarnation is a unique backup of the target database that is identified by a unique DB_KEY value. A new incarnation is generated each time a database is opened with RESETLOGS or BACKUP CONTROLFILE.
SQL> select dbid from V$database;
SQL> conn rman_user/rman_user@ora101rc
SQL> select db_key from rc_database where dbid=72737292;
Using LIST Commands
Is used to query the RMAN repository and get the data regarding the BACKUP command, COPY command, and database incarnations. The output of LIST commands displays the files that the CHANGE, CROSSCHECK, and DELETE commands have used.
LIST command displays backup information by using the BY BACKUP and BY FILE options. There are also SUMMARY and VERBOSE options to condense or expand the output.
RMAN> list backupset by backup summary;
RMAN> list backupset by file;
Using REPORT commands
Used to query RMAN repository and get the data regarding which need a backup, unneeded backups, database physical schema, and whether or not unrecoverable operations were performed on files. The output of REPORT commands will generate more detailed information from RMAN repository.
NOTE: The RMAN repository must synchronize with the controlfile. In addition, the CHANGE, UNCATALOG, and CROSSCHECK commands should have been recently executed for the report to be completely accurate.
The REPORT command options include REPORT NEED BACKUP, REPORT OBSOLETE, and REPORT SCHEMA.
RMAN> report obsolete;
RMAN> report schema;
Block Change Tracking using RMAN
Enabling and Disabling Block Change Tracking
Block change tracking is new capability in Oracle 10g. The block change tracking process records the blocks modified since the last backup and stores them in a block change tracking file. RMAN uses this file to determine the blocks that were backed up in an incremental backup. This improves performance because RMAN doesn’t have to scan the whole datafile during the backup. This change was a big improvement for large databases.
Block change tracking is enabled and disabled with a SQL command. By default, block change tracking is disables. Block change tracking status can be verified by accessing a dynamic view v$block_change_tracking.
SQL> alter database enable block change tracking using file ‘C:\oracle\ora_block_track.log’;
NOTE: There is a new background process responsible for writing data to the block change tracking file, which is called block change writer CTRW.
SQL> select filename, status, bytes from v$block_change_tracking;
SQL> alter database disable block change tracking;
Block change tracking is new capability in Oracle 10g. The block change tracking process records the blocks modified since the last backup and stores them in a block change tracking file. RMAN uses this file to determine the blocks that were backed up in an incremental backup. This improves performance because RMAN doesn’t have to scan the whole datafile during the backup. This change was a big improvement for large databases.
Block change tracking is enabled and disabled with a SQL command. By default, block change tracking is disables. Block change tracking status can be verified by accessing a dynamic view v$block_change_tracking.
SQL> alter database enable block change tracking using file ‘C:\oracle\ora_block_track.log’;
NOTE: There is a new background process responsible for writing data to the block change tracking file, which is called block change writer CTRW.
SQL> select filename, status, bytes from v$block_change_tracking;
SQL> alter database disable block change tracking;
Parallelization of BackupSets and Backup Options
Parallelization of Backup Sets
Parallelization of backup sets is performed by causing multiple backup sets to be concurrently backed up over multiple device channels. This is done by allocating multiple channels, one for each backup set that needs to be concurrently backed up before the backup process occurs. You can either modify the CONFIGURE settings for channel parallelism greater than 1 or use manual channel allocation.
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup
(datafile 1, 2, 3 channel c1)
(archivelog all channel c2);
}
The automated method of parallelizing your backup requires modifying the CONFIGURE setting of parallelization parameter.
RMAN> configure device type disk parallelism 3;
RMAN> backup
(datafile 1, 2)
(datafile 3, 4)
(archivelog all);
Backup Options
RMAN provides many options for the backup process. These options control filenames, backup performance, and the size of backups. Options that control filenames are handled with FORMAT and TAG parameters with BACKUP command. The RATE option limits backup I/O bandwidth usage on a computer. This limits RMAN from consuming all of a server’s resources during backup operations. The DURATION option determines the maximum time a backup can process before being terminated. The options that control sizes are MAXSETSIZE and MAXPIECESIZE. These options limit the size of backup sets and backup pieces.
RMAN> backup tablespace users format=’user_bs_%d%p%s’;
RMAN> backup as copy tablespace users format=’C:\oracle\backup\ora101c\users_%d%p%s’;
RMAN> backup database tag weekly_backup;
The RATE option is designed to limit RMAN from using excessive system resources during backup and restore operations.
RMAN> configure channel device type disk rate 5M;
RMAN> configure channel device type disk maxsetsize=10G;
RMAN> backup database maxsetsize=10G;
RMAN> configure channel device type disk maxpeicesize=2G;
NOTE: Caution must be taken when using MAXSETSIZE parameter. If the datafile being backed up is larger than the MAXSETSIZE parameter, the backup will fail.
Parallelization of backup sets is performed by causing multiple backup sets to be concurrently backed up over multiple device channels. This is done by allocating multiple channels, one for each backup set that needs to be concurrently backed up before the backup process occurs. You can either modify the CONFIGURE settings for channel parallelism greater than 1 or use manual channel allocation.
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup
(datafile 1, 2, 3 channel c1)
(archivelog all channel c2);
}
The automated method of parallelizing your backup requires modifying the CONFIGURE setting of parallelization parameter.
RMAN> configure device type disk parallelism 3;
RMAN> backup
(datafile 1, 2)
(datafile 3, 4)
(archivelog all);
Backup Options
RMAN provides many options for the backup process. These options control filenames, backup performance, and the size of backups. Options that control filenames are handled with FORMAT and TAG parameters with BACKUP command. The RATE option limits backup I/O bandwidth usage on a computer. This limits RMAN from consuming all of a server’s resources during backup operations. The DURATION option determines the maximum time a backup can process before being terminated. The options that control sizes are MAXSETSIZE and MAXPIECESIZE. These options limit the size of backup sets and backup pieces.
RMAN> backup tablespace users format=’user_bs_%d%p%s’;
RMAN> backup as copy tablespace users format=’C:\oracle\backup\ora101c\users_%d%p%s’;
RMAN> backup database tag weekly_backup;
The RATE option is designed to limit RMAN from using excessive system resources during backup and restore operations.
RMAN> configure channel device type disk rate 5M;
RMAN> configure channel device type disk maxsetsize=10G;
RMAN> backup database maxsetsize=10G;
RMAN> configure channel device type disk maxpeicesize=2G;
NOTE: Caution must be taken when using MAXSETSIZE parameter. If the datafile being backed up is larger than the MAXSETSIZE parameter, the backup will fail.
Compressed, Full and Incremental Backups
Compressed Backups
New with 10g RMAN is capability to compress backups. In previous versions, reducing the size of backups was performed by backing up only used blocks and skipping unused blocks. With 10g, you can now compress backups regardless of the contents of the datafiles. This allows real compression of backups. Compressed backups works only with backup sets, not image copies. This includes database, tablespace, and datafile backup sets.
RMAN> backup as compressed backupset database;
A default device can be configured for compressed backups.
RMAN> configure device type disk backup type to compressed backupset;
NOTE: Compressed database backup sets are compressed at approximately a 5-to-1 ratio, or 20 percent of the size of a standard backup set.
Full and Incremental Backups
The full and incremental backups are differentiated by how the data blocks are backed up in the target database. The full backup backs up all the data blocks in the datafiles, modified or not. An incremental backup backs up only the data blocks in the datafiles that were modified since the last incremental backup. The baseline backup for an incremental backup is a level 0 backup. A level 0 backup is a full backup at that point in time. Thus, all blocks, modified or not are backed up, allowing the level 0 backup to serve as a baseline for all future incremental backups. Benefit of incremental backup is that it is quicker, because not all data blocks need to be backed up.
There are two types of incremental backups: differential and cumulative. Both differential and cumulative backups backup only modified blocks.
Differential incremental backup backs up only data blocks modified since the most recent backup at the same level or lower. It is the default incremental backup. The cumulative incremental backup backs up only the data blocks that have changed since the most recent backup of the next lowest level or n-1 or lower (with ‘n’ being the existing level of backup).
NOTE: Full backups do not mean the complete database was backed up.
Performing Differential Incremental Backup
RMAN> backup incremental level 0 database;
Next, take level 1 incremental backup after some data has been changed in the database. The incremental level 1 backup will pick up the changes since the level 0 backup.
RMAN> backup incremental level 1 database;
Performing Cumulative Incremental Backup
It requires more space than incremental back ups. The benefit of this is that cumulative incremental backups are usually faster and easier to restore because only one backup for a given level is needed to restore.
RMAN> backup incremental level 1 cumulative database;
New with 10g RMAN is capability to compress backups. In previous versions, reducing the size of backups was performed by backing up only used blocks and skipping unused blocks. With 10g, you can now compress backups regardless of the contents of the datafiles. This allows real compression of backups. Compressed backups works only with backup sets, not image copies. This includes database, tablespace, and datafile backup sets.
RMAN> backup as compressed backupset database;
A default device can be configured for compressed backups.
RMAN> configure device type disk backup type to compressed backupset;
NOTE: Compressed database backup sets are compressed at approximately a 5-to-1 ratio, or 20 percent of the size of a standard backup set.
Full and Incremental Backups
The full and incremental backups are differentiated by how the data blocks are backed up in the target database. The full backup backs up all the data blocks in the datafiles, modified or not. An incremental backup backs up only the data blocks in the datafiles that were modified since the last incremental backup. The baseline backup for an incremental backup is a level 0 backup. A level 0 backup is a full backup at that point in time. Thus, all blocks, modified or not are backed up, allowing the level 0 backup to serve as a baseline for all future incremental backups. Benefit of incremental backup is that it is quicker, because not all data blocks need to be backed up.
There are two types of incremental backups: differential and cumulative. Both differential and cumulative backups backup only modified blocks.
Differential incremental backup backs up only data blocks modified since the most recent backup at the same level or lower. It is the default incremental backup. The cumulative incremental backup backs up only the data blocks that have changed since the most recent backup of the next lowest level or n-1 or lower (with ‘n’ being the existing level of backup).
NOTE: Full backups do not mean the complete database was backed up.
Performing Differential Incremental Backup
RMAN> backup incremental level 0 database;
Next, take level 1 incremental backup after some data has been changed in the database. The incremental level 1 backup will pick up the changes since the level 0 backup.
RMAN> backup incremental level 1 database;
Performing Cumulative Incremental Backup
It requires more space than incremental back ups. The benefit of this is that cumulative incremental backups are usually faster and easier to restore because only one backup for a given level is needed to restore.
RMAN> backup incremental level 1 cumulative database;
RMAN Backup Sets and Image Copies
Database files in backup sets are stored in a special RMAN format and must be processed with the RESTORE command before these files are usable. This can take more time and effort during the recovery process.
Creating Backup Sets
The RMAN BACKUP command is used to perform the backup set backup process.
>rman
RMAN> connect target
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
NOTE: Backup sets have an inherent performance capability called multiplexing. Image copies cannot be multiplexed.
Creating Image Copies
Image copies are actual copies of database files, archive logs, or control files and are not stored in a special RMAN format. Image copies can be stored only on disk. An image copy in RMAN is equivalent to an operating system copy command such as cp in Unix or COPY in Windows. Thus, no RMAN restore processing is necessary to make image copies usable in a recovery situation. This can improve speed and efficiency of restore and recovery process. However, there is also a price for this restore efficiency—the size of image copy backups. The image copy backup cannot be compressed and requires much more space than backup set.
RMAN> run
{
allocate channel ch1 type disk;
copy
datafile 1 to ‘C:\oracle\staging\ora101t\system.dbf’,
current controlfile to ‘C:\oracle\staging\ora101t\control01.ctl’;
}
In Oracle 10g, there is a new backup command that simplifies image copies: BACKUP AS COPY. The benefit of this image copy is that you can perform image copies of an entire database, multiple tablespaces, datafiles and archive logs without having to specify all of the individual files.
[OR]
RMAN> connect target
RMAN> backup as copy tag “062508_backup” database;
Creating Backup Sets
The RMAN BACKUP command is used to perform the backup set backup process.
>rman
RMAN> connect target
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
NOTE: Backup sets have an inherent performance capability called multiplexing. Image copies cannot be multiplexed.
Creating Image Copies
Image copies are actual copies of database files, archive logs, or control files and are not stored in a special RMAN format. Image copies can be stored only on disk. An image copy in RMAN is equivalent to an operating system copy command such as cp in Unix or COPY in Windows. Thus, no RMAN restore processing is necessary to make image copies usable in a recovery situation. This can improve speed and efficiency of restore and recovery process. However, there is also a price for this restore efficiency—the size of image copy backups. The image copy backup cannot be compressed and requires much more space than backup set.
RMAN> run
{
allocate channel ch1 type disk;
copy
datafile 1 to ‘C:\oracle\staging\ora101t\system.dbf’,
current controlfile to ‘C:\oracle\staging\ora101t\control01.ctl’;
}
In Oracle 10g, there is a new backup command that simplifies image copies: BACKUP AS COPY. The benefit of this image copy is that you can perform image copies of an entire database, multiple tablespaces, datafiles and archive logs without having to specify all of the individual files.
[OR]
RMAN> connect target
RMAN> backup as copy tag “062508_backup” database;
About RMAN
RMAN backup is a physical backup method. There are two other methods of backup and recovery in Oracle database: user-managed and Oracle EXPORT utility. The EXPORT utility is essentially a logical backup utility that performs backups only on the logical components of the database.
RMAN> show all; //will show all configuration parameters
Error messages are reported in the V$RMAN_OUPUT
Status of RMAN backups in V$RMAN_BACKUP_JOB_DETAILS
Some commonly used configuration settings that help you use RMAN.
DEVICE TYPE
BACKUP TYPE
COMPRESSED BACKUPSET
CHANNEL DISK DEVICE
CHANNEL TAPE DEVICE
To configure default device to tape and then to disk
RMAN> configure default device type to sbt;
RMAN> configure default device type to disk;
To configure default backup type for an image copy and then for a backup set
RMAN> configure device type disk backup type to copy;
RMAN> configure device type disk backup type to backupset;
To configure a default device for either tape or disk to a compressed backup set
RMAN> configure device type disk backup type to compressed backupset
RMAN> configure device type sbt backup type to compressed backupset;
Some RMAN format options:
%d specifies the name of the database
%f specifies absolute file number
%l specifies DBID
%N tablespace name
%s backup set number
%p specifies piece number within the backup set
%t backup set timestamp
Ex: RMAN> configure channel device type disk format ‘C:\backup\sample\ora_dev_f%t_s%s_s%p’;
Describing Retention Policies
The retention policy is the determined length of time that a backup is retained for use in a potential restore, determined by configuration parameter RETENTION POLICY.
RMAN> configure retention policy to recovery window of 30 days;
i.e. backups are kept for only 30 days within the recovery catalog.
Now, lets create a backup and use TAG clause to mark this backup with a unique name called MONTHLY_BACKUP. TAG is a clause that identifies a specific name to a backup so that it can be more easily identified.
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’ tag monthly_backup;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
Next, you can modify or change this backup so that the backup will not be kept until the end of retention policy. Following statement will cause a backup to expire so that it is not protected by retention policy:
RMAN> change backupset tag monthly_backup nokeep;
Next, you can modify or change the backup to block the 30day retention policy you just had expire. The following statement blocks or excludes this backup from expiring in 30 days, which is the existing retention policy. You will set this backup to be kept until 01-DEC-08 by using the KEEP UNTIL TIME clause.
RMAN> change backupset tag monthly_backup keep until time ’01-DEC-08’ logs;
Configuring Control File Autobackup
RMAN can be configured to automatically backup the control file and other server parameter files whenever information impacting the control file is changed or modified. This is a valuable asset to a backup because this allows RMAN to recover the database even if a control file or server parameter file is lost. This process is called control file autobackup.
RMAN> configure controlfile autobackup on;
You can also configure the format of the autobackup of control file.
RMAN> configure controlfile autobackup format for device type disk to ‘C:\oracle\staging\sample\cf_%F’;
NOTE:
RMAN> show all; //will show all configuration parameters
Error messages are reported in the V$RMAN_OUPUT
Status of RMAN backups in V$RMAN_BACKUP_JOB_DETAILS
Some commonly used configuration settings that help you use RMAN.
DEVICE TYPE
BACKUP TYPE
COMPRESSED BACKUPSET
CHANNEL DISK DEVICE
CHANNEL TAPE DEVICE
To configure default device to tape and then to disk
RMAN> configure default device type to sbt;
RMAN> configure default device type to disk;
To configure default backup type for an image copy and then for a backup set
RMAN> configure device type disk backup type to copy;
RMAN> configure device type disk backup type to backupset;
To configure a default device for either tape or disk to a compressed backup set
RMAN> configure device type disk backup type to compressed backupset
RMAN> configure device type sbt backup type to compressed backupset;
Some RMAN format options:
%d specifies the name of the database
%f specifies absolute file number
%l specifies DBID
%N tablespace name
%s backup set number
%p specifies piece number within the backup set
%t backup set timestamp
Ex: RMAN> configure channel device type disk format ‘C:\backup\sample\ora_dev_f%t_s%s_s%p’;
Describing Retention Policies
The retention policy is the determined length of time that a backup is retained for use in a potential restore, determined by configuration parameter RETENTION POLICY.
RMAN> configure retention policy to recovery window of 30 days;
i.e. backups are kept for only 30 days within the recovery catalog.
Now, lets create a backup and use TAG clause to mark this backup with a unique name called MONTHLY_BACKUP. TAG is a clause that identifies a specific name to a backup so that it can be more easily identified.
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’ tag monthly_backup;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
Next, you can modify or change this backup so that the backup will not be kept until the end of retention policy. Following statement will cause a backup to expire so that it is not protected by retention policy:
RMAN> change backupset tag monthly_backup nokeep;
Next, you can modify or change the backup to block the 30day retention policy you just had expire. The following statement blocks or excludes this backup from expiring in 30 days, which is the existing retention policy. You will set this backup to be kept until 01-DEC-08 by using the KEEP UNTIL TIME clause.
RMAN> change backupset tag monthly_backup keep until time ’01-DEC-08’ logs;
Configuring Control File Autobackup
RMAN can be configured to automatically backup the control file and other server parameter files whenever information impacting the control file is changed or modified. This is a valuable asset to a backup because this allows RMAN to recover the database even if a control file or server parameter file is lost. This process is called control file autobackup.
RMAN> configure controlfile autobackup on;
You can also configure the format of the autobackup of control file.
RMAN> configure controlfile autobackup format for device type disk to ‘C:\oracle\staging\sample\cf_%F’;
NOTE:
- Password file backups are not supported with RMAN
- CONTROL_FILE_RECORD_KEEP_TIME determines the capacity of RMAN repository
RMAN Backup using Recovery Catalog
Recovery Catalog is designed to be a central storage place for multiple database’s RMAN information. Unlike using control file as a repository, recovery catalog can support multiple Oracle databases or an enterprise environment. This centralizes the location of RMAN information instead of having this information dispersed in each target database’s control file.
To enable the catalog an account with CONNECT, RESOURCE, and RECOVERY_CATALOG_OWNER privileges must be created to hold the catalog tables.
Step by Step example:
RMAN> conn catalog rman_user/rman_user
Once the recovery catalog is created, there are few steps that must be performed for each target database so that backup and recovery can be stored. The first step is registering the database. Once an incarnation of the database is registered, data may be stored in the recovery catalog for that particular target database. An incarnation of the database is a reference for a database in the recovery catalog.
Registering a database:
> rman target /
RMAN> connect catalog “rman_user/rman_user@ora101rc”;
RMAN> register database;
Once the database is registered you can backup the target database. This will store the backup data in the recovery catalog.
RMAN backup with catalog example
> set ORACLE_SID=sample
> sqlplus / no log
SQL> connect / as sysdba
SQL> startup mount
Now, start rman at command prompt.
>rman
RMAN> connect target
RMAN> connect catalog rman_user/rman_user@sample;
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
Once the backup is complete, database may be restored and recovered. The database must be mounted but not opened. In the restore and recover script, choose three disk channels to utilize parallelization of the restore process. This is not necessary but improves the restore and recovery time.
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
recover database;
alter database open;
}
NOTE: A typical target database uses only about 120mb of space per year in the recovery catalog database for metadata storage.
To enable the catalog an account with CONNECT, RESOURCE, and RECOVERY_CATALOG_OWNER privileges must be created to hold the catalog tables.
Step by Step example:
- First, you must point to the database where the recovery catalog will reside. This is not the target database.
- Create user that will store the catalog.
SQL> create user rman_user identified by rman_user
default tablespace data
temporary tablespace temp;
SQL> grant connect, resource, recovery_catalog_owner to rman_user;
default tablespace data
temporary tablespace temp;
SQL> grant connect, resource, recovery_catalog_owner to rman_user;
- Launch RMAN tool
RMAN> conn catalog rman_user/rman_user
- Finally, create the recovery catalog
Once the recovery catalog is created, there are few steps that must be performed for each target database so that backup and recovery can be stored. The first step is registering the database. Once an incarnation of the database is registered, data may be stored in the recovery catalog for that particular target database. An incarnation of the database is a reference for a database in the recovery catalog.
Registering a database:
> rman target /
RMAN> connect catalog “rman_user/rman_user@ora101rc”;
RMAN> register database;
Once the database is registered you can backup the target database. This will store the backup data in the recovery catalog.
RMAN backup with catalog example
> set ORACLE_SID=sample
> sqlplus / no log
SQL> connect / as sysdba
SQL> startup mount
Now, start rman at command prompt.
>rman
RMAN> connect target
RMAN> connect catalog rman_user/rman_user@sample;
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}
Once the backup is complete, database may be restored and recovered. The database must be mounted but not opened. In the restore and recover script, choose three disk channels to utilize parallelization of the restore process. This is not necessary but improves the restore and recovery time.
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
recover database;
alter database open;
}
NOTE: A typical target database uses only about 120mb of space per year in the recovery catalog database for metadata storage.
Friday, January 16, 2009
Creating DUPLICATE Database with RMAN
About RMAN DUPLICATE Command:
Once you have RMAN backup of primary database, you can create a duplicate database (independent copy of primary database) on the same host or different host using Recovery Manager DUPLICATE command. The duplicate database can be identical to primary database or can contain only subset of tablespaces in primary database.
As long as RMAN is able to connect both auxiliary and primary instances, RMAN client can run on any host. All backups and archived redo logs used for creating and recovering the duplicate databaase must be accessible by server session on duplicate host. If duplicate host is not the same as the target host, then you must make backups on disk on target host available to duplicate host with the same full path name as in the prim
ary database.
As part of duplicating operation, RMAN automates the following steps:
In the following section, I have listed detailed instructions along with screen shots to create a duplicate database (oradup) using the primary database (oratest) which is in archive log mode. Both the databases in this scenario are on the same host. Note that you should have RMAN backup of primary database (oratest) before you do this activity.
Task 1: Prepare init file for auxiliary instance (initoradup.ora). Make sure that initoradup.ora either exists in $ORACLE_HOME/dbs folder or has symbolic link that points to the file at other location.
Minimum requirement:
db_name=oradup
db_block_size=8192
control_files = (/export/home/oracle/oradup/files/control01.ctl,/export/home/oracle/oradup/files/control02.ctl)
db_file_name_convert=(/export/home/oracle/oratest/files/,/export/home/oracle/oradup/files/)
Task 2: Add the entry for duplicate database in oratab file (/var/opt/oracle/ on Solaris box)
oratest:/export/home/oracle/102:N
oradup:/export/home/oracle/102:N
Task 3:Take hot backup of primary database using RMAN as shown in screen shots
Task 4:Configure the listener. ora file in $ORACLE_HOME/network/admin to add entry for oratest
Task 5: Reload the listener (lsnrctl reload) and see that the primary database is in READY mode using lsnrctl status .
Task 6: Add alias name in tnsnames.ora file in $ORACLE_HOME/network/admin for primary database, so that primary database can be connected using this alias name. Also, ping the alias name to test it.
Task 7: Using ORAPWD utility create a password file for primary database (oratest) and grant sysdba privilege to the user who will duplicate the primary database. Also note that the parameter REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE in init file of primary database (initoratest.ora)
Real Time Scenario: Note that, if you forget to create password file, you will receive the errors related to insufficient privileges when connecting to target database as shown in the screen shot below
Task 8: Start the duplicate database (oradup) in nomount state.
Task 9: Now, connect to the target and auxiliary databases using RMAN, and issue DUPLICATE command as shown below.
If you have configured everything correctly, you should be able to create duplicate database successfully.
Real Time Scenario: DUPLICATE command will issue the following error if the init file of duplicate database is not in $ORACLE_HOME/dbs folder.
Skip this if DUPLICATE command is successful. In case you end up with above problem, use the following instructions:
Once you have RMAN backup of primary database, you can create a duplicate database (independent copy of primary database) on the same host or different host using Recovery Manager DUPLICATE command. The duplicate database can be identical to primary database or can contain only subset of tablespaces in primary database.
As long as RMAN is able to connect both auxiliary and primary instances, RMAN client can run on any host. All backups and archived redo logs used for creating and recovering the duplicate databaase must be accessible by server session on duplicate host. If duplicate host is not the same as the target host, then you must make backups on disk on target host available to duplicate host with the same full path name as in the prim
ary database.
As part of duplicating operation, RMAN automates the following steps:
- Creates a control file for duplicate database
- Restores target datafiles to the duplicate database and performs incomplete recovery by using all available incremental backups and archived redo logs.
- Shuts down and starts the auxiliary instance
- Opens the duplicate database with RESETLOGS option after incomplete recovery to create online redo logs (except when running DUPLICATE...FOR STANDBY, in which case RMAN does not open the database)
- Generates a new, unique DBID for duplicate database (except when running DUPLICATE...FOR STANDBY, in which case RMAN does not open the database)
In the following section, I have listed detailed instructions along with screen shots to create a duplicate database (oradup) using the primary database (oratest) which is in archive log mode. Both the databases in this scenario are on the same host. Note that you should have RMAN backup of primary database (oratest) before you do this activity.
Task 1: Prepare init file for auxiliary instance (initoradup.ora). Make sure that initoradup.ora either exists in $ORACLE_HOME/dbs folder or has symbolic link that points to the file at other location.
Minimum requirement:
db_name=oradup
db_block_size=8192
control_files = (/export/home/oracle/oradup/files/control01.ctl,/export/home/oracle/oradup/files/control02.ctl)
db_file_name_convert=(/export/home/oracle/oratest/files/,/export/home/oracle/oradup/files/)
log_file_name_convert=(/export/home/oracle/oratest/files/,/export/home/oracle/oradup/files/)
Task 2: Add the entry for duplicate database in oratab file (/var/opt/oracle/ on Solaris box)
oratest:/export/home/oracle/102:N
oradup:/export/home/oracle/102:N
Task 3:Take hot backup of primary database using RMAN as shown in screen shots
Task 4:Configure the listener. ora file in $ORACLE_HOME/network/admin to add entry for oratest
Task 5: Reload the listener (lsnrctl reload) and see that the primary database is in READY mode using lsnrctl status .
Task 6: Add alias name in tnsnames.ora file in $ORACLE_HOME/network/admin for primary database, so that primary database can be connected using this alias name. Also, ping the alias name to test it.
Task 7: Using ORAPWD utility create a password file for primary database (oratest) and grant sysdba privilege to the user who will duplicate the primary database. Also note that the parameter REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE in init file of primary database (initoratest.ora)
Real Time Scenario: Note that, if you forget to create password file, you will receive the errors related to insufficient privileges when connecting to target database as shown in the screen shot below
Task 8: Start the duplicate database (oradup) in nomount state.
Task 9: Now, connect to the target and auxiliary databases using RMAN, and issue DUPLICATE command as shown below.
If you have configured everything correctly, you should be able to create duplicate database successfully.
Real Time Scenario: DUPLICATE command will issue the following error if the init file of duplicate database is not in $ORACLE_HOME/dbs folder.
Skip this if DUPLICATE command is successful. In case you end up with above problem, use the following instructions:
- Shutdown both the primary and duplicate databases immediately
- Remove the data files, control, redo files that are created in the duplicate database directory destination
- Startup the primary database
- Move init file of duplicate database to $ORACLE_HOME/dbs
- Startup duplicate database in nomount state (force it if required)
- And then connect to target and auxiliary database using RMAN
Sunday, December 21, 2008
RMAN Tablespace Backup
RMAN> backup tablespace users;
Above command will execute successfully in the following conditions:
1) The databse is in NOARCHIVELOG mode and the tablespace is offline
2) The database is in ARCHIVELOG mode and the tablespace is offline
3) The database is in ARCHIVELOG mode and the tablespace is online
Above command will execute successfully in the following conditions:
1) The databse is in NOARCHIVELOG mode and the tablespace is offline
2) The database is in ARCHIVELOG mode and the tablespace is offline
3) The database is in ARCHIVELOG mode and the tablespace is online
Saturday, December 13, 2008
Restore critical Datafile (system, undo, sysaux) using RMAN
1) shutdown abort;
2) startup mount pfile =
3) RMAN> restore database;
4) RMAN> recover database;
5) RMAN> sql 'alter database open';
2) startup mount pfile =
3) RMAN> restore database;
4) RMAN> recover database;
5) RMAN> sql 'alter database open';
Restore Non-critical Datafile using RMAN
1) See for which tablespace the datafile belongs.
SQL> select file_name, tablespace_name from dba_data_files;
2) alter tablespace offline;
3) RMAN> restore tablespace;
4) RMAN> recover tablespace;
5) RMAN> sql 'alter tablespace online';
SQL> select file_name, tablespace_name from dba_data_files;
2) alter tablespace
3) RMAN> restore tablespace
4) RMAN> recover tablespace
5) RMAN> sql 'alter tablespace
RMAN Backup (when db is online)
1) Check the ORACLE_SID. The target database will be the value stored in it.
2) Make sure that the database is in archive_log mode
select log_mode from v$database;
3) Add these parameters in init.ora file
db_recovery_file_dest=/.../.../flash_recovery_area
db_recovery_file_dest_size= 2147483648
4) startup pfile =
5) > rman target / nocatalog
6) RMAN> backup database plus archivelog;
2) Make sure that the database is in archive_log mode
select log_mode from v$database;
3) Add these parameters in init.ora file
db_recovery_file_dest=/.../.../flash_recovery_area
db_recovery_file_dest_size= 2147483648
4) startup pfile =
5) > rman target / nocatalog
6) RMAN> backup database plus archivelog;
Subscribe to:
Posts (Atom)