Getting Schema Structure

To remove the storage attributes,table space keywords while generating ddl , use the bellow commands

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false)
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);


DECLARE

STRUC clob;

BEGIN

FOR RC IN (
SELECT TABLE_NAME FROM USER_TABLES) LOOP

DBMS_OUTPUT.PUT_LINE(' TABLE ---  '||RC.TABLE_NAME||'  ---- START');

SELECT DBMS_METADATA.GET_DDL('TABLE',RC.TABLE_NAME ,'MIS_USER') INTO STRUC FROM DUAL;


DBMS_OUTPUT.PUT_LINE(REPLACE(SUBSTR(STRUC,1,30000),'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');

FOR RC1 IN (
SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME=RC.TABLE_NAME) LOOP

SELECT DBMS_METADATA.GET_DDL('TRIGGER',RC1.TRIGGER_NAME ,'MIS_USER') INTO STRUC FROM DUAL;

DBMS_OUTPUT.PUT_LINE(REPLACE(STRUC,'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');

END LOOP;

DBMS_OUTPUT.PUT_LINE('                                         ');

FOR RC2 IN (

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME=RC.TABLE_NAME AND CONSTRAINT_TYPE!='R' ) LOOP

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',RC2.CONSTRAINT_NAME ,'MIS_USER') INTO STRUC FROM DUAL;

DBMS_OUTPUT.PUT_LINE(REPLACE(STRUC,'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');

END LOOP;

FOR RC2 IN (

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME=RC.TABLE_NAME AND CONSTRAINT_TYPE='R' ) LOOP

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',RC2.CONSTRAINT_NAME ,'MIS_USER') INTO STRUC FROM DUAL;

DBMS_OUTPUT.PUT_LINE(REPLACE(STRUC,'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');

END LOOP;


DBMS_OUTPUT.PUT_LINE('                                         ');

FOR RC3 IN (

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME=RC.TABLE_NAME ) LOOP

SELECT DBMS_METADATA.GET_DDL('INDEX',RC3.INDEX_NAME ,'MIS_USER') INTO STRUC FROM DUAL;

DBMS_OUTPUT.PUT_LINE(REPLACE(STRUC,'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');

END LOOP;

DBMS_OUTPUT.PUT_LINE('                                         ');


DBMS_OUTPUT.PUT_LINE(' TABLE ---  '||RC.TABLE_NAME||'  ---- END');
END LOOP;

FOR RC4 IN (

SELECT VIEW_NAME FROM USER_VIEWS ) LOOP

SELECT DBMS_METADATA.GET_DDL('VIEW',RC4.VIEW_NAME ,'MIS_USER') INTO STRUC FROM DUAL;

DBMS_OUTPUT.PUT_LINE(REPLACE(STRUC,'"',''));

DBMS_OUTPUT.PUT_LINE('                                         ');
END LOOP;



END;

Migrating DB From Non Asm to Asm

Primary Step :  Create Asm Disk Groups  using Raw devises or  Asm libs
Here i have created two  disk groups
1. DISK_GRP_DATA  -- for CRD files
2.DISK_GRP_FRA     -- for archive log files and rman backups


Step:1
Login to SYSDBA user and alter below three parameter for controlfile,datafile or FRA location with SPFILE option.

1. First need to set below parameter for controlfile,datafile or FRA.
NOTE: I have two disk group here I am using "DGRP2" disk group.

SQL> alter system set control_files='+DISK_GRP_DATA' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DISK_GRP_DATA' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DISK_GRP_DATA' scope=spfile;
System altered.

Step: 2 Restart DB server to take above parameter value.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Step:3 Connect with RMAN session & restore controlfile on ASM system.

C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)


Restore Controlfile.

RMAN> restore controlfile from 'C:\app\RADHA\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of

recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 

device type=DISK
channel ORA_DISK_1: copied control file copyoutput file

name=+DISK_GRP_DATA/test/controlfile/current.256.652270419Finished restore

at 17-APR-08

Step:4 Mount Oracle Database and take backup of database.

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

RMAN> backup as copy database format '+DISK_GRP_DATA';

Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel

ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile

copyinput datafile file number=00001

name=C:\APP\RADHA\ORADATA\TEST\SYSTEM01.DBFoutput file 

name=+DISK_GRP_DATA/test/datafile/system.257.652270565 tag=TAG20080417T101550

RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, 

elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput 

datafile file number=00002 

name=C:\APP\RADHA\ORADATA\TEST\SYSAUX01.DBFoutput file

name=+DISK_GRP_DATA/test/datafile/sysaux.258.652270761

tag=TAG20080417T101550 RECID=2 STAMP=652270908channel 

ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel 

ORA_DISK_1: starting datafile copyinput datafile file number=00003

name=C:\APP\RADHA\ORADATA\TEST\UNDOTBS01.DBFoutput file 

name=+DISK_GRP_DATA/test/datafile/undotbs1.259.652270927

tag=TAG20080417T101550 RECID=3 STAMP=652270952channel 

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

ORA_DISK_1: starting datafile copycopying current control 

fileoutput file name=+DISK_GRP_DATA/test/controlfile/backup.260.652270971 

tag=TAG20080417T101550 RECID=4 STAMP=652270975channel 

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

ORA_DISK_1: starting datafile copyinput datafile file number=00004 

name=C:\APP\RADHA\ORADATA\TEST\USERS01.DBFoutput file

name=+DISK_GRP_DATA/test/datafile/users.261.652270989 tag=TAG20080417T101550 

RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, 

elapsed time: 00:00:03

channel ORA_DISK_1: starting full datafile backup setchannel 

ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE 

in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel 

ORA_DISK_1: finished piece 1 at 17-APR-08piece 

handle=+DISK_GRP_DATA/test/backupset/2008_04_17/

nnsnf0_tag20080417t101550_0.262.652271003 

tag=TAG20080417T101550 comment=NONEchannel 

ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished

backup at 17-APR-08



RMAN> switch database to copy;


datafile 1 switched to datafile copy 

"+DISK_GRP_DATA/test/datafile/system.257.652270565" 

datafile 2 switched to datafile copy 

"+DISK_GRP_DATA/test/datafile/sysaux.258.652270761" 

datafile 3 switched to datafile copy 

"+DISK_GRP_DATA/test/datafile/undotbs1.259.652270927" 

datafile 4 switched to datafile copy 

"+DISK_GRP_DATA/test/datafile/users.261.652270989"

Step:5 Again connect to sqlplus session and perform incomplete recovery

C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 

11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and 

Real Application Testing options


SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for 

thread 1ORA-00289: suggestion : +DGRP2ORA-15173: entry 'ARCHIVELOG' 

does not exist in directory 'TEST'ORA-00280: change 1071679 for 

thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.

Step:6 OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

Step:7 Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\RADHA\oradata\test\temp01.dbf' 2 

drop including datafiles;

Database altered.


SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 

250m maxsize unlimited;
Tablespace altered.


SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------ ----------
TEMP +DISK_GRP_DATA/test/tempfile/temp.266. 536870912 652271571

Step:8 Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes 

2 from v$logfile a, v$log b where a.group# = b.group#;


GROUP# MEMBER BYTES

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

3 C:\APP\RADHA\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\RADHA\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\RADHA\ORADATA\TEST\REDO01.LOG 52428800


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT 

2 UNUSED 

3 UNUSED


SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.


SQL> select group#,status from v$log;
GROUP# STATUS

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

1 ACTIVE 

2 ACTIVE 

3 CURRENT


SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed

for crash recovery of instance test (thread 1)ORA-00312: online log 1 

thread 1: 'C:\APP\RADHA\ORADATA\TEST\REDO01.LOG'


When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.


SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.


SQL> column member format a30

SQL> select a.group#, a.member, b.bytes 

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES

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

3 +DISK_GRP_DATA/test/onlinelog/group_3. 10485760 277.652273117
2 +DISK_GRP_DATA/test/onlinelog/group_2. 10485760 274.652273019
1 +DISK_GRP_DATA/test/onlinelog/group_1. 10485760 271.652272977
1 +DISK_GRP_DATA/test/onlinelog/group_1. 10485760 272.652272979
2 +DISK_GRP_DATA/test/onlinelog/group_2. 10485760 275.652273021
3 +DISK_GRP_DATA/test/onlinelog/group_3. 10485760 278.652273119

6 rows selected.

Step:9 Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DISK_GRP_DATA/spfileTEST.ora' from 

pfile='c:\initTEST.ora';
File created.

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Convert a Physical Standby Database into a Snapshot Standby Database using dgmgrl

From Oracle 11g onwards there is one more Standby database addition to Phycal and Logical. It is called Snapshot standby database. Snapshot standby is updatable version of the standby database. It is created from the existing standby database .You can modify the database and again you can convert it back to the physical standby database. The snapshot standby database receives the archive logs(redo) from the primary database but does not apply the redo in standby .It is automatically applied when you convert the snapshot standby database backup to physical standby database and the conversion can be done at any point of time

 It provides the replicated version of the primary database which can be used for development, testing and reporting purposes. You can always refresh the snapshot standby database to be in sync with the primary by converting it to physical standby database. Again you can convert to snapshot version do the testing and resynchronize with primary. This cycle can be repeated any number of times!

Step 01: Invoke dgmgrl utility from OS and connect to eithe primary or physical standby database.

[oracle@oracls1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;

Configuration - DATAGUARD

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY- Primary database
    STANDBY- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Step 02: Issue CONVERT DATABASE TO SNAPSHOT to convert a physical standby to a snapshot standby database.

DGMGRL> convert database standby to snapshot standby;
Converting database "standby" to a Snapshot Standby database, please wait...
Database "standby" converted successfully

DGMGRL>

Step 03: Issue SHOW CONFIGURATION command again to see the status.

Configuration - dataguard

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>





Step 04:
To convert the snapshot standby database back to a physical standby database use the command  CONVERT DATABASE TO PHYSICAL STANDBY. The conversion process does perform shutdown of the standby  database which is required for the FLASHBACK DATABASE operation.

DGMGRL> convert database standby to physical standby;

Converting database "standby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "standby" on database "standby"
Shutting down instance "standby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standby" on database "standby"
Starting instance "standby"...
ORACLE instance started.
Database mounted.
Continuing to convert database "standby" ...
Operation requires shutdown of instance "standby" on database "standby"
Shutting down instance "standby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standby" on database "standby"
Starting instance "standby"...
ORACLE instance started.
Database mounted.
Database "standby" converted successfully

DGMGRL>