Pages

Wednesday, July 30, 2014

Unable to Drop ASM DiskGroup Giving ORA-15027

SCENARIO:

When I tried to remove all the contents of a database using ASM I encountered an error from acmca. Even tried with sqlplus and ended up with the same below error.

ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATAGRP1" precludes its dismount

REASON:

  • ASM parameter file was residing in the Diskgroup DATAGRP1. This was the reason behind.

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;
NAME             TYPE           TOTAL_MB    FREE_MB  REQUIRED_MIRROR_FREE_MB    USABLE_FILE_MB
——————— —————- ———-——— ————–   ————————————————   —————————
DATAGRP1      EXTERN      7168000      7167858                       0                             7167858
FRAGRP1        EXTERN      512000        511222                         0                             511222

SQL> column HEADER_STATUS format a20
SQL> set pages 100
SQL> col PATH format a25
SQL> SELECT name, header_status, path FROM V$ASM_DISK where name like '%DATA%';
NAME             HEADER_STATUS       PATH
——————  ——————————– ————————-
DATA_0001        MEMBER              /dev/rdisk/disk01
DATA_0002        MEMBER              /dev/rdisk/disk02
DATA_0003        MEMBER              /dev/rdisk/disk03
DATA_0004        MEMBER              /dev/rdisk/disk04
DATA_0005        MEMBER              /dev/rdisk/disk05
5 rows selected.

  • I tried to drop the diskgroup DATAGRP1 but I couldn't drop it.


SQL> DROP DISKGROUP datagrp1 INCLUDING CONTENTS;
DROP DISKGROUP datagrp INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATAGRP1" precludes its dismount
SQL>
SQL> DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS;
DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15230: diskgroup 'DATAGRP1' does not require the FORCE option

  • I created a pfile from spfile and restarted the ASM Instance using this created pfile "init_orcldb.ora".


SQL> create pfile='/usr2/grid/init_asm.ora' from spfile;
File created.
SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown

  • When I started the ASM Instance the diskgroup did not get mounted.

SQL> startup pfile='/usr2/grid/init_asm.ora';
ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2169104 bytes
Variable Size             256595696 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

SQL>  DROP DISKGROUP datagrp1 INCLUDING CONTENTS;
DROP DISKGROUP datagrp1 INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATAGRP1" does not exist or is not mounted

  • Finally used FORCE option to drop the diskgroup and it's been dropped successfully.


SQL> DROP DISKGROUP datagrp1 FORCE INCLUDING CONTENTS;
Diskgroup dropped.
SQL>

Oracle 11g RMAN VIRTUAL PRIVATE CATALOG

Earlier before 11g, we use to have N number of databases registered with Only one catalog database. So the administration of the whole RECOVERY CATALOG becomes harder to maintain. 

To overcome this Oracle 11g has bought in a new feature called VIRTUAL PRIVATE CATALOG.

One of the New Features in Oracle 11g is the RMAN VIRTUAL PRIVATE CATALOG. 

Using this feature we can grant restricted access to the RMAN CATALOG to some users, so that they can only access a limited number of databases which are registered with the RMAN CATALOG.

In the below scenario, there are two databases namely GGDB1 and GGDB2 registered with one catalog owned by the user RMAN. 

SQL> select * from rman.rc_database;

 DB_KEY    DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
-------------    -----------------   ---------------   --------- ---------------------------------    ------------------
1          2         4150396312 GGDB2        995548    12-JUN-14
     417      418         3580803551 GGDB1        995548    12-JUN-14

Now we will restrict the access to the GGDB1 database to a single user RMAN_GGDB1. In order to proceed with further, firstly we need to create a user called RMAN_GGDB1.


SQL> create user rman_ggdb1 identified by rman_ggdb1
  2  default tablespace TBSRMAN
  3  temporary tablespace temp
  4  quota unlimited on tbsrman 
  5  /

User created.

SQL> grant recovery_catalog_owner to rman_ggdb1;

Grant succeeded.

Connect to catalog as catalog owner and grant permissions on the one database "GGDB1".

[oracle@ogg2 ~]$ rman catalog rman/rman@ggdb2 target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:17:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> grant catalog for database GGDB1 to rman_ggdb1;

Grant succeeded.

Now connect as the RMAN_GGDB1 user and create the VIRTUAL PRIVATE CATALOG.

[oracle@ogg2 ~]$ rman target / catalog rman_ggdb1/rman_ggdb1@GGDB2

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:18:24 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

Connect as a Original RMAN user and issue the command LIST INCARNATION.

[oracle@ogg2 ~]$ rman catalog rman/rman@ggdb2 target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:30:12 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key   DB Name   DB ID            STATUS     Reset SCN    Reset Time
-------       -----------  -------------  --------------     -------------    --------------    -----------------
417          434           GGDB1      3580803551    PARENT            1               17-SEP-11
417          418           GGDB1      3580803551    CURRENT     995548         12-JUN-14
    1            18           GGDB2      4150396312    PARENT            1               17-SEP-11
    1              2           GGDB2      4150396312    CURRENT     995548         12-JUN-14

Connect as the rman_ggdb1 user and check the command LIST INCARNATION.

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 30 17:30:36 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB2 (DBID=4150396312)
connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key   DB Name   DB ID            STATUS     Reset SCN    Reset Time
-------       -----------  -------------  --------------     -------------    --------------    -----------------
417          434           GGDB1      3580803551    PARENT            1               17-SEP-11
417          418           GGDB1      3580803551    CURRENT     995548         12-JUN-14

From the above output we can clearly see that only one database is registered in this catalog when we connect as RMAN_GGDB1.











Migrate database from one RMAN catalog to another RMAN catalog

IMPORT CATALOG is a new feature of RMAN in Oracle 11g. We can move or merge schemas of different RECOVERY CATALOG of different databases into a centralized repository.

In a nut shell It is nothing but importing a CATALOG from one database to another, or in other words, "moving" or "migrating" CATALOG.


SOURCE DATABASE - GGDB1 

TARGET DATABASE - GGDB2

ORACLE VERSION - 11.2.0.3.0

OS VERSION - Linux 6.0 X86_64

The below scenario describes the steps to move a CATALOG from GGDB1 (Source) to GGDB2 (Target).

In GGDB1 - The catalog is registered.



In GGDB2 - Check the CATALOG for the registered database.



In GGDB2 - Connect to the destination database that will receive the new CATALOG data.



Issue the IMPORT CATALOG command specifying the owner of the source catalog and Check the registered database in the catalog in GGDB2 (Target database).


We can see GGDB1 is also registered to this CATALOG.





In GGDB1 - Check the source catalog (GGDB1) for the registered database.,



On the Source Catalog, the database will be automatically Unregistered after the IMPORT CATALOG.

If you need to retain the catalog on the source side even after the import then a keyword should be added to the IMPORT CATALOG command.

Connect to the RMAN prompt and issue the IMPORT CATALOG command with the keyword NO UNREGISTER.,



Check whether the source database (GGDB1) is registered to the New target catalog database (GGDB2).




Now check on the Source (GGDB1) side whether the imported database exists. 



You can clearly see that the database is not Unregistered from the Source side.



Thursday, July 3, 2014

Restoring With RMAN and Using The DATABASE RECOVERY ADVISOR (DRA)


The Database Recovery Advisor (DRA) is a new feature introduced in Oracle 11g to help us to diagnose any persistent failure with the database.
The main advantage of this feature is, it helps us to fix the issue taking away some of the stress always associated with this type of operation.

Database Recovery Advisor is useful only if we use RMAN for our backups.

The main benefits of this feature DRA is below.,

1. It simplifies the diagnosis, analysis and recovery steps we perform.
2. It allows us to easily detect and check the failures happened. 
3. It reduces the recovery time.
4. Reduces Human Errors.
5. It provides us with suggestions, steps and advices to repair the failure occur.
6. We can generate a repair script and recover the failure using the generated script on requirement.

Database Recovery Advisor can be used via OEM or through RMAN command line interface. The below are some of the commands used in RMAN command line interface.

1. LIST FAILURE : List all failures that the database encountered.

2. ADVISE FAILURE : Analyzes all the backups and provide a recovery script to repair the failure occur.

3. CHANGE FAILURE : This command changes the priority or status of the failures.

4. REPAIR FAILURE : Repairs the failure occured with the automatically generated script (which is previously generated by the ADVISE FAILURE command).

As I mentioned earlier, Database Recovery Advisor is useful only if we use RMAN for our backups. I have taken full backups before working on the scenarios.

SCENARIO - Recovery of DATAFILE:

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------



As I have taken the backups before, the RMAN requires no further backup is required.
I have taken the USERS tablespace offline and removed it's datafile as below.,

RMAN> sql 'alter tablespace users offline';

sql statement: alter tablespace users offline
starting full resync of recovery catalog
full resync complete

[oracle@localhost ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     10236     7784                0            7784              0             N  DATA_GRP/
MOUNTED  EXTERN  N         512   4096  1048576     10236     9855                0            9855              0             N  FRA_GRP/

ASMCMD> cd DATA_GRP/PRODDB/DATAFILE

ASMCMD> ls

EXAMPLE.265.848660933
SYSAUX.257.848660835
SYSTEM.256.848660835
TBSRMAN.267.848663633
UNDOTBS1.258.848660835
USERS.259.848660835

ASMCMD> rm USERS.259.848660835

ASMCMD> ls

EXAMPLE.265.848660933
SYSAUX.257.848660835
SYSTEM.256.848660835
TBSRMAN.267.848663633
UNDOTBS1.258.848660835

From the above list we can clearly see that the USERS datafile has been removed.

Now we are going to recover this datafile using the DATABASE RECOVERY ADVISOR.

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
594        HIGH     OPEN      03-JUL-14     Tablespace 4: 'USERS' is offline
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
582        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are offline

We can see that the LIST FAILURE command lists all the failures detected by the DRA. We can see the details of the failure by issuing the command LIST FAILURE <failure_id> DETAIL as below,

RMAN> list failure 588 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 588
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  591        HIGH     OPEN      03-JUL-14     Datafile 4: '+DATA_GRP/proddb/datafile/users.259.848660835' is missing
    Impact: Some objects in tablespace USERS might be unavailable



Now we will issue the ADVISE FAILURE command, so that the DRA suggests / advices to fix all the current failures.

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
588        HIGH     OPEN      03-JUL-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 588
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  591        HIGH     OPEN      03-JUL-14     Datafile 4: '+DATA_GRP/proddb/datafile/users.259.848660835' is missing
    Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file +DATA_GRP/proddb/datafile/users.259.848660835 was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm


From the above details, we can clearly see that the ADVISE FAILURE automatically command generated a repair script and stored it in the location /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

If needed we can use the command REPAIR FAILURE PREVIEW to see the details of the script generated by the ADVISE FAILURE command before we order the DRA to repair the failures.

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;


Now issue the REPAIR FAILURE command to repair the failures.

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/proddb/proddb/hm/reco_8097687.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 03-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA_GRP/proddb/datafile/users.259.848660835
channel ORA_DISK_1: reading from backup piece /rman/bkup_0kpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0kpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-JUL-14

Starting recover at 03-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /arch/proddb/1_14_848660904.dbf
archived log for thread 1 with sequence 15 is already on disk as file /arch/proddb/1_15_848660904.dbf
archived log for thread 1 with sequence 16 is already on disk as file /arch/proddb/1_16_848660904.dbf
archived log file name=/arch/proddb/1_14_848660904.dbf thread=1 sequence=14
media recovery complete, elapsed time: 00:00:01
Finished recover at 03-JUL-14
repair failure complete
starting full resync of recovery catalog
full resync complete


From the above, we can clearly see that the USERS datafile is recovered.

ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    EXAMPLE.265.848660933
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    SYSAUX.257.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    SYSTEM.256.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    TBSRMAN.267.848663633
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    UNDOTBS1.258.848660835
DATAFILE  UNPROT  COARSE   JUL 03 12:00:00  Y    USERS.259.851949575
ASMCMD>

RMAN> sql 'alter tablespace USERS online';

sql statement: alter tablespace USERS online
starting full resync of recovery catalog
full resync complete



SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME  STATUS
------------------------------------------------------- -------------------- ---------
+DATA_GRP/proddb/datafile/users.259.851949575 USERS     AVAILABLE
+DATA_GRP/proddb/datafile/undotbs1.258.848660835 UNDOTBS1     AVAILABLE
+DATA_GRP/proddb/datafile/sysaux.257.848660835 SYSAUX     AVAILABLE
+DATA_GRP/proddb/datafile/system.256.848660835 SYSTEM     AVAILABLE
+DATA_GRP/proddb/datafile/example.265.848660933 EXAMPLE     AVAILABLE
+DATA_GRP/proddb/datafile/tbsrman.267.848663633 TBSRMAN     AVAILABLE

6 rows selected.


An important thing to be considered on DRA is, if we are not using the controlfile autobackup option when doing your backups and the recovery of the controlfile is required, the DRA will not be able to do a full automatic recovery process and which leads to a combination of automatic and manual recovery process to recover the failure during this situation.

Monday, June 30, 2014

RMAN RESTORE VALIDATE - A Proactive Health Check

RMAN RESTORE VALIDATE is the new feature introduced in Oracle 11g to check and verify the integrity of the backups which are stored in the Tape or Disk. It is used to detect any corrupted block in the database. It is used to check the corruption at database, tablespace, datafile or datafile block level.

The VALIDATE command for backup and restore is very useful to check and confirm that the backup and the restoration is possible and it is a valid one without actually backing up or restoring datafiles.

Apart from the database, you can also validate the below files,

BACKUPSET
CONTROLFILECOPY
CURRENT CONTROLFILE
DB_RECOVERY_FILE_DEST
SPFILE

RESTORE DATABASE VALIDATE:

This command will check for the last level 0 or FULL backup taken in Disk or Tape.

We can issue the command to validate the whole database using the command RESTORE DATABASE VALIDATE. But the point in concern is, it validates only the datafiles backup and not either the controlfile or archivelog or spfile.
For this we need to issue additional commands like,

RESTORE CONTROLFILE VALIDATE;
RESTORE ARCHIVELOG VALIDATE;
RESTORE SPFILE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE:

This command will check for all the archivelog files catalogued based on the retention policy we set/configure in the RMAN. Keeping this in mind, we should first check for the existence
of the archivelog files in the Disk or Tape which have been generated since the last FULL or LEVEL 0 backup. Else it will end up with error like RMAN-03002, RMAN-06026 and RMAN-06025.


VALIDATE CHECK LOGICAL:

By default RMAN validate command checks only for the Physical Corruption and not the Logical Corruption. For this we need to issue the command
VALIDATE CHECK LOGICAL;

The below example is to just validate the database and not restore.

RMAN> validate database;

Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_GRP/proddb/datafile/system.256.848660835
input datafile file number=00002 name=+DATA_GRP/proddb/datafile/sysaux.257.848660835
input datafile file number=00006 name=+DATA_GRP/proddb/datafile/tbsrman.267.848663633
input datafile file number=00005 name=+DATA_GRP/proddb/datafile/example.265.848660933
input datafile file number=00003 name=+DATA_GRP/proddb/datafile/undotbs1.258.848660835
input datafile file number=00004 name=+DATA_GRP/proddb/datafile/users.259.848660835
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14959        92200           1160396
  File Name: +DATA_GRP/proddb/datafile/system.256.848660835
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              61210        
  Index      0              12900        
  Other      0              3091          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              23037        70407           1160394
  File Name: +DATA_GRP/proddb/datafile/sysaux.257.848660835
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              11483        
  Index      0              8380          
  Other      0              27500        

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            8960            1160427
  File Name: +DATA_GRP/proddb/datafile/undotbs1.258.848660835
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0            
  Index      0              0            
  Other      0              8959          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              18           667             1130722
  File Name: +DATA_GRP/proddb/datafile/users.259.848660835
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              91            
  Index      0              39            
  Other      0              492          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              33684        44240           1130675
  File Name: +DATA_GRP/proddb/datafile/example.265.848660933
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6604          
  Index      0              1148          
  Other      0              2804          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              63357        64000           1160427
  File Name: +DATA_GRP/proddb/datafile/tbsrman.267.848663633
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              163          
  Index      0              85            
  Other      0              395          

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2            
Control File OK     0              594          
Finished validate at 30-JUN-14

RMAN>

From the above result, this is like an health check reports listing all the datafiles. Then it gives us the detail about the total number of blocks, number of empty blocks and the corrupt blocks per datafile.
It also give the details of the objects inside the datafiles and finally it completes by checking the status of the spfile and controlfile.


As said earlier the below example is to check and confirm that the backup and the restoration is possible and it is a valid one without actually backing up or restoring datafiles.

RMAN> restore database validate;

Starting restore at 30-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/bkup_0kpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0kpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/bkup_0jpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0jpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
Finished restore at 30-JUN-14


RMAN> restore controlfile validate;

Starting restore at 30-JUN-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/controlfile/ctl_c-588708646-20140625-01
channel ORA_DISK_1: piece handle=/rman/controlfile/ctl_c-588708646-20140625-01 tag=TAG20140625T210428
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 30-JUN-14

VALIDATING a Particular TABLESPACE:

RMAN> validate tablespace system;

Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_GRP/proddb/datafile/system.256.848660835
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14959        92200           1160705
  File Name: +DATA_GRP/proddb/datafile/system.256.848660835
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              61210        
  Index      0              12900        
  Other      0              3091          

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2            
Control File OK     0              594          
Finished validate at 30-JUN-14


VALIDATE ARCHIVELOG ALL:

RMAN> validate archivelog all;

Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=10 RECID=7 STAMP=851200648
input archived log thread=1 sequence=11 RECID=8 STAMP=851201836
input archived log thread=1 sequence=12 RECID=9 STAMP=851201936
input archived log thread=1 sequence=13 RECID=10 STAMP=851202206
input archived log thread=1 sequence=14 RECID=11 STAMP=851202266
input archived log thread=1 sequence=15 RECID=12 STAMP=851205645
input archived log thread=1 sequence=16 RECID=13 STAMP=851599672
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    10      OK     0              11946           /arch/proddb/1_10_848660904.dbf
1    11      OK     0              6023            /arch/proddb/1_11_848660904.dbf
1    12      OK     0              448             /arch/proddb/1_12_848660904.dbf
1    13      OK     0              2086            /arch/proddb/1_13_848660904.dbf
1    14      OK     0              411             /arch/proddb/1_14_848660904.dbf
1    15      OK     0              77739           /arch/proddb/1_15_848660904.dbf
1    16      OK     0              41867           /arch/proddb/1_16_848660904.dbf
Finished validate at 30-JUN-14


RMAN> restore archivelog all validate;

Starting restore at 30-JUN-14
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log /arch/proddb/1_10_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_11_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_12_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_13_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_14_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_15_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_16_848660904.dbf
Finished restore at 30-JUN-14


As mentioned earlier, while validating the archivelog, we should first check for the existence of the archivelog files in the Disk or Tape which have been generated since the last FULL or LEVEL 0 backup.
Else it will end up with error like RMAN-03002, RMAN-06026 and RMAN-06025.

Now I moved or renamed one of the archivelog file as below.,

[oracle@localhost proddb]$ ls -l
total 70276
-rw-r----- 1 oracle oinstall  6116864 Jun 25 20:37 1_10_848660904.dbf
-rw-r----- 1 oracle oinstall  3084288 Jun 25 20:57 1_11_848660904.dbf
-rw-r----- 1 oracle oinstall   229888 Jun 25 20:58 1_12_848660904.dbf
-rw-r----- 1 oracle oinstall  1068544 Jun 25 21:03 1_13_848660904.dbf
-rw-r----- 1 oracle oinstall   210944 Jun 25 21:04 1_14_848660904.dbf
-rw-r----- 1 oracle oinstall 39802880 Jun 25 22:00 1_15_848660904.dbf
-rw-r----- 1 oracle oinstall 21436416 Jun 30 11:27 1_16_848660904.dbf
[oracle@localhost proddb]$ 
[oracle@localhost proddb]$ 
[oracle@localhost proddb]$ mv 1_16_848660904.dbf 1_16_848660904.dbf_old
[oracle@localhost proddb]$ ls -l
total 70276
-rw-r----- 1 oracle oinstall  6116864 Jun 25 20:37 1_10_848660904.dbf
-rw-r----- 1 oracle oinstall  3084288 Jun 25 20:57 1_11_848660904.dbf
-rw-r----- 1 oracle oinstall   229888 Jun 25 20:58 1_12_848660904.dbf
-rw-r----- 1 oracle oinstall  1068544 Jun 25 21:03 1_13_848660904.dbf
-rw-r----- 1 oracle oinstall   210944 Jun 25 21:04 1_14_848660904.dbf
-rw-r----- 1 oracle oinstall 39802880 Jun 25 22:00 1_15_848660904.dbf
-rw-r----- 1 oracle oinstall 21436416 Jun 30 11:27 1_16_848660904.dbf_old
[oracle@localhost proddb]$ 

RMAN> restore archivelog all validate;

Starting restore at 30-JUN-14
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/30/2014 12:00:20
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 1134214 found to restore


RMAN> validate archivelog all;

Starting validate at 30-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 06/30/2014 12:00:47
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /arch/proddb/1_16_848660904.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


From the above example we can clearly see the failure of the validation of the archivelog.

This RESTORE VALIDATE feature can be safely run at anytime when needed. Because it does not affects an open database even if you simply forget to enter the keyword VALIDATE like below.,

RMAN > restore database;

An error similar to the one below will be received if you try to overwrite files of an open database.

RMAN-03002: failure of restore command at 06/30/2014 12:04:53
ORA-19870: error while restoring backup piece /rman/bkup_0kpbok52_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1

RMAN will never restore if the database is in the OPEN State.





Tuesday, June 17, 2014

RMAN backups not storing configure location


RMAN Configured parameters does not get reflected or get changed


After configuration of RMAN parameters, it does not get reflected.

RMAN> show all;


RMAN configuration parameters for database with db_unique_name PRODDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0.3/db_1/dbs/snapcf_proddb.f'; # default


Configuring the RMAN Parameters below.,

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';
new RMAN configuration parameters are successfully stored


RMAN> configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';
new RMAN configuration parameters are successfully stored

RMAN> configure channel device type disk format '/rman/bkup_%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/rman/bkup_%U';
new RMAN configuration parameters are successfully stored

After Configuration of the parameters checking the rman configuration by issuing the below command.,


RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRODDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0.3/db_1/dbs/snapcf_proddb.f'; # default


From the above output show all command we can clearly see that there was no changes reflected in the configuration.

Solution:


Login to the sqlprompt and login as sysdba user.


[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 17 11:51:20 2014

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

SQL> conn / as sysdba
Connected.

Issue the below command, this resets all your parameters configured.

execute dbms_backup_restore.resetConfig;

SQL> execute dbms_backup_restore.resetConfig;

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


This will reset all your CONFGURE parameters, so be sure to capture all your present CONFIGURE parameters prior to executing this command.

Now login to RMAN prompt and configure the RMAN parameters and check whether the configuration is reflected or not.

[oracle@localhost ~]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jun 17 11:51:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect catalog rman/rman@proddb

connected to recovery catalog database

RMAN> connect target /

connected to target database: PRODDB (DBID=588708646)

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name PRODDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0.3/db_1/dbs/snapcf_proddb.f'; # default


Now configure the RMAN parameters and check whether it has got applied or reflected.

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/rman/bkup_%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/rman/bkup_%U';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRODDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/controlfile/ctl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/rman/bkup_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0.3/db_1/dbs/snapcf_proddb.f'; # default


The set parameters got reflected :)