CREATE USER RMAN IDENTIFIED BY RMAN
DEFAULT TABLESPACE XYX
TEMPORARY TABLESPACE TEMP;
INCREASE TEMP ,UNDO TBS
GRANT RECOVERY_CATALOG_OWNER TO RMAN;
----------------CONFIGURE ARCHIVE MODE----------------
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;
===========================================
RMAN
===========================================
-----------ENTER INTO RMAN------------------
Connect to a target database without using a recovery catalog:
% rman TARGET SYS/pwd@target_str
RMAN TARGET / CATALOG RMAN/RMAN@TEST
######connect to both the target database and the recovery catalog:#####
rman TARGET SYS/system@test CATALOG x/x@test
RMAN> CONNECT target sys/oracle@target
CREATE CATALOG;
REGISTER DATABASE;
RMAN> CONNECT TARGET SYS/oracle@trgt
RMAN> CONNECT CATALOG rman/cat@catdb
----connect to target,auxiliary,and recovery catalog databases, launch the RMAN client--------
% rman TARGET SYS/oracle@trgt AUXILIARY SYS/aux@auxdb CATALOG rman/cat@catdb
RMAN> CONNECT AUXILIARY SYS/aux@auxdb
-----------CONFIGURATION----------------------
show all;
CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT CLEAR;
The following command configures RMAN to write disk backups to the
/tmp directory:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mybackupdir/cf%F';
------------------RMAN COMMAND--------------------
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN
’SYSDATE-31’ AND ’SYSDATE-7’;
RMAN> BACKUP TABLESPACE system, users, tools;
RMAN> BACKUP AS BACKUPSET DATAFILE
’ORACLE_HOME/oradata/trgt/users01.dbf’,
’ORACLE_HOME/oradata/trgt/tools01.dbf’;
RMAN> BACKUP CURRENT CONTROLFILE TO ’/backup/curr_cf.copy’;
RMAN> BACKUP SPFILE;
RMAN> BACKUP BACKUPSET ALL;
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
----------DELETE BACKUP------------------
RMAN> delete noprompt ARCHIVELOG ALL;
RMAN> delete noprompt backup of database;
RMAN> delete noprompt copy of database;
-----------------------DATABASE---------------------
Recovering the Whole Database
Use the RESTORE DATABASE and RECOVER
STARTUP FORCE MOUNT;
run
{
allocate channel c1 type to disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
----------------------TABLESPACE--------------------
Recovering Current Tablespaces
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
====================================================
---------------------DATAFILE-----------------------\
====================================================
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
-------USER-MANAGED
SQLPLUS / AS SYSDBA
COPY DATAFILE FROM BACKUP LOCATION
SET NLS_DATE_FORMATE=DD-MON-YYYY HH24:MI:SS
STARTUP MOUNT
RECOVER DATABASE UNTIL TIME ' ';
APPLY THE LOGS
ALTER DATABASE OPEN RESETLOGS;
------------------BLOCK------------------------------
Recovering Individual Data Blocks RMAN can recover individual corrupted
datafile blocks. When RMAN performs a complete scan of a file for a
backup, any corrupted blocks are listed in V$DATABASE_BLOCK_
CORRUPTION. Corruption is usually reported in alert logs, trace files or
results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:
RMAN> BLOCKRECOVER CORRUPTION LIST;
You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;
-------------------VALIDATE------------------------------
Validating Restores You can run a RESTORE... VALIDATE operation to
confirm that a restore operation can be performed successfully. RMAN
decides which backup sets, datafile copies, and archived logs are needed for
the operation, and scans them to verify that they are usable. For example:
RMAN> RESTORE DATABASE VALIDATE;
----------------------LIST---------------------------
Listing Backups Run the LIST BACKUP and LIST COPY commands to
display information about backups and datafile copies listed in the
repository. You can display specific objects, as in the following examples:
LIST BACKUP; # lists backup sets, image copies, and proxy copies
LIST BACKUPSET; # lists only backup sets and proxy copies
LIST COPY; # lists only disk copies
LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies
LIST COPY BY FILE; # shows only disk copies
LIST EXPIRED BACKUP;
LIST EXPIRED BACKUP BY FILE;
LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies
LIST EXPIRED BACKUP SUMMARY;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cf.cpy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
For backups, you can control the format of LIST output with these options:
# lists backups of all files in database
LIST BACKUP OF DATABASE;
# lists copy of specified datafile
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf';
# lists specified backup set
LIST BACKUPSET 213;
# lists datafile copy
LIST DATAFILECOPY '/tmp/tools01.dbf';
# specify a backup set by tag
LIST BACKUPSET TAG 'weekly_full_db_backup';
# specify a backup or copy by device type
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
# specify a backup by directory or path
LIST BACKUP LIKE '/tmp/%';
# specify a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';
# specify logs backed up at least twice to tape
LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
-------------------------------------------------------------
Parameter Example Explanation
-------------------------------------------------------------
BY BACKUP LIST BACKUP OF BY BACKUP LIST BACKUP OF
For both backups and copies you have the following additional options:
Reporting on Database Files and Backups The REPORT command performsmore
complex analysis than LIST. Some of the main options are:
Monitoring RMAN Through V$ Views Status information for jobs in progress and
completed jobs is stored in V$RMAN_STATUS. V$RMAN_OUTPUT contains
the text ouptut of all RMAN jobs.
BY FILE LIST BACKUP BY FILE Lists the backups according to which
file was backed up.
SUMMARY LIST BACKUP
SUMMARY
Displays reduced output. By default,
the output is VERBOSE.
Parameter Example Explanation
EXPIRED LIST EXPIRED COPY Displays files inaccessible based
on the CROSSCHECK command.
RECOVERABLE LIST BACKUP RECOVERABLE Specifies datafile backups or
copies that are available and that
can be restored and recovered in
the current database incarnation--------
-----------------DELETE INPUT----------------------
With DELETE INPUT,
RMAN only deletes the specific copy of the archived redo log chosen for the backup set.
With DELETE ALL INPUT,
RMAN will delete each backed-up archived redo log file from all log archiving destinations.
For example, assume that you archive to /arc_dest1, /arc_dest2, and /arc_
dest3, and you run the following command:
BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;
In this case RMAN backs up only one copy of each log sequence number in these
directories, and then deletes all copies of any log that it backed up from the
archiving destinations.
If you had specified DELETE INPUT rather than DELETE ALL INPUT,
then RMAN would only delete the specific archived redo log files that it backed up
================================================
SCENARIO
================================================
----------------------COMPRESS PARALLEL BACKUP--------------------
configure device type disk parallelism 3;
configure default device type to disk;
configure channel 1 device type disk format '/u01/backup/%U';
configure channel 2 device type disk format '/u02/backup/%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/u03/backup/%U';
backup as compressed backupset database plus archivelog;
----------ONLINE BACKUP-------------------------
1. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
2. Runs BACKUP ARCHIVELOG ALL. Note that if backup optimization is enabled,
then RMAN skips logs that it has already backed up to the specified device.
3. Backs up the rest of the files specified in BACKUP command.
4. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
5. Backs up any remaining archived logs generated during the backup.
This guarantees that datafile backups taken during the command are recoverable to
a consistent state.
---------LOST OF CONTROL FILE-------------------
SQLPLUS /NOLOG
SQLPLUS > CONNECT / AS SYSDBA
STARTUP NOMOUNT
RMAN> CONNECT TARGET /
SET DBID
RESTORE CONTROLFILE FROM BACKUP;
ALTER DATABASE MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
-------------RECREATE CONTROLFILE------------------
SQLPLUS /NOLOG
CONNECT / AS SYSDBA
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
EDIT TRACE FILE
COPY PASTE NORESETLOG PART.(TAKE FULL UPTO ALTER DATABASE OPEN)
STARTUP NOMOUNT
RUN COMMAND TO CREATE CONTROLFILE
IF SOMETHING ASKED APPLY LOGS
OR REDO-LOGS ALSO
------USING CANCEL OPTION
SQLPLUS /NOLOG
CONNECT / AS SYSDBA
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
EDIT TRACE FILE (TAKE UPTO CHARACTERSET OR END OF STATMENT OF CREATE CONTROLFILE)
COPY PASTE RESETLOG PART.*
STARTUP NOMOUNT
RUN COMMAND TO CREATE CONTROLFILE
IF SOMETHING ASKED
CANCEL
ALTER DATABASE OPEN RESETLOGS;
----if backup fails last night--------------
if backup fails
backup database not backed up since time 'sysdate-1';
--------recover deleted records------------------
1 option
INSERT INTO EMPLOYEE_TEMP (SELECT * FROM EMPLOYEE AS OF TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS'))
2.option
SQL> select DBMS_FLASHBACK. GET_SYSTEM_CHANGE_NUMBER from dual;
SQL> INSERT INTO EMPLOYEE_TEMP(SELECT * FROM EMPLOYEE AS OF SCN 10280403339);
3.option
SELECT NAME,FIRST_TIME,NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY first_time DESC;
I will chose the appropriate arcive log.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(logfilename=>'/home/oracle/ARCHIVE_LOG/1_20_650546031.dbf',options=>DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SELECT username,OPERATION,sql_undo,sql_redo
FROM v$logmnr_contents
4.option
conn system/pwd
execute dbms_flashback.enable_at_time(sysdate-10/1440);
select the records now and see 10 minutes old data
execute dbms_flashback.disable;
-------recover table after drop-------------------
FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;
-------recover database to back in time--------------
1.option
SQL> Flashback database to timestamp sysdate-(1/24);
SQL> ALTER DATABASE OPEN RESETLOGS;
------------ restore datafile to different location ------------------------
----------WITH SHUTDOWN DB-----------------------
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
3> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
4> restore tablespace data_ts;
5> switch datafile all; # Updates repository with new datafile location.
6> recover tablespace data_ts;
7> alter database open;
8> }
place the datafiles offline and then set their new names for restore and recovery:
-------------WITH OPEN DB------------------------------
RMAN> run{
2> sql 'alter database datafile 4, 5 offline';
3> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
4> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
5> restore datafile 4, 5;
5> switch datafile all; # Updates repository with new datafile location.
6> recover datafile 4, 5;
7> sql 'alter database datafile 4, 5 online';
8> }
=====================================================
-------------INCOMPLETE RECOVERY---------------------
=====================================================
----BY TIME
STARTUP MOUNT
SET NLS_DATE_FORMATE=DD-MON-YYYY HH24:MI:SS
....FROM RMAN
RUN
{
SET UNTIL TIME '06-SEP-2010 11:22:20';
RESTORE DATABASE;
RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;
----BY REDO
SELECT * FROM V$LOG_HISTORY;
STARTUP MOUNT;
FROM RMAN
RUN
{
SET UNTIL SEQUENCE 3 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;
-----------------------------------------------------
KNOWLEDGEBASE
=====================================================
By default, a backup set consists of one backup piece.
For example, you can back up ten datafiles into a single backupset containing a single backup piece
-----------------------WHOLE-------------------------
A WHOLE backup is which is a backup of all datafiles and the current control file
whole database backups with the database mounted or open.To perform a whole database backup
----procedure for taking a whole database backup
RMAN> BACKUP DATABASE; # uses automatic channels to make backup
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # switches logs and archives all
logs
By archiving the logs immediately after the backup, you ensure that you have a full set
of archived logs through the time of the backup. This guarantees that you can perform
media recovery after restoring this backup.
----------------------PARTIAL------------------------
----------------------FULL ---------------------------
A full datafile backup is a backup that includes every used data block in the file.
--------------------INCREMANTEL----------------------
RMAN incremental backups back up only datafile blocks that have changed since a
specified previous backup. You can make incremental backups of databases,
individual tablespaces or datafiles.
Incremental backups are differential by default.
incremental backup can be either of the following types:
________________________________________________________________________________
# A differential backup, which backs up all blocks changed after the most recent
incremental backup at level 1 or 0
________________________________________
level0 level1 level2 level3
1/2/09 1/3/09 1/4/09 1/5/09
-------------------------------------------
*
-------------->
------------>
----------->
BACKUP INCREMENTAL LEVEL 1 DATABASE;
#BACKUP INCREMENTAL LEVEL 0 DATABASE;
#BACKUP INCREMENTAL LEVEL 1
TABLESPACE SYSTEM
DATAFILE 'ora_home/oradata/trgt/tools01.dbf';
________________________________________________________________________________
# A cumulative backup, which backs up all blocks changed after the most recent
incremental backup at level 0
____________________________________________
level0 level1 level2 level3
1/2/09 1/3/09 1/4/09 1/5/09
---------------------------------------------
*
--------------->
---------------------------->
---------------------------------------->
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE TABLESPACE users;
____________________________________________________
Incremental backups are differential by default.
You can make incremental backups of databases, individual tablespaces or datafiles.
in ARCHIVELOG mode, you can make incremental backups if the database is open;
in NOARCHIVELOG mode, then you can only make incremental backups when the database is closed.
------------Incrementally Updated Backups: Rolling Forward Image Copy Backups-----------------
At the beginning of a backup strategy, RMAN creates an image copy backup of the datafile.
Then, at regular intervals, such as daily, level 1 incremental backups are taken,
and applied to the image copy backup, rolling it forward to the point in time when the
level 1 incremental was created.
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update'; #apply the increment backup to copy(refresh backup)
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE; (take backup of incremental 1 if its not there it takes the backup from level 1)
}
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update'
UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}
# On the first night the RECOVER COPY... UNTIL TIME statement has no effect,
and the BACKUP INCREMENTAL... FOR RECOVER OF COPY statement creates
the incremental level 0 copy.
# On the eighth and all subsequent nights night, the RECOVER COPY... UNTIL
TIME statement applies the level 1 incremental from seven days ago to the copy of
the database. The BACKUP INCREMENTAL... FOR RECOVER OF COPY
statement creates an incremental backup containing the changes for the previous
day
====================================================
RMAN WITH RAC
====================================================
By default, channels will be allocated from one node.
To split the load across all the nodes in the cluster,
individual channels need special configuration.
So in two-node cluster, we can equally distribute the load as
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';
Caution: Compressed Backup is CPU bound, so number of channels should be carefully used.
=========================================================================
PARALLELISM
=========================================================================
Note: that if you configure specific channels with numbers higher than the parallelism setting,
RMAN will not use these channels.
In this example, you want to send disk backups to two different disks. Configure disk channels as follows:
CONFIGURE DEFAULT DEVICE TYPE TO disk; # backup goes to disk
CONFIGURE DEVICE TYPE disk PARALLELISM 2; # two channels used in in parallel
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/disk1/%U' # 1st channel to disk1
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/disk2/%U' # 2nd channel to disk2
BACKUP DATABASE; # backup - first channel goes to disk1 and second to disk2
===========================================================================
QUERY
===========================================================================
SELECT * FROM V$RECOVERY_FILE_DEST; FOR SIZE OF RECOVERY AREA
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BTOTH SID="*";
The following query displays
the number of blocks written to a backup set for each datafile with at least 50% of its
blocks backed up:
SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .5
ORDER BY COMPLETION_TIME;
===============================================
REFERANCE
===============================================
http://www.oracle-base.com/articles/9i/ArchivelogModeOnRAC9i.php ARCHIVE MODE ON RAC
COPY FROM RMAN QUICK START PAGE NO 11 COMMAND LIST
==================================================================
DUPLICATE DATABASE WITH RMAN
==================================================================
1.CREATE PASSWORD FILE FOR DUPLICATE INSTANCE
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
2. ADD TNS ENTRIES
# Added to the listener.ora SID_LIST
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = DUP)
)
# Added to the tnsnames.ora
DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.tshcomputing.com)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = DUP)
)
)
# Reload listener
lsnrctl reload
3. Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as
the original we must convert the file names so there is no conflict:
# Minimum Requirement.
DB_NAME=DUP
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
/u02/oradata/DUP/control02.ctl,
/u02/oradata/DUP/control03.ctl)
# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/)
LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)
# make sure block_size and compatible parameters
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
4.Next we connect to the duplicate instance:
ORACLE_SID=DUP; export ORACLE_SID
sqlplus /nolog
conn / as sysdba
5. CREATE SPFILE
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
6.Next we start the database in NOMOUNT mode:
STARTUP FORCE NOMOUNT;
7.With the duplicate database started we can now connect to it from RMAN.
For the duplication to work we must connect to the original database (TARGET),
the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):
ORACLE_SID=DUP; export ORACLE_SID
rman TARGET sys/password@tsh1 CATALOG rman/rman@tshadm AUXILIARY /
8. DUPLICATE DB
# Duplicate database to TARGET's current state.
RUN
{
SET NEWNAME FOR DATAFILE 1 TO /oracle/data/file2.f; # rename datafile 1 as file2.f
SET NEWNAME FOR DATAFILE 2 TO /oracle/data/file1.f; # rename datafile 2 as file1.f
DUPLICATE TARGET DATABASE TO newdb;
}
OR
RUN
{
# set new filenames for the datafiles
SET NEWNAME FOR DATAFILE 1 TO '/dup/oracle/oradata/trgt/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/dup/oracle/oradata/trgt/undotbs01.dbf';
. . .
# issue the duplicate command
DUPLICATE TARGET DATABASE TO dupdb
# create at least two online redo log groups
LOGFILE
GROUP1
(
'/dup/oracle/oradata/trgt/redo01a.log',
'/dup/oracle/oradata/trgt/redo01b.log',
'/dup/oracle/oradata/trgt/redo01c.log';
) SIZE 200K,
GROUP2
(
'/dup/oracle/oradata/trgt/redo02a.log',
'/dup/oracle/oradata/trgt/redo02b.log',
'/dup/oracle/oradata/trgt/redo02c.log';
) SIZE 200K,
GROUP3
(
'/dup/oracle/oradata/trgt/redo03a.log',
'/dup/oracle/oradata/trgt/redo03b.log',
'/dup/oracle/oradata/trgt/redo03c.log';
) SIZE 200K;
}
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';
http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmdupdb.htm#441876
==============================================================
==============================================================
92 from bkp_concept
732 912 1064 bipinbahai
732 912 1499 aka
236 c cinder road edison
Example
——–
Restore RMAN backup from CRM production server on us01ap17 to test server on test_us01ap17
The example assumes:
- the target database is on us01ap17
- the database is to be restored onto test_us01ap17
- the RMAN catalog is installed on us01ap19.
- the directory structure of test_us01ap17 is the same as us01ap17
- the ORACLE_SID will not change for the restored database
- a recovery catalog is being used
- the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)
The following steps are required:
- backup the target on us01ap17
- list the datafile locations on us01ap17
- make the backup available to test_us01ap17
- make a copy of the init.ora available to test_us01ap17
- if directory structure is different, edit the init.ora to reflect directory structure changes
- configure SQL*Net connectivity from host to the recovery catalog and duplicated database
- set up a password file for the duplicated database
- startup nomount the duplicated database
- RMAN restore the controlfile(s)
- mount the database
- restore and rename the datafiles
- recover and open the database
These steps are expanded further below.
1.0 Create Database on test_us01ap17
1.1 Install Oracle database using CD or downloaded files from Oracle
1.2 Ceate Oracle service
oradim -new -sid CRM -intpwd change_on_install -maxusers 20 -startmode auto
1.3 Check to see if password file was created, if not, create using the following command
orapwd file=C:\oracle\ora92\database\PWDCRM.ora password=change_on_install entries=30
1.4 Create spfile
sqlplus /nolog
connect sys/change_on_install@CRM as sysdba
shutdown immediate;
create spfile from pfile=’C:\oracle\admin92\CRM\pfile\initCRM.ora’;
exit;
2.0 Backup the Production database on us01ap17
———————————————-
The target database needs to be backed up using RMAN.
The following is one example of RMAN doing an online database backup. In this example, the backup sets (full hot backup) are written to disk.
First, create a windows batch file, fullbackup.bat
rman catalog rmanager/rmanager@rman target sys/change_on_install@CRM cmdfile=C:\Oracle\job\rman_scripts\rman_full_backup.txt log=C:\Oracle\job\rman_logs\%date:~10,4%_%date:~4,2%_%date:~7,2%_log.txt
Then, create a RMAN script file rman_full_backup.txt
run
{
resync catalog;
allocate channel ch1 device type disk format ‘H:\Rman_backup\%d_%u_%s_%p’;
allocate channel ch2 device type disk format ‘H:\Rman_backup\%d_%u_%s_%p’;
allocate channel ch3 device type disk format ‘H:\Rman_backup\%d_%u_%s_%p’;
#BACKUP INCREMENTAL LEVEL 0 tag = ‘weekly full backup’ database filesperset 1 include current controlfile;
backup incremental level 0 tag = ‘full_hot_backup’ ( database setsize 8192000 filesperset 1 include current controlfile );
sql ‘alter system switch logfile’;
sql ‘alter system archive log current’;
BACKUP filesperset 1 archivelog all delete input;
release channel ch1;
release channel ch2;
release channel ch3;
}
Run the batch script and you will find backup files in H:\Rman_backup
2.1 Export RMAN catalog schema
exp rmanager/rmanager@rman file=P:\Rman\exp\rman_CRM_exp_%date:~0,3%.dmp
this will create an export dump file, e.g., rman_Thu.dmp
2.2 List Datafile Locations on us01ap17
————————————-
If you still have access to us01ap17, meaning it is still running (thank God it is!), you should find out each datafile’s number and location.
SQL> select file#, name from v$datafile;
FILE# NAME
———- ————————————–
1 H:\ORADATA\CRM\SYSTEM01.DBF
2 H:\ORADATA\CRM\UNDOTBS01.DBF
3 H:\ORADATA\CRM\CRM_DATA01.DBF
4 H:\ORADATA\CRM\CRM_DATA02.DBF
5 H:\ORADATA\CRM\CRM_DRSYS01.DBF
6 H:\ORADATA\CRM\CRM_INDX01.DBF
7 H:\ORADATA\CRM\CRM_INDX02.DBF
8 H:\ORADATA\CRM\CRM_TOOLS01.DBF
9 H:\ORADATA\CRM\CRM_USERS01.DBF
10 H:\ORADATA\CRM\CRM_XDB01.DBF
11 H:\ORADATA\I3FP\VERITAS_I3_ORCL.DBF
12 H:\ORADATA\CRM\CRM_DATA03.DBF
The log file names should also be recorded.
SQL> select group#, member from v$logfile;
GROUP# MEMBER
——- ——————————————
1 J:\ORADATA\CRM\REDO01.LOG
2 J:\ORADATA\CRM\REDO02.LOG
3 J:\ORADATA\CRM\REDO03.LOG
4 J:\ORADATA\CRM\CRM_SRL0.F
5 J:\ORADATA\CRM\CRM_SRL1.F
3.0 Make the Backups Available to test_us01ap17
———————————————–
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways:
- create the same directory structure on us01ap17 and test_us01ap17
- copy RMAN backup files to test_us01ap17
Use Windows explorer to copy RMAN backup files from H:\rman_backup on us01ap17 to test_us01ap17
Also copy RMAN catalog export file to test_us01ap19
4.0 init.ora on test_us01ap17
—————————–
The “init.ora”, e.g., C:\oracle\admin92\CRM\pfile\initCRM.ora needs to be made available on test_us01ap17. Any location specific parameters must be ammended. For example,
- ifile
- *_dump_dest
- log_archive_dest*
- control_files
5.0 SQL*Net configuration
————————-
If running rman from us01ap17:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on us01ap17 to connect to duplicated database on test_us01ap17 configure listener.ora on test_us01ap17 to accept connections for duplicated database
If running rman from test_us01ap17:
a. configure tnsnames.ora on test_us01ap17 to connect to catalog
listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on test_us01ap17 to connect to duplicated db on test_us01ap17 configure listener.ora on test_us01ap17 to accept connections for duplicated database
If running rman from test_us01ap19 (ie, neither us01ap17 or test_us01ap17):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on test_us01ap19 to connect to duplicated db on test_us01ap17
configure listener.ora on test_us01ap17 to accept connections for duplicated database
In this example, RMAN catalog database is installed on test_us01ap19 on test_us01ap17, the tnsnames.ora has the following lines for RMAN
RMAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = test_us01ap19)(Port = 1521))
)
(CONNECT_DATA =
(SID = rman)
(SERVER = DEDICATED)
)
)
5.1 Create RMAN catalog schema.
Run this script to create a new RMAN catalog schema
sqlplus /nolog
connect sys/change_on_install@rman as sysdba
rem create tablespace rmancatalog datafile ‘P:\Rman\oradata\rmancatalog.dbf’ size 50m;
drop user rmanunan cascade;
create user rmanunan identified by rman4CRM
temporary tablespace temp
default tablespace rmancatalog
quota unlimited on rmancatalog;
grant recovery_catalog_owner, connect, resource to rmanunan;
exit;
5.2 Import RMAN catalog export into rmanunan schema
imp userid=rmanunan/rman4CRM@rman file=rman_Sun.dmp fromuser=rmanager touser=rmanunan
6.0 Connect to RMAN catalog and check backup
rman catalog rmanunan/rman4CRM@rman target sys/change_on_install@CRM log=C:\Oracle\job\rman_logs\%date:~10,4%_%date:~4,2%_%date:~7,2%_log.txt
6.1 Check available backup with this command
list backup
7.0 Recover Duplicated Database
——————————-
7.1 startup nomount the database using SQLPLUS
SQL> startup nomount pfile=C:\oracle\admin92\CRM\pfile\initCRM.ora
7.2 restore the controlfile(s) (RMAN)
connect to RMAN
run{
allocate channel c1 type disk;
restore controlfile to ‘J:\Oradata\CRM\control04.ctl’;
}
Use OS command, copy J:\Oradata\CRM\control04.ctl to control01.ctl, control02.ctl, control03.ctl
copy J:\Oradata\CRM\control04.ctl J:\Oradata\CRM\control01.ctl
copy J:\Oradata\CRM\control04.ctl J:\Oradata\CRM\control02.ctl
copy J:\Oradata\CRM\control04.ctl J:\Oradata\CRM\control03.ctl
7.2 Mount the database
RMAN> alter database mount;
7.3 rename and restore the datafiles, and perform database recovery
RMAN can be used to change the location of the datafiles from the location on us01ap17 to the new location on test_us01ap17.
Use windows explorer, copy temporary tablespace datafiles to H:\ORADATA\CRM directory. Then run this command
run {
allocate channel c1 type disk;
restore database;
recover database;
}
7.4 restore archive log files (optional)
RMAN> restore archivelog all;
7.5 Recover using SQLPLUS
sqlplus /nolog
SQL>connect sys/change_on_install@CRM as sysdba
SQL>recover automatic database using backup controlfile until cancel;
cancel;
SQL>alter database open resetlogs;
SQL> connect CRM/DBA@CRM;
==================================================================================================
==================================================================================================
/////////////((Creating a Duplicate Database on the Same Host with backup)/////////////////
PROD1 = TARGET
TEST1 = AUXILARY
1. Back up the target database as follows:
2. ADD TEST1 to listener.ora
3. Add TEST1 to tnsnames.ora
4. Create the init.ora file for the new duplicate database, test1.
5. Start the new auxiliary database (duplicate database) instance. in nomount
6. Start RMAN, and connect to the target database ORACLE_SID=prod1.Note that the target database can be mounted or open.
7. Connect to the duplicate database using the keyword auxiliary through a SQL*Net
8. Issue the duplicate target database command to start the database duplication
RMAN> connect target /
RMAN> backup database plus archivelog;
2. Use a dedicated listener configuration for RMAN by making the following additions to
your listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)
3. Add the following information to the tnsnames.ora file, located in the
$ORACLE_HOME/network/admin directory:
test1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
4. Create the init.ora file for the new duplicate database, test1.
db_name = test1
db_block_size = 8192
compatible = 11.1.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/u01/app/oracle/test1/control01.ctl',
'/u01/app/oracle/test1/control02.ctl')
db_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')
log_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')
5. Start the new auxiliary database (duplicate database) instance. in nomount
$ export ORACLE_SID=test1
$ sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance
SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittest1.ora
Oracle Instance started.
Total System Global Area 113246208 bytes
Fixed Size 1218004 bytes
Variable Size 58722860 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
SQL> exit
6. Start RMAN, and connect to the target database
ORACLE_SID=prod1. Note that the target database can be mounted or open.
$ rman
Recovery Manager: Release 11.1.0.1.0 - Beta on Sat Jun 9 14:03:42 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database: prod1 (DBID=2561840016)
7. Connect to the duplicate database using the keyword auxiliary through a SQL*Net
connection:
RMAN> connect auxiliary sys/@test1
connected to auxiliary database: test1 (not mounted)
RMAN>
8. Issue the duplicate target database command to start the database duplication
process:
RMAN> duplicate target database to test1;
==========================================================================================
==========================================================================================
/////////////////Duplicating a Database Without Any RMAN Backups//////////////////////////
1. ADD dedicated listener.ora file:
2. Add tnsnames.ora file
3. CRATE INIT.ORA FILE FROM SPFILE (CHANGE DB_NAME=TEST1)
4. CREATE PASSWORD FILE FOR
5. START THE AUX INSTANCE IN NO MOUNT MODE
6. Start up RMAN, and connect to the target database
7.Connect to the duplicate database auxiliary through a SQL*Net
8. Issue the duplicate target database command to start the database duplication
process:
1. Use a dedicated listener configuration for RMAN by making the following additions to
your listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)
2. Add tnsnames.ora file,
test1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
3. CRATE INIT.ORA FILE FROM SPFILE (CHANGE DB_NAME=TEST1)
4. CREATE PASSWORD FILE FOR
5. START THE AUX INSTANCE IN NO MOUNT MODE
$ export ORACLE_SID=test1
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount
SQL> exit
6. Start up RMAN, and connect to the target database
$rman target sys/sammyy1@eleven
7.Connect to the duplicate database auxiliary through a SQL*Net
RMAN> connect auxiliary sys/sammyy1@auxdb
connected to auxiliary database: AUXDB (not mounted)
8. Issue the duplicate target database command to start the database duplication
process:
RMAN> duplicate target database
2> to auxdb
3> from active database
4> spfile
5> parameter_value_convert =
'/u01/app/oracle/eleven/eleven','/u01/app/oracle/eleven/auxdb'
6> set log_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb'
7> db_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb';
NOTE: DUPLICATE ON DIFF HOST WITH SAME DIRECTORY STRUCTURE , THE METHOD WILL BE SAME UPTO 8 STEP
COMMAND 9 WILL BE DIFFRENT.
RMAN> duplicate database
to newdb
from active database
spfile
nofilenamecheck;
==============================================================================================
==============================================================================================
///////////Creating a Duplicate Database on a Remote Host with the Same File Structure/////////
PRIMARY DB = PROD
DUPLICATE DB = AUX
1. Back up the primary database. You must take a full backup and include all the archive
logs as well as the control file.
2. Create directories, init file for the duplicate database.
3. Start up the auxiliary instance in nomount mode:
4. connect to rman and aux and duplicate db.
1. Back up the primary database. You must take a full backup and include all the archive
logs as well as the control file.
[oracle@linux] rman target=/ catalog rman/rman@catdb
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
2. Create directories, init file for the duplicate database.
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
control_files =('/oradata/AUX/control01.ctl',
'/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')
#the following sets the source and target location for data files
db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#the following sets the source and target location for redo log files
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#the following lines must be the same as those on the target instance
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 11.1.0.1.0
3. Start up the auxiliary instance in nomount mode:
$ export ORACLE_SID=AUX
$ sqlplus '/as sysdba'
SQL> startup nomount;
4. connect to rman and aux and duplicate db.
$ export ORACLE_SID=AUX
$ rman target sys/sys@PROD catalog rman/rman@catdb auxiliary /
RMAN> duplicate target database to AUX
nofilenamecheck;
NOTE: IF YOU WANT TO DUPLICATE WITH YOU OWN CUSTOM DIRECTORY STRUCTURE
RMAN> run
{set newname for datafile 1 to '/u01/app/oracle/testdata/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/testdata/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/testdata/data01.dbf';
set newname for datafile 4 to '/u01/app/oracle/testdata/index01.dbf';
set newname for datafile 5 to '/u01/app/oracle/testdata/undotbs01.dbf';
duplicate target database to newdb
logfile
group 1 ('/u01/app/oracle/testdata/logs/redo01a.log',
('/u01/app/oracle/testdata/logs/redo01b.log') size 10m reuse,
group 2 ('/u01/app/oracle/testdata/logs/redo02a.log',
('/u01/app/oracle/testdata/logs/redo02b.log') size 10m reuse;
}
=====================================================================================
=====================================================================================
/////////////////.Duplicating a Database with Several Directories//////////////////////
