We use RMAN COMMANDS for doing lot of tasks like backup,cloning,conversion of filesystems from NON-ASM to ASM and vice-versa.The commands of RMAN are many so I decided to make a note of few crucial ones,hope it can be useful.
There are two types of RMAN commands:
1)Stand-alone:Executed at the RMAN prompt and are generally self-contained. CHANGE, CONNECT,CREATE CATALOG, RESYNC CATALOG. CREATE SCRIPT, DELETE SCRIPT, REPLACE SCRIPT etc.
2)JOB:These are usually grouped and RMAN executes this commands inside of RUN command block sequentially. If any command fails, RMAN ceases processing, no further commands within the block are executed.
•THE CONFIGURE COMMAND :
CONFIGURE AUTOMATIC CHANNEL :
RMAN > CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/db01/backup/%U’;
IMPLEMENT RETENTION POLICY BY SPECIFYING RECOVERY WINDOW :
RMAN >CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 days ;
- Recovery window: a period of time that begins with the current time and extends backward in time to the point of recoverability. In above example, the command ensures that for each data file, one back up that is older than the point of recoverability (7 days) must be retained.
IMPLEMENT RETENTION POLICY BY SPECIFYING REDUNDANCY:
RMAN >CONFIGURE REDUNDANCY POLICY TO REDUNDANCY 2;
- Redundancy value indicates that any number of backups or copies beyond a specified number need not be retained. The default is 1 day.
CONFIGURE DUPLEXED BACKUP SETS:
RMAN >CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;
- We can create up to 4 copies (in above example 2) of each back up piece in a backup set for all backup commands that use automatic channels. This applies only for datafiles and archived redo log files.
CONFIGURE BACKUP OPTIMIZATION:
RMAN >CONFIGURE BACKUP OPTIMIZATION ON;
- With this setting on, the BACKUP command does not backup files to a device type if the identical file has already been backed up to the device type. For two files to be identical, there contents must be exactly the same. Default value is off.
USE THE CLEAR OPTION TO RETURN TO THE DEFAULT VALUE:
RMAN >CONFIGURE RETENTION POLICY CLEAR;
RMAN >CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
•THE SHOW COMMAND :
- This command displays persistent configuration settings.
AUTOMATIC CHANNEL CONFIGURATION SETTINGS:
RMAN>SHOW CHANNEL;
RMAN>SHOW DEVICE TYPE;
RMAN>SHOW DEFAULT DEVICE TYPE;
RMAN RETENTION POLICY CONFIGURATION SETTINGS:
RMAN>SHOW RETENTION POLICY;
NUMBER OF BACKUP COPIES:
SHOW DATAFILE BACKUP COPIES;
MAXIMUM SIZE OF BACKUP SETS:
SHOW MAXSETSIZE;
TABLESPACES EXCLUDED FROM WHOLE DATABASE BACKUP:
SHOW EXCLUDE;
STATUS OF BACKUP OPTIMIZATON:
SHOW BACKUP OPTIMIZATION;
•LIST COMMAND:
- LIST command is used to produce a detailed report listing all information for the following.
LIST BACKUPS OF ALL FILES IN THE DATABASE:
RMAN >LIST BACKUP OF DATABASE;
LIST ALL BACKUP SETS CONTAINING THE uses1.dbf DATAFILE:
RMAN >LIST BACKUP OF DATAFILE ”/db1/oradata/u03/users1.dbf”;
LIST ALL COPIES OF DATAFILES IN THE SYSTEM TABLESPACE:
RMAN >LIST COPY OF TABLESPACE “SYSTEM”;
•THE REPORT COMMAND:
- This command helps to analyze information in the RMAN repository in more detail.
WHAT IS THE STRUCTURE OF THE DATABASE?
RMAN >REPORT SCHEMA;
WHICH FILES NEED TO BE BACKED UP?
RMAN >REPORT NEED BACKUP …;
WHICH BACKUPS CAN BE DELETED?
RMAN >REPORT OBSOLETE;
WHICH FILES ARE NOT RECOVERABLE B’COS OF UNRECOVERABLE OPERATIONS:
RMAN >REPORT UNRECOVERABLE …;
•THE REPORT NEED BACKUP COMMAND :
- This command is used to identify all data files that need a backup. There are three options with this command.
INCREMENTAL: An integer specifies the maximum number of incremental backups that should be restored during recovery. If this number or more is required then the data file needs a new full backup.
RMAN >REPORT NEED BACKUP incremental 3 database;
This example will report files needing three or more incremental backups for recovery.
DAYS: An integer specifies max. number of days since the last full or incremental backup of file. The file needs a backup if the most recent backup is equal to or greater than this number.
RMAN >REPORT NEED BACKUP days 3 tablespace system;
To report what system files have not been backed up for three days, use above command.
REDUNDANCY: An integer specifies the min. level of redundancy considered necessary. For example, redundancy level two requires a backup if there are not two or more backups.
RMAN >REPORT NEED BACKUP redundancy 3;
•RECOVERY MANAGER PACKAGES:
- Two packages named DBMS_RCVCAT AND DBMS_RCVMAN are used by RMAN to perform its tasks.
-DBMS_RCVMAN is created in target database. It queries the control file or recovery catalog.
-DBMS_RCVCAT is used by recovery manager to maintain the information in the recovery catalog.
-DBMS_BACKUP_RESTORE package is created by dbmsbkrs.sql and prvtbkrs.plb script called by catproc.sql. it is used to interface with oracle and os to create,restore and recover bkups of datafiles and archived redo log files.
Hope it helps...I will increase this thread continously as Oracle 11g has lot of new commands.
There are two types of RMAN commands:
1)Stand-alone:Executed at the RMAN prompt and are generally self-contained. CHANGE, CONNECT,CREATE CATALOG, RESYNC CATALOG. CREATE SCRIPT, DELETE SCRIPT, REPLACE SCRIPT etc.
2)JOB:These are usually grouped and RMAN executes this commands inside of RUN command block sequentially. If any command fails, RMAN ceases processing, no further commands within the block are executed.
•THE CONFIGURE COMMAND :
CONFIGURE AUTOMATIC CHANNEL :
RMAN > CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/db01/backup/%U’;
IMPLEMENT RETENTION POLICY BY SPECIFYING RECOVERY WINDOW :
RMAN >CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 days ;
- Recovery window: a period of time that begins with the current time and extends backward in time to the point of recoverability. In above example, the command ensures that for each data file, one back up that is older than the point of recoverability (7 days) must be retained.
IMPLEMENT RETENTION POLICY BY SPECIFYING REDUNDANCY:
RMAN >CONFIGURE REDUNDANCY POLICY TO REDUNDANCY 2;
- Redundancy value indicates that any number of backups or copies beyond a specified number need not be retained. The default is 1 day.
CONFIGURE DUPLEXED BACKUP SETS:
RMAN >CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;
- We can create up to 4 copies (in above example 2) of each back up piece in a backup set for all backup commands that use automatic channels. This applies only for datafiles and archived redo log files.
CONFIGURE BACKUP OPTIMIZATION:
RMAN >CONFIGURE BACKUP OPTIMIZATION ON;
- With this setting on, the BACKUP command does not backup files to a device type if the identical file has already been backed up to the device type. For two files to be identical, there contents must be exactly the same. Default value is off.
USE THE CLEAR OPTION TO RETURN TO THE DEFAULT VALUE:
RMAN >CONFIGURE RETENTION POLICY CLEAR;
RMAN >CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
•THE SHOW COMMAND :
- This command displays persistent configuration settings.
AUTOMATIC CHANNEL CONFIGURATION SETTINGS:
RMAN>SHOW CHANNEL;
RMAN>SHOW DEVICE TYPE;
RMAN>SHOW DEFAULT DEVICE TYPE;
RMAN RETENTION POLICY CONFIGURATION SETTINGS:
RMAN>SHOW RETENTION POLICY;
NUMBER OF BACKUP COPIES:
SHOW DATAFILE BACKUP COPIES;
MAXIMUM SIZE OF BACKUP SETS:
SHOW MAXSETSIZE;
TABLESPACES EXCLUDED FROM WHOLE DATABASE BACKUP:
SHOW EXCLUDE;
STATUS OF BACKUP OPTIMIZATON:
SHOW BACKUP OPTIMIZATION;
•LIST COMMAND:
- LIST command is used to produce a detailed report listing all information for the following.
LIST BACKUPS OF ALL FILES IN THE DATABASE:
RMAN >LIST BACKUP OF DATABASE;
LIST ALL BACKUP SETS CONTAINING THE uses1.dbf DATAFILE:
RMAN >LIST BACKUP OF DATAFILE ”/db1/oradata/u03/users1.dbf”;
LIST ALL COPIES OF DATAFILES IN THE SYSTEM TABLESPACE:
RMAN >LIST COPY OF TABLESPACE “SYSTEM”;
•THE REPORT COMMAND:
- This command helps to analyze information in the RMAN repository in more detail.
WHAT IS THE STRUCTURE OF THE DATABASE?
RMAN >REPORT SCHEMA;
WHICH FILES NEED TO BE BACKED UP?
RMAN >REPORT NEED BACKUP …;
WHICH BACKUPS CAN BE DELETED?
RMAN >REPORT OBSOLETE;
WHICH FILES ARE NOT RECOVERABLE B’COS OF UNRECOVERABLE OPERATIONS:
RMAN >REPORT UNRECOVERABLE …;
•THE REPORT NEED BACKUP COMMAND :
- This command is used to identify all data files that need a backup. There are three options with this command.
INCREMENTAL: An integer specifies the maximum number of incremental backups that should be restored during recovery. If this number or more is required then the data file needs a new full backup.
RMAN >REPORT NEED BACKUP incremental 3 database;
This example will report files needing three or more incremental backups for recovery.
DAYS: An integer specifies max. number of days since the last full or incremental backup of file. The file needs a backup if the most recent backup is equal to or greater than this number.
RMAN >REPORT NEED BACKUP days 3 tablespace system;
To report what system files have not been backed up for three days, use above command.
REDUNDANCY: An integer specifies the min. level of redundancy considered necessary. For example, redundancy level two requires a backup if there are not two or more backups.
RMAN >REPORT NEED BACKUP redundancy 3;
•RECOVERY MANAGER PACKAGES:
- Two packages named DBMS_RCVCAT AND DBMS_RCVMAN are used by RMAN to perform its tasks.
-DBMS_RCVMAN is created in target database. It queries the control file or recovery catalog.
-DBMS_RCVCAT is used by recovery manager to maintain the information in the recovery catalog.
-DBMS_BACKUP_RESTORE package is created by dbmsbkrs.sql and prvtbkrs.plb script called by catproc.sql. it is used to interface with oracle and os to create,restore and recover bkups of datafiles and archived redo log files.
Hope it helps...I will increase this thread continously as Oracle 11g has lot of new commands.
RMAN backup types and Usage
Hi,
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.
RMAN BACKUPS:
----------------
1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).
1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)
INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....
WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...
BACKUP STRATERGY:
--------------------
SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)
TIMESTAMP SYMBOLS:
-------------------
%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET
RMAN COMMANDS:
----------------
INCREMENTAL BACKUP:
---------------------
1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP
1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
2)CUMULATIVE BACKUP:
----------------------
-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
allocated channel: c1
channel c1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: c1
ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------
run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}
I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
COMPRESSION LEVEL ORACLE 11g:
----------------------------------
basic =DEFAULT
none =Not recomended
medium = license
high =license
Drawbacks:
------------
medium =>consume hight system resource
high =>consume high system resource
II)RMAN COLD BACKUP:
----------------------
=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..
run_orcl.txt:
-----------------------------------------------------
*SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.
Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.
$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
rman_cold.txt:
--------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_orcl.bat:
---------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log
Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.
RMAN BACKUP DETAILS Views:
---------------------------------
We can use Dictionary Views for checking the status of rman backups.
1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;
2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;
3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;
Note:On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman
Hope it helps...
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.
RMAN BACKUPS:
----------------
1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).
1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)
INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....
WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...
BACKUP STRATERGY:
--------------------
SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)
TIMESTAMP SYMBOLS:
-------------------
%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET
RMAN COMMANDS:
----------------
INCREMENTAL BACKUP:
---------------------
1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP
1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
2)CUMULATIVE BACKUP:
----------------------
-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
allocated channel: c1
channel c1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: c1
ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------
run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}
I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
COMPRESSION LEVEL ORACLE 11g:
----------------------------------
basic =DEFAULT
none =Not recomended
medium = license
high =license
Drawbacks:
------------
medium =>consume hight system resource
high =>consume high system resource
II)RMAN COLD BACKUP:
----------------------
=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..
run_orcl.txt:
-----------------------------------------------------
*SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.
Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.
$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
rman_cold.txt:
--------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_orcl.bat:
---------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log
Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.
RMAN BACKUP DETAILS Views:
---------------------------------
We can use Dictionary Views for checking the status of rman backups.
1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;
2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;
3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;
Note:On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman
Hope it helps...
RMAN Recovery Catalog Database Creation and Configuration
Hi,
RMAN Recovery Catalog is an excellent way of keeping our backup safe.Since backup is the medicine of Database in case of failure or Data loss,so we don't want to keep the medicine and poison in the same bottle(which will be the case when target Database 'Controlfile' will be using for keeping the information of Backup.If the 'Controlfile' is corrupted or deleted(usually it is multiplexed),but still 'Controlfile' will be acting as poison and medicine.Hence Best option will be to use the 'Recovery Catalog'(Other Database to keep the RMAN metadata) where our backup can be safe.
Below are some steps for Creating and Configuring Recovery Catalog for our Database which might be production,test or Development:
Step 1:Create a new database for RMAN – Recovery catalog database->'CATDB' Database created using dbca.
Note: We can create a small database with minimal sizes of tablespaces and others, and we can name the database as CATDB for naming convention and to avoid the confusion between our production and rman databases.
Step 2:Create a new tablespace in the new database (CATDB)
$ sqlplus /nolog
CONNECT SYS/welcome@catdb AS SYSDBA;
CREATE TABLESPACE rman
DATAFILE 'D:\rafi\catdb\rman\rman01.dbf' size 200m;
Step 3:Create the Recovery Catalog Owner in the new database (CATDB)
CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
Step 4:Grant the necessary privileges to the schema owner
SQL> GRANT recovery_catalog_owner TO rman;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.RECOVERY_CATALOG_OWNER role has the CREATE_SESSION privileges so need to grant separately.
Step 5:Creating the Recovery Catalog
Connect to the database which will contain the catalog as the catalog owner.
For example:'rman' user is catalog owner in our example.
On Linux(UNIX):
------------------
Run the 'CREATE CATALOG' command to create the catalog
$ rman target / catalog rman/rman@catdb
RMAN> CREATE CATALOG;
recovery catalog created
On Windows:
--------------------
C:\Windows\system32>rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 00:44:02 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
Step 6:Registering a Database in the Recovery Catalog
Connect to the target database and recovery catalog database.
$ export ORACLE_SID=ORCL
$ rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sun July 1 14:25:30 2007
Copyright (c) 1982, 2010, Oracle. All rights reserved.
connected to target database: ORCL (DBID=3677528376)
connected to recovery catalog database
windows:
-------------
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32> rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 01:03:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002)
connected to recovery catalog database
Step 7:Register and Verify after connection
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
On Windows (OR) Linux Environment:
-------------------------------
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Register the target Database using 'REGISTER DATABASE' command as seen above.
Make sure that the registration is successful by running REPORT SCHEMA:
RMAN> REPORT SCHEMA;
Report of database schema
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
2 570 SYSAUX NO D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
3 55 UNDOTBS1 YES D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
4 6144 USERS NO D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE NO D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\APP\RAFIALVI\ORADATA\ORCL\TEMP01.DBF
Note:
In Windows:
---------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32>rman target / CATALOG rman/rman@CATDB
In Linux:
-----------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
$export ORACLE_SID=ORCL
$rman target / CATALOG rman/rman@CATDB
Rest all steps are prety much similar in both environment.....
RMAN Recovery Catalog is an excellent way of keeping our backup safe.Since backup is the medicine of Database in case of failure or Data loss,so we don't want to keep the medicine and poison in the same bottle(which will be the case when target Database 'Controlfile' will be using for keeping the information of Backup.If the 'Controlfile' is corrupted or deleted(usually it is multiplexed),but still 'Controlfile' will be acting as poison and medicine.Hence Best option will be to use the 'Recovery Catalog'(Other Database to keep the RMAN metadata) where our backup can be safe.
Below are some steps for Creating and Configuring Recovery Catalog for our Database which might be production,test or Development:
Step 1:Create a new database for RMAN – Recovery catalog database->'CATDB' Database created using dbca.
Note: We can create a small database with minimal sizes of tablespaces and others, and we can name the database as CATDB for naming convention and to avoid the confusion between our production and rman databases.
Step 2:Create a new tablespace in the new database (CATDB)
$ sqlplus /nolog
CONNECT SYS/welcome@catdb AS SYSDBA;
CREATE TABLESPACE rman
DATAFILE 'D:\rafi\catdb\rman\rman01.dbf' size 200m;
Step 3:Create the Recovery Catalog Owner in the new database (CATDB)
CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
Step 4:Grant the necessary privileges to the schema owner
SQL> GRANT recovery_catalog_owner TO rman;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.RECOVERY_CATALOG_OWNER role has the CREATE_SESSION privileges so need to grant separately.
Step 5:Creating the Recovery Catalog
Connect to the database which will contain the catalog as the catalog owner.
For example:'rman' user is catalog owner in our example.
On Linux(UNIX):
------------------
Run the 'CREATE CATALOG' command to create the catalog
$ rman target / catalog rman/rman@catdb
RMAN> CREATE CATALOG;
recovery catalog created
On Windows:
--------------------
C:\Windows\system32>rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 00:44:02 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
Step 6:Registering a Database in the Recovery Catalog
Connect to the target database and recovery catalog database.
$ export ORACLE_SID=ORCL
$ rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sun July 1 14:25:30 2007
Copyright (c) 1982, 2010, Oracle. All rights reserved.
connected to target database: ORCL (DBID=3677528376)
connected to recovery catalog database
windows:
-------------
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32> rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 01:03:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002)
connected to recovery catalog database
Step 7:Register and Verify after connection
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
On Windows (OR) Linux Environment:
-------------------------------
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Register the target Database using 'REGISTER DATABASE' command as seen above.
Make sure that the registration is successful by running REPORT SCHEMA:
RMAN> REPORT SCHEMA;
Report of database schema
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
2 570 SYSAUX NO D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
3 55 UNDOTBS1 YES D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
4 6144 USERS NO D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE NO D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\APP\RAFIALVI\ORADATA\ORCL\TEMP01.DBF
Note:
In Windows:
---------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32>rman target / CATALOG rman/rman@CATDB
In Linux:
-----------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
$export ORACLE_SID=ORCL
$rman target / CATALOG rman/rman@CATDB
Rest all steps are prety much similar in both environment.....
Scheduling RMAN cold backup and copy controlfile
Hi,
RMAN cold backup has its own advantage compare to the traditional cold backup:
1)RMAN as we all know backup only used blocks so it is fast.Here entire Database I'm taking backup.
2)In my below script I'm copying the control file to have the latest copy with me in case of recovery.
This is because of below factors:
-------------------------------------------------------------------------
"copy current controlfile"
Personally I like and use this,Because of below reasons:
*restore after total loss.
I run this command as the *final* command of my database,
run {allocate channel c1 type disk; copy current controlfile to '/some OS location'.
Effectively,I'm backing up my "Backup Meta Data" at the end of each RMAN backup.
My RMAN shell scripts look like this:
--------------------------------------------
1)Backup database in mount State because rman expect Database to be in mount state for taking cold backup
2)copy current controlfile to some location
I'm not saying do not use these other options, just be sure we know ,What we are getting and whether we are able to recover the Database.Test your backups in competency servers and be sure that you can do restore & recovery in case of failure.
3)In my rman scripts,I've used
%U=To have unique backupset number
%T=Date of backup
4)Snapshot Controlfile:
Snapshot controlfile is used by rman to backup control file.Personal speaking not required to do so.If we have copy of current controlfile.Here I have used to save one in the current location of rman backup location.
5)Retention policy:
It is that period of time,till when you want to keep the backup in rman repository.Usually decide this policy well in advance and inform the team.In my case till 7 days I can obtain the backup for restore and recovery purpose.
Below are my scripts:
------------------------
1)run_TESTDB.txt:
---------------------
run_TESTDB.txt is for the setting I'm going to configure for RMAN.We have to create this file first than the actual shell script for taking rman backup.
-- RMAN Configured
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET2_%U_%T';
backup database TAG='TESTDB_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/snapcf_TESTDB.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
2)rman_TESTDB.sh:
--------------------
rman_TESTDB.sh consist of setting Oracle Environment Variables and calling the run_TESTDB.txt file for taking the rman cold backup.
-- TESTDB RMAN COLD BACKUP
export ORACLE_SID=TESTDB
export ORACLE_HOME=/u01/oracle11g/product/11.2.0/dbhome_1
export ORACLE_BASE=/u01/oracle11g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
rman target sys/TESTDBdba @/u05/DB_BACKUP_TESTDB/Backup_Scipts/run_TESTDB.txt log=/u05/DB_BACKUP_TESTDB/logs/rman_cold_weekly_backup_logs/rman_TESTDB.log
Scheduling RMAN Backup job:
-------------------------------
If I want to schedule this backup every sunday at 9pm.The cronjob script will be:
$vi crontab.oracle
#Script for RMAN COLD BACKUP WEEKLY TESTDB DATABASE
################################################################
00 21 * * 0 /u05/DB_BACKUP_TESTDB/SCRIPTS/rman_TESTDB.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/testdb_rman_cron.log
$crontab crontab.oracle =>scheduling job using 'crontab' command at 9pm every sundays.
Verifying cronjob:
-----------------------
$crontab -l =>List the jobs that are scheduled using Cronjob.Verify the rman job also if we have schedule as above.
AN jobs in windows server
RMAN cold backup has its own advantage compare to the traditional cold backup:
1)RMAN as we all know backup only used blocks so it is fast.Here entire Database I'm taking backup.
2)In my below script I'm copying the control file to have the latest copy with me in case of recovery.
This is because of below factors:
-------------------------------------------------------------------------
"copy current controlfile"
Personally I like and use this,Because of below reasons:
*restore after total loss.
I run this command as the *final* command of my database,
run {allocate channel c1 type disk; copy current controlfile to '/some OS location'.
Effectively,I'm backing up my "Backup Meta Data" at the end of each RMAN backup.
My RMAN shell scripts look like this:
--------------------------------------------
1)Backup database in mount State because rman expect Database to be in mount state for taking cold backup
2)copy current controlfile to some location
I'm not saying do not use these other options, just be sure we know ,What we are getting and whether we are able to recover the Database.Test your backups in competency servers and be sure that you can do restore & recovery in case of failure.
3)In my rman scripts,I've used
%U=To have unique backupset number
%T=Date of backup
4)Snapshot Controlfile:
Snapshot controlfile is used by rman to backup control file.Personal speaking not required to do so.If we have copy of current controlfile.Here I have used to save one in the current location of rman backup location.
5)Retention policy:
It is that period of time,till when you want to keep the backup in rman repository.Usually decide this policy well in advance and inform the team.In my case till 7 days I can obtain the backup for restore and recovery purpose.
Below are my scripts:
------------------------
1)run_TESTDB.txt:
---------------------
run_TESTDB.txt is for the setting I'm going to configure for RMAN.We have to create this file first than the actual shell script for taking rman backup.
-- RMAN Configured
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET2_%U_%T';
backup database TAG='TESTDB_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/snapcf_TESTDB.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
2)rman_TESTDB.sh:
--------------------
rman_TESTDB.sh consist of setting Oracle Environment Variables and calling the run_TESTDB.txt file for taking the rman cold backup.
-- TESTDB RMAN COLD BACKUP
export ORACLE_SID=TESTDB
export ORACLE_HOME=/u01/oracle11g/product/11.2.0/dbhome_1
export ORACLE_BASE=/u01/oracle11g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
rman target sys/TESTDBdba @/u05/DB_BACKUP_TESTDB/Backup_Scipts/run_TESTDB.txt log=/u05/DB_BACKUP_TESTDB/logs/rman_cold_weekly_backup_logs/rman_TESTDB.log
Scheduling RMAN Backup job:
-------------------------------
If I want to schedule this backup every sunday at 9pm.The cronjob script will be:
$vi crontab.oracle
#Script for RMAN COLD BACKUP WEEKLY TESTDB DATABASE
################################################################
00 21 * * 0 /u05/DB_BACKUP_TESTDB/SCRIPTS/rman_TESTDB.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/testdb_rman_cron.log
$crontab crontab.oracle =>scheduling job using 'crontab' command at 9pm every sundays.
Verifying cronjob:
-----------------------
$crontab -l =>List the jobs that are scheduled using Cronjob.Verify the rman job also if we have schedule as above.
AN jobs in windows server
Hi ,
In windows server environment we use scheduler(cronjob in unix environment) to schedule a job.
Note:Please make sure you have enough space in the drives(disks in unix environment) before scheduling the jobs.
The important steps here are as follows:
FULL DATABASE BACKUP(INCREMENTAL LEVEL O):
Taking Database backup weekly(incremental level 0):
1)Create a cmd file(command file):
Let us create a file name backup_weekly_db1.cmd using a text file editor(any editor) write the rman script to take the backup.Suppose I want to take full backup(incremental 0) than the script for taking the weekly incremental 0 backup would be like this,assuming controlfile autobackup is off(default).
backup_weekly_db1.cmd
RMAN>run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
Allocate channel t1 type disk format '\path of taking backup\%d_ctrl_%U';
Backup current controlfile;
Release channel t1;
}
INCREMENTAL BACKUP(LEVEL1,LEVEL2,LEVE3 BACKUP):
2)Create the bat file(Batch file is for batch job execution by scheduler)
Let us create a batch file backup_weekly_db1.bat(This batch file we are creating for executing this file through schedule(as cronjob in unix environment).
The script is:
backup_weekly_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_weekly_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log
3)Use scheduler for scheduling job:
Go to control panel->scheduler tasks->Add scheduler task->command prompt->Perform this task->weekly->choose day and timings when you want to run this job weekly->specify administrator user and password->finish
Taking Database backup Daily(incremental level 1)
All the above steps remain the same,but the scripts and scheduler task option changes slightly:
1)
backup_daily_db1.cmd
run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
backup incremental level=1 database tag='Incremental_Backup';
Release channel ch1;
}
2)
backup_daily_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_daily_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log
3)
Scheduler option choose daily and timings when you want to schedule the job
ARCHIVE LOGS BACKUP: AND CONTROLFILE BACKUP
Finally,It is good practice to take archives backup,It can be done as follows:
TESTDB_archives_backup.cmd:
Run{
Crosscheck Archivelog All;
Sql 'alter system archive log current';
Allocate channel t1 device type disk;
Delete Noprompt Copy of Archivelog All Completed before 'SYSDATE-1';
Backup Format 'D:\oracle10g\oradata\TESTDB\Archives\%d_ARCH_%U_%T' Archivelog All;
Release Channel t1;
Allocate channel ch1 device type disk FORMAT 'D:\oracle10g\oradata\TESTDB\Archives\%d_CTRL_%U_%T';
Backup Current Controlfile;
Release channel ch1;
}
TESTDB_archives_backup.bat:
set ORACLE_SID=TESTDB
rman target sys/TESTDBDBA@TESTDB cmdfile=D:\backup\TESTDB_archives_backup.cmd log=D:\backup\Log\backup_archives_TESTDB_%date:~4,2%_%date:~7,2%_%date:~10%.log
Hope this helps.
In windows server environment we use scheduler(cronjob in unix environment) to schedule a job.
Note:Please make sure you have enough space in the drives(disks in unix environment) before scheduling the jobs.
The important steps here are as follows:
FULL DATABASE BACKUP(INCREMENTAL LEVEL O):
Taking Database backup weekly(incremental level 0):
1)Create a cmd file(command file):
Let us create a file name backup_weekly_db1.cmd using a text file editor(any editor) write the rman script to take the backup.Suppose I want to take full backup(incremental 0) than the script for taking the weekly incremental 0 backup would be like this,assuming controlfile autobackup is off(default).
backup_weekly_db1.cmd
RMAN>run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
Allocate channel t1 type disk format '\path of taking backup\%d_ctrl_%U';
Backup current controlfile;
Release channel t1;
}
INCREMENTAL BACKUP(LEVEL1,LEVEL2,LEVE3 BACKUP):
2)Create the bat file(Batch file is for batch job execution by scheduler)
Let us create a batch file backup_weekly_db1.bat(This batch file we are creating for executing this file through schedule(as cronjob in unix environment).
The script is:
backup_weekly_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_weekly_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log
3)Use scheduler for scheduling job:
Go to control panel->scheduler tasks->Add scheduler task->command prompt->Perform this task->weekly->choose day and timings when you want to run this job weekly->specify administrator user and password->finish
Taking Database backup Daily(incremental level 1)
All the above steps remain the same,but the scripts and scheduler task option changes slightly:
1)
backup_daily_db1.cmd
run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
backup incremental level=1 database tag='Incremental_Backup';
Release channel ch1;
}
2)
backup_daily_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_daily_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log
3)
Scheduler option choose daily and timings when you want to schedule the job
ARCHIVE LOGS BACKUP: AND CONTROLFILE BACKUP
Finally,It is good practice to take archives backup,It can be done as follows:
TESTDB_archives_backup.cmd:
Run{
Crosscheck Archivelog All;
Sql 'alter system archive log current';
Allocate channel t1 device type disk;
Delete Noprompt Copy of Archivelog All Completed before 'SYSDATE-1';
Backup Format 'D:\oracle10g\oradata\TESTDB\Archives\%d_ARCH_%U_%T' Archivelog All;
Release Channel t1;
Allocate channel ch1 device type disk FORMAT 'D:\oracle10g\oradata\TESTDB\Archives\%d_CTRL_%U_%T';
Backup Current Controlfile;
Release channel ch1;
}
TESTDB_archives_backup.bat:
set ORACLE_SID=TESTDB
rman target sys/TESTDBDBA@TESTDB cmdfile=D:\backup\TESTDB_archives_backup.cmd log=D:\backup\Log\backup_archives_TESTDB_%date:~4,2%_%date:~7,2%_%date:~10%.log
Hope this helps.
Creating a transportable tablespace set from RMAN backupsets
Creating a transportable tablespace set from RMAN backupsets:
As of 10.2,we can create an transportable tablespace set from RMAN backupsets without any impact to the current live database. Further, it can be used as a work-around to the error ORA-29308 when trying to perform TSPITR against a LOB object.
The TRANSPORT TABLESPACE will result in the following steps, carried out automatically by RMAN:
1) create an auxiliary instance
- create an auxiliary init file
- startup the auxiliary instance in nomount mode
- restore the controlfile from the source to the auxiliary
- alter database mount
2) recover auxiliary instance
- restore system related tablespace (system, undo, sysaux)
- restore transportable set datafiles to the auxiliary destination
- switch datafile to point to auxiliary destination
- recover auxiliary instance apply archivelogs if necessary and removing them once completed
- alter database open resetlogs on the auxiliary instance
3) perform data pump auxiliary instance
- recovery set of tablespaces are placed in read-only mode
- data pump export invoked to generate set of transportable recovery set of tablespace
A sample TTS script (tts.rcv)
run {
transport tablespace "USERS"
Tablespace destination '/ora_backup/tts'
Auxiliary destination '/ora_backup/tts'
Datapump directory data_pump_dir
dump file 'tts_test.dmp'
Import script 'tts_test.imp'
Export log 'exp_tts_test.log'
UNTIL TIME "to_date('02 NOV 2007 14:37:00','DD MON YYYY hh24:mi:ss')";
}
At the source database
1) set ORACLE_SID to the source db, eg:
$ export ORACLE_SID=ORA1020
2) Ensure all directories exist at both the operating system level and the database. To confirm the datadump directory:
SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/
3) Execute the tts command file:
$ rman target / catalog rman/rman@rcat cmdfile tts.rcv log tts.log
Now you have a complete set of script and transportable tablespace set to plug into the destination database.
At the destination database
1) Ensure that the data pump directory exists at the destination host
SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/
2) Transfer the data pump export dump to data_pump_dir
3) Transfer all the datafiles from the "tablespace destination" to the destination host's transport_tablespace directory
4) Ensure that the tablespace to be imported does not already exist at the target database, otherwise drop it
SQL> select tablespace_name, status from dba_tablespaces;
5) Ensure that the owners of the objects in the tablespace exist in this database.If not , create the users.
6) Transfer the import script "tts_test.imp" to the destination host.
7) Run the tts_test.imp file:
a) Import the tablespaces:
$ impdp / directory=data_pump_dir dumpfile= 'tts_test.dmp' transport_datafiles= /ora_backup/tts/users01.dbf
b) Attach the datafiles:
$ sqlplus / as sysdbda
SQL> @tts_test.imp
The tablespace should now be plugged in and ready for use.
Note: For read only tablespaces, you should put them into read-write, then read only again. This is to avoid a known bug when backing up read-only tablespaces via RMAN.
Sample content of tts_test.imp
/*
The following command may be used to import the tablespaces.
Substitute values forand .
impdpdirectory= dumpfile= 'tts_test.dmp' transport_datafiles= /ora_backup/tts/users01.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/ora_backup/tts/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'tts_test.dmp';
dump_file.directory_object := 'data_pump_dir';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'users01.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Conditions necessary for transporting tablespace:
Condition 1:
------------
To check whether a tablespace can be transported and whether a tablespace is self contained, run:
dbms_tts.transport_set_check
The dbms_tts package is owned by sys, so to execute as another user either create a synonym or precede the package with 'sys'. In addition, the user must have been granted the role execute_catalog_role.
To create a synonym:
create synonym dbms_tts for sys.dbms_tts;
Run the procedure with the tablespace names as
SQL> execute dbms_tts.transport_set_check('LAVA',TRUE);
Statement processed.
This is going to populate a table called transport_set_violations. This
table is owned by the user sys. To query the table, either precede the
tablename by 'sys.' or create a synonym:
create synonym transport_set_violations for sys.transport_set_violations;
Check the view for any possible violations.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object CATEGORIES in tablespace LAVA not allowed in pluggable set
1 row selected.
As of 10.2,we can create an transportable tablespace set from RMAN backupsets without any impact to the current live database. Further, it can be used as a work-around to the error ORA-29308 when trying to perform TSPITR against a LOB object.
The TRANSPORT TABLESPACE will result in the following steps, carried out automatically by RMAN:
1) create an auxiliary instance
- create an auxiliary init file
- startup the auxiliary instance in nomount mode
- restore the controlfile from the source to the auxiliary
- alter database mount
2) recover auxiliary instance
- restore system related tablespace (system, undo, sysaux)
- restore transportable set datafiles to the auxiliary destination
- switch datafile to point to auxiliary destination
- recover auxiliary instance apply archivelogs if necessary and removing them once completed
- alter database open resetlogs on the auxiliary instance
3) perform data pump auxiliary instance
- recovery set of tablespaces are placed in read-only mode
- data pump export invoked to generate set of transportable recovery set of tablespace
A sample TTS script (tts.rcv)
run {
transport tablespace "USERS"
Tablespace destination '/ora_backup/tts'
Auxiliary destination '/ora_backup/tts'
Datapump directory data_pump_dir
dump file 'tts_test.dmp'
Import script 'tts_test.imp'
Export log 'exp_tts_test.log'
UNTIL TIME "to_date('02 NOV 2007 14:37:00','DD MON YYYY hh24:mi:ss')";
}
At the source database
1) set ORACLE_SID to the source db, eg:
$ export ORACLE_SID=ORA1020
2) Ensure all directories exist at both the operating system level and the database. To confirm the datadump directory:
SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/
3) Execute the tts command file:
$ rman target / catalog rman/rman@rcat cmdfile tts.rcv log tts.log
Now you have a complete set of script and transportable tablespace set to plug into the destination database.
At the destination database
1) Ensure that the data pump directory exists at the destination host
SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/
2) Transfer the data pump export dump to data_pump_dir
3) Transfer all the datafiles from the "tablespace destination" to the destination host's transport_tablespace directory
4) Ensure that the tablespace to be imported does not already exist at the target database, otherwise drop it
SQL> select tablespace_name, status from dba_tablespaces;
5) Ensure that the owners of the objects in the tablespace exist in this database.If not , create the users.
6) Transfer the import script "tts_test.imp" to the destination host.
7) Run the tts_test.imp file:
a) Import the tablespaces:
$ impdp / directory=data_pump_dir dumpfile= 'tts_test.dmp' transport_datafiles= /ora_backup/tts/users01.dbf
b) Attach the datafiles:
$ sqlplus / as sysdbda
SQL> @tts_test.imp
The tablespace should now be plugged in and ready for use.
Note: For read only tablespaces, you should put them into read-write, then read only again. This is to avoid a known bug when backing up read-only tablespaces via RMAN.
Sample content of tts_test.imp
/*
The following command may be used to import the tablespaces.
Substitute values for
impdp
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/ora_backup/tts/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'tts_test.dmp';
dump_file.directory_object := 'data_pump_dir';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'users01.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Conditions necessary for transporting tablespace:
Condition 1:
------------
To check whether a tablespace can be transported and whether a tablespace is self contained, run:
dbms_tts.transport_set_check
The dbms_tts package is owned by sys, so to execute as another user either create a synonym or precede the package with 'sys'. In addition, the user must have been granted the role execute_catalog_role.
To create a synonym:
create synonym dbms_tts for sys.dbms_tts;
Run the procedure with the tablespace names as
SQL> execute dbms_tts.transport_set_check('LAVA',TRUE);
Statement processed.
This is going to populate a table called transport_set_violations. This
table is owned by the user sys. To query the table, either precede the
tablename by 'sys.' or create a synonym:
create synonym transport_set_violations for sys.transport_set_violations;
Check the view for any possible violations.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object CATEGORIES in tablespace LAVA not allowed in pluggable set
1 row selected.
MY RMAN practices
RMAN BACKUP taken on different locations by using 'format' keyword. Here is how we can take rman backup in different locations:
RUN
{
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG Weekly_Friday_0_Hotpsms_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
ALLOCATE CHANNEL B1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL B2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP DATABASE ARCHIVELOG ALL DELETE ALL INPUT TAG Weekly_Friday_0_Hotpsms_bkp;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
RUN
{
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG Weekly_Friday_0_Hotpsms_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
ALLOCATE CHANNEL B1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL B2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP DATABASE ARCHIVELOG ALL DELETE ALL INPUT TAG Weekly_Friday_0_Hotpsms_bkp;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
No comments:
Post a Comment