Clone database using cold backup

1) Create one database ‘PROD’ using dbca

[oracle@oracle10g ~]$ export ORACLE_SID=prod
[oracle@oracle10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 19:38:02 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

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

OPEN_MODE  LOG_MODE     NAME
---------- ------------ ---------
READ WRITE ARCHIVELOG   PROD

2) Create a pfile for ‘CLONE’ database

SQL> create pfile='$ORACLE_HOME/dbs/initclone.ora from spfile;

File created.

3) Make the necessary changes in pfile of ‘CLONE’ database as shown below

clone.__db_cache_size=188743680
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=83886080
clone.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/clone/adump'
*.background_dump_dest='/u01/app/oracle/admin/clone/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/clone/control01.ctl','/u01/app/oracle/oradata/clone/control02.ctl','/u01/app/oracle/oradata/clone/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/clone/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='clone'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/clone/udump'

4) Fire the following command to backup ‘PROD’ database controlfile to trace file.

SQL> alter database backup controlfile to trace;

After firing the above command open the latest trace file generated in udump folder.Copy the following content and paste it in a text file.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/prod/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/prod/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/prod/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/prod/system01.dbf',
  '/u01/app/oracle/oradata/prod/undotbs01.dbf',
  '/u01/app/oracle/oradata/prod/sysaux01.dbf',
  '/u01/app/oracle/oradata/prod/users01.dbf',
  '/u01/app/oracle/oradata/prod/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

Change the name of the text file as control.sql.
Open the control.sql file and make the necessary changes

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/clone/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/clone/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/clone/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/clone/system01.dbf',
  '/u01/app/oracle/oradata/clone/undotbs01.dbf',
  '/u01/app/oracle/oradata/clone/sysaux01.dbf',
  '/u01/app/oracle/oradata/clone/users01.dbf',
  '/u01/app/oracle/oradata/clone/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

5) Shutdown the ‘PROD’ database

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6) Make necessary directories for clone database

$mkdir /u01/app/oracle/oradata/clone

$mkdir /u01/app/oracle/admin/clone

$mkdir /u01/app/oracle/admin/clone/adump

$mkdir /u01/app/oracle/admin/clone/bdump

$mkdir /u01/app/oracle/admin/clone/cdump

$mkdir /u01/app/oracle/admin/clone/udump

7) Copy all the datafiles and redolog files from ‘PROD’ database directory to ‘CLONE’ database directory

8) Fire following command to connect to CLONE database

[oracle@oracle10g ~]$ export ORACLE_SID=clone
[oracle@oracle10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 20:38:58 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

9) Run the control.sql script on clone database

SQL> @/home/oracle/Desktop/control.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes

Control file created.

10) Fire the following command to open the database

SQL> alter database open resetlogs;

Database altered.

APPS DBA INTERVIEW QUESTIONS

Q. How would you know that your applications setup is a using shared APPL_TOP configuration ?

- If you apply a patch, it should be visible on all nodes (APPL_TOP) which are part of shared APPL_TOP configuration.
- Since APPL_TOP is on a shared disk, create file in APPL_TOP from one node, it should be visible from other nodes.

To Know more on shared APPL_TOP check metalink note
243880.1  Shared APPL_TOP FAQ
384248.1  Sharing The Application Tier File System in Oracle E-Business Suite Release 12
233428.1  Sharing the Application Tier File System in Oracle Applications 11i

Q. What is difference between shared APPL_TOP and shared Application Tier ?
- In shared APPL_TOP, only APPL_TOP & COMMON_TOP are shared across multiple middle tier where as in Shared Application Tier ORACLE_HOME (Middle Tier 806 & iAS directory) are also shared except configuration files (iAS).

Q. How would you know about which servers are running on a particular node ?
i) Check value of variable, isConc, isWeb, isForms, isAdmin in CONTEXT FILE
ii) Control scripts – only the services which are running from particular node will have the corresponding control scripts installed on that node (This answer is partially right, depending on AD & FND version).
iii) Check FND_NODES table

Q. Adsplice – I have a 3 node installation: 1. Forms 2. Web 3. CM/Admin/Reports
and DB which node I will run the adsplice on, to install a new product ?
– You must run adsplice on all nodes (APPL_TOPs) so that application utilities recognize new product.

