Every one thinking about RMAN(Recovery Manager) when we are talking about the Database backup and recovery. But there are still different some different procedures are preset via which we can backup and restore or recover the database,.Ex. import and export and manually etc
Here are the manually hot backup /restore of an Oracle 11gR2 database.
The steps to successfully accomplish a manual hot backup and restore of an Oracle11gR2 database without using the RMAN utility.
Pre-requisites-
Perform the following steps to set up your database for a backup operation
1. Determine the location of your database. For example, in oracle 11gR2 default install location for a database instance with SID=UPGRADE would be:
C:\app\<username>\oradata\UPGRADE
2. Database comprises of control files, redo log files, Data files
3. Locate the pfile and spfile of your database instance, and back them up if necessary. (refer to this link)
4. Connect to your database instance using sqlplus. Run the following command on the command prompt
C:\>sqlplus / as sysdba
5. Run the following query on the SQL Plus syntax to determine if you are connected to the right database
Query: Select name from v$database;
Set up the database to be able to perform a hot backup-
1. Check if the database is in archivelogmode
Query: Select log_mode from v$database;
2. If the database is not in archivelogmode then put the database in the archive log mode
a.) View the location where the archive logs would be written
Query: show parameter log_archive_dest_1;
b.) Set the value of this parameter to a desired location
Query: alter system set log_archive_dest_1=’LOCATION=<path to desired directory>’ scope=spfile;
Example: alter system set
log_archive_dest_1=’LOCATION=c:\my_directory_for_logfiles’ scope=spfile;
c.) Shutdown the database
Query: shutdown immediate;
d.) Start database in mount mode
Query: startup mount;
e.) Alter the database to start archive logging
Query: Alter database archivelog;
f.) Open the database so that it is available for transactions
Query: Alter database open;
g.) Verify that the database is in archivelogmode
Query: select log_mode from v$database;
h.) View other details of the archive logging
Query: Archive log list;
Note- One can switch the current archive log by executing the command
Query: Alter system archive log current;
Taking a hot backup-
Now that we have prepared our database for a hot backup, we can go ahead with actually backing up the files.
Follow the following steps to take hot backup of the tablespaces
1. Find out the number of tablespaces associated with the database
Query: Select tablespace_name from dba_tablespaces;
The output is a list which contains names of tablespaces you will need to backup for the whole database.
2. Find out if the tablespaces are ready for hot backup
Query: Run select * from v$backup;
If the output says not active then it is not in hot backup mode
Note- Make sure that the database is in archivelog mode before trying to attempt this. You cannot take a hot backup of your tablespaces unless your database is operating in the archive log mode
3. Put the tablespaces in hot backup mode
Query: Alter database begin backup;
Query: Select * from v$backup;
Now the output should say active.
4. Copy the tablespace files on the hard drive to the backup location.
5. Put the tablespaces out of the backupmode
Query: Alter database end backup;
6. Verify that the tablespaces indeed have come out of the backup mode
Query: Select * from v$backup;
7. Switch the archive log
Query: Alter system archive log current;
8. Backup the control file
Note- Don’t use the operating system’s copy command to do this
Query: Alter database backup controlfile to ‘<path>\ backup filename’
9. Copy the archive logs to the backup location
Backup of the database finished.
Restoring the oracle database from a hot backup
1. Copy the tablespace files from the backup location to the installation directory of the database instance. Also copy the controlfilebackup.
2. Rename it to CONTROL01.CTL as it was earlier.
Note- If you had another copy of the control file with the name CONTROL02.CTL, then just create a second copy of the CONTROL01.CTL and rename it CONTROL02.CTL
3. DO NOT COPY OR restore the REDO logs. If the REDO logs from the previous backup period persist then delete them
4. Start the database in mount mode
Query: startup mount;
5. Recover your database using the following
Query: Recover database until cancel using backup controlfile;
Note- the oracle system will suggest an ‘archive log file name’ to use for recovery, if you have copied the backup logs to the same location which was being used for storing the logs by the database, then u can just keep on pressing enter. Or you may give the full path to the log file.
6. When you have applied all the logs that you had used to take the backup, then write cancel on the prompt and press enter.
7. The transaction logs have been applied. Run the following query to open the database for transactions
Query: Alter database open resetlogs;
Restore complete.....
Now CHECK IT carefully..
Now, if you going and start your instance and try to open the database if it gets open without giving any error means you are right there and you hit the bulls eye.
But ,if the database is not opened,hen you are forced to get the below error:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open .
like this----->
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>set oracle_sid=dev
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 7 10:13:03 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
The Solution for it: sql> alter database open resetlog; DID the below process after login into the database with sqlplus
shut immediate;
startup mount:
alter databse open resetlogs;
your database will be up and running
The true cause for getting this error is:
Cause: Either incomplete or backup controlfile recovery has been performed. After these types of recovery you must specify either the RESETLOGS option or the NORESETLOGS
option to open your database.
WE can also visit the link: http://www.club-oracle.com/forums/how-to-take-hot-backup-of-oracle-database-t5153/
No comments:
Post a Comment