How to drop a database in oracle?

 

This document describes how to drop a database in oracle. Please consider this post for only practice purpose on your won test instances and should not practice in any client's environment.

We may get the requirement to drop an existing test database as part of pre-clone steps. We should have proper approval from the management before drop the  database. Please make sure we are dropping test database. Below are the steps to drop a database.

Step 1: 

Take necessary backups before drop the database.

Step 2:

Shutdown the database.

 

Step 3:

Start the database in mount state with exclusive restrict mode. 

SQL> startup mount exclusive restrict;

Step 4:

Drop the database with below command. It will drop the database and cleanup the space which is occupied by datafiles. Make sure we are dropping the correct database. We can monitor the progress of drop database by tailing the alert log.

 

 

Thanks for going through the post.....Please consider this post for only practice purpose and should not practice in any client's environment.

 

How to kill running expdp/impdp job?

This document describes how to kill running export/import job.

EXPDP/IMPDP is an oracle utility to take logical backup/refresh of database, schemas, tables etc...

Sometimes we may need to stop/kill the running export or import job due to some errors or enhancements.

Export/Import job won't stop if we kill the process of the expdp/impdp or CTRL+C from windows machine.  Job runs in background even if we kill the process. Below is the process to stop the export/import job.

Step 1: Get the running expdp/impdp job details from dba_datapump_jobs view.

SQL> select * from dba_datapump_jobs;

OWNER_NAME                                                             JOB_NAME                                          OPERATION
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
JOB_MODE                                                             STATE                                                  DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------- -----------------
SYS                                                                 EXPDP_GOLD                                          EXPORT
FULL                                                                 EXECUTING                                           2             1               4


SQL>

Step2: Find the job_name from above detials.

Job Name= EXPDP_GOLD

 

Step3: Connect to the database using attached export job_name

expdp "'/as sysdba'" attach=EXPDP_GOLD

 

Step 4: Find the expdp/impdp job status with "STATUS" command.

Export> status

Step 5: Kill the expdp/impdp job.

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes   

Step 6: We can cross verify the datapump job after complete the above  command .

Thanks for going through the blog..................

 

How to find which region we subscribed and logged in from OCI console?

This document describes us how to find which region we are logged in, what is our home region and how to subscribe to other regions. 

 

Log In to Cloud console: 

Provide the cloud account credentials to login.

 

 Click on "Sign In"

 Click on "Manage Regions" from the right top drop down menu.

We can able to see Home Region and other available regions.

 

 

 

Click on subscribe button to subscribe any other regions.

Thanks for going through the post................

 

 

What is logical backup or datapump in oracle database? How to perform logical backup(Export and Import) or datapump?

This document describes what is logical backup in oracle database and how to do a logical backup. 

What is logical backup or Data Pump in oracle database? 

We are taking backup of logical objects(Schemas, Tables, Indexes, Views, Procedures, Tablespaces and Full database). That is the reason we are calling it as logical backup or Data Pump. We are using oracle provided utilities EXPDP and IMPDP which are command line utilities to take logical backups in the database. 

The Data Pump utilities EXPDP and IMPDP invoke Data Pump export and Data Pump import respectively. These utilities uses the procedures in DBMS_DATAPUMP pl/sql package to perform export/import.

We require one logical directory to use data pump(expdp/impdp) export or import.

Please click Here to find how to create a logical directory in the database. 

Full Database Export and Import:

Use below commands to export and import full database. We can utilize parallel option to improve data pump job and we can split the dump files instead of single dump file. It is easier to copy the dump files to  other servers as well. 

expdp "'/ as sysdba'" full=Y directory=EXPDP parallel=5 dumpfile=EXPDP_DBNAME_dump_%U.dmp job_name=EXPDP_DBNAME logfile=EXPDP_DBNAME.log

impdp "'/ as sysdba'" full=Y directory=IMPDP parallel=5 dumpfile=EXPDP_DBNAME_dump_%U.dmp job_name=IMPDP_DBNAME logfile=IMPDP_DBNAME.log

Schema Export and Import:

Use below command to take export or import of a single schema in the database.

expdp "'/ as sysdba'" schemas=INVOICE directory=EXPDP parallel=5 dumpfile=EXPDP_DBNAME_INVOICE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_invoice.log

impdp "'/ as sysdba'" schemas=INVOICE directory=IMPDP parallel=5 dumpfile=EXPDP_DBNAME_INVOICE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_invoice.log

 

Tablespace Export and Import:

Use below command to export or import tablespaces in the database.

expdp "'/ as sysdba'" tablespaces=hr_data directory=EXPDP parallel=5 dumpfile=EXPDP_TABLESPACE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_tablespace.log

impdp "'/ as sysdba'" tablespaces=hr_data directory=IMPDP parallel=5 dumpfile=EXPDP_TABLESPACE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_tablespace.log

 

Table Export and Import:

Use below command to export or import table in the database.

expdp "'/ as sysdba'" tables=SCOTT,HR directory=EXPDP parallel=5 dumpfile=EXPDP_TABLE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_table.log

impdp "'/ as sysdba'" tables=SCOTT,HR directory=IMPDP parallel=5 dumpfile=EXPDP_TABLE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_table.log

 

How to verify existing Data  Pump jobs?

We can use dba_datapump_jobs or user_datapump_jobs view to verify existing data pump jobs in the database.

 

 

 

Thanks for going through the post.......

 

What is directory objects in oracle? How to create a directory object in it.

This document describes about the directory objects, why are we using directory objects, what is the use of directory creation in oracle database and how to create directory in the oracle database.

 

What are directory objects? What is the use of directory objects?

A DIRECTORY object describes a logical alias name for a physical directory which lies on the database server file system under which the files to be accessed are located. We can access the files which are under physical directory by using logical directory if we have privileges. Depends on the privilege(Ex: select, read,write etc...) we can restrict the users to access the objects which are under directory.

We require logical directory to export or import the data in oracle database.

How to create a directory in oracle database?

we can create directory using create directory command. 

Create one physical directory in the system wherever we want.

[oracle@server1 u02]$ mkdir EXPDP_DATA

[oracle@server1 u02]$ cd EXPDP_DATA/

[oracle@server1 EXPDP_DATA]$ pwd
/u02/EXPDP_DATA

Connect as sysdba to the database and create logical directory by using below command.

 

SQL> create directory EXPDP as '/u02/EXPDP_DATA';

Directory created.

SQL>

How to find the information about existing directories in the database?

We can use dba_directories view to find the existing directories in the database.

Ex:

SQL> col OWNER for a10
SQL> col DIRECTORY_NAME for a15
SQL> col DIRECTORY_PATH for a30
SQL> set lines 200
SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name='EXPDP';

OWNER       DIRECTORY_NAME    DIRECTORY_PATH
----------         ---------------                    ------------------------------
SYS                EXPDP                          /u02/EXPDP_DATA
 
SQL>

 

 

Thanks for going through the post.........

adcfgclone.pl failed while running autoconfig with the error "ORA-04063: package body "APPS.AD_ZD_ADOP" has errors"

This document describes about the error while running autoconfig as part of adcfgclone.pl

Autoconfig failed with below error while running autoconfig as part of adcfgclone.

Error details:

[APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/oracle/ERPGOLD/fs1/FMW_Home/webtier/perl/bin/perl -I /u01/oracle/ERPGOLD/fs1/FMW_Home/webtier/perl/lib/5.10.0 -I /u01/oracle/ERPGOLD/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I /u01/oracle/ERPGOLD/fs1/EBSapps/appl/au/12.0.0/perl -I /u01/oracle/ERPGOLD/fs1/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/oracle/ERPGOLD/fs1/inst/apps/ERPGOLD_erpgoldapp/admin/install

      txkGenADOPWrapper.pl    INSTE8_APPLY      

Found below error in autoconfig log file and package body "APPS.AD_ZD_ADOP" is invalid.

SQL> SELECT ad_zd_adop.get_node_type('erpgoldapp') FROM DUAL

ERROR at line 1:

ORA-04063: package body "APPS.AD_ZD_ADOP" has errors

Solution:

SQL> alter package apps.AD_ZD_ADOP compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APPS.AD_ZD_ADOP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2682/3   PL/SQL: Statement ignored
2682/7   PLS-00201: id

SQL> grant execute on DBMS_METADATA_UTIL to apps;

Grant succeeded.

SQL> alter package apps.AD_ZD_ADOP compile body;

Package body altered.

SQL>

Reran the autoconfig. It should complete.

Thanks for going through the post.

How to find unusable indexes in oracle database and how to rebuild the unusable indexes

This document describes how to find unusable indexes in oracle database and how to rebuild unusable indexes to make it use.

In some scenarios we get unusable indexes in the database. We have to find which indexes are unusable and we have to rebuild them to make it use.

Use below command to find unusable indexes in the database.

col owner for a15
col index_name for a35
col table_name for a40 
select owner,index_name,status,table_name from dba_indexes where status='UNUSABLE';

Use below command to rebuild indexes.

alter index <owner>.<index name> rebuild online;

SQL> alter index XXEMPLOYEES_EMPNO_T1 rebuild online;

Thanks for going through the post......................