Get DDL command from SQL query

select dbms_metadata.get_ddl('TABLE','table name') FROM DUAL;

e.g:

select dbms_metadata.get_ddl('TABLE','ORDR') FROM DUAL;

View Free space from temporary tablespace

select substr(a.tablespace_name,1,20) "TS_name", substr(a.file_name,1,35) "File_Name",
round(sum(a.bytes)/1024/1024,2) "Size_MB",sum(NVL(b.bytes,0)) "FreeSpace" from dba_TEMP_files a,
(select substr(tablespace_name,1,20) tablespace_name,file_id, round(sum(bytes_used)/1024/1024,2) used_sp,
round(sum(blocks_free * 8192)/1024/1024,2) bytes from v$temp_space_header group by substr(tablespace_name,1,20),file_id ) b where
a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by
substr(a.tablespace_name,1,20), substr(a.file_name,1,35)
/

View database size from sql

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;

Migrate OCR and Vote disk on ASM storage

Storing the OCR and vote disk inside ASM eliminates the need for third party cluster and volume managers and also helps eliminate the complexity of managing separate disk partitions for the OCR in oracle clusterware installations

Steps to migrate OCR and Vote disk on ASM storage:

1. Create ASM diskgroup using ASMCA


2. Voting disk on raw before migration

(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 63b1d7539d25ef7affff433cbdc40f28 (/dev/vx/rdsk/ocrvotedg/votevol) []
Located 1 voting disk(s).


3. Relpacing votedisk from raw to ASM
(root@mysystem) /oracle/product/grid/bin/crsctl replace votedisk +ocrdg

4. Vote disk on ASM after migration
===================================
(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 96c7f0f903174fd1bf15432ad5c6dee6 (/dev/vx/rdsk/asmdg/crsvol1) [OCRDG]
2. ONLINE 14e557f987874f36bf1f116c8a51ba6c (/dev/vx/rdsk/asmdg/crsvol2) [OCRDG]
3. ONLINE 0b1961148b674f00bf64b21ea4e6a126 (/dev/vx/rdsk/asmdg/crsvol3) [OCRDG]
Located 3 voting disk(s).


5. View existing OCR configuration before Migration
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Cluster registry integrity check succeeded

6. ADD new OCR by providing asm diskgroup
( root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -add +ocrdg

7. OCRCHECK after adding ASMDG for OCR
=========================================
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Device/File integrity check succeeded
Device/File Name : +ocrdg
Cluster registry integrity check succeeded

Logical corruption check succeeded

8. DELETING Old OCR

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -delete /dev/vx/rdsk/ocrvotedg/ocrvol


9. OCRCHECK after deleting old OCR
=====================================

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : +ocrdg
Device/File integrity check succeeded
Cluster registry integrity check succeeded

Migrate Oracled database from Admin managed to Policy Managed

Two Management Styles for Oracle RAC. Policy managed database introduced in oracle 11gr2


Steps to migrate Oracled database from Admin managed to Policy Managed

1. Add server pool
srvctl add srvpool -g oradbsrv -l 4 -u 4 -i 999 -n slial01,slial02,slial03,slial04

2. Check configuration of serverpool
srvctl config srvpool

3. Modify database with new serverpool created in step1
srvctl modify database -d oradb -g oastsrv

4. Check configuration of serverpool
srvctl config database -d oradb -a

5. Check status base of database and name of instances since its will changes after restarting instances
srvctl status database -d oradb

6. Stop and start DB
srvctl stop database -d oradb
srvctl start database -d oradb

5. Check status base of database with new name of instances
srvctl status database -d oradb

Note: New instance name will be diffrent from instance name before migration.