Oracle Database 11g R2 Backup and Recovery in Linux 5

Export/Import dump File

exp system/system@mir file='g:\backup\expdat.dmp' log='g:\backup\expdat.log' buffer=2048000 full=y
it will export full database. you import full database using this dump or you can import users/tables using the same dump.
please search google for basic export and import commands/scripts.

exp system/system@mir file='g:\backup\expdat.dmp' log='g:\backup\expdat.log' buffer=2048000 owner=MIR,ACCOUNTS,PURCHASE,HRMS,CRM,CRS 
save this as .cmd file and schedule a job in task manages/using AT utility

Super fast Database Copying/Cloning

Oracle Tips by Burleson Consulting

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 
STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
 
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.

Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode

Create your own RMAN backup scripts from the scratch 

http://selectsysdatefromdual.gnusystems.net/?p=71

Quick RMAN configuration for disk based backups

This document will detail a quick set up of RMAN for disk based backups using Oracle Database 11gR2 base. It is by no means intended to be an exhaustive introduction to RMAN nor should what is presented be used for production based backup configuration. It is just intended to get your feet wet while exploring the capabilities of RMAN for backup and recovery.

By default the RMAN configuration is stored in the control file. You can see the default values for the configuration parameters by using RMAN to connect to the database and issuing the SHOW ALL command.

