Opening or Bringing the database in Archivelog mode.
To open the database in Archive log
mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Take a full offline backup.
STEP 3: Set the following parameters in parameter file.
LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
If
you want you can specify second destination also
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”
Step 3: Start and mount the database.
SQL>
STARTUP MOUNT
STEP
4: Give the following command
SQL>
ALTER DATABASE ARCHIVELOG;
STEP 5: Then type the following to confirm.
SQL>
ARCHIVE LOG LIST;
STEP 6: Now open the database
SQL>alter database open;
Step 7: It is recommended that you take a full backup after
you brought the database in archive log mode.
To again bring back the database in NOARCHIVELOG mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Comment the following parameters in parameter file
by putting " # " .
#
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
#
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
#
LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
STEP 3: Startup and mount the database.
SQL>
STARTUP MOUNT;
STEP 4: Give the following Commands
SQL>
ALTER DATABASE NOARCHIVELOG;
STEP 5: Shutdown the database and take full offline backup.
TAKING OFFLINE BACKUPS. ( UNIX )
Shutdown the database if it is
running. Then start SQL Plus and connect as SYSDBA.
$sqlplus
SQL>
connect / as sysdba
SQL>
Shutdown immediate
SQL>
Exit
After Shutting down the database. Copy all the datafiles, logfiles,
controlfiles, parameter file and password file to
your backup destination.
TIP:
To identify the datafiles, Logfiles
query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files
to the backup destination /u02/backup.
$cd /u01/ica
$cp
* /u02/backup/
Be sure to remember the destination of each file. This will
be useful when restoring from this backup. You can create text file and put the
destinations of each file for future use. Now you can open the database.
TAKING ONLINE (HOT) BACKUPS.(UNIX)
To take online backups the database should be running in Archivelog mode. To check whether the database is running in Archivelog
mode or Noarchivelog mode. Start sqlplus
and then connect as SYSDBA.
After connecting give the command "archive log
list" this will show you the status of archiving.
$sqlplus
Enter
User:/ as sysdba
SQL>
ARCHIVE LOG LIST
If the database is running in archive log mode then you can
take online backups.
Let us suppose we want to take online backup of "USERS"
tablespace. You can query the V$DATAFILE view to find out the name of datafiles
associated with this tablespace. Lets suppose the file
is
"/u01/ica/usr1.dbf ".
Give the following series of commands to take online backup
of USERS tablespace.
$sqlplus
Enter
User:/ as sysdba
SQL>
alter tablespace users begin backup;
SQL>
host cp /u01/ica/usr1.dbf /u02/backup
SQL>
alter tablespace users end backup;
SQL>
exit;
RECOVERING THE DATABASE IF IT IS RUNNING IN NOARCHIVELOG MODE.
Option 1: When you don’t have a backup.
If you have lost one datafile and if you don't have any
backup and if the datafile does not contain important objects then, you can
drop the damaged datafile and open the database. You will loose all information
contained in the damaged datafile.
The following are the steps to drop a damaged datafile and
open the database.
(UNIX)
STEP 1: First take full backup of database for safety.
STEP 2: Start the sqlplus and give
the following commands.
$sqlplus
Enter
User:/ as sysdba
SQL>
STARTUP MOUNT
SQL>
ALTER DATABASE DATAFILE
'/u01/ica/usr1.dbf '
offline drop;
SQL>alter
database open;
Option 2:When you have the Backup.
If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.
i . Either you can drop the damaged datafile, if it does not
contain important information which you can
afford to loose.
afford to loose.
ii . Or you can restore from full backup. You will loose all the
changes made to the database since last full
backup.
backup.
To drop the damaged datafile follow the steps shown
previously.
To restore from full database
backup. Do the following.
STEP 1: Take a full backup of current database.
STEP 2: Restore from full database backup i.e. copy all the
files from backup to their original locations.
(UNIX)
Suppose the backup is in "/u2/oracle/backup"
directory. Then do the following.
$cp
/u02/backup/* /u01/ica
This will copy all the files from backup directory to
original destination. Also remember to copy the control files to all the
mirrored locations.
RECOVERING FROM LOST OF CONTROL FILE.
If you have lost the control file
and if it is mirrored. Then simply
copy the control file from mirrored location to the damaged location and open
the database
If you have lost all the mirrored control files and all the
datafiles and logfiles are intact. Then you can
recreate a control file.
If you have already taken the backup of control file
creation statement by giving this
command. " ALTER DATABASE BACKUP
CONTROLFILE TO TRACE; " and if you have not added any tablespace since
then, just create the controlfile by executing the statement
Buf If you have added any new
tablespace after generating create controlfile statement. Then you have to
alter the script and include the filename and size of the file in script
file.
If your script file containing the
control file creation statement is "CR.SQL"
Then just do the following.
STEP 1: Start sqlplus
STEP 2: connect / as sysdba
STEP 3: Start and do not mount a database like this.
SQL>
STARTUP NOMOUNT
STEP 4: Run the "CR.SQL" script file.
STEP 5: Mount and Open the database.
SQL>alter database mount;
SQL>alter database open;
If you do not have a backup of Control file creation
statement. Then you have to manually give the CREATE CONTROL FILE statement.
You have to write the file names and sizes of all the datafiles. You will lose
any datafiles which you do not include.
Refer to "Managing Control File" topic for the
CREATE CONTROL FILE statement.
Recovering Database when the database is running in ARCHIVELOG Mode.
Recovering from the lost of Damaged Datafile.
If you have lost one datafile. Then follow the steps shown below.
STEP 1. Shutdown the Database if it is running.
STEP 2. Restore the datafile from most recent backup.
STEP 3. Then Start sqlplus and connect as
SYSDBA.
$sqlplus
Enter
User:/ as sysdba
SQL>Startup
mount;
SQL>Set
autorecovery on;
SQL>alter
database recover;
If all archive log files are available then recovery should
go on smoothly.
After you get the "Media Recovery Completely"
statement. Go on to next step.
STEP 4. Now open the database
SQL>alter database open;
Recovering from the Lost Archived Files:
If you have lost the archived files. Then Immediately shutdown the database and take a full
offline backup.
Time Based Recovery (INCOMPLETE RECOVERY).
Suppose a user has a dropped a crucial table accidentally
and you have to recover the dropped table.
You have taken a full backup of the database on Monday
13-Aug-2007 and the table was created on Tuesday 14-Aug-2007 and thousands of
rows were inserted into it. Some user accidently drop the table on Thursday
16-Aug-2007 and nobody notice this until Saturday.
Now to recover the table follow
these steps.
STEP 1. Shutdown the database and take a full offline backup.
STEP 2. Restore all the datafiles, logfiles
and control file from the full offline backup which was taken on Monday.
STEP 3. Start SQLPLUS and start and mount the database.
STEP 4. Then give the following command to recover database until
specified time.
SQL>
recover database until time '2007:08:16:13:55:00'
using backup
controlfile;
STEP 5. Open the database and reset the logs. Because you have
performed a Incomplete Recovery, like this
SQL>
alter database open resetlogs;
STEP 6. After database is open. Export the
table to a dump file using Export Utility.
STEP 7. Restore from the full database backup which you have taken
on Saturday.
STEP 8. Open the database and Import the table.
Note: In Oracle 10g you can easily recover drop tables by
using Flashback feature. For further information please refer to Flashback
Features Topic in this book.
No comments:
Post a Comment