Q. There are four Oracle Databases running on a mahine. How would you check the location of their Oracle Homes ?
- Check entry in oratab file
– If you know any other way to find out update it as comment

Q. How would you check if the tablespace is in backup mode ?
- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;

Q. When Database is up, listener is up. listener.ora and tnsnames.ora both are configured properly, still client is not being able to make a connection to the database. What may be the possible issues ?
– Firewall blocking db port between client and server
– Authentication restricted in sqlnet.ora (Client allowed from only selected machines)

Q. What is “Custom TOP” in apps ?
- Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP

Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?– System picks up dbc file based on profile option value “Applications Database ID“

Q. Why do we get error when we run “opmnctl status” from “$INST_TOP/ ora/ 10.1.3/ opmn/ bin” where as $ADMIN_SCRIPTS_HOME/adopmnctl.sh works ?
For those who are new to Oracle Apps R12 or OPMN

ORACLE_HOME in application tier in Applications R12
Similar to Oracle Applications 11i, there are two ORACLE_HOMEs (but of version 10g) on application tier (three in total including database)
i) 10.1.3 (10g R3) for Web-Apps Tier (HTTP, OC4J – oacore, oafm, forms)
ii) 10.1.2 (10g R2) for forms & reports server

For technical differences between 11i & R12 click here

What is opmnctl/adopmnctl ?

OPMN - Oracle Process Manager & Notification Server is component in Oracle Application Server (10g) to manage (start/stop/status) Application Server components (HTTP, OC4J)

adopmnctl - is wrapper around opmnctl to manage application server (10g R3 & 10g R3) in Oracle Applications (EBS) R12 . More information here

.
What is INST_TOP in Oracle Apps ?
INST_TOP denotes new TOP in Apps R12 INSTANCE_HOME, more informationhere . INSTANCE_HOME contains scripts, log and configuration files .

.  

Now back to question Why do we get error when we run “opmnctl status” from “$INST_TOP/ ora/ 10.1.3/ opmn/ bin” although we get output on running$ADMIN_SCRIPTS_HOME/ adopmnctl.sh
When you run opmnctl status from $INST_TOP/ora/10.1.3/opmn/bin you get error like below

$ ./opmnctl status
Error reading opmn.xml
Message:{$INST_TOP/ora/10.1.2/opmn/conf/opmn.xml (No such file or directory)}
Filename:{$INST_TOP/ora/10.1.2/opmn/conf/opmn.xml}
FileContent:{}

.

This is because when you run “opmnctl status” from $INST_TOP/ora/10.1.3/opmn/bin,opmnctl executable picks up OPMN config file (opmn.xml) from $ORACLE_CONFIG_HOME/opmn/conf/opmn.xml and $ORACLE_CONFIG_HOME by default is set to 10.1.2  ORACLE_HOME .
             It is still possible to display status using $INST_TOP/ora/10.1.3/opmn/bin/status but first set $ORACLE_CONFIG_HOME to 10.1.3  (This can be done by executing 10.1.3 environment file i.e. $INST_TOP/ora/10.1.3/$SID_$hostname.env )

Why don’t you get above error by running $ADMIN_SCRIPTS_HOME/ adopmnctl.sh ? adopmnctl.sh calls $INST_TOP/ora/10.1.3/$SID_$hostname.env and sets $ORACLE_HOME and $ORACLE_CONFIG_HOME to 10.1.3 related executable and configuration files. adopmnctl.sh is correct script to manage services (start/stop/status) controlled by OPMN in R12.

MULTI NODE INSTALLATION OF ORACLE APPS(R12)

Once unzipped you need to navigate to 

startCD/Disk1/rapidwiz directory.

Here you will be able to see rapidwiz executable 

# ./rapidwiz

Select the Oracle Applications Release 12.1.1

Select suitable option if you need to download updates

Select [New Configuration]

Select the port pool, I have chosen the default i.e. 0, for my env.

Enter DB host info along with user and group

Select [Suite Licensing]

Install the languages if you need multi-lingual support

Enter Application Node info along with owner and group

Review the info you entered for hosts. You can also add 

