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.

OCR, Voting and OLR files

Over view:

Oracle Clusterware includes two important components that manage configuration and node membership: Oracle Cluster Registry (OCR), which also includes the local component Oracle Local Registry (OLR), and voting disks.

·       OCR manages Oracle Clusterware and Oracle RAC database configuration information

·       OLR resides on every node in the cluster and manages Oracle Clusterware configuration information for each particular node

·       Voting disks manage information about node membership. Each voting disk must be accessible by all nodes in the cluster for nodes to be members of the cluster

You can store OCR and voting disks on Oracle Automatic Storage Management (Oracle ASM), or a certified cluster file system.

Oracle Universal Installer for Oracle Clusterware 11g release 2 (11.2), does not support the use of raw or block devices. However, if you upgrade from a previous Oracle Clusterware release, then you can continue to use raw or block devices. Oracle recommends that you use Oracle ASM to store OCR and voting disks.Oracle recommends that you configure multiple voting disks during Oracle Clusterware installation to improve availability.

OCR:

OCR contains information about all Oracle resources in the cluster.

Oracle recommends that you configure:

·       At least three OCR locations, if OCR is configured on non-mirrored or non-redundant storage. Oracle strongly recommends that you mirror OCR if the underlying storage is not RAID. Mirroring can help prevent OCR from becoming a single point of failure.

·       At least two OCR locations if OCR is configured on an Oracle ASM disk group. You should configure OCR in two independent disk groups. Typically this is the work area and the recovery area.

·       At least two OCR locations if OCR is configured on mirrored hardware or third-party mirrored volumes.

Backing Up Oracle Cluster Registry:

Automatic backups:Oracle Clusterware automatically creates OCR backups every four hours, each full day and end of the week. Oracle Database always retains the last three backup copies of OCR.  You cannot customize the backup frequencies or the number of files that Oracle Database retains. This backups will be done by CRSD process.

Manual backups:  Use the following  command on a node to force Oracle Clusterware to perform a backup of OCR at any time.

ocrconfig -manualbackup

 The -manualbackup option is especially useful when you want to obtain a binary backup on demand, such as before you make changes to OCR.

NOTE: The OCRCONFIG executable is located in the $GRID_HOME/bin directory.  

OCRCONFIG utility:

Use the following command to display the OCR backup files.

ocrconfig -showbackup

To check manually/auto taken OCR backups saparatly use the flag manual/auto as follow.

ocrconfig –showbackup manual

ocrconfig –showbackup auto

The default location for generating backups on Linux or UNIX systems is $GRID_HOME/cdata/cluster_name, where cluster_name is the name of your cluster.

The OCRCONFIG utility creates a log file in $GRID_HOME/log/host_name/client

OCRCHECK Utility

The OCRCHECK utility displays the version of the OCR's block format, total space available and used space, OCRID, and the OCR locations that you have configured. OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that you have configured. It also returns an individual status for each file and a result for the overall OCR integrity check. 

You can only use OCRCHECK when the Oracle Cluster Ready Services stack is ONLINE on all nodes in the cluster.

# ocrcheck

==============================================

Voting Disk:

Voting disks manage information about node membership. Each voting disk must be accessible by all nodes in the cluster for nodes to be members of the cluster.

Storing Voting Disks on Oracle ASM

Oracle ASM manages voting disks differently from other files that it stores. If you choose to store your voting disks in Oracle ASM, then Oracle ASM stores all the voting disks for the cluster in the disk group you choose.

Once you configure voting disks on Oracle ASM, you can only make changes to the voting disks' configuration using the crsctl replace votedisk command. This is true even in cases where there are no working voting disks.

Backing Up Voting Disks

In Oracle Clusterware 11g release 2 (11.2), you no longer have to back up the voting disk. The voting disk data is automatically backed up in OCR as part of any configuration change and is automatically restored to any voting disk added.

Restoring Voting Disks

Run the following command as root from only one node to start the Oracle Clusterware stack in exclusive mode, which does not require voting files to be present or usable:

# crsctl start crs -excl

Run the crsctl query css votedisk  command to retrieve the list of voting files currently defined

crsctl query css votedisk

This list may be empty if all voting disks were corrupted, or may have entries that are marked as status 3 or OFF

If the voting disks are stored in Oracle ASM, then run the following command to migrate the voting disks to the Oracle ASM disk group you specify:

crsctl replace votedisk +asm_disk_group

If you did not store voting disks in Oracle ASM, then run the following command using the File Universal Identifier (FUID) obtained in the previous step:

$ crsctl delete css votedisk FUID

Add a voting disk, as follows:

$ crsctl add css votedisk path_to_voting_disk

Stop the Oracle Clusterware stack as root:

# crsctl stop crs

Restart the Oracle Clusterware stack in normal mode as root:

# crsctl start crs

======================================

OLR: 

In Oracle Clusterware 11g release 2 (11.2), each node in a cluster has a local registry for node-specific resources, called an Oracle Local Registry (OLR), that is installed and configured when Oracle Clusterware installs OCR. It contains manageability information about Oracle Clusterware, including dependencies between various services. Oracle High Availability Services uses this information. OLR is located on local storage on each node in a cluster.

Its default location is in the path $GRID_HOME/cdata/host_name.olr 

To Check OLR status on each node using following command.

# ocrcheck -local

If we need to see the contents of the OLR

# ocrdump -local -stdout

To backup OLR manually

# ocrconfig –local –manualbackup

TO see the contents of the backup OLR file

ocrdump -local -backupfile olr_backup_file_name

To change backup location

ocrconfig -local -backuploc new_olr_backup_path

To restore OLR follow the step

# crsctl stop crs

# ocrconfig -local -restore file_name

# ocrcheck -local

# crsctl start crs

$ cluvfy comp olr