[oracle@aries ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 26 21:26:22 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /

OR

RMAN>connect target sys/ptcserver@orcl

connected to target database: ORCL (DBID=1244946728)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN>


For this example we will only be setting enough parameter to allow us to perform disk based backups meeting the following criteria.
1. The Control File is to be automatically backed up every time a back up is taken and when the structure of the database changes in manner that would require a new backup of the control file.
2. The control file backups are to be stored on disk in the /u01/app/oracle/oradata/orcl/backup directory.
3. The backups should be made using backup sets and stored on disk.
4. Each backup set piece size should be 2GB or smaller in size and should be stored on disk in the /u01/app/oracle/oradata/orcl/backup directory.
5. Since this is a test database we only want to keep the last two backups made.

Configuring RMAN

Changing the configuration in RMAN is fairly straightforward. If you look back at the output from the show all command above, you will notice that each line begins with CONFIGURE and end in a semi colon representing a complete command set the configuration item. You could copy and paste one of those lines in an RMAN prompt.

For example we will copy the configure line for BACKUP OPTIMIZATION.

RMAN> CONFIGURE BACKUP OPTIMIZATION OFF; # default
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters are successfully stored
RMAN>













If you make a mistake you or other wise wish to clear the configuration you can use CLEAR to rest the configuration parameter to its default value.
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;       
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
RMAN configuration parameters are successfully reset to default value
RMAN>

Parameters that are set at their default value will have the # default at the end of line.



RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default

1. The control file is to be automatically backed up every time a back up is taken and when the structure of the database changes in manner that would require a new backup of the control file.

How many times have you added new data files to a table space or created a table space and forgot to backup the control file? RMAN can be configured to automatically backup the control file when a data file is added or when a backup is taken with CONTROLFILE AUTOBACKUP. By default CONTROLFILE AUTOBACKUP is set to OFF.



RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>

2. The control file backups are to be stored on disk in the /u01/app/oracle/oradata/orcl/backup directory.

To set the location in which to write the control file auto backup you will need to set the FORMAT for the CONTROLFILE AUTOBACKUP. By default the FORMAT has only the substitution variable %F which translates into c--YYYYMMDD-QQ. For example the control file auto back for this database would be c-1244946728-20100601-00. The value 1244946728 is the database ID, 20100601 is the date and 00 is the sequence number in hexadecimal.
The %F has to be in the FORMAT and there can be no other substitution variables.



RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>

3. The backups should be made using backup sets and stored on disk.

RMAN can store the backup in one or more binary files called a backup set that contain data from one ore more data files, archive logs, control files or an SPFILE. These files are written in an RMAN specific format and be compressed during the creation process. Each individual file in a back set is called a backup piece.
RMAN can also create exact copies of the data files, archive logs, control files and SPFILE. These image copies are not created in an RMAN specific format.
In RMAN the type of backup is configured via the device type to be used to create the backup. By default the default device type is disk and RMAN is configured to write backup sets to disk.



RMAN> show default device type;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> show device type;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
RMAN>

So this requirement is already present by default.

4. Each backup set piece size should be 2GB or smaller in size and should be stored on disk in the /u01/app/oracle/oradata/orcl/backup directory.

In order for RMAN to read or write data it must have a channel configured. In configuring the channel we can set the FORMAT, or location on disk, in which to read and write backups. The channel is also were we configure the size of the backup piece in the case of backup sets.
Like the CONTROLFILE AUTOBACKUP FORMAT, the CHANNEL FORMAT has a substitution variable %U. The %U specifies a system generated unique file name. There are other substitution variables that can be used in place or in conjunction with the %U see the documentation Oracle Database Backup and Recovery Reference for a complete list. Unlike the CONTROLFILE AUTOBACKUP FORMAT you can have multiple substitution variables.


RMAN> configure channel device type disk format '/u01/app/oracle/oradata/orcl/backup/%U' maxpiecesize 2 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/oradata/orcl/backup/%U' MAXPIECESIZE 2 G;
new RMAN configuration parameters are successfully stored
RMAN>


5 Keep only the last two backups made.

When discussing how long to keep a backup we are talking about a retention policy. Using RMAN you can choose a recovery window (have a rolling window of days), or a set redundancy (rolling number of backups).
One thing to keep in mind is that when you specify a recovery window you are specifying how many days you want to keep not the number of backups, and when you specify a redundancy you are stating how many backups you want to keep not the number of days.
In this example we have set the Redundancy to two so we are always going to keep the last two backups regardless of how old they are.



RMAN> configure retention policy to redundancy 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored
RMAN>

Test out the configuration

At this time we have a very basic configuration for disk based backup. We can test this out with a simple backup. The output below is from the database configured in this document. Since this database is not in archive log mode a cold backup was taken.


[oracle@aries ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 1 13:16:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1244946728, not open)
RMAN> backup database;
Starting backup at 01-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-10
channel ORA_DISK_1: finished piece 1 at 01-JUN-10
piece handle=/u01/app/oracle/oradata/orcl/backup/02lf51pj_1_1 tag=TAG20100601T131635 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
Finished backup at 01-JUN-10
Starting Control File and SPFILE Autobackup at 01-JUN-10
piece handle=/u01/app/oracle/oradata/orcl/backup/c-2502578563-20100601-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-JUN-10
RMAN> alter database open;
database opened
RMAN>

Or apply this commond.

RMAN> backup database plus archivelog;

Or run this script.

run{
crosscheck backup;
crosscheck archivelog all;
allocate channel t1 type disk;
allocate channel c1 type disk;
BACKUP INCREMENTAL LEVEL 1 DATABASE
format '/image_backup/Greens/config/%d_%s_%p.bkp' tag=fullbkp
PLUS ARCHIVELOG
format '/image_backup/Greens/config/ARC_%d_%s_%p.bkp' tag=arcbkp;
release channel t1;
release channel c1;
crosscheck backup;
crosscheck archivelog all;
DELETE NOPROMPT OBSOLETE;
}



This configuration is enough to get you started in exploring backup and recovery with RMAN.

For more detail visit http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmbackp009.htm

 

Oracle 11gR2 – Using RMAN to duplicate a live database

Create a backup of the source database that you want to duplicate. (By default the backup set will reside in $ORACLE_HOME/dbs)

RMAN> rman target=/
RMAN> configure controlfile autobackup on;
RMAN> backup database plus archivelog;
 
or described above. 
 
Create a pfile on the source database and copy it to the destination database later;

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G' from spfile;
 
Create a password for both the source and destination database so that RMAN can connect to them later on.

# orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password
 
Create a entry in tnsnames.ora in the destination database. (We will run RMAN on the destination host to connect to the source DB)

DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host =DB11G_host)(Port = 1521))
(CONNECT_DATA = (SERVICE_NAME = DB11G))
)
 