Additional Nodes here for Application tier file system

Now the OUI will perform the pre-checks before the actual installation kicks in

Review the Validation Report. check the failed ones and click 

on Retry

Installation will kick in, keep an eye on it. It will usually take around couple of hours depending on the speed of your machine

Once installation completes, the post install validation box will 

reappear. Make sure everything is green before going further

At the end, click on Finish to end the installation of DB tier.

Next step is to install the application tier on appsnode host.

Before you start the install of the App tier you need to copy the 

config file from DB tier to apps tier.

To do this there are two ways

    1. Copy <sid>_<host>.xml file aka context file to appsnode

    2. start the install by issuing ./rapidwiz and provide the DB 

details to copy the config from DB


One can copy context file as follows

[oracle@appsdbnode appsutil]$ cd 

/u01/app/oracle/visr12/db/tech_st/11.1.0/appsutil

[oracle@appsdbnode appsutil]$ scp conf_visr12.txt 

 appsnode:/home/oracle/

conf_visr12.txt                                                                               

   100%   13KB  12.8KB/s   00:00

click on [Saved configuration] option, and enter the path 

copied 

context file 

check the validation report to make sure everything is healthy.

Click on [Next]

Review the progress of the installation

After install was finished, my post install checks failed as my 

HTTP server failed to start hence all other dependencies failed

Cause - 
After a bit of research I found out that the start failed due to the 

fact it failed to locate on library component and fix is the create 

the link for that missing component 




Solution -

[root@appsnode ~]# ln -s /usr/lib/libgdbm.so.2.0.0 

/usr/lib/libdb.so.2

[oracle@appsnode scripts]$ adstrtal.sh  apps/apps

[oracle@appsnode scripts]$ adopmnctl.sh status

You are running adopmnctl.sh version 120.6

Checking status of OPMN managed processes...

Processes in Instance: 

visr12_appsnode.appsnode.localdomain

---------------------------------+--------------------+---------+------------
----------
ias-component                              | process-type       |  pid         | status
---------------------------------+--------------------+---------+----------------------
OC4JGroup:default_group          | OC4J:oafm           |   31135 | 

Alive

OC4JGroup:default_group          | OC4J:forms          |   31070 | 

Alive

OC4JGroup:default_group          | OC4J:oacore        |   30984 | 

Alive

HTTP_Server                                   | HTTP_Server        |   

30933 | Alive



After manually start the opmn stack, I click on [Retry] on 

validation page of installer and there you go. Everything came 

up

click on [Finish] to end the install

Post install, I tried to login to Apps console using following 

URL. 

URL Format : http://<hostname.domain>:<port_pool_no>

URL - http://appsnode.localdomain:8000

use default credentials - sysadmin/sysadmin to login console

That's it. You have now successfully installed the EBS R12.1.1 

successfully.

Next -

Apply 12.1.2 or 12.1.3 RUP to Apps R12 (12.1.1)

RECREATING CONTROL FILES

A. Create controlfiles when
1. You have lost all your control files.
2. When you want to rename a database name (db_name)

Q. How to create control files.

You need a create controlfile script for recreating control files.

Code:

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:04:00 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 11.2.0.2.0 - Production

SQL> alter database backup controlfile to trace;

Database altered.

SQL>

This will create a trace file in the /u01/app/oracle/diag/rdbms/db11g/DB11G/trace

/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/orcl_ora_2052.trc

Code:

CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/redo03.log'  SIZE 100M BLOCKSIZE 512 
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/users01.dbf'
CHARACTER SET AL32UTF8
;   

and rename it to ctl.sql


Then startup the database in nomount mode and run the ctl.sql file as sys as sysdba user

Code:

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:25:14 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area   80812648 bytes
Fixed Size                   453224 bytes
Variable Size              54525952 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> @E:\oracle\admin\ORCL\udump\ctl.sql

Control file created.


Database altered.

SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL03.CTL

To rename the database change reuse to set in the create control file script as shown below

Code:Code:

CREATE CONTROLFILE SET DATABASE "DB11G" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/redo03.log'  SIZE 100M BLOCKSIZE 512 
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/users01.dbf'
CHARACTER SET AL32UTF8
;