Sep 19, 2008

Backup using Uman

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the data should that loss occur. The reconstructing of data is achieved through media recovery, which refers to the various operations involved in restoring, rolling forward, and rolling back a backup of database files.

Physical Backup
Hot backup
1. DB is in open stage
2. Check whether Archive is enabling or not. If not it must be enable.
3. Datafile and archive are to be backed up
4. Media recovery required.
5. Backup everything except binary files.
1. Get the listing of tablespace and Datafile.
a.Select tablespace_name, file_name from dba_data_files;
2. Put the tablespace in backup mode.
a. Alter tablespace tbsname begin backup;
3. Copy each Datafile in os level using CP command and move file to backup location
4. End the tablespace in backup mode
a.Alter tablespace tbsname end backup;
5. Repeat steps from 2 to 4 for each tablespace.
6. Backup the controlfile trace to bdump.
a. Alter database backup controlfile to trace;
7. Confirm all tablespace returned to normal mode.
a. Select file#, status, change#, time from v$backup where status != ‘NOT ACTIVE’;
8. Perform an archive log switch.
a. Alter system archive log current;
9. Backup all archive log files

When executing BEGIN BACKUP the Datafile header corresponding to the tablespace get freeze until executing END BACKUP. In between the END BACKUP time Datafile has to be copied from one location to another location (BL) using OS command CP. While moving the Datafile from one location to backup location data block will be split due to the OS block size. Still the transaction has been gone thought but the SCN number will not update in the Datafile header until trigger end backup.
1. Block corruption cannot be finding in HB.
2. Split block will occur.
3. Database inconsistence
4. Partial SCN will update

Cold Backup
1. Database is in closed stage
2. Not available to the users
3. Get the down time from client
4. Schedule the time and plan it
5. Copy each datafiles and control file.

1. Shutdown immediate
2. Copy each Datafiles and control file in os level
Logical Backup
Export and Import
Export and import is an Oracle utility in operating system level. It will be installed automatically while installing the oracle software itself. It is a tool allows you to extract data from the database and stored in a file called dump file in binary format. We can easily carry the binary file from one operating system to another operating system and also we move the data from one oracle database to another oracle database. So this tool is absolutely platform independent. The sizes of the dump (binary) file are compress into 1/3 of our Datafile size.

Export modes
1. Full database
2. Tablespace level
3. Schema level (user)
4. Table level
5. Record level (using query clause)

1. Exp_full_database
2. Imp_full_database

Export clause
Buffer=0 - Os dependent. As a result, the max no. of rows in an array fetch by export.
Buffer_size = rows_in_array * maximum_row_size
Statistics=none/compute/estimate (d)
Query= where condition

Database Cloning
Hot Clone
1. Create required Directories in Clone db location.
2. Put the tablespace in backup mode.
a. Alter tablespace begin backup;
3. Copy the snapshot of each data files in this tablespaces.
4. End the tablespace in backup mode.
a. Alter tablespace end backup;
5. Repeat steps 2, 3, and 4 for each Tablespaces.
6. Backup parameter file.
a. Create pfile from spfile to clone db location;
b. edit the path and db name
7. Trace the control file.
a. Alter database backup control file to trace;
b. edit this file: removing the head, strike out "reuse", change "noresetlogs" to "resetlogs”
8. Copy each redo log file to clone db location.
9. Backup all archive files.
10. create new service id and password file
a. oradim –new –sid xx
b. orapwd file=xx.ora password=xx entries=5
11. Set the environment
a. Set oracle_sid=xx
b. Sqlplus “/as sysdba”
12. Startup the clone db with new pfile and put it in mount state
a. Startup pfile=/initxx.ora nomount
13. Run the control file script to re-create control file and other Datafile.
a. @create.sql
14. Recover the database using controlfile if required.
a. Recover database using backup controlfile until cancel
b. Apply the archive files
15. Open the database with resetlogs mode.
a. Alter database open resetlog

Cold Clone
1. Trace the control file to udump.
Alter database backup control file to trace;
2. Create pfile from spfile for clone db.
3. Shutdown the database
4. Create required Directories in Clone destination
5. Copy and paste all the Datafile from target database.
6. Edit the parameter file and replace db name and path.
7. Edit the control file also
8. Startup the clone db with new pfile and place it in mount state
9. Run the control file script to re-create control file and other datafiles.
10. Alter database open resetlogs;

1 comment:

Unknown said...

good for reference...