oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

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”

click below button to copy the code. By - oracle tutorial - team

Copy Code

• We can specify second destination also

          LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 4: Start and mount the database.

SQL> STARTUP MOUNT

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 5: Give the following command

SQL> ALTER DATABASE ARCHIVELOG;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 6: Then type the following to confirm.

• STEP 7: Now open the database

SQL>alter database open;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 8: It is recommended that we take a full backup after we 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

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 3: Startup and mount the database.

SQL> STARTUP MOUNT;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 4: Give the following Commands

SQL> ALTER DATABASE NOARCHIVELOG;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 5: Shutdown the database and take full offline backup.

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

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

click below button to copy the code. By - oracle tutorial - team

Copy Code

• After Shutting down the database,copy all the data files, log files, control files, parameter file and password file to our backup destination.

Oracle backup and recovery

TIP:

• To identify the data files, Log files query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
• 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/

click below button to copy the code. By - oracle tutorial - team

Copy Code

• Be sure to remember the destination of each file. This will be useful when restoring from this backup. We can create text file and put the destinations of each file for future use. Now we 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 we the status of archiving.

$ sqlplus
              Enter User:/ as sysdba
              SQL> ARCHIVE LOG LIST

click below button to copy the code. By - oracle tutorial - team

Copy Code

• If the database is running in archive log mode then we can take online backups.

• Let us suppose we want to take online backup of "USERS" tablespace.
• We 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 ".

click below button to copy the code. By - oracle tutorial - team

Copy Code

• 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;

click below button to copy the code. By - oracle tutorial - team

Copy Code

RECOVERING THE DATABASE IF IT IS RUNNING IN NOARCHIVELOG MODE

Option 1: When we don’t have a backup.

• If We have lost one datafile and if We don't have any backup and if the datafile does not contain important objects then, We can drop the damaged datafile and open the database. We 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;

click below button to copy the code. By - oracle tutorial - team

Copy Code

Option 2: When we have the Backup

• If the database is running in Noarchivelog mode and if We have a full backup. Then there are two options for We.
• Either we can drop the damaged datafile, if it does not contain important information which we can afford to loose.
• Or we can restore from full backup. We will loose all the changes made to the database since last full backup.

Oracle backup and recovery

• 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

click below button to copy the code. By - oracle tutorial - team

Copy Code

• 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 we 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 we have lost all the mirrored control files and all the datafiles and log files are intact. Then we can recreate a control file.
• If we have already taken the backup of control file creation statement by giving this command." ALTER DATABASE BACKUP CONTROLFILE TO TRACE; " and if we have not added any tablespace since then, just create the control file by executing the saved statement
• But If we have added any new tablespace after generating create control file statement. Then we have to alter the script and include the filename and size of the file in script file.
• If we 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.

• STEP 4: Run the "CR.SQL" script file.
• STEP 5: Mount and Open the database.

                SQL> alter database mount;
                SQL> alter database open;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• If we do not have a backup of Control file creation statement. Then we have to manually give the CREATE CONTROL FILE statement. We have to write the file names and sizes of all the datafiles. We will lose any datafiles which we do not include.

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Recovering Database when the database is running in ARCHIVELOG Mode

Recovering from the lost of Damaged Datafile

• If we 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;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• $ sqlplus
• Enter User:/ as sysdba
• SQL> Startup mount;
• SQL> Set autorecovery on;
• SQL> alter database recover;

• STEP 4: Now open the database

                 SQL>alter database open;

click below button to copy the code. By - oracle tutorial - team

Copy Code

Recovering from the Lost Archived Files:

• If we have lost the archived files. Then Immediately shutdown the database and take a full offline backup.

Time Based Recovery (INCOMPLETE RECOVERY)

• STEP 1: Shutdown the database and take a full offline backup.
• STEP 2: Restore all the datafiles, log files 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 '2016:03:16:14:30:00' using backup control file;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 5: Open the database and reset the logs. Because We have performed a Incomplete Recovery, like this

SQL> alter database open reset logs;

click below button to copy the code. By - oracle tutorial - team

Copy Code

• STEP 6: After database is open. Export the table to a dump file using Export Utility.
• STEP 7: Open the database and Import the table.

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning