Posts tagged "restore oracle"

Manual hot backup/restore of an Oracle 11gR2 database

Hot Backup mode provided by ADEP Document services 10.0-

As stated at this link, ADEP Document Services 10.0 offers a feature to operate in a safe backup mode so that the users can take a hot backup of the Document Server without affecting any of its functionality.

We recommend the users to take backups of the database as recommended in its corresponding documentation. Here are the steps to successfully accomplish a manual hot backup and restore of an Oracle11gR2 database without using the RMAN utility. The database can continue to operate without the need to shut down while a successful ‘point in time’ backup is obtained.


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:


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