Create all the required file structure on the destination host. Also, copy all the backup sets as created on source server, passwordfile and pfile to $ORACLE_HOME/dbs on the destination host

On the destination database;

SQL> sqlplus / as sysdba
SQL> startup nomount;
SQL> exit
# rman
RMAN> connect target sys/password@DB11G
connected to target database: DB11G (DBID=12345678)
RMAN> connect auxiliary /
connected to auxiliary database: DB11G (not mounted)
RMAN> duplicate target database to DB11G 
2> nofilenamecheck;
 
Verify if the destination database is duplicated correctly.
 
#sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 14:55:13 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DB11G 

Backup-Based Duplication

Create a backup of the source database, if a suitable one doesn't already exist.
$ rman target=/

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG
All subsequent actions occur on the server running the duplicate database.
Create a password file for the duplicate instance.
$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10
Add the appropriate entries into the "tnsnames.ora" file in the "$ORACLE_HOME/network/admin" directory to allow connections to the target database from the duplicate server.
# Added to the tnsnames.ora
DB11G-SOURCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )
Create a PFILE for the duplicate database. Since we are duplicating the database onto a separate server with the same filesystem as the original, we don't need to convert the file names. In this case, the PFILE is called "initDB11G.ora" and is placed in the "$ORACLE_HOME/dbs" directory.
# Minimum Requirement.
DB_NAME=DB11G

# Convert file names to allow for different directory structure if necessary.
#DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u01/app/oracle/oradata/NEWSID/'
#LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u02/app/oracle/oradata/NEWSID/'
We don't need all the other parameters as the clone will copy the SPFILE from the primary database. If you are duplicating a database on the same machine you will probably need to create a PFFILE/SPFILE manually from the primary database SPFILE, manually change the values and avoid the SPFILE clause in the duplicate command.
Create any directories necessary for start the duplicate database.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Make the backup files from the source database available to the destination server. That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use the following type of commands.
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/autobackup /u01/app/oracle/fast_recovery_area/DB11G
Connect to the duplicate instance.
$ ORACLE_SID=DB11G; export ORACLE_SID
$ sqlplus / as sysdba
Start the database in NOMOUNT mode.
SQL> STARTUP NOMOUNT;
With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).
 
$ ORACLE_SID=DB11G; export ORACLE_SID

# No target or catalog. Metadata comes from backups.
$ rman AUXILIARY / 
 
We can then duplicate the database using one of the following commands. 
Remember, remove the SPFILE clause if you have manually created a full 
PFILE or SPFILE.
 
# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;
The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.
 
SQL> select instance_name,status from v$instance;

INSTANCE_NAME     STATUS
---------------- ------------
orcl         OPEN
 
SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
1351764587 ORCL
 
How to Resolve ORA-01078 with LRM-00109 Error

For resolving these error, you should need to replace init.ora or spfile if you have backup of same files. If you don't have backup then go to alert.log and copy all parameters in one file and save as "init.ora". Modify same init.ora with some parameters which need single quotation mark " ' " like background_dump_dest, controlfiles, db_name etc. Connect as sysdba in SQL*Plus and execute command "create spfile from pfile". This command will generate again your server parameter file (spfile) in system which needs to be starting database.

Means creating new spfile or pfile will solve error of LRM-00109 error with ORA-01078 error. When you got error check first default location for same files exist or not. If you don't find out any of files then re-create it. If you find out any of one file then check exact location and try to open database using "startup pfile=' '" command. Does it working or not? If it is working then create spfile from it. Or create pfile from alert.log file is last solution to resolve ORA-01078 with LRM-00109 error.

 

Building a failover database using Oracle Database 11g Standard Edition and File Watchers





No comments:

Post a Comment

Subscribe to: Posts (Atom)