This is some basic RMAN script to backup the entire database and archived redo logs for a non-RAC environments. In this example we will save the script in a file called “dbbackup_??.scp”. The script contains disk location which can be changed according to your specific environment. The resulting shell script can be run manually from the command line or schedule using CRON. For RMAN place the following command into cmdfile or create a batch file. The batch file must include a catalog=entry if a recovery catalog is used.
There is several RMAN Backup & Recovery script i already uploaded you can search them and update it as per your environment. Below is some basic shell script i found. Hope it will be useful. While creating the RMAN shell script some of the point you must keep in mind. Click on the below link: Point to be considered with RMAN Backup Scripts
Consider you have already configure RMAN controlfile autobackup parameter ON
configure controlfile autobackup format for device type disk to '/u10/catdb/backup/auto_cntrl_%F';
configure controlfile autobackup on;
RMAN Script: Database Full Backup
To take rman offline backup mount the database and run the below script. Do not forget to startup the database again. It is better to include shutdown/startup as a part of the script. For more details please check our other rman offline backup script.
run {
allocate channel c1 type disk;
backup tag weekly_orcl3_full
format '/u07/orcl3/backup/full_%d_%s_%p_%t'
(database);
release channel c1;
allocate channel c2 type disk;
backup format '/u07/orcl3/backup/archive_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 2 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 Catalog Catalog/Catalog@rman cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm_level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 2 cumulative tag orcl3_cm2
format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 1 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 1 cumulative tag orcl3_cm1
format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 0 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level0 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm0.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 0 cumulative tag orcl3_cm0
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Deleting archivelog when catalog exists.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/orcl3_archflush.scp'
exit
# RMAN SCRIPT: DELETING ARCHIVE LOGS
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
#or RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel c1;
}
RMAN Script: Deleting the old archives when no catalog exists
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="catdb"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@catdb cmdfile='/u04/rman_archflush.scp'
exit
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
# OR delete archivelog until sequence=;
release channel c1;
RMAN Script: Backing up all the archivelog files
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman ARCHIVE backup for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/arch_bkup.scp'
echo rman ARCHIVE backup for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup
format '/u10/catdb/backup/arch_%d_%s_%p_%t'
(archivelog all);
release channel c1;
# deleting archive logs older than 8 days
allocate channel c2 type disk;
delete archivelog until time 'SYSDATE-5';
release channel c2;
}
Database Backup Script (OFFLINE / LOGICAL BKUP)
Script1 : Offline backup
This scripts first take the logical export using datapump, then it shutdown the database, Copy the files to tape drive using
TAR(it coppies all oracle db related file like spfile, pwdfile, listener,tnsnames files aswell) , then start the database.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo orcl3 database export started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (TAKING DATAPUMP EXP)
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo ORCL3 database export ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database shutdown begin at `date` >> /u05/orcl3/export/offbkup_orcl3.log (SHUTTING DOWN THE DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/shutdown_orcl3.sql
echo ORCL3 database shutdown at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo TAPE archiving started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (COPYING FILES TO TAPEDRIVE USING TAR)
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u01/app/oracle/product/10.2.0/dbs/initorcl3.ora /u01/app/oracle/product/10.2.0/dbs/spfileorcl3.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/orcl3 /u02/orcl3/oradata /u03/orcl3/oradata /u04/orcl3/oradata /u05/orcl3/oradata /u06/orcl3/orcl3/scripts /u06/orcl3/arch
echo TAPE archiving ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database getting started at `date` >> /u05/orcl3/export/offbkup_orcl3.log(STARTING DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/startup_orcl3.sql
echo ORCL3 database started at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 offline backup finished at `date` >> /u05/orcl3/export/offbkup_orcl3.log
$ more startup_orcl3.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_orcl3.sql
connect / as sysdba;
shutdown immediate;
exit;
Script2: Datapump export backup.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo export started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo export stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
echo tape archiving started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u06/orcl3/arch
echo tape archiving stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
Script3: Backup all archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/orcl3/arch
echo morning tape archiving ended
Scripts to Stop/Start the Enterprise Manager while performing Offline backup
$ more emctl_start.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STARTING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl start dbconsole
$ more emctl_stop.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
RMAN SCRIPT : DISASTER RECOVERY
# The commands below assume that all initialization parameters files are in place and the complete directory structure for the datafiles is recreated and you already set LS_LANG environment variable
setenv NLS_LANG amarican_america.we8dec
# Start RMAN without the target option, and use the following commands to RESTORE and RECOVER the database
# SET DBID 63198018;
# not required if using recovery catalog. You can find DBID from RMAN Controlfile Autobackup.
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
# optionally you can set newname and switch commands to restore datafiles to a new location
restore controlfile from autobackup;
alter database mount;
restore database;
reocver database;
alter database open resetlogs;
Note: You must take a new whole database backup after resetlogs, since backups of previous incarnation are not easily usable.
RMAN Script: POINT IN TIME RECOVERY
# This scenario assumes that all initializaiton filesa and the current controlfile are in place and you want to recover to a point in time '2012-05-22"10:30:00'.
# Ensure you set your NLS_LANG enviroment variable
STARTUP MOUNT FORCE;
RUN
{
SET UNTIL TIME "TO_DATE('2012-05-22"10:30:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Note: You must take a new whole database backup after resetlogs,since backups of previous incarnation are not easily usable
RMAN SCRIPT : CONTROLFILE RECOVERY
# Oracle strongly recommends that you specify multiple controlfiles, on separate physical disks and controllers, in the CONTROL_FILE initialization parameter.
# - If one copy is lost due to media failure, copy one of the others over the lost controlfile and restart the instance.
# - If you lose all copies of the controlfile, you must re-create it using the create controlfile sql command
# You should use RMAN to recover a backup controlfile only if you have lost all copies of the current controlfile, because after restoring a backup controlfile, you will have to open RESETLOGS and take a new whole database backup.
# This section assumes that all copies of the current controlfile have been lost, and than all initialization parameter files, datafiles and online logs are intact.
# Ensure you set your NLS_LANG environment variable e.g. in unix (csh):
# >setenv NLS_LANG american_america.we8dec
# Start RMAN without the TARGET option, and use the following commands to restore and recover the database;
# SET DBID 63198018;
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
restore controlfile from autobackup # or directly provide the controlfile backup location
alter database mount;
recover database;
alter database open resetlogs;
}
# you must take a new whole database backup after reerlogs, since backups of previous incarnation are not easily usable
RMAN Script: DATAFILE RECOVERY
# This section assumes that datafile 5 has been damaged and needs to be restored and recovered, and that the current controlfile and all other datafiles are intact. The database is mounted during the restore and recovery.
# - offlie the datafile that needs recovery
# - restore the datafile from backups
# - apply incrementals and archivelogs as necessary to recover.
# - make online recovered datafile
run
{
sql 'alter database datafile 5 offline';
#if you want to restore to a different location,uncomment the following command
# Set newname for datafile 5 to '/newdirectory/new_filename.dbf';
restore datafile 5;
# if you restored to a different locatin, uncomment the command below to switch the controlfile to point to the file in the new location
# SWITCH DATAFILE ALL;
recover datafile 5;
sql 'alter database datafile 5 online';
}
Showing posts with label rman first time. Show all posts
Showing posts with label rman first time. Show all posts
How to configure Oracle RMAN backup for the first time
How to configure Oracle RMAN backup for the first time
RMAN is a oracle utility to backup, restore & recovery of database.
The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)
Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.
catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.
Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”
Note: Create catalog/repository database with the help of DBCA.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.
catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.
Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”
Note: Create catalog/repository database with the help of DBCA.
Lets consider following Step by Step syntax to do so:
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 3 11:28:24 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Database is in NOARCHIVELOG mode.
Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step # 4: Startup the database in mount state.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.
Step # 5: Configure database in ARCHIVELOG mode.
SQL> alter database archivelog;
Database altered.
Step # 6: Alter database to open state.
SQL> alter database open;
Database altered.SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
Step # 7: Ensure ARCHIVELOG destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
In case you wish to change default archive log destination then issue the following command.
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
Step # 8: Ensure the flash/fast recovery area location.
SQL> show parameter db_recovery_file_dest
Step # 9: Connect to RMAN prompt with target database.
[oracle@centos ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 3 11:46:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
RMAN>
Step # 10: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.
RMAN> configure controlfile autobackup on;
Step # 11: To enable backup optimization run the following command, by default backup optimization has been configured OFF.
RMAN> configure backup optimization on;
Step # 12: Configure retention policy for backup.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Step # 13: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
CATALOGDSQL> create tablespace catalogtbs datafile '/home/oracle/dbfile/catalogtbs1.dbf' size 100M autoextend on maxsize unlimited;
Tablespace created.
Step # 14: Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.
SQL> create user recoveryman identified by recoveryman;
User created.SQL> alter user recoveryman default tablespace catalogtbs temporary tablespace temp;
User altered.SQL> grant recovery_catalog_owner to recoveryman;
Grant succeeded.SQL> grant connect,resource to recoveryman;
Grant succeeded.
Step # 15: Connect to RMAN on target and recovery catalog database.
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 14:30:28 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
connected to recovery catalog database
Step # 16: create catalog by issuing the following command in RMAN prompt.
RMAN> create catalog;
recovery catalog created
Step # 17: After creating catalog, Ensure RMAN repository tables by logging into repository database as RMAN user. This is only for the first time.
[oracle@oracle ~]$ sqlplus "recoveryman/recoveryman@catalogdb"
SQL> show user;
USER is "RECOVERYMAN"SQL> select table_name from user_tables;
Step # 18: Register database with recovery catalog.
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Step # 19: Check whether registration was successful.
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 670 SYSTEM YES /home/oracle/app/oracle/oradata/orcl/system01.dbf
2 490 SYSAUX NO /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 30 UNDOTBS1 YES /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS NO /home/oracle/app/oracle/oradata/orcl/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ----------- --------- -------------------------------
1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
OR
RMAN> LIST INCARNATION OF DATABASE;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- ------------ --- --------
89 102 ORCL 1363580714 PARENT 1 15-AUG-09
89 90 ORCL 1363580714 CURRENT 945184 02-JAN-14
Target database is registered with the RMAN.
Now you can backup your target(registered) database as per your convenience.
Subscribe to:
Posts (Atom)