Hot backup and restore exercise

Hotbakup exersize:

-Check the database is in archive log list.

SQL> archive log list;             

 

Database log mode         No Archive Mode

Automatic archival        Disabled

Archive destination       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 107

Current log sequence       109

If database is not in archive log mode, put database in archive log mode.

SQL> shut immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     107

Next log sequence to archive   109

Current log sequence           109

Check the archive log location, datafiles location,redolog location,controlfiles location and save it in text file and keep it as backup so that it will be usefull in future in case of full restore . So that we can take backup of the archivelog files.

Notedown the all C/R/D files locations.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                        TYPE        VALUE

------------------------- ----------- ------------------------------

db_recovery_file_dest      string      /u01/app/oracle/flash_recover_area

db_recovery_file_dest_size           big integer 3852M

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/sample01.dbf

/u01/app/oracle/test111.dbf

/u01/app/oracle/test11.dbf

/u01/app/oracle/oradata/undodatafile1.dbf

-no need to take temp files location its optional.

-Put the database is in begin backup mode.

SQL> alter database begin backup; 

Database altered.

- Check the datafiles status whether they are in backup mode or not, This status must be 'Active' for all datafiles.

SQL> select file#, status from v$backup;

           FILE# STATUS
---------- ------------------
         1 ACTIVE
         2 ACTIVE
         3 ACTIVE
         4 ACTIVE
         5 ACTIVE
         6 ACTIVE

         7 ACTIVE

         8 ACTIVE

         9 ACTIVE

9 rows selected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

- Now copy of all datafiles to backup location

[oracle@localhost 2015_02_09]$ mkdir /u01/app/hotbkp

[oracle@localhost 2015_02_09]$ cd /u01/app/hotbkp

[oracle@localhost hotbkp]$ cp /u01/app/oracle/users01.dbf .

[oracle@localhost hotbkp]$ cp /u01/app/oracle/oradata/orcl/* .

[oracle@localhost hotbkp]$ cp /u01/app/oracle/oradata/sample01.dbf .

[oracle@localhost hotbkp]$ cp /u01/app/oracle/test111.dbf test11.dbf .

[oracle@localhost hotbkp]$ cp /u01/app/oracle/oradata/undodatafile1.dbf .

- End the  backup mode.

SQL> alter database end backup;

Database altered.

-Check the status of the datafiles

SQL> select file#, status from v$backup;

     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         4 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE

         7 NOT ACTIVE

         8 NOT ACTIVE

         9 NOT ACTIVE 

Switch the logfile, so that it generate archive for current log. Then take control file backup.

SQL> alter system switch logfile;

System altered.

SQL>  alter database backup controlfile to '/u01/app/hotbkp/backupcntrl.ctl';

Database altered.

Take archivelog file backup.

[oracle@localhost hotbkp]$ pwd

/u01/app/hotbkp

[oracle@localhost hotbkp]$ cp /u01/app/oracle/flash_recovery_area

/ORCL/archivelog/2015_02_09/* .

[oracle@localhost hotbkp]$ ls

backupcntrl.ctl  o1_mf_1_109_bfhs0lfy_.arc  o1_mf_1_112_bfhsrm00_.arc  redo02.log    sysaux01.dbf  test111.dbf        undotbs01.dbf

control01.ctl    o1_mf_1_110_bfhs0onh_.arc  o1_mf_1_113_bfhstzm4_.arc  sample01.dbf  system01.dbf  test11.dbf         users01.dbf

example01.dbf    o1_mf_1_111_bfhs0tz0_.arc  redo01.log                 siva.log      temp01.dbf    undodatafile1.dbf

Now corrupt the database by deleting some dbfiles physically

[oracle@localhost hotbkp]$ cd /u01/app/oracle/oradata/

[oracle@localhost oradata]$ ls

orcl  redo03.log  sample01.dbf  undodatafile1.dbf

[oracle@localhost oradata]$ rm redo03.log sample01.dbf undodatafile1.dbf

- Now connect to database and try to shut down.

SQL> shut immediate

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/sample01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

- since shut down is not possible. go to shut abort.

SQL> shut abort

ORACLE instance shut down.

SQL> exit

now cleanup all C/R/D files

[oracle@localhost oradata]$ cd /u01/app/oracle/oradata/orcl/

[oracle@localhost orcl]$ ls

control01.ctl  example01.dbf  redo01.log  redo02.log  siva.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf

[oracle@localhost orcl]$ rm *

[oracle@localhost orcl]$ cd /u01/app/oracle/

[oracle@localhost oracle]$ ls

admin        checkpoints  flash_recovery_area  oradiag_oracle  siva.log      test111.dbf  trace.txt

cfgtoollogs  diag         oradata              product         siva_tab.dmp  test11.dbf   users01.dbf

[oracle@localhost oracle]$ rm users01.dbf test111.dbf test11.dbf

[oracle@localhost oracle]$ rm siva.log

-Restore all controlfiles and datafiles from backup location except redo log files.

[oracle@localhost oracle]$ cd /u01/app/hotbkp/

[oracle@localhost hotbkp]$ cp undotbs01.dbf system01.dbf sysaux01.dbf example01.dbf /u01/app/oracle/oradata/orcl/

[oracle@localhost hotbkp]$ cp users01.dbf test111.dbf test11.dbf /u01/app/oracle/

[oracle@localhost hotbkp]$ cp sample01.dbf undodatafile1.dbf /u01/app/oracle/oradata

[oracle@localhost hotbkp]$ cp backupcntrl.ctl /u01/app/oracle/oradata/orcl/control01.ctl

[oracle@localhost hotbkp]$ cp backupcntrl.ctl /u01/app/oracle/flash_recovery_area/orcl/control02.ctl

-if you loose archive log also, then you need to restore the archive log files also.

-now database needs recovery from archivelogs

[oracle@localhost hotbkp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 9 04:39:05 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  313860096 bytes

Fixed Size                  1336232 bytes

Variable Size             268438616 bytes

Database Buffers           37748736 bytes

Redo Buffers                6336512 bytes

Database mounted.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 2329427 generated at 02/09/2015 04:03:41 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_112_bfhsr

m00_.arc

ORA-00280: change 2329427 for thread 1 is in sequence #112

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 2329904 generated at 02/09/2015 04:15:38 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_113_bfhst

zm4_.arc

ORA-00280: change 2329904 for thread 1 is in sequence #113

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_112_bfhs

rm00_.arc' no longer needed for this recovery

ORA-00279: change 2330361 generated at 02/09/2015 04:16:55 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_114_%u_.a

rc

ORA-00280: change 2330361 for thread 1 is in sequence #114

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_113_bfhs

tzm4_.arc' no longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_09/o1_mf_1_114_%u_.

arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database open resetlogs;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>

-full database restore from hot backup is done.