Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;

Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;

You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)

FROM USER_TABLES U;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)

FROM USER_INDEXES U;  

In case if you need a View DDL, you can run below command;
SQL>select DBMS_METADATA.GET_DDL('VIEW',<View_Name>’) from dual;
or
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>','<schema_name>') from DUAL;

You can run below command to generate the DDL statements of a SCHEMA
set pages 30000
set linesize 1000
set lines 500

SQL> SELECT DBMS_METADATA.GET_DDL('USER','<schema_name>') FROM dual;

You can run below command to generate DDL statements for more than one schema

SQL> SELECT DBMS_METADATA.GET_DDL('USER',U.USERNAME) FROM DBA_USERS U WHERE USERNAME IN ('USER1','USER2');

You can run below command to generate DDL statement of the System Grant/ role granted/object granted to a schema owner

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',<Schema_Name>’) from dual;

You can run below command to generate DDL statement of the role
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;

You can also use SQL Developer to get the DDL script for a specific object.

You can refer oracle documentation for more details on DBMS_METADATA

Thanks & Regards

http://oracleracexpert.com

Error while loading shared libraries: libdb.so.2 - After Migrating REHL4 to REHL5

We have migrated Oracle Agile application from RedHat Linux 4 to RedHat Linux 5 and while starting up the Agile application we have received following error.

$ opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=agile:6201
    1 of 2 processes started.

ias-instance id=plm922.etna.corp.intusurg.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server/

Error
--> Process (index=1,uid=6575556334,pid=75645)
    failed to start a managed process after the maximum retry limit
    Log:
     /oracle/product/10.1.3.1/OracleAS/opmn/logs//HTTP_Server~1.log

From HTTP Serve log, we have found that Apache service failed while coming up. We have found below error message

/oracle/product/10.1.3.1/OracleAS/Apache/Apache/bin/apachectl startssl: execing httpd
/oracle/product/10.1.3.1/OracleAS Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

There is no shared library/usr/lib/ libdb.so.2 and found that the soft link is missing.

You may see this error with applications that are using Oracle Application services after cloning or migrating from REHL 4 to REHL5.

To resolve the issue follow the steps
1. Stop the Oracle Application Service
2. Login as root and create the soft link
   # ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
3. Start the Oracle Application Service.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Convert Single Instance to RAC – Part1: Duplicate DB using RMAN

Hi Everyone,

My articles published in Oracle Experts website media "Allthingsoracle.com" by RedGate

Please view the article using below link.
Convert Single Instance to RAC - Part1: Duplicate DB using RMAN

I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Migrate Database to ASM Using RMAN

We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.

Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;

Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;

Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.

Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;

Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';

Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;

Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';

Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.

Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;

Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/