Steps To Drop 19c Oracle Database Manually

In this document ,we will see how to drop an Oracle database.

Prerequisite Steps

Find the location of the data files ,control files and online redo logs of the database to be dropped. Later point , we will cross verify. 

SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/system01.dbf
/u01/app/oracle/oradata/STAGE/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/system01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/users01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/system01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/users01.dbf

NAME
---------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_sysaux_k9g4g142_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_undotbs1_k9g4g143_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_users_k9g4g145_.dbf

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


15 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/control01.ctl
/u01/app/oracle/fra/STAGE/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/redo03.log
/u01/app/oracle/oradata/STAGE/redo02.log
/u01/app/oracle/oradata/STAGE/redo01.log

SQL>

--> Stop Database and start in Exclusive mode 

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 1073738888 bytes

Fixed Size                  9143432 bytes

Variable Size             616562688 bytes

Database Buffers          440401920 bytes

Redo Buffers                7630848 bytes

Database mounted.

SQL>

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

NAME      OPEN_MODE            DATABASE_ROLE

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

STAGE     MOUNTED              PRIMARY

SQL>

--> Drop Database

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

--> Manually verify the file

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/system01.dbf

ls: cannot access /u01/app/oracle/oradata/STAGE/system01.dbf: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf

ls: cannot access /u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/control01.ctl

ls: cannot access /u01/app/oracle/oradata/STAGE/control01.ctl: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/redo03.log

ls: cannot access /u01/app/oracle/oradata/STAGE/redo03.log: No such file or directory

[oracle@srv1 ~]$

Oracle : Drop Pluggable Database (PDB) 19c

 

Oracle : Drop Pluggable Database (PDB)

• Dropping pluggable database is similar to dropping any other regular database, you have two options while dropping pluggable database related to its datafiles
• Dropping PDB including datafiles
• Dropping PDB keeping datafiles

Before dropping pluggable database we can check PDB status by

a. SQL> show pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL>

b. select con_id, name,open_mode from v$containers;

SQL> set lin 1000

SQL> col name format a20

SQL> select con_id, name,open_mode from v$containers;

    CON_ID NAME                 OPEN_MODE

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

         1 CDB$ROOT        READ WRITE

         2 PDB$SEED         READ ONLY

         3 PDB1                 READ WRITE

         4 PDB2                 READ WRITE

         5 PDB3                 READ WRITE

--> We will drop PDB3 now. Before dropping we need to close database.

SQL> alter pluggable database PDB3 close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

if our database is multi node RAC then we can use instances=all option. 

SQL> alter pluggable database PDB3 close immediate instances=all;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL> alter pluggable database PDB3 close immediate instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

--> Dropping Database

SQL> drop pluggable database PDB3 including datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL>

SQL> drop pluggable database PDB3 keep datafiles; --> with keep datafiles option

Pluggable database dropped.

adop phase=actualize_all

 

How to Drop Old Database Editions in EBS R12.2 

In EBS R12.2, as each online patching cycle is completed, the database will accumulate an additional old database edition. An additional column ZD_EDITION_NAME is populated in the seed tables. If the number of these grows too large, system performance will start to be affected. When the number of old database editions reaches 25 or more, we should consider dropping all old database editions by running the adop actualize_all phase and then performing a full cleanup. 

Important: This procedure will take a large amount of time (significantly longer than a normal patching cycle), and should only be performed when there is no immediate need to start a new patching cycle.

Before starting, you should ensure that the system has the recommended database patches and latest AD-TXK code level installed.


When no patches need to be applied in Online Patching

To proceed, run the following commands in the order shown:
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

Old database editions would be cleared now


OR

Every-time online patching is performed:

$ adop phase=prepare
$ adop phase=apply patches=1,2,3
$ adop phase=actualize_all   -------------> Do this here
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

It has to be performed just before phase=finalize/cutover

How to execute an Empty Patching cycle in Oracle Apps R12.2

 

How to execute an Empty Patching cycle in Oracle Apps R12.2

Purpose: How to run empty patching cycle in EBS R12.2 

Occasion to run : 

> Testing purpose

> Switching the filesystem from fs1 to fs2, or vice versa.

Simple Command

login to application tier and invoke env file

cd /u01/install/APPS

. EBSapps.env RUN 

adop phase=prepare,finalize,cutover,cleanup

R12.2-Autoconfig-Testmode

 Purpose : How to run autoconfig in test mode in EBS 12.2 Database and Application

Source : https://docs.oracle.com/cd/E26401_01/doc.122/e22953/T174296T589913.htm (Section: Using the check config utility - adchkcfg.sh )

>>>> DB Tier

> Source the run file system environment

cd /u01/install/APPS/12.1.0/ . EBSDB_apps.env

> Navigate to $ORACLE_HOME/appsutil/bin

> Execute the command - ./adchkcfg.sh contextfile=$CONTEXT_FILE

The script generates both HTML and TEXT reports in $ORACLE_HOME/appsutil/out/$CONTEXT_NAME/MONDDHHMI/ directory on Database Tier

The reports contain both the File System Changes and Database Changes

File System Changes :

• Autoconfig Context File Changes
• Service Group Status
• Changed Configuration Files
• New Configuration Files
• Template Customizations

Database Changes :

• Profile Value Changes
• Profile Values
• Other Database Updates

[oracle@apps bin]$ cd $ORACLE_HOME/appsutil/bin

[oracle@apps bin]$

[oracle@apps bin]$ ./adchkcfg.sh contextfile=$CONTEXT_FILE

Enter the APPS password:

The log file for this session is located at: /u01/install/APPS/12.1.0/appsutil/log/EBSDB_apps/06120427/adconfig.log

AutoConfig is running in test mode and building diffs...

AutoConfig will consider the custom templates if present.

        Using ORACLE_HOME location : /u01/install/APPS/12.1.0

        Classpath                   : :/u01/install/APPS/12.1.0/jdbc/lib/ojdbc6.jar:/u01/install/APPS/12.1.0/appsutil/java/xmlparserv2.jar:/u01/install/APPS/12.1.0/appsutil/java:/u01/install/APPS/12.1.0/jlib/netcfg.jar:/u01/install/APPS/12.1.0/jlib/ldapjclnt12.jar

        Using Context file          : /u01/install/APPS/12.1.0/appsutil/out/EBSDB_apps/06120427/EBSDB_apps.xml

Context Value Management will now update the test Context file

        Updating test Context file...COMPLETED

        [ Test mode ]

        No uploading of Context File and its templates to database.

Updating rdbms version in Context file to db121

Updating rdbms type in Context file to 64 bits

Testing templates from ORACLE_HOME ...

Differences text report is located at: /u01/install/APPS/12.1.0/appsutil/out/EBSDB_apps/06120427/cfgcheck.txt

        Generating Profile Option differences report...COMPLETED

Differences text report for the Database is located at: /u01/install/APPS/12.1.0/appsutil/out/EBSDB_apps/06120427/ProfileReport.txt

        Generating File System differences report......COMPLETED

Differences html report is located at: /u01/install/APPS/12.1.0/appsutil/out/EBSDB_apps/06120427/cfgcheck.html

Differences Zip report is located at: /u01/install/APPS/12.1.0/appsutil/out/EBSDB_apps/06120427/ADXcfgcheck.zip

AutoConfig completed successfully.

[oracle@apps bin]$

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

>>>> Application Tier

> Source the run file system environment

> Navigate to $AD_TOP/bin

> Execute the command - ./adchkcfg.sh contextfile=$CONTEXT_FILE

The script generates both HTML and TEXT reports in $INST_TOP/admin/out/MONDDHHMI/ directory on Application Tier

[oracle@apps ~]$ cd /u01/install/APPS [oracle@apps APPS]$ . EBSapps.env RUN E-Business Suite Environment Information ---------------------------------------- RUN File System : /u01/install/APPS/fs1/EBSapps/appl PATCH File System : /u01/install/APPS/fs2/EBSapps/appl Non-Editioned File System : /u01/install/APPS/fs_ne DB Host: apps.example.com Service/SID: EBSDB Sourcing the RUN File System ... [oracle@apps APPS]$ cd $AD_TOP/bin [oracle@apps bin]$ ./adchkcfg.sh contextfile=$CONTEXT_FILE Enter the APPS password: The log file for this session is located at: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/log/06120432/adconfig.log wlsDomainName: EBS_domain WLS Domain Name is VALID. AutoConfig is running in test mode and building diffs... AutoConfig will consider the custom templates if present. Using CONFIG_HOME location : /u01/install/APPS/fs1/inst/apps/EBSDB_apps Classpath : /u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/install/APPS/fs1/EBSapps/comn/java/classes Using Context file : /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/out/06120432/EBSDB_apps.xml Context Value Management will now update the test Context file Updating test Context file...COMPLETED [ Test mode ] No uploading of Context File and its templates to database. Testing templates from all of the product tops... Testing AD_TOP........COMPLETED Testing FND_TOP.......COMPLETED Testing ICX_TOP.......COMPLETED Testing MSC_TOP.......COMPLETED Testing IEO_TOP.......COMPLETED Testing BIS_TOP.......COMPLETED Testing CZ_TOP........COMPLETED Testing SHT_TOP.......COMPLETED Testing AMS_TOP.......COMPLETED Testing CCT_TOP.......COMPLETED Testing WSH_TOP.......COMPLETED Testing CLN_TOP.......COMPLETED Testing OKE_TOP.......COMPLETED Testing OKL_TOP.......COMPLETED Testing OKS_TOP.......COMPLETED Testing CSF_TOP.......COMPLETED Testing IBY_TOP.......COMPLETED Testing JTF_TOP.......COMPLETED Testing MWA_TOP.......COMPLETED Testing CN_TOP........COMPLETED Testing CSI_TOP.......COMPLETED Testing WIP_TOP.......COMPLETED Testing CSE_TOP.......COMPLETED Testing EAM_TOP.......COMPLETED Testing GMF_TOP.......COMPLETED Testing PON_TOP.......COMPLETED Testing FTE_TOP.......COMPLETED Testing ONT_TOP.......COMPLETED Testing AR_TOP........COMPLETED Testing AHL_TOP.......COMPLETED Testing IES_TOP.......COMPLETED Testing OZF_TOP.......COMPLETED Testing CSD_TOP.......COMPLETED Testing IGC_TOP.......COMPLETED Differences text report is located at: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/out/06120432/cfgcheck.txt Generating Profile Option differences report...COMPLETED Differences text report for the Database is located at: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/out/06120432/ProfileReport.txt Generating File System differences report......COMPLETED Differences html report is located at: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/out/06120432/cfgcheck.html Differences Zip report is located at: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/out/06120432/ADXcfgcheck.zip AutoConfig completed successfully. [oracle@apps bin]$

Background Media Recovery terminated with ORA-1274 after adding a Datafile

Symptom : Recently I got error in alert log of DR database like :-

Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /ora/visprod/home/product/11.2.0/log/diag/rdbms/visprodr/VISPRODR/trace/VISPRODR_pr00_25255.trc:
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
ORA-01157: cannot identify/lock data file 286 - see DBWR trace file
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
Slave exiting with ORA-1111 exception
Errors in file /ora/visprod/home/product/11.2.0/log/diag/rdbms/visprodr/VISPRODR/trace/VISPRODR_pr00_25255.trc:
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
ORA-01157: cannot identify/lock data file 286 - see DBWR trace file
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (VISPRODR)

Reason :- Datafile was added on Prod on one mount point, on DR site there was no space left out so newly added datafile went to $ORACLE_HOME/dbs/UNNAMED00286 name. This stop archivelog to apply on DR database and if we try to start MRP process, it failed to start.

Solution: - 

1. Shutdown DR database.

2. Change pfile parameter "Standy_file_management" and set to MANUAL.

3. Mount DR with pfile.

4.  alter database create datafile '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286' as 'New FileName';

5.Shutdown DR database. 

6. Change again "Standy_file_management" to Auto

7. Start DR on Mount. 

8. Start MRP process. 

Now Archive will start applying.

How to compile invalid objects

UTL_RECOMP

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects. Although invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.

Parallel recompilation can exploit multiple CPUs to reduce the time taken to recompile invalid objects. The degree of parallelism is specified by the first argument to RECOMP_PARALLEL Procedure.

In general, a parallelism setting of one thread for each available CPU provides a good initial setting. However, please note that the process of recompiling an invalid object writes a significant amount of data to system tables and is fairly I/O intensive. A slow disk system may be a significant bottleneck and limit speedups available from a higher degree of parallelism.

Examples

Recompile all objects sequentially:

EXECUTE UTL_RECOMP.RECOMP_SERIAL();

Recompile objects in schema SCOTT sequentially:

EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');

Recompile all objects using 4 parallel threads:

EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:

EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');

Another simple method is to use utlrp.sql command located in $ORACLE_HOME/rdbms/admin

 

sqlplus "/as sysdba"

sqlplus >@$ORACLE_HOME/rdbms/admin/utlrp.sql;