Start an Instance Without a Parameter File

The following starts an instance without using a parameter file, then runs RESTORE SPFILE:

SET DBID 1447326980;
STARTUP FORCE NOMOUNT; # RMAN starts instance with dummy parameter file
RESTORE SPFILE FROM AUTOBACKUP; # restore a server parameter file
STARTUP FORCE; # restart instance with restored server parameter file

adoafmctl.sh: exiting with status 152

You are running adoafmctl.sh version 120.6.12000000.3
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 152

Solution:
Clear the TLD cache:
- stop all middle tier services
- Delete/backup files this file:
$COMMON_TOP/_TldCache
- start all middle tier services

What happening when executing adpreclone.pl in DB and Apps Tier?

adpreclone.pl - This is the preparation phase, will collects information about the source system, creates a cloning stage area, and generates templates and drivers. All of these are to reconfigure the instance on a Target machine.
Preclone will do the following:

Convert Symbolic links
All symbolic links pointing to a static path will be converted into relative paths

Create templates
Any files under the $ORACLE_HOME that contain system specific information, will be replicated and converted into a template. These templates are placed into the $ORACLE_HOME/appsutil/template directory.

Create driver(s)
A driver file, relating to these new templates is created called instconf.drv. This contains a list of all the templates and their locations, and the destination configuration files that these templates will create.
This driver file is called instconf.drv and is placed into directory
$ORACLE_HOME/appsutil/driver

Create Stage area
A clone stage is created containing the required java code and scripts to reconfigure the instace on the Target machine

Rapid Clone stage area:
dbTier : $ORACLE_HOME/appsutil/clone
appsTier(s) - $COMMON_TOP/clone

The stage area(s) consist of the following directories:-
 jre used to run the java code on the Target machine.
 bin contains the RapidClone scripts that can be run on the Target machine:-

•    adclone.pl is the main cloning script
•    adcfgclone.pl is used to configure the Target system, this calls adclone.pl
•    adclonectx.pl is used to clone a Source XML file manually
•    adaddnode.pl is used to add a new node to the Patch History tables
•    adchkutl.sh checks for existence of require O/S utils, cc, make, ar and ld

 jlib contains all the Rapid Clone java code, jdbc libraries etc
 context contains templates used for a Target XML file
 data (Database Tier only) contains the driver file, and templates used to generate the control file SQL script
 adcrdb.zip contains the template and list of datafiles on the Source
 addbhomsrc.xml contains information on the datafile mount points of the Source
 appl (Applications Tier only) this is used when merging appltops, i.e Multi-node to Single node cloning

Executing adpreclone.pl will create a log file:-
Rapid Clone:
dbTier : $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/StageDBTier_xxxxxx.log
appsTier : $APPL_TOP/admin/$CONTEXT_NAME/log/StageAppsTier_xxxxxx.log

Once this adpreclone.pl step has been completed successfully, all the java .class files under the following directories should be identical to those under $JAVA_TOP/oracle :

RDBMS $ORACLE_HOME/appsutil/java/oracle
RDBMS $ORACLE_HOME/appsutil/clone/jlib/java/oracle
$COMMON_TOP/clone/jlib/java/oracle

Oracle Apps Clone: Difference between dbTechStack and dbTier

perl adcfgclone.pl dbTechStack
Will configure the ORACLE_HOME on the target database tier node + recreate the controlfiles.
This is specially used in case of standby database and/or hot backups.  

perl adcfgclone.pl dbTechStack

Will configure the ORACLE_HOME on the target database tier node only. When running this command you will have to recreate the controlfiles manually.

Converting a RAC database to single instance database – Oracle RAC 10g

I tried the following steps in test instance an it worked for me.

 

1) Stop database using srvctl

srvctl stop database -d prod

 

2) Remove the database entry from crs -- Remove instances then the database

$ srvctl remove instance -d prod -i prod1
Remove instance prod1 from the database prod? (y/[n]) y
$ srvctl remove instance -d prod -i prod2
Remove instance prod2 from the database prod? (y/[n]) y
$ srvctl remove database -d prod
Remove the database prod? (y/[n]) y

3) Start the database on first instance

SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             188746216 bytes
Database Buffers          645922816 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> alter system set cluster_database_instances=1 scope=spfile;
System altered.
SQL> alter database disable thread 2;
Database altered.

 

4) Delete the unwanted thread and redo logfiles

SQL> select thread#, group# from v$log;
THREAD#     GROUP#
---------- ----------
1          1
1          2
1          3
2          4
2          5
2          6
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.

 

5) Drop the unwanted undo tablespace

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

6) Create pfile from spfile

SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

7) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

 

Find the FND_USER that lock table

SELECT c.owner ,c.object_name,c.object_type,
       fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time,
      vs.module,vs.machine ,vs.osuser
      ,vlocked.oracle_username,vs.sid,vp.pid
      ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr
      ,vs.audsid ,vs.process

FROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked
    ,v$process vp ,v$session vs,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('AP_INVOICES_ALL') || '%'
AND nvl(vs.status ,'XX') != 'KILLED';

How to rename oracle database

To rename an Oracle database we use newid utility as follow:

• STEP 1: Backup the database.
   
• STEP 2: Mount the database after a clean shutdown:

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT

• STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

  nid TARGET=sys/password@OLDNAME DBNAME=NEWNAME

  Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:

  C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
  DBNEWID: Release 9.2.0.3.0 - Production
  Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
  
  Connected to database TSH1 (DBID=1024166118)
  
  Control Files in database:
      C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
      C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
      C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
  
  Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y
  
  Proceeding with operation
  Changing database ID from 1024166118 to 1317278975
  Changing database name from TSH1 to TSH2
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
      Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
      Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
      Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
      Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name
  Database name changed to TSH2.
  Modify parameter file and generate a new password file before restarting.
  Database ID for database TSH2 changed to 1317278975.
  All previous backups and archived redo logs for this database are unusable.
  Shut down database and open with RESETLOGS option.
  Succesfully changed database name and ID.
  DBNEWID - Completed succesfully.

• STEP 4: Shutdown the database:

  SHUTDOWN IMMEDIATE

• STEP 5: Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

  STARTUP MOUNT
  ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
  SHUTDOWN IMMEDIATE

• STEP 6: Create a new password file:

  orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

• STEP 7: Rename the SPFILE to match the new DBNAME.
   
• STEP 8: If you are using Windows you must recreate the service so the correct name and parameter file are used:
   

  oradim -delete -sid TSH1
  oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora

  If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
   

  ORACLE_SID=NEWNAME; export ORACLE_SID

• STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
   

  lsnrctl reload

• STEP 10: Open the database with RESETLOGS:
   

  STARTUP MOUNT
  ALTER DATABASE OPEN RESETLOGS;

• STEP 11: Backup the database.