Oracle Knowledge Sharing Blog - Veera (Oracle Certified Professional)

Unable to Drop ASM DiskGroup Giving ORA-15027

2014-07-30T19:20:00.002+05:30

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

2014-07-30T17:42:00.001+05:30

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

2014-07-30T16:33:00.001+05:30

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.

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

2014-07-03T12:56:00.001+05:30

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.

RMAN RESTORE VALIDATE - A Proactive Health Check

2014-06-30T12:22:00.000+05:30

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.

RMAN backups not storing configure location

2014-06-17T17:20:00.002+05:30

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 :)

How to resolve Could not validate ASMSNMP password due to following error- "ORA-01031: insufficient privileges"

2014-05-24T08:35:00.002+05:30

Could not validate ASMSNMP password due to following error- "ORA-01031: insufficient privileges"

SOLUTION:

1. Go to Grid Home

[oracle@rac1 ~]$ cd $ORACLE_HOME

[oracle@rac1 grid]$ cd bin

[oracle@rac1 bin]$ pwd

/u01/app/11.2.0.3/grid/bin

2. Connect to ASM instance and check whether the user ASMSNMP exists.

[oracle@rac1 bin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 24 08:09:49 2014

SQL> conn / as sysasm

Connected.

SQL> grant sysdba to asmsnmp;

grant sysdba to asmsnmp

ERROR at line 1:

ORA-01918: user 'ASMSNMP' does not exist

3. Create the ASMSNMP user and grant the SYSDBA privilege to it.

SQL> create user asmsnmp identified by oracle;

User created.

SQL> grant sysdba to asmsnmp;

Grant succeeded.

SQL>

How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database

2014-03-10T10:37:00.000+05:30

Resync the standby database using an incremental RMAN backup from the primary database.

Advantages:

-----------

* This does not require a full rebuild of the standby database and can be a lot more efficient.

* Time saving.

Scenario:

---------

Primary Database - pdb

Standby Database - sdb

One of the archivelog files is missing and there is no backup of that archivelog. Primary database and Standby database are not in SYNC. There is an archival gap.

Resyncing the standby data using RMAN incremental backup taken from the primary database.

On Primary Database:

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

connect as sys user and check the archive log list

SQL> conn sys/oracle@sdb as sysdba

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

sdb

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 23

Next log sequence to archive 25

Current log sequence 25

Issue alter system switch logfile on primary database

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 24

Next log sequence to archive 26

Current log sequence 26

ON Standby database:

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

SQL> conn sys/oracle@sdb as sysdba

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

sdb

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 24

Next log sequence to archive 0

Current log sequence 26

ON Primary Database:

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

Create a user, connect to that user, create table and insert records in it.

SQL> conn veera/veera@pdb

Connected.

SQL>

SQL> select * from tab;

no rows selected

SQL>

SQL> create table emp (empid number(4), emp_name varchar2(12));

Table created.

SQL> insert into emp values(&empid,'&emp_name');

Enter value for empid: 1111

Enter value for emp_name: veera

old 1: insert into emp values(&empid,'&emp_name')

new 1: insert into emp values(1111,'veera')

1 row created.

SQL> /

Enter value for empid: 1309

Enter value for emp_name: suresh

old 1: insert into emp values(&empid,'&emp_name')

new 1: insert into emp values(1309,'suresh')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

You can see that we connected to a user "veera", created a table "emp" and inserted rows in to it.

ON Standby Database:

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

connect to the standby database and check whether the table has been replicated or not.

SQL> conn sys/oracle@sdb as sysdba

Connected.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 25

Next log sequence to archive 0

Current log sequence 27

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

From the above we can see that the data has been replicated to the standby database from the primary database.

Now cancel the media recovery in Standby database.

alter database recover managed standby database cancel;

On Primary Database:

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

Insert in to the same table two more rows.

SQL> insert into emp values(&empid,'&emp_name');

Enter value for empid: 1234

Enter value for emp_name: mannoj

old 1: insert into emp values(&empid,'&emp_name')

new 1: insert into emp values(1234,'mannoj')

1 row created.

SQL> insert into emp values(&empid,'&emp_name');

Enter value for empid: 9876

Enter value for emp_name: thiyagu

old 1: insert into emp values(&empid,'&emp_name')

new 1: insert into emp values(9876,'thiyagu')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

1234 mannoj

9876 thiyagu

Now connect as a sys user and switch the logfile.

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 26

Next log sequence to archive 28

Current log sequence 28

Now remove this archivelog file physically from the production database.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 26

Next log sequence to archive 28

Current log sequence 28

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 27

Next log sequence to archive 29

Current log sequence 29

ON Standby Database:

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

The archivelog file with sequence# 27 will be moved to standby database. Since the media recovery process has been canceled the archivelog will not be applied to the standby database.

Now remove that archivelog with sequence# 27 physically from standby server also.

Once after deleting the archivelog file from both production and standby database servers, enable the media recovery process in standby database.

alter database recover managed standby database disconnect from session;

SQL> archive log list

Database log mode Archive Mo

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 26

Next log sequence to archive 0

Current log sequence 28

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

SQL> archive log list

Database log mode Archive Mo

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 26

Next log sequence to archive 0

Current log sequence 28

SQL>

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list

Database log mode Archive Mo

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 27

Next log sequence to archive 0

Current log sequence 29

SQL>

SQL> archive log list

Database log mode Archive Mo

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 27

Next log sequence to archive 0

Current log sequence 29

SQL>

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

2 YES YES

3 YES YES

4 YES YES

5 YES YES

6 YES YES

7 YES YES

8 YES YES

9 YES YES

10 YES YES

11 YES YES

12 YES YES

SEQUENCE# ARC APPLIED

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

13 YES YES

14 YES YES

15 YES YES

16 YES YES

17 YES YES

18 YES YES

19 YES YES

20 YES YES

21 YES YES

22 YES YES

23 YES YES

SEQUENCE# ARC APPLIED

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

24 YES YES

25 YES YES

26 YES YES

27 YES NO

28 YES NO

27 rows selected.

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

From the above we can clearly see that there is an archival gap sequence# 27 has not been applied to the standby database. But the archivelogs are moving to the standby database location but not applying.

ON Primary Database:

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

SQL> insert into veera.emp values(&empid,'&emp_name');

Enter value for empid: 2345

Enter value for emp_name: vivek

old 1: insert into veera.emp values(&empid,'&emp_name')

new 1: insert into veera.emp values(2345,'vivek')

1 row created.

SQL> insert into veera.emp values(&empid,'&emp_name');

Enter value for empid: 3456

Enter value for emp_name: shek

old 1: insert into veera.emp values(&empid,'&emp_name')

new 1: insert into veera.emp values(3456,'shek')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

1234 mannoj

9876 thiyagu

2345 vivek

3456 shek

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

1234 mannoj

9876 thiyagu

2345 vivek

3456 shek

6 rows selected.

On Standby Database:

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

SQL> select * from veera.emp;

EMPID EMP_NAME

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

1111 veera

1309 suresh

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

2 YES YES

3 YES YES

4 YES YES

5 YES YES

6 YES YES

7 YES YES

8 YES YES

9 YES YES

10 YES YES

11 YES YES

12 YES YES

SEQUENCE# ARC APPLIED

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

13 YES YES

14 YES YES

15 YES YES

16 YES YES

17 YES YES

18 YES YES

19 YES YES

20 YES YES

21 YES YES

22 YES YES

23 YES YES

SEQUENCE# ARC APPLIED

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

24 YES YES

25 YES YES

26 YES YES

27 YES NO

28 YES NO

29 YES NO

28 rows selected.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 28

Next log sequence to archive 0

Current log sequence 30

Overall Scenario:

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

Archivelog with sequence# 27 is missing from primary and also from standby database. Due to this there is an archival gap between primary database and standby database. The synchronization between primary database and standby database has been lost.

We have to recover / resync the standby database with the primary database.

The steps to resync the standby database using an incremental RMAN backup from the primary database are as follows.

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

1. Get the current scn of the standby database.,

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1053647

2. Connect to the primary database as the RMAN target and create an incremental backup from the current SCN (for a standby lagging far behind the primary):

primary:/vol1 />rman catalog rman/rman@pdb target sys/oracle@pdb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 13 15:54:00 2013

connected to target database: PDB (DBID=3232692579)

connected to recovery catalog database

RMAN> backup incremental from scn 1053647 database;

Starting backup at 13-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

backup will be obsolete on date 20-MAR-13

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/VOL1/PDB/SYSTEM01.DBF

input datafile file number=00002 name=/VOL1/PDB/SYSAUX01.DBF

input datafile file number=00005 name=/VOL1/PDB/TBSRMAN.DBF

input datafile file number=00003 name=/VOL1/PDB/UNDOTBS01.DBF

input datafile file number=00004 name=/VOL1/PDB/USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 13-MAR-13

channel ORA_DISK_1: finished piece 1 at 13-MAR-13

piece handle=/RMAN/DBF_BKUP/DBF_09O4EBAE_1_1 tag=TAG20130313T155700 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

using channel ORA_DISK_1

backup will be obsolete on date 20-MAR-13

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 13-MAR-13

channel ORA_DISK_1: finished piece 1 at 13-MAR-13

piece handle=/RMAN/DBF_BKUP/DBF_0AO4EBBJ_1_1 tag=TAG20130313T155700 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 13-MAR-13

RMAN>

3. Transfer all the backup pieces created on the primary to the standby.

4. Create a standby controlfile on the primary and ship to the standby.

SQL> alter database create standby controlfile as '/oracle/CONTROL01.CTL';

Database altered.

Now on Standby database:

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

5. Recreate the standby controlfile on the standby:

a. shutdown abort the database.,

SQL> shut abort

ORACLE instance shut down.

b. Start the database in nomount.,

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1686925312 bytes

Fixed Size 2255824 bytes

Variable Size 989856816 bytes

Database Buffers 687865856 bytes

Redo Buffers 6946816 bytes

SQL>

c. connect to the rman prompt and connect to the target database (sdb).,

secondary:/vol2 />rman target sys/oracle@sdb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 13 16:20:45 2013

connected to target database: PDB (not mounted)

d. restore the controlfile using the below command.,

restore controlfile from '/oracle/CONTROL01.CTL';

RMAN> restore controlfile from '/oracle/CONTROL01.CTL';

Starting restore at 13-MAR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/VOL2/SDB/STDCONTROL01.CTL

Finished restore at 13-MAR-13

RMAN>

NOTE:

-----

Rename standby data/temp files and redo if different from the primary using ALTER DATABASE RENAME FILE

e. Mount the standby database.,

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

f. Catalog backup pieces if the location on the standby is different from the primary.

connect to rman as target as standby database (stb).,

secondary:/vol2 />rman target sys/oracle@sdb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 13 16:01:03 2013

connected to target database: PDB (DBID=3232692579)

RMAN> catalog start with '/rman/rman_sdb';

searching for all files that match the pattern /rman/rman_sdb

List of Files Unknown to the Database

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

File Name: /rman/rman_sdb/DBF_0BO4GTHJ_1_1

File Name: /rman/rman_sdb/DBF_0CO4GTIO_1_1

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /rman/rman_sdb/DBF_0BO4GTHJ_1_1

File Name: /rman/rman_sdb/DBF_0CO4GTIO_1_1

g. Apply incremental backup to the standby.,

RMAN> recover database noredo;

Starting recover at 13-MAR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=195 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /VOL2/SDB/SYSTEM01.DBF

destination for restore of datafile 00002: /VOL2/SDB/SYSAUX01.DBF

destination for restore of datafile 00003: /VOL2/SDB/UNDOTBS01.DBF

destination for restore of datafile 00004: /VOL2/SDB/USERS01.DBF

destination for restore of datafile 00005: /VOL2/SDB/TBSRMAN.DBF

channel ORA_DISK_1: reading from backup piece /RMAN/DBF_BKUP/DBF_09O4EBAE_1_1

channel ORA_DISK_1: piece handle=/RMAN/DBF_BKUP/DBF_09O4EBAE_1_1 tag=TAG20130313T155700

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 13-MAR-13

RMAN>

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

You can see the Alert log in which the recovery process is clearly mentioned.,

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

ARC0: STARTING ARCH PROCESSES COMPLETE

Lost write protection disabled

ARC2: Becoming the active heartbeat ARCH

Thu Mar 14 15:26:28 2013

Completed: alter database mount

Thu Mar 14 15:26:34 2013

Using STANDBY_ARCHIVE_DEST parameter default value as /vol2/sdb/arch

Thu Mar 14 15:27:36 2013

Incremental restore complete of datafile 4 /VOL2/SDB/USERS01.DBF

checkpoint is 1084371

last deallocation scn is 3

Incremental restore complete of datafile 3 /VOL2/SDB/UNDOTBS01.DBF

checkpoint is 1084371

last deallocation scn is 1025328

Incremental restore complete of datafile 5 /VOL2/SDB/TBSRMAN.DBF

checkpoint is 1084371

last deallocation scn is 994065

Incremental restore complete of datafile 2 /VOL2/SDB/SYSAUX01.DBF

checkpoint is 1084371

last deallocation scn is 1005551

Incremental restore complete of datafile 1 /VOL2/SDB/SYSTEM01.DBF

checkpoint is 1084371

last deallocation scn is 1004787

Thu Mar 14 15:28:03 2013

alter database recover managed standby database disconnect from session

Thu Mar 14 15:28:03 2013

MRP0 started with pid=28, OS id=3188

started logmerger process

Thu Mar 14 15:28:08 2013

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 32

Completed: alter database recover managed standby database disconnect from session

Thu Mar 14 15:28:56 2013

RFS[1]: Assigned to RFS process 1416

RFS[1]: Opened log for thread 1 sequence 32 dbid -1062274717 branch 809697765

Archived Log entry 1 added for thread 1 sequence 32 rlc 809697765 ID 0xc0af5d63 dest 2:

Thu Mar 14 15:29:00 2013

Media Recovery Log /VOL2/SDB/ARCH/ARC0000000032_0809697765.0001

Media Recovery Waiting for thread 1 sequence 33

Thu Mar 14 15:29:16 2013

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

h. Start the media recovery process in standby database

alter database recover managed standby database disconnect from session;

SQL> alter database recover managed standby database disconnect from session;

i. Check the archive log list on primary and standby databases.,

ON Primary :

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

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 31

Next log sequence to archive 33

Current log sequence 33

ON Standby :

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

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 31

Next log sequence to archive 0

Current log sequence 33

j. Cancel the recovery mode in Standby Database and open the database for read only access. Start the media recovery process.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

32 YES YES

ON Primary Database:

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

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 31

Next log sequence to archive 33

Current log sequence 33

ON Standby Database:

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

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

32 YES YES

33 YES NO

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

32 YES YES

33 YES NO

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

32 YES YES

33 YES IN-MEMORY

SQL> select sequence#,archived,applied from v$archived_log;

SEQUENCE# ARC APPLIED

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

32 YES YES

33 YES YES

k. Now finally check the synchronization of primary and standby databases.

ON Primary Database:

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

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol1/pdb/arch

Oldest online log sequence 32

Next log sequence to archive 34

Current log sequence 34

ON Standby Database:

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

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /vol2/sdb/arch

Oldest online log sequence 32

Next log sequence to archive 0

Current log sequence 34

From the above check, we can clearly see that both primary and standby databases are in sync.

Convert a Non ASM database to ASM database in Oracle 11gR2

2014-02-20T19:53:00.002+05:30

1. Install the ASM Binaries.

2. Configure the ASM and create 2 Diskgroups namely DATAGRP1 and FRAGRP1. Assign the respective disks to the groups.

3. Start the ASM instance and keep it up.

4. Now you will be logged in as grid user. Change the user to Oracle.

5. Create a pfile from the spfile just for a safety purpose.

create pfile=<location/init.ora> from spfile;

6. Since we are converting the instance from Non-ASM to ASM, we need to change the location of the controlfile, db_create_file_dest, db_create_online_log_dest_1 etc to the ASM location.

Set the below parameters.,

alter system set control_files=’+DATAGRP1’ scope=spfile;
alter system set db_create_file_dest=’+DATAGRP1’ scope=spfile;
alter system set db_recovery_file_dest=’+FRAGRP1’ scope=spfile;
alter system set db_create_online_log_dest_1=’+DATAGRP1’ scope=spfile;
alter system set db_create_online_log_dest_2=’+FRAGRP1’ scope=spfile;

7. Since the changes has been done to the spfile, you can take one more backup of the pfile.

8. Shutdown the database.

shutdown immediate;

9. Startup the database in nomount.

startup nomount;

10. Now connect to the RMAN,

a. If you use a seperate database for the rman catalog then use the below command.,

rman target / catalog rman/*****@catalogdb

b. If the RMAN is maintained in the same database,

rman target /

11. Restore the controlfile to the new location.

restore controlfile from '<name of the controlfile with location>';

Egs: RMAN > restore controlfile from '/vol1/TESTDB/control01.ctl';

12. Mount the database now. (you can mount it from the RMAN prompt itself).

alter database mount;

13. Backup the database to the new location. i.e., ASM diskgroup location.

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

Backup as copy database format ‘+DATAGRP1’;

release channel c1;

release channel c2;

release channel c3;

}

14. Now the switch the database using the switch command as below.,

switch database to copy;

A backup copy is taken in the step 13. Now this switch command in RMAN will switch the entire database to the backup copy taken in to the Diskgroup '+DATAGRP1'.

15. After the switch, the database will be in a inconsistent state. So we need to recover the database. In the SQL prompt connect as sysdba and issue the below command.

recover database using backup controlfile until cancel;

16. Now recover if it prompts for any archivelogs or redo logs. Else cancel the recover using cancel command.

17. Open the database with the resetlogs option.

alter database open resetlogs;

18. Drop the old temp tablespace and recreate it in the new location (ASM Location).

a. You can drop the tablespace as a whole.

drop tablespace TEMP including contents and datafiles;

b. You can also add a new tempfile to the new ASM location and remove the old one.

alter tablespace TEMP add tempfile '+DATAGRP1' size 2048M autoextend on next 5M maxsize unlimited;

alter database tempfile '<tempfile name with location on the filesystem>' drop including datafiles;

c. Recreate the temp tablespace.

create temporary tablespace TEMP tempfile '+DATAGRP1' size 2048M autoextend on next 5M maxsize unlimited;

alter tablespace TEMP add tempfile '+DATAGRP1' size 2048M autoextend on next 5M maxsize unlimited;

19. Recreate the redo log group on ASM Diskgroup. Use the below command to list out the current Redo log group used in the database.

select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group#=b.group#;

select group#,status from v$log;

For example if you have 3 Redo log groups namely,

select group#,status from v$log;

GROUP# STATUS

1 ACTIVE

2 ACTIVE

3 CURRENT

Drop it one by one. First drop the first group, create it and then move to the second one. Before recreating the redo log group issue the below command to avoid (ORA-01624: log 1 needed for crash recovery of instance)

alter system checkpoint global;

a. Drop and recreate the group 1.

alter database drop logfile group 2;

alter database add logfile group 1 size 100M;

b. Drop and recreate the group2.

alter database drop logfile group 2;

alter database add logfile group 2 size 100M;

c. Drop and recreate the group 3.

alter database drop logfile group 3;

alter database add logfile group 3 size 100M;

Purging statistics from the SYSAUX tablespace

2014-01-29T11:50:00.000+05:30

In Oracle Database, Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring purpose. But this old statistics which are saved automatically, are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system.

Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value of Retention is 31 days.

If the retention period is not managed then the SYSAUX tablespaces can grow very large.

This is only a small workout in a small database with less data. So the reclaim size of the SYSAUX tablespace will be less. There will be a huge difference in space reclaim of the SYSAUX tablespace on a big database with huge data.

This blog entry will provide the scripts and steps to diagnose and correct the excessive or rapid growth of the tablespace due to retained statistics.

1. Existing Details of the SYSAUX Tablespace

set lines 200 pages 1000
column tablespace_name format a30
column allocated format 999999.99
column free format 999999.99
column used format 999999.99
col Contiguous format 999999.99
break on report
compute sum of allocated on report
compute sum of used on report
compute sum of free on report
compute sum of contiguous on report

select rpad(a.tablespace_name,30,'.')tablespace_name,
   sum(a.bytes)/(1024*1024) Allocated,
   sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
   max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,
   max(nvl(b.cont,0))/(1024*1024) Contiguous
from dba_data_files a,
   (select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
    from dba_free_space
    group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name='SYSAUX'
group by a.tablespace_name
order by a.tablespace_name
/

From the output we can see that only 150 MB of free space is available in SYSAUX tablespace.

COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40

SELECT occupant_name "Item",
    space_usage_kbytes/1048576 "Space Used (GB)",
    schema_name "Schema",
    move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/

2. Check the retention of the Stats.

select dbms_stats.get_stats_history_retention from dual;

3. Alter the retention period of the Stats to 10 Days.

exec dbms_stats.alter_stats_history_retention(10);

select dbms_stats.get_stats_history_retention from dual;

4. Now execute the below command to PURGE the Old Statistics. That is purge the statistics which are older than 10 Days.

If there is huge data, then it is recommended to purge in stages like (sysdate-30,sydate-25 etc).

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

5. Now check the History of the Statistics availability.

select dbms_stats.get_stats_history_availability from dual;

6. Show how big the tables are and rebuild after stats have been purged.

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
/

7. Show how big the indexes are ready for a rebuild after stats have been purged.

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/

8. Now we have to rebuild the table. Here in this case, enable row movement and shrink the tables are not possible since the Indexes are function based.

So use the below script to generate the move table script.

select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
/

9. Run the commands generated by the script.

10. Rebuild the indexes since the status of the Indexes will be in Unusable state.

Use the below script to generate the rebuild statements.

select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
/

11. Run the rebuild statements generated by the script.

12. Before rebuilding the indexes, the status of the indexes will be in Unusable state. After rebuilding the indexes check the status of the indexes.

select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/

13. Now check the size of the SYSAUX tablespace.

set lines 200 pages 1000
column tablespace_name format a30
column allocated format 999999.99
column free format 999999.99
column used format 999999.99
col Contiguous format 999999.99
break on report
compute sum of allocated on report
compute sum of used on report
compute sum of free on report
compute sum of contiguous on report

select rpad(a.tablespace_name,30,'.')tablespace_name,
   sum(a.bytes)/(1024*1024) Allocated,
   sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
   max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,
   max(nvl(b.cont,0))/(1024*1024) Contiguous
from dba_data_files a,
   (select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
    from dba_free_space
    group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name='SYSAUX'
group by a.tablespace_name
order by a.tablespace_name
/

From the output we can clearly see that there is space gain of nearly 250 MB from the Sysaux tablespace.

14. Now the Retention period is set to 10 Days.

select dbms_stats.get_stats_history_retention from dual;

If required you can bring back the retention period to the default value 31 days.

exec dbms_stats.alter_stats_history_retention(31);

select dbms_stats.get_stats_history_retention from dual;

Query to check Allocated Space, Used Space and Free Space of the Tablesapces in Oracle Database

2014-01-26T14:43:00.003+05:30

The below query gives the details of the Tablespaces in a Oracle Database. It shows the details of Size allocated, Used size and Free size of the tablespaces.

NOTE: Temporary tablespace is not included in this.

set lines 200 pages 1000

column tablespace_name format a30

column allocated format 999999.99

column free format 999999.99

column used format 999999.99

col Contiguous format 999999.99

break on report

compute sum of allocated on report

compute sum of used on report

compute sum of free on report

compute sum of contiguous on report

select rpad(a.tablespace_name,30,'.')tablespace_name,

sum(a.bytes)/(1024*1024) Allocated,

sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,

max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,

max(nvl(b.cont,0))/(1024*1024) Contiguous

from dba_data_files a,

(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

group by a.tablespace_name

order by a.tablespace_name

Example:

MIGRATE DATA FROM OLD ASM DISK TO NEW ASM DISK

2014-01-24T12:29:00.000+05:30

1. INTRODUCTION / OVERVIEW

Data can be migrated from one SAN disk to another in Oracle database. The below document describes the steps to migrate the data from one ASM disk to another ASM disk.

OS admin, can perform the migration if it is on the Regular File system. There is a role for a DBA to be played, when ASM comes in to picture.

The OS admin, will add the raw disks and grant the necessary permissions to the disks which are to be added.

cd /dev

ls –lart ASM*

In this scenario the old disk are ASM_DISK1 and ASM_DISK2.

We have to add the new disks ASM_DISK3 and ASM_DISK4 to the ASM and remove the old ones.

1. STEPS TO ADD DISK TO ASM

1. Check for the Existing ASM Disks. Check whether the permissions of the disk are given correctly as oracle:dba.

Pluto8:/usr2/oracle />ls -lart /dev/ASM*

2. From database level, when we query the ASM views, the disk added will be displayed in the view result. The name of the Disk Group will be specified as [CANDIDATE]. First we need to login to the ASM instance.

For Oracle 10g Version,

export ORACLE_SID=+ASM

sqlplus" / as sysdba "

For Oracle 11g Version,

. ./.profile_grid

sqlplus /nolog

conn / as sysasm

SQL> set lines 200 pages 1000

SQL> column disk_group_name format a30

columndisk_file_path format a50

SQL> SQL> column disk_file_name format a30

SQL> column disk_file_fail_group format a30

SQL> SELECT

2 NVL(a.name, '[CANDIDATE]') disk_group_name

3 , b.pathdisk_file_path

4 , b.name disk_file_name

5 , b.failgroupdisk_file_fail_group

6 FROM

7 v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

8 ORDER BY

9 a.name;

You can even check the disk info as below.,

3. Add the disk to the ASM.

alterdiskgroup ASMDISK add disk '/dev/ASM_DISK3','/dev/ASM_DISK4' rebalance power 9;

4. After adding the disk to the ASM, check the free space in ASM.

5. After executing the command for adding disk to the ASM, the disk will be added soon but there will be a data Re-Org processing inside the ASM. So it will take some time for it to get completed. To check the status of the process issue the below query.,

select * from v$asm_operation;

From the above result we can clearly see that there is a Rebalancing operation going on in ASM. The Estimated Time for completion is 7mins.

6. Keep on checking the status of the view v$asm_operation. At one specific time the result will selects no rows. It means the disk has been successfully added to the ASM and the Rebalance of the DATA is also completed successfully.

7. In the same way all the disks are added to the ASM. Check the ASM view to see the status of the Added disks.,

2. REMOVE DISK FROM ASM

Deletion / Removal of disks are similar to the Addition of disks to the ASM. The command to remove the disk will vary.

In Migration of ASM Disk, to add a disk to the ASM we use the OS Level name of the disk, whereas while dropping / removing a disk from ASM we should specify the Database level name of the Disk. This we can get it by querying the view v$asm_diskgroup.

The below are the steps to carry out the Removal Of Disk from ASM.

1. Check the current status of the ASM.

The above is the current status of the ASM.

2. To drop or remove a disk from ASM issue the below command.,

alterdiskgroup ASMDISK drop disk ASMDISK_0000,ASMDISK_0001 rebalance power 9;

3. As we saw in the addition of disk, here also a Disk Re-Org or Rebalance operation will be processed.

4. Wait till the Rebalance operation to complete. The view should return no rows.

5. Now check the status of ASM disks,

1. Once after removing the disk from the ASM, a restart of the database is required. There will be some locks existing on disks in the OS level. So OS team will be not able to remove the old disks.

2. After restarting the Database inform to OS team so that they will easily remove the old Disks.

1. CONCLUSION

a. The Migration of Data can be done Online.

b. Only removal of disk needs a reboot whereas for adding disks restart of the database is not required.

PREVENT USERS (SCHEMAS) FROM GETTING DROPPED

2014-01-17T10:45:00.000+05:30

The below is the Trigger which is used to prevent the users or schemas from getting dropped.

Create OR Replace Trigger TrgDropUserRestrict
Before Drop On Database
Declare
Begin
If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','user1') Then
Raise_Application_Error(-20001,'Cannot Drop User
'||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');
End If;
End;
/

1. create a new user and grant privileges to the new user.

ORADB>create user user1 identified by laser default tablespace users;

User created.

ORADB>grant connect,resource,debug connect session,debug any procedure to user1;

Grant succeeded.

ORADB>

ORADB>select username from all_users where username = 'USER1';

USERNAME
------------------------------
USER1

ORADB>

2. Run the procedure to prevent the user to be dropped.

ORADB>Create OR Replace Trigger TrgDropUserRestrict
Before Drop On Database
Declare
Begin
If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','USER1') Then
Raise_Application_Error(-20001,'Cannot Drop User
'||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');
End If;
End;
/ 2 3 4 5 6 7 8 9 10

Trigger created.

Note: You can specify N number of users in the Ora_Dict_Obj_Name which should not get dropped.

3. Try to drop the user and check if the user is dropped.

ORADB>drop user user1 cascade;

drop user user1 cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

user1 Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4

ORADB>

ORADB>drop user scott cascade;

drop user scott cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

SCOTT Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4

Unable to remove ASM Diskgroup from the ASM Instance

2014-01-09T18:51:00.000+05:30

SCENARIO

I Was not able to remove the newly added ASM diskgroup ASM_DISK1 from ASM instance.

INSTANCE - ASM

ASM GRID - ORACLE 11G

REMOVED DISKGROUP NAME - ASM_DISK1

OS LEVEL DISK NAME ASSIGNED - DATA1

OPERATING SYSTEM - OEL 6.0

WORKINGS

SQL> select name from v$asm_diskgroup;

NAME
---------------------------------
DATA_GRP1
ASM_DISK1

SQL> select name from v$asm_disk;

NAME

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

DATA_GRP1_0000

ASM_DISK1_0000

While dropping the diskgroup, i got the following error and I was not able to drop it.,

SQL> alter diskgroup ASM_DISK1 drop disk ASM_DISK1_0000;

alter diskgroup ASM_DISK1 drop disk ASM_DISK1_0000

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15250: insufficient diskgroup space for rebalance completion

SQL> alter diskgroup ASM_DISK1 drop disk ASM_DISK1_0000 rebalance power 8;

alter diskgroup ASM_DISK1 drop disk ASM_DISK1_0000 rebalance power 8

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15250: insufficient diskgroup space for rebalance completion

Checked the Total space and Free space of the ASM Diskgroup. Since the disk was a newly added one, there was enough free space.

SQL> set lines 200 pages 1000

SQL> col name format a30

SQL> select name,total_mb,free_mb from v$asm_disk;

NAME TOTAL_MB FREE_MB

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

DATA_GRP1_0000 21050 20998

ASM_DISK1_0000 5567 5508

I tried to drop it again,

SQL> drop diskgroup ASM_DISK1 including contents;

drop diskgroup ASM_DISK1 including contents

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15027: active use of diskgroup "ASM_DISK1" precludes its dismount

I dismounted the diskgroup and tried to drop it.

SQL> alter diskgroup ASM_DISK1 dismount force;

Diskgroup altered.

SQL> drop diskgroup ASM_DISK1 including contents;

drop diskgroup ASM_DISK1 including contents

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15001: diskgroup "ASM_DISK1" does not exist or is not mounted

The below was the solution which I followed to remove the ASM diskgroup from the ASM Instance.

Created a pfile from the spfile in a different location and restarted the database using that pfile.

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +ASM_DISK1/asm/asmparameterfile/registry.253.836335963

SQL> create pfile='/vol2/grid/init.ora' from spfile;

File created.

SQL> shut immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup nomount pfile='/vol2/grid/init.ora';

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

SQL>

SQL> alter diskgroup ASM_DISK1 mount;

Diskgroup altered.

SQL> drop diskgroup ASM_DISK1 including contents;

Diskgroup dropped.

Now mount the other ASM diskgroup.

SQL> alter diskgroup DATA_GRP1 mount;

Diskgroup altered.

SQL> create spfile from pfile='/vol2/grid/init.ora';

File created.

SQL> shut immediate

ASM diskgroups dismounted

ASM instance shutdown

Now create a spfile from the pfile and start up the instance.

SQL> create spfile from pfile='/vol2/grid/init.ora';

File created.

SQL> shut immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL>

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ORA-15032: not all alterations performed

ORA-15017: diskgroup "ASM_DISK1" cannot be mounted

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

"ASM_DISK1"

Here I faced another error. Even though after successful dropping of the ASM Disk, while starting the ASM instance, it automatically referred the removed disk also.

So I deleted the ASM disk using the Oracleasm command,

oracleasm deletedisk DATA1

[root@primary ~]# oracleasm deletedisk DATA1

Clearing disk header: done

Dropping disk: done

[root@primary ~]# oracleasm listdisks

DATA2

[root@primary ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@primary ~]#

I also changed the value of ORACLEASM_SCANORDER="" to ORACLEASM_SCANORDER="dm". But this also did not work.

primary:/vol2/grid />cat /etc/sysconfig/oracleasm

# This is a configuration file for automatic loading of the Oracle

# Automatic Storage Management library kernel driver. It is generated

# By running /etc/init.d/oracleasm configure. Please use that method

# to modify this file

# ORACLEASM_ENABELED: 'true' means to load the driver on boot.

ORACLEASM_ENABLED=true

# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.

ORACLEASM_UID=

# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.

ORACLEASM_GID=

# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.

ORACLEASM_SCANBOOT=true

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning

ORACLEASM_SCANORDER=dm

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan

ORACLEASM_SCANEXCLUDE=""

primary:/vol2/grid />

Even though after deleting the ASM disk in OS level, the ASM instance was starting with the error.

SQL> conn / as sysasm

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ORA-15032: not all alterations performed

ORA-15017: diskgroup "ASM_DISK1" cannot be mounted

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

"ASM_DISK1"

SOLUTION

Remove the old diskgroup name from the parameter file (PFILE) asm_diskgroups.

SQL> show parameter asm_diskgroups

NAME TYPE VALUE

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

asm_diskgroups string DATA_GRP1, ASM_DISK1

SQL>

SQL> alter system set asm_diskgroups='DATA_GRP1' scope=both;

System altered.

SQL> show parameter disk

NAME TYPE VALUE

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

asm_diskgroups string DATA_GRP1

asm_diskstring string /dev/oracleasm/disks/*

SQL>

Create a pfile from spfile.

SQL> create pfile='/vol2/grid/init.ora' from spfile;

File created.

Startup the ASM instance with the pfile.

SQL> shut immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup pfile='/vol2/grid/init.ora';

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL>

ASM Started normally.

Create a SPFILE and restart the ASM instance.

SQL> create spfile from pfile='/vol2/grid/init.ora';

File created.

SQL> shut immediate;

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL>

SQL> set lines 200 pages 1000

SQL> col name format a25

SQL> select name,total_mb,free_mb from v$asm_disk;

NAME TOTAL_MB FREE_MB

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

DATA_GRP1_0000 21050 20998

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB

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

DATA_GRP1 21050 20998

SQL>

Now we can see that there was no error message during the instance startup.

Table De-Fragmentation to Reclaim the Space and Different Methods of De-Fragmentation

2014-01-01T18:25:00.002+05:30

ENVIRONMENT

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

SERVER NAME	SERVER1
DATABASE NAME	TESTDB
SCHEMA NAME	VSRIDHAR
FRAGMENTED TABLE	MYTABLE

There are many ways to reclaim the space from the table. Some of the Traditional ways are below.,

1. Imp/exp

2. Alter tablespace move

3. Truncate and Insert

4. user dbms_redefinition package to copy the table

Oracle New Feature is

1. Using shrink feature of Oracle 10g

a. Shrink compact

b. Shrink cascade

2. Redefinition of Tables using DBMS_REDIFINITION

A Brief about SHRINK tables

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

SHRINK CLAUSE

Alter table table_name shrink space;

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, sub partition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

Note:

Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause. The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for such tables.

COMPACT

Alter table table_name shrink space compact;

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

Alter table table_name shrink space compact;

Alter table table_name shrink space; ----Row level locking happen. This command can be executed in OFF Peak Hours.

The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

CASCADE

alter table table_name shrink space cascade;

The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package

To shrink a basicfile LOB:

alter table <table_name> MODIFY LOB <column_name> shrink space

To shrink a single partition of a partition table:

alter table <table_name> MODIFY PARTITION <partition_name> shrink space;

Oracle split this process as below.,

Oracle move the rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and make the segment more compact. This process is moving the data row by row. It acquires a row level lock when the row is moved down to the beginning of the segment. The corresponding index data will be handled like any other row level DML. So we do not need to worry about rebuilding the indexes for the row. Also row level lock will happen for very short moment. Before we start this phase, we need to enable row movement.

Here we have used the 1st Method - Shrink feature to reclaim the space from the table.

1-a. Shrink compact feature

Firstly, Created a table "MYTABLE" using the below Procedure which also populates the table with 10000000 records. The steps

SET ECHO ON

SET SERVEROUTPUT ON SIZE 1000000

CREATE TABLE MYTABLE

(id NUMBER NOT NULL

,CONSTRAINT id_pk PRIMARY KEY (id));

DECLARE

TYPE number_table IS TABLE OF MYTABLE.id%TYPE INDEX BY BINARY_INTEGER;

number_list NUMBER_TABLE;

BEGIN

FOR i IN 1..10000000 LOOP

number_list(i) := i;

END LOOP;

FORALL i IN 1..number_list.COUNT

INSERT INTO MYTABLE VALUES (number_list(i));

COMMIT;

END;

**********************************************************************

Created the table "MYTABLE”

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

CREATE TABLE mytable

(id NUMBER NOT NULL

,CONSTRAINT id_pk PRIMARY KEY (id));

SQL> SQL> 2 3

Table created.

SQL>

SQL> DECLARE

3 TYPE number_table IS TABLE OF mytable.id%TYPE INDEX BY BINARY_INTEGER;

4 5

6 number_list NUMBER_TABLE;

8 BEGIN

11 FOR i IN 1..10000000 LOOP

13 number_list(i) := i;

15 END LOOP;

18 FORALL i IN 1..number_list.COUNT

19 INSERT INTO mytable VALUES (number_list(i));

21 COMMIT;

23 END;

24 /

PL/SQL procedure successfully completed.

Populated the table with 10000000 records

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

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

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

10000000

Size and Blocks of the table "MYTABLE"

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

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

**********************************************************************

Fragmentation Method 1-a:

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

Step 1:

======

Analyze the Table.,

analyze table vsridhar.MYTABLE compute statistics;

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 2:

=====

Check the Size and Blocks of the table.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

select blocks from dba_tables where table_name='MYTABLE';

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

Step 3:

=====

Delete some rows from table and analyze the table again.,

analyze table vsridhar.MYTABLE compute statistics;

SQL> delete from vsridhar.mytable where id like '%9%';

5217031 rows deleted.

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

----------

4782969

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 4:

=====

Again check the blocks and size of the table whether the blocks are reduced or not.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

select blocks from dba_tables where table_name='MYTABLE';

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

Step 5:

=====

Before shrinking the table, one should enable the row movement in that table. After enabling the row movement, Shrinking contains of two simple steps.,

alter table vsridhar.MYTABLE enable row movement;

alter table vsridhar.MYTABLE shrink space compact;

SQL> alter table vsridhar.MYTABLE shrink space compact;

Table altered.

Step 6:

======

Now just for checking purpose, check the size and blocks of the table.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

select blocks from dba_tables where table_name='MYTABLE';

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

Step 7:

======

Now Execute the next statement to get the accurate result i.e., to shrink the table completely.

alter table vsridhar.MYTABLE shrink space;

analyze table vsridhar.MYTABLE compute statistics;

SQL> alter table vsridhar.MYTABLE shrink space;

Table altered.

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Step 8:

=====

Now check the blocks and size using the below query and we can clearly see the difference in space.,

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

select blocks from dba_tables where table_name='MYTABLE';

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 59.8125 2048

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

7521

Note:

=====

When shrinking was in process, simultaneously we have also tested some DML operations on the same table. It worked fine. So this process can be carried out ONLINE.

SQL> select count(*) from mytable where id like '%1%';

COUNT(*)

----------

2685818

SQL> update mytable set id=9 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from mytable where id=9;

----------

SQL> select * from mytable where id=1;

no rows selected

**********************************************************************

Fragmentation Method 1-b – Using “shrink cascade” command

1. Checked the Size of the segments.,

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner='VSRIDHAR';

SEGMENT_NAME SIZE_MB

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

MYTABLE 58

ID_PK2 88

2. Checked the count of the table “MYTABLE”

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

----------

4782969

3. Deleted some rows from the table “MYTABLE”.

SQL> delete from vsridhar.mytable where id like '%2%';

2685817 rows deleted.

4. Checked the size again after deleting but the size of the segment is not reduced.,

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner='VSRIDHAR';

SEGMENT_NAME SIZE_MB

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

MYTABLE 58

ID_PK2 88

5. Checked the status of the index and analyzed the table.,

SQL> select index_name,table_name,owner,status from dba_indexes where table_name='MYTABLE';

INDEX_NAME TABLE_NAME OWNER STATUS

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

ID_PK2 MYTABLE VSRIDHAR VALID

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner='VSRIDHAR';

SEGMENT_NAME SIZE_MB

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

MYTABLE 58

ID_PK2 88

Even after analyzing the table the size of the table did not reduce.

STEP 1:

======

Enable the row movement for that table by using the below command.,

alter table vsridhar.mytable enable row movement;

SQL> alter table vsridhar.mytable enable row movement;

Table altered.

STEP 2:

======

Shrink the table using the below command to reclaim the space.,

alter table vsridhar.mytable shrink space cascade;

SQL> alter table vsridhar.mytable shrink space cascade;

Table altered.

STEP 4:

======

After shrinking the table check the size of the table and also check whether the index to the corresponding table is in the VALID state or not.

select segment_name,bytes/1024/1024 size_mb from dba_segments where owner='VSRIDHAR';

select index_name,table_name,owner,status from dba_indexes where table_name='MYTABLE';

SQL> select segment_name,bytes/1024/1024 size_mb from dba_segments where owner='VSRIDHAR';

SEGMENT_NAME SIZE_MB

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

MYTABLE 25.4375

ID_PK2 37.125

SQL> select index_name,table_name,owner,status from dba_indexes where table_name='MYTABLE';

INDEX_NAME TABLE_NAME OWNER STATUS

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

ID_PK2 MYTABLE VSRIDHAR VALID

**********************************************************************

Fragmentation Method 2:

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

ONLINE TABLE REDEFINITION

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. See here for details on using the dbms_redefinition package for reorganizing Oracle tables online. The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the "Create table as select" command:

Before going to the steps, as we did in the earlier stage create a table "MYTABLE".

SQL> SET ECHO ON

SQL> SET SERVEROUTPUT ON SIZE 1000000

CREATE TABLE mytable

(id NUMBER NOT NULL

,CONSTRAINT id_pk PRIMARY KEY (id));

SQL> SQL> 2 3

DECLARE

TYPE number_table IS TABLE OF mytable.id%TYPE INDEX BY BINARY_INTEGER;

number_list NUMBER_TABLE;

BEGIN

FOR i IN 1..10000000 LOOP

number_list(i) := i;

END LOOP;

FORALL i IN 1..number_list.COUNT

INSERT INTO mytable VALUES (number_list(i));

COMMIT;

END;

Table created.

SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 /

PL/SQL procedure successfully completed.

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

----------

10000000

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

Elapsed: 00:01:08.66

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

Elapsed: 00:00:00.18

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

Elapsed: 00:00:00.03

Delete some rows from the table "MYTABLE":

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

delete from vsridhar.mytable where id like '%9%'

SQL> delete from vsridhar.mytable where id like '%9%'

/ 2

5217031 rows deleted.

Elapsed: 00:03:06.87

SQL> select count(*) from vsridhar.mytable;

COUNT(*)

----------

4782969

Elapsed: 00:00:00.23

SQL>

Analyze the Table again and check the size and blocks of the Table:

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

analyze table vsridhar.MYTABLE compute statistics;

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

select blocks from dba_tables where table_name='MYTABLE';

SQL> analyze table vsridhar.MYTABLE compute statistics;

Table analyzed.

Elapsed: 00:00:37.25

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 120 2048

Elapsed: 00:00:00.23

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

15197

Elapsed: 00:00:00.02

From the above we can clearly see there was no difference in the size and blocks of the table even after the deletion of rows. The space can be reclaimed using the ONLINE TABLE REDEFINITION Feature in Oracle.

Please follow the below steps for Online Table Redefinition., - Method 2

STEP 1:

======

Check whether the table can be redefined or not.

EXEC Dbms_Redefinition.Can_Redef_Table('VSRIDHAR', 'MYTABLE');

SQL> EXEC Dbms_Redefinition.Can_Redef_Table('VSRIDHAR', 'MYTABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

STEP 2:

======

Create new table with CTAS - "Creating Table As Select"

CREATE TABLE VSRIDHAR.MYTABLE2

TABLESPACE mis AS

SELECT * FROM VSRIDHAR.MYTABLE

SQL> CREATE TABLE VSRIDHAR.MYTABLE2

TABLESPACE mis AS

SELECT * FROM VSRIDHAR.MYTABLE

/ 2 3 4

Table created.

Elapsed: 00:00:02.69

STEP 3:

=======

Start Redefinition

EXEC Dbms_Redefinition.Start_Redef_Table( -

'VSRIDHAR', -

'MYTABLE', -

'MYTABLE2', -

'ID ID');

SQL> EXEC Dbms_Redefinition.Start_Redef_Table( -

'VSRIDHAR', -

'MYTABLE', -

'MYTABLE2', -

'ID ID');> > > >

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.32

STEP 4:

======

Optionally synchronize new table with interim data

EXEC dbms_redefinition.sync_interim_table( -

'VSRIDHAR', 'MYTABLE', 'MYTABLE2');

SQL> EXEC dbms_redefinition.sync_interim_table( -

'VSRIDHAR', 'MYTABLE', 'MYTABLE2');>

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

STEP 5:

=======

Add new keys, FKs and triggers

ALTER TABLE VSRIDHAR.MYTABLE2 ADD (CONSTRAINT id_pk2 PRIMARY KEY (id))

SQL> ALTER TABLE VSRIDHAR.MYTABLE2 ADD (CONSTRAINT id_pk2 PRIMARY KEY (id))

/ 2

Table altered.

Elapsed: 00:00:08.35

STEP 6:

=======

Complete redefinition

EXEC Dbms_Redefinition.Finish_Redef_Table( -

'VSRIDHAR', 'MYTABLE', 'MYTABLE2');

SQL> EXEC Dbms_Redefinition.Finish_Redef_Table( -

'VSRIDHAR', 'MYTABLE', 'MYTABLE2');>

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.07

STEP 7:

=======

Remove original table which now has the name of the new table

DROP TABLE VSRIDHAR.MYTABLE2;

SQL> DROP TABLE VSRIDHAR.MYTABLE2;

Table dropped.

Elapsed: 00:00:00.49

AFTER TABLE ONLINE REDEFINITION SIZE AND BLOCKS OF THE TABLE:

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

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 "SIZE_MB",MAX_SIZE/1024/1024 "MAX_SIZE_MB" from dba_segments where SEGMENT_NAME='MYTABLE';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB MAX_SIZE_MB

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

MYTABLE TABLE 58 2048

Elapsed: 00:00:00.66

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

Elapsed: 00:00:00.23

Note: Since the table is not analyzed the result shows the blocks as empty.

SQL> analyze table vsridhar.mytable compute statistics;

Table analyzed.

Elapsed: 00:00:34.30

SQL> select blocks from dba_tables where table_name='MYTABLE';

BLOCKS

----------

7405

Elapsed: 00:00:00.01

select status,constraint_name from DBA_constraints where table_name = 'MYTABLE';

SQL> select status,constraint_name from DBA_constraints where table_name = 'MYTABLE';

STATUS CONSTRAINT_NAME

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

ENABLED SYS_C009785

ENABLED ID_PK2

Elapsed: 00:00:00.26

There are several advantages over traditional methods. The advantages are below.,

1. It can be done in online. There is a table level lock for very short moment. Traditional methods are not supporting to reset the HWM in online.

2. It does not take extra space while resetting the HWM. If we use traditional method, DBMS_REDEFINITION package uses double the amount of space.

3. It does acquire only row level lock while performing majority of the shrinking (moving rows) work. It acquires table level lock only when it resets the HWM which is in phase II. But traditional methods requires table down time for resetting the HWM except using dbms_redefinition package.

4. Index will be maintained and remain usable. But in traditional methods, we need to rebuild the index. Especially when we use ALTER TABLESPACE MOVE command.

5. It can be made in one command (alter table EMP shrink space). In traditional method, there are multiple steps.

6. If you are not sure that you can afford table level lock at specific time, then you can do the majority of the shrinking work and later we can reset the HWM. Since table level lock is required only while resetting the HWM. The whole process can be done in two steps. This advantage is not available in traditional methods.

Some of the Restrictions are.,

1. It is only possible in ASSM tablespace

2. Not supporting for clustered tables, tables with column data type LONG

RMAN ACTIVE DUPLICATION ASM TO ASM

2014-01-01T17:59:00.002+05:30

RMAN Active Duplication an Overview:

It is a new feature in Oracle 11g where cloning is done from one database to another database without any outage or downtime of the primary database.

Earlier we use to clone the database using cold back or rman backup. But RMAN Active duplication feature allows a database to be duplicated directly from its live source database instead of using its backup.

RMAN directly reads the data from the database using PRIMARY database CONTROLFILE.

Advantages:

1. No outage or downtime required for the Primary Database.

2. No Backup required for cloning.

3. It can be performed in NoCatalog mode itself.

4. Instant Database Clones possible (On Demand Cloning)

Disadvantages:

1. When cloning there will be more I/O's in the Primary Database.

2. There will be performance overheads in the Primary Database.

3. Incomplete recovery NOT possible (no SET UNTIL) - Active Database Duplication cannot be used to duplicate a database until a point in time. One has to rely on the backup-based database duplication method if one wants to create a duplicate database until a specific point in time ( a specific time, log sequence or SCN). Furthermore, active duplicate copies data only until the last archived log file in the source database. The contents of the online redologs will not be copied to the duplicate database.

Environment Details:

PRIMARY SERVER	RMDB	SERVER1	11gR2 ASM Grid and Oralce 11gR2 11.2.0.2.0 Binaries
SECONDARY SERVER	CLONEDB	SERVER2	11gR2 ASM Grid and Oralce 11gR2 11.2.0.2.0 Binaries

Step 1:

Copy the password file from Target database to auxiliary database location and rename it.

scp orapwrmdb oracle@server2:/usr2/oracle/product/11.2.0.2/dbs/.

cd /usr2/oracle/product/11.2.0.2/dbs

mv orapwrmdb orapwclonedb

Step 2:

Create the pfile from target database for Auxilarydatabase( if target has spfile create pfile from spfile)

Parameter File of Target Database:

*.audit_file_dest='/usr2/oracle/admin/rmdb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA1/rmdb/controlfile/current.260.816249205'

*.db_block_size=8192

*.db_create_file_dest='+DATA1'

*.db_domain=''

*.db_file_name_convert='+DATA1/rmdb/datafile','+DATA1/clonedb/datafile'

*.db_name='rmdb'

*.diagnostic_dest='/usr2/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rmdbXDB)'

*.local_listener='LISTENER_RMDB'

*.log_archive_dest_1='location=/vol1/arch'

*.log_file_name_convert='+DATA1/rmdb/onlinelog','+DATA1/clonedb/onlinelog'

*.memory_target=1932525568

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Edited Parameter File of Auxiliary Database:

*.audit_file_dest='/usr2/oracle/admin/clonedb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA1/clonedb/controlfile/current.257.816499055'#Set by RMAN

*.db_block_size=8192

*.db_create_file_dest='+DATA1'

*.db_domain=''

*.db_file_name_convert='+DATA1/rmdb/datafile','+DATA1/clonedb/datafile'

*.db_name='CLONEDB'#Reset to original value by RMAN

*.diagnostic_dest='/usr2/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'

*.log_archive_dest_1='location=/vol1/arch'

*.log_file_name_convert='+DATA1/rmdb/onlinelog','+DATA1/clonedb/onlinelog'

*.memory_target=1932525568

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Note:

In Step 2, we have added two parameters db_file_name_convert and log_file_name_convert.

This parameter is required to match the location of the datafiles and logfiles. It can be removed once after the cloning / duplication is completed.

Step 3:

Configure the linster&tnsname on both target database and auxiliary database.

Egs:

Tnsnames entries:

RMDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = rmdb)

)

CLONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = clonedb)

)

Step 4:

Create the required directory structure in Auxiliarydb location (based on pfile).

Step 5:

Open the Auxiliary database in Nomount state. Created spfile from pfile and again shutdown the auxiliary database and brought up to Nomount state using spfile.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

SQL> conn sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1937457152 bytes

Fixed Size 2220848 bytes

Variable Size 1124076752 bytes

Database Buffers 805306368 bytes

Redo Buffers 5853184 bytes

SQL>

Step 6:

Go to the Target Database server and enter in to the RMAN prompt. Issue the below command to duplicate the Target database to auxiliary database.

rman

connect target sys/*******@rmdb

connect auxiliary sys/********@clonedb

duplicate target database to clonedb from active database nofilenamecheck;

RMAN> connect target sys/*****@rmdb

connected to target database: RMDB (DBID=873495541)

RMAN> connect auxiliary sys/*****@clonedb

connected to auxiliary database: CLONEDB (not mounted)

RMAN> duplicate target database to clonedb from active database nofilenamecheck;

Starting Duplicate Db at 27-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA1/clonedb/controlfile/current.256.816499053'' comment=

''Set by RMAN'' scope=spfile";

sql clone "alter system set db_name =

''RMDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''CLONEDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '+DATA1/clonedb/controlfile/current.257.816499055';

sql clone "alter system set control_files =

''+DATA1/clonedb/controlfile/current.257.816499055'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA1/clonedb/controlfile/current.256.816499053'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''RMDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''CLONEDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1937457152 bytes

Fixed Size 2220848 bytes

Variable Size 1107299536 bytes

Database Buffers 822083584 bytes

Redo Buffers 5853184 bytes

Starting backup at 27-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/usr2/oracle/product/11.2.0.2/dbs/snapcf_rmdb.f tag=TAG20130527T051755 RECID=1 STAMP=816499076

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 27-MAY-13

sql statement: alter system set control_files = ''+DATA1/clonedb/controlfile/current.257.816499055'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1937457152 bytes

Fixed Size 2220848 bytes

Variable Size 1107299536 bytes

Database Buffers 822083584 bytes

Redo Buffers 5853184 bytes

database mounted

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

setnewname for datafile 1 to

"+data1";

setnewname for datafile 2 to

"+data1";

setnewname for datafile 3 to

"+data1";

setnewname for datafile 4 to

"+data1";

backup as copy reuse

datafile 1 auxiliary format

"+data1" datafile

2 auxiliary format

"+data1" datafile

3 auxiliary format

"+data1" datafile

4 auxiliary format

"+data1" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 27-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

inputdatafile file number=00001 name=+DATA1/rmdb/datafile/system.256.816249133

output file name=+DATA1/clonedb/datafile/system.258.816499099 tag=TAG20130527T051817

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

inputdatafile file number=00002 name=+DATA1/rmdb/datafile/sysaux.257.816249133

output file name=+DATA1/clonedb/datafile/sysaux.259.816499105 tag=TAG20130527T051817

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

inputdatafile file number=00003 name=+DATA1/rmdb/datafile/undotbs1.258.816249133

output file name=+DATA1/clonedb/datafile/undotbs1.260.816499113 tag=TAG20130527T051817

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

inputdatafile file number=00004 name=+DATA1/rmdb/datafile/users.259.816249133

output file name=+DATA1/clonedb/datafile/users.261.816499113 tag=TAG20130527T051817

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 27-MAY-13

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/vol1/arch/1_9_816249207.dbf" auxiliary format

"/vol1/arch/1_9_816249207.dbf" ;

catalog clone archivelog "/vol1/arch/1_9_816249207.dbf";

switch clone datafile all;

}

executing Memory Script

Starting backup at 27-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=9 RECID=8 STAMP=816499115

output file name=/vol1/arch/1_9_816249207.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 27-MAY-13

cataloged archived log

archived log file name=/vol1/arch/1_9_816249207.dbf RECID=8 STAMP=816499117

datafile 1 switched to datafile copy

inputdatafile copy RECID=1 STAMP=816499117 file name=+DATA1/clonedb/datafile/system.258.816499099

datafile 2 switched to datafile copy

inputdatafile copy RECID=2 STAMP=816499117 file name=+DATA1/clonedb/datafile/sysaux.259.816499105

datafile 3 switched to datafile copy

inputdatafile copy RECID=3 STAMP=816499117 file name=+DATA1/clonedb/datafile/undotbs1.260.816499113

datafile 4 switched to datafile copy

inputdatafile copy RECID=4 STAMP=816499117 file name=+DATA1/clonedb/datafile/users.261.816499113

contents of Memory Script:

{

set until scn 1171572;

recover

clone database

deletearchivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 27-MAY-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=96 device type=DISK

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /vol1/arch/1_9_816249207.dbf

archived log file name=/vol1/arch/1_9_816249207.dbf thread=1 sequence=9

media recovery complete, elapsed time: 00:00:01

Finished recover at 27-MAY-13

Oracle instance started

Total System Global Area 1937457152 bytes

Fixed Size 2220848 bytes

Variable Size 1107299536 bytes

Database Buffers 822083584 bytes

Redo Buffers 5853184 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''CLONEDB'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''CLONEDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1937457152 bytes

Fixed Size 2220848 bytes

Variable Size 1107299536 bytes

Database Buffers 822083584 bytes

Redo Buffers 5853184 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '+data1' ) SIZE 50 M REUSE,

GROUP 2 ( '+data1' ) SIZE 50 M REUSE,

GROUP 3 ( '+data1' ) SIZE 50 M REUSE

DATAFILE

'+DATA1/clonedb/datafile/system.258.816499099'

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

setnewname for tempfile 1 to

"+data1";

switch clone tempfile all;

catalog clone datafilecopy "+DATA1/clonedb/datafile/sysaux.259.816499105",

"+DATA1/clonedb/datafile/undotbs1.260.816499113",

"+DATA1/clonedb/datafile/users.261.816499113";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamedtempfile 1 to +data1 in control file

catalogeddatafile copy

datafile copy file name=+DATA1/clonedb/datafile/sysaux.259.816499105 RECID=1 STAMP=816499143

catalogeddatafile copy

datafile copy file name=+DATA1/clonedb/datafile/undotbs1.260.816499113 RECID=2 STAMP=816499143

catalogeddatafile copy

datafile copy file name=+DATA1/clonedb/datafile/users.261.816499113 RECID=3 STAMP=816499143

datafile 2 switched to datafile copy

inputdatafile copy RECID=1 STAMP=816499143 file name=+DATA1/clonedb/datafile/sysaux.259.816499105

datafile 3 switched to datafile copy

inputdatafile copy RECID=2 STAMP=816499143 file name=+DATA1/clonedb/datafile/undotbs1.260.816499113

datafile 4 switched to datafile copy

inputdatafile copy RECID=3 STAMP=816499143 file name=+DATA1/clonedb/datafile/users.261.816499113

Reenablingcontrolfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 27-MAY-13

RMAN>

Step 7:

Check the status of the cloned database.

SQL> select name,db_unique_name,database_role,dbid from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE DBID

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

CLONEDB CLONEDB PRIMARY 903172166

VERIFICATIONS:

1. Check the DBID of both the databases RMDB and CLONEDB. It will be different.

Primary Database – RMDB

SQL> select name,db_unique_name,database_role,dbid from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE DBID

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

RMDB RMDB PRIMARY 873495541

Clone Database - CLONEDB

SQL> select name,db_unique_name,database_role,dbid from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE DBID

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

CLONEDB CLONEDB PRIMARY 903172166

2. Before performing Active Duplication I created a schema and a table. The same has been replicated to the cloned database.

On Primary Database – RMDB

SQL> conn test/*****@rmdb

Connected.

SQL> select count(*) from tab;

COUNT(*)

----------

SQL> select * from t1;

----------

SQL>

On Clone Database – CLONEDB

SQL> conn test/*****@clonedb

Connected.

SQL> select count(*) from tab;

COUNT(*)

----------

SQL> select * from t1;

----------

Recreating the Oracle Inventory files in Oracle 10g and Oracle 11g

2014-01-01T17:26:00.005+05:30

Normally, an oraInventory directory will be created in the system when we run the Oracle Universal Installer (OUI) first time on a system.

Oracle Inventory - oraInventory

oraInventory is repository which stores the oracle software products and their home locations (ORACLE_HOME) in a system. This oraInventory is created on the first run of the Oracle Universal Installer in the system.

There are two types of Inventories. They are,

1. Global Inventory - which is also known as Central Inventory.

2. Local Inventory - Oracle Home Inventory.

Global Inventory

Global Inventory consists of the information of all the Oracle Products installed in a machine. inventory.xml is the file where it lists all the ORACLE_HOMEs installed in the system.

Local Inventory

Each Oracle Home consists of its own Inventory File which is called the Local Inventory or Oracle Home Inventory. Each Oracle Inventory contains the information of its respective ORACLE_HOME.

There is also a file called comps.xml,which contains all the components as well as patchsets or interim patches installed in the ORACLE_HOME.

Note:

No down time is required to create the Global Inventory (oraInventory).

If you have a corrupted or improper Oracle Inventory, you can rename it using the mv command as below.,

mv oraInventory oraInventory_Old

To determine or find the oraInventory is location in the system open the file 'oraInst.loc'. This file will be located in the below locations depending upon your platform.

/var/opt/oracle/oraInst.loc or /etc/oraInst.loc

The file oraInst.loc looks as below,

/var/opt/oracle/oraInst.loc

inst_group=dba

inventory_loc=/app/oracle/oraInventory

ORACLE_HOME/oraInst.loc

inst_group=dba

inventory_loc=/apps/oracle/oraInventory

To find the ORACLE_HOME & ORACLE_HOME name

You can view it from the old Oracle Inventory file located in

/app/oracle/oraInventory/ContentsXML/inventory.xml

cat /app/oracle/oraInventory/ContentsXML/inventory.xml

<?xml version="1.0" standalone="yes" ?>

<VERSION_INFO>

<SAVED_WITH>10.2.0.5.0</SAVED_WITH>

<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

</HOME_LIST>

</INVENTORY>

Go to the Oracle Universal Installer location for creating the Oracle oraInventory,

$ORACLE_HOME/oui/bin

If you have more than one Oracle product installed in your machine/system, then you have to update the inventory file for all the Oracle Home.

When there is multiple Oracle Homes then, you have to run the oui in their respective homes separately.

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/app/oracle/10.2.0.5" ORACLE_HOME_NAME="orahome_1"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc

The inventory is located at /app/oracle/oraInventory

'AttachHome' was successful.

For example if you have Oracle agent installed in your machine, then you should run it in AGENT_HOME,

$AGENT_HOME/oui/bin

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/app/oracle/product/agent10g" ORACLE_HOME_NAME="agent10g"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc

The inventory is located at /app/oracle/oraInventory

'AttachHome' was successful.

Skip DML Replication of a Table to Logical Standby site and Re-Instantiate it

2013-12-31T22:10:00.000+05:30

ENVIRONMENT

DATABASE - ORACLE 11gR2 11.2.0.3.0

PRIMARY DATABASE - PRODDB

STANDBY DATABASE - LOGSTDB (LOGICAL STANDBY)

SCHEMA NAME - TEST

NOTE: Already two tables are created in the TEST schema namely T1 and T2.

On Primary Database

Connect to the schema and check the table rows.

PRODDB>conn test/test@PRODDB

Connected.

PRODDB>select * from tab;

TNAME TABTYPE CLUSTERID

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

T1 TABLE

T2 TABLE

PRODDB>select * from t1;

----------

PRODDB>select * from t2;

----------

7 rows selected.

On Logical Standby Database

Stop the Logical Standby apply.

alter database stop logical standby apply;

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

Execute the below statement to skip a table replication.,

exec dbms_logstdby.skip('DML','SCHEMA_NAME','TABLE_NAME',null);

LOGSTDB-LOGICAL>exec dbms_logstdby.skip('DML','TEST','T1',null);

PL/SQL procedure successfully completed.

Start the Logical Standby apply.

alter database start logical standby apply immediate;

LOGSTDB-LOGICAL>alter database start logical standby apply immediate;

Database altered.

On Primary Database

Insert rows in to the tables T1 and T2.

PRODDB>insert into t1 values(4);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from t1;

----------

PRODDB>insert into t2 values(7);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from t2;

----------

8 rows selected.

On Logical Standby Database

Now Check whether the inserted rows in to tables T1 and T2 are replicated to the Logical Standby Database.

LOGSTDB-LOGICAL>select * from test.t1;

----------

LOGSTDB-LOGICAL>select * from test.t2;

----------

8 rows selected.

NOTE:

From the above output we can clearly see that the inserted rows are not replicated to the Logical Standby Database. Replication to table T1 is skipped.

Rows in the table T2 is replicated since it is not skipped

RE-INSTANTIATE THE SKIPPED TABLES

On Logical Standby Database

Stop SQL apply process.

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

Drop and recreate the table test.t1.

LOGSTDB-LOGICAL>drop table test.t1;

Table dropped.

LOGSTDB-LOGICAL>create table test.t1 (no number(4));

Table created.

On the logical standby database execute the following the query.

LOGSTDB-LOGICAL>SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN

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

2087190 2090492 2087191

Take a backup of the table test.T1 from the production database.

host expdp test/test@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

PRODDB>host expdp test/test@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

Export: Release 11.2.0.3.0 - Production on Wed Feb 22 20:15:01 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."T1" 5.078 KB 4 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/cbsprddbs/oracle/admin/PRODDB/dpdump/test.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:15:53

Note: Flashback scn will be restart_scn which you get from v$logstdby_progress.

Then import the datapump data to the Logical.

host impdp dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

LOGSTDB-LOGICAL>host impdp dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

Import: Release 11.2.0.3.0 - Production on Wed Feb 22 20:18:44 2012

Username: sys@LOGSTDB as sysdba

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": sys/********@LOGSTDB AS SYSDBA dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Table "TEST"."T1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."T1" 5.078 KB 4 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 20:19:20

Then unskip the skip rule for the table.

EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','SCHEMA_NAME','TABLE_NAME');

LOGSTDB-LOGICAL>EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','TEST','T1');

PL/SQL procedure successfully completed.

Then start sql apply.

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

LOGSTDB-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Note: If required analyze table test.T1 compute statistics or use dbms_stats to gather statistics.

REPLICATION TESTING AFTER RE-INSTANTIATION

On Primary Database

PRODDB>insert into test.t1 values(5);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from test.t1;

----------

On Logical Standby Database

LOGSTDB-LOGICAL>select * from test.t1;

----------

Effect of Creating Tablespace / Datafile on Primary when Logical Standby in Place (ORA-01119)

2013-12-31T20:34:00.001+05:30

Effect of Creating Tablespace / Datafile on Primary when Logical Standby in Place (ORA-01119)

SYMPTOMS

Dataguard environment with Logical standby.

When we add a new datafile on primary and both primary and standby are in different file name directory, SQL APPLY from production database to Logical Standby database will fail with ORA-01119.

CAUSE

1. If databse is OMF on both primary and standby then no issues in creating datafile at logical standby side.

2. If no OMF then Apply terminate with (ORA-1119) below errors, Because logcal standby will not honour the db_file_name_convert.

SOLUTION

Use OMF as a proactive measure.

If not run skip handler.

WORKOUT

On Production

create a tablespace as below,

PRODDB>create tablespace tbstest2 datafile '/oradata1/PRODDB/tbstestt201.dbf' size 1m autoextend on next 1m maxsize unlimited;

Tablespace created.

PRODDB>select name from v$tablespace where name ='TBSTEST2';

NAME
------------------------------

TBSTEST2

On Logical Standby database

Check whether the tablespace has replicated from primary site to logical standby site.

LOGSTDB-LOGICAL>select name from v$database where name='TBSTEST2';

no rows selected

From the above result we can understand that, the tablespace created in primary site did not replicate to logical standby. The following steps has to be followed to replicate the tablespace to logical standby site.

Stop the logical standby apply process.

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

Execute the below procedure.

LOGSTDB-LOGICAL>CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
NEW_STMT := REPLACE(OLD_STMT,'/oradata1/PRODDB','/oradata2/LOGSTDB');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

Procedure created.

After creating the procedure, Execute the below dbms statement.

LOGSTDB-LOGICAL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');

PL/SQL procedure successfully completed.

Start the logical standby apply process.

LOGSTDB-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Check whether the tablespace is replicated in logical standby site.

LOGSTDB-LOGICAL>select name from v$tablespace where name='TBSTEST2';

NAME
------------------------------
TBSTEST2

PREVENT USERS (SCHEMAS) FROM GETTING DROPPED

2013-12-31T18:42:00.001+05:30

The below Trigger is used to prevent users and schemas from Getting Dropped Mistakenly,

Create OR Replace Trigger TrgDropUserRestrict
Before Drop On Database
Declare
Begin
If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','user1') Then
Raise_Application_Error(-20001,'Cannot Drop User
'||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');
End If;
End;

/

1. create a new user and grant privileges to the new user.

ORADB>create user user1 identified by laser default tablespace users;

ORADB>grant connect,resource,debug connect session,debug any procedure to user1;

Grant succeeded.

ORADB>select username from all_users where username = 'USER1';

USERNAME

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

USER1

2. Run the procedure to prevent the user to be dropped.

ORADB>Create OR Replace Trigger TrgDropUserRestrict

Before Drop On Database

Declare

Begin

If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','USER1') Then

Raise_Application_Error(-20001,'Cannot Drop User

'||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');

End If;

End;

/ 2 3 4 5 6 7 8 9 10

Trigger created.

ORADB>

Note: You can specify N number of users in the Ora_Dict_Obj_Name which should not get dropped.

3. Try to drop the user and check if the user is dropped.

ORADB>drop user user1 cascade;

drop user user1 cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

user1 Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4

ORADB>drop user scott cascade;

drop user scott cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

SCOTT Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4

Switchover and Switchback with Logical Standby

2013-12-31T13:40:00.001+05:30

SWITCHOVER IN LOGICAL STANDBY

DATABASE - Oracle 11gR2 11.2.0.3.0

Operating System - Oracle Enterprise Linux

Primary Database - PRODDB

Logical Standby Database - LOGSTDB

On Primary Database:

Check the switchover status and role of the Primary Database.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE

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

proddb proddb PRIMARY READ WRITE

Issue the below command to prepare the Primary Database to switchover to logical standby.

alter database prepare to switchover to logical standby;

SQL> alter database prepare to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

PREPARING SWITCHOVER

Here the switchover status is PREPARING SWITCHOVER. Change the switchover status of Logical standby database and Wait for some time so that the status of primary database will change as TO LOGICAL STANDBY.

On Logical Standby Database

Check the switchover status and database role of the database.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE

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

logstdb logstdb LOGICAL STANDBY READ WRITE

Issue the command to prepare the Logical standby database to switchover to Primary Database.

alter database prepare to switchover to logical standby;

SQL> alter database prepare to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

On Primary Database

Now the check the switchover status of primary database. The status will be changed as TO LOGICAL STANDBY.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO LOGICAL STANDBY

Now switchover primary database to Logical Standby using the below command.

alter database commit to switchover to logical standby;

SQL> alter database commit to switchover to logical standby;

Database altered.

Now check the database role of the primary database. It will be changed to Logical Standby.

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE

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

proddb proddb LOGICAL STANDBY READ WRITE

SQL>

On Logical Standby Database

Now check the switchover status of Logical Standby Database.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

Switchover the Logical standby database to Primary using the below command.

alter database commit to switchover to primary;

SQL> alter database commit to switchover to primary;

Database altered.

Check the database role of the Logical Standby database. It will be changed as Primary.

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE

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

logstdb logstdb PRIMARY READ WRITE

On Primary Database

Start the Logical Standby apply process.

alter database start logical standby apply immediate;

SQL> alter database start logical standby apply immediate;

Database altered.

Check the Replication between the New Primary and Switched Logical Standby Database.

On Logical Standby Database (Current Primary Database)

Insert a row in a table and check whether it has been replicated to Primary Database(Currently Logical Standby).

SQL> select * from scott.t1;

----------

SQL> insert into scott.t1 values(4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.t1;

----------

SQL>

On Primary Database (Current Logical Standby Database)

Check the replication to Primary Database(Current Logical Standby).

SQL> select * from scott.t1;

----------

SQL> select * from scott.t1;

----------

*****************************************

SAME HAS TO BE FOLLOWED FOR SWITCHBACK

*****************************************

SWITCHBACK TO OLD ROLE

On Logical Standby Database(Current Primary Database)

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Prepare the Database for switchover.

alter database prepare to switchover to logical standby;

SQL> alter database prepare to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

PREPARING SWITCHOVER

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO LOGICAL STANDBY

Now Switchover the role of Current primary database to logical standby.

alter database commit to switchover to logical standby;

SQL> alter database commit to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL> select open_mode,database_role,name from v$database;

OPEN_MODE DATABASE_ROLE NAME

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

READ WRITE LOGICAL STANDBY logstdb

Start the Logical Standby apply process.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata3/logstdb/archives

Oldest online log sequence 17

Next log sequence to archive 19

Current log sequence 19

SQL> select * from scott.t1;

----------

On primary Database (Current Logical Standby Database)

Prepare the Database for switchover.

alter database prepare to switchover to primary;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata2/proddb/archives

Oldest online log sequence 33

Next log sequence to archive 35

Current log sequence 35

SQL> alter database prepare to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

PREPARING SWITCHOVER

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

Now Switchover the role of Logical Standby database to Primary Database.

alter database commit to switchover to primary;

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

LOG SWITCH GAP

Check the role and switchover status of the Primary database.

select switchover_status from v$database;

SQL> select open_mode,database_role,name from v$database;

OPEN_MODE DATABASE_ROLE NAME

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

READ WRITE PRIMARY proddb

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

LOG SWITCH GAP

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata2/proddb/archives

Oldest online log sequence 40

Next log sequence to archive 42

Current log sequence 42

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Note: logfile is switched to change the switchover status of the Primary Database.

SQL> select * from scott.t1;

----------

SQL> insert into scott.t1 values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.t1;

----------

On Logical Standby Database

SQL> select * from scott.t1;

----------

FULL DATABASE RESTORE AND RECOVER USING RMAN

2013-12-31T11:50:00.005+05:30

SCENARIO

Recovering the Database when database is fully crashed and no files are available using RMAN Backup files

1. Created a database called 'TESTDB' using DBCA.

2. Converted the database from NoArchivelog to Archivelog mode.

3. Configured RMAN.

4. Taken Incremental level 0 backup using RMAN (Database plus Archivelog).

5. Shutdown the Database.

6. Deleted the controlfiles, spfile, pfile, redolog files and datafiles.

7. Deleted the sid also.

8. Recovered the full database using RMAN backup.

ENVIRONMENT

DATABASE - ORACLE 11.2.0.3.0 64Bit

OS - Windows 2008 R2 64Bit

Database Name - testdb

*********************************************************************************

STEPS TO FOLLOW

1. Create New SID for TESTDB.

C:\>oradim -new -sid testdb -syspwd oracle -startmode auto

Instance created.

2. start the instance using a dummy pfile. The pfile may contain only the db_name and db_unique_name parameters.

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 4 21:09:12 2012

SQL> conn sys/oracle@testdb as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='f:\inittestdb.ora';

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2262048 bytes

Variable Size 616565728 bytes

Database Buffers 444596224 bytes

Redo Buffers 5513216 bytes

SQL>

3. Connect to RMAN.

C:\>rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 4 21:14:41 2012

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

4. Restore the SPFILE from autobackup.

RMAN> set dbid=2568236785

executing command: SET DBID

RMAN> connect target sys/oracle@testdb

connected to target database: (not mounted)

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'f:\testdb\controlfile_bkup\CTL_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Restoration of SPFILE from

RMAN> restore spfile from autobackup;

Starting restore at 04-JUN-12
using channel ORA_DISK_1

recovery area destination: d:\oracle\fast_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120604
channel ORA_DISK_1: AUTOBACKUP found: f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-JUN-12

RMAN>

5. Restore the CONTROLFILE from autobackup.

RMAN> set dbid=2568236785

executing command: SET DBID

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'f:\testdb\controlfile_bkup\CTL_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 04-JUN-12
using channel ORA_DISK_1

recovery area destination: d:\oracle\fast_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120604
channel ORA_DISK_1: AUTOBACKUP found: f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\TESTDB\CONTROL01.CTL
output file name=D:\ORACLE\FAST_RECOVERY_AREA\TESTDB\CONTROL02.CTL
Finished restore at 04-JUN-12

RMAN>

6. Mount the Database TESTDB.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

7. Restore the Database.

RMAN> restore database;

Starting restore at 04-JUN-12
Starting implicit crosscheck backup at 04-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 04-JUN-12

Starting implicit crosscheck copy at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 04-JUN-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2

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 00002 to F:\TESTDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\TESTDB\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to F:\TESTDB\TBSRMAN01.DBF
channel ORA_DISK_1: reading from backup piece F:\TESTDB\DATAFILE_BKUP\DBF_0INCNGIR_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to F:\TESTDB\SYSTEM01.DBF
channel ORA_DISK_2: restoring datafile 00004 to F:\TESTDB\USERS01.DBF
channel ORA_DISK_2: reading from backup piece F:\TESTDB\DATAFILE_BKUP\DBF_0HNCNGIR_1_1
channel ORA_DISK_1: piece handle=F:\TESTDB\DATAFILE_BKUP\DBF_0INCNGIR_1_1 tag=TAG20120604T205137
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_2: piece handle=F:\TESTDB\DATAFILE_BKUP\DBF_0HNCNGIR_1_1 tag=TAG20120604T205137
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:36
Finished restore at 04-JUN-12

RMAN>

8. After restoring checked the status of the DATABASE.

role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTDB testdb MOUNTED PRIMARY

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\testdb\archives
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12

SQL>

9. Recover the DATABASE.

RMAN> recover database;

Starting recover at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file F:\TESTDB\ARCHIVES\ARC0000000011_0784908660.0001
archived log file name=F:\TESTDB\ARCHIVES\ARC0000000011_0784908660.0001 thread=1 sequence=11
unable to find archived log
archived log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/04/2012 22:15:20
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 1076477

Note: From the above Error, use the Last SCN specified "1076477"

RMAN> recover database until SCN 1076477;

Starting recover at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 04-JUN-12

RMAN>

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/04/2012 22:15:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Note: Open the Database with RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

RMAN>

10. After Recovering the DATABASE check the status.

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTDB testdb READ WRITE PRIMARY

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\testdb\archives
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>

SQL> desc scott.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>

NOTE:

Before Deleting the DATABASE i had created a table T1 using scott schema.

From the above status we can clearly see that the table T1 exists in scott schema.

Flashback the Database Using SCN

2013-12-30T11:35:00.001+05:30

Bringing Back The Database to Previous State after Importing a schema Using Flashback Technology

Step 1 : Firstly we should enable flashback on the database.

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

Step 2: To check whether the flashback in enabled or not issue the below query.,

select flashback_on from v$database;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step 3: create the schema to which you have to import the data (dump).

create user test identified by test default tablespace users;

grant connect,resource,debug connect session,debug any procedure to test;

SQL> create user test identified by laser default tablespace users;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

Step 4: Make sure that there is no data in the newly created schema "test".

connect test/test@db3

select * from tab;

The result will be "no rows selected"

SQL> conn test/laser@db3

Connected.

SQL> select * from tab;

no rows selected

Step 5: We need to take a note of the current SCN (System Change Number) of the database. To know the
current SCN issue the below query.,

select current_scn from v$database;

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1039995

Step 6: Import the data to the newly created schema 'test'

SQL> host imp file='d:\db3\exp_scott.dmp' commit=y ignore=y fromuser=scott touser=test

Import: Release 11.2.0.3.0 - Production on Mon Mar 4 12:46:15 2013

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

Username: sys/oracle@db3 as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings.

Step 7: Check the tables of the new schema 'test'.

connect test/laser@db3

select * from tab;

SQL> conn test/laser@db3
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

Step 8: If you need to bring back the database to the previous stage (the stage before import) issue the following command.,

NOte: You had taken the current scn of the database before importing the data.

conn sys/password@db3 as sysdba

Shutdown immediate;

startup mount;

flashback database to scn scn_no;

alter database open resetlogs;

SQL> conn sys/oracle@db3 as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2255864 bytes
Variable Size 989856776 bytes
Database Buffers 704643072 bytes
Redo Buffers 6868992 bytes
Database mounted.

SQL> flashback database to scn 1039995;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Step 9: Check the archive log list of the database. It will start from the sequence 1.

archive log list;

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\db3\arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

Step 10: connect as schema 'test' and check whether there is any tables/data in it.,

conn test/test@db3

select * from tab;

SQL> conn test/laser@db3

Connected.

SQL> select * from tab;

no rows selected