Steps to clone Oracle Applications database from PROD to TEST

The following is a brief Guideline on steps to follow to clone you Oracle Applications Database from PROD to TEST

• log In to PROD Server as applprod user
• go to /applprod/prodappl
• run APPSORA.env
• sqlplus system/manager
• SQL> alter database backup controlfile to trace;
• This will generate a trace file in $ORACLE_HOME//admin/_/udump/*.trc
• Go to above directory
• Run ls – alt
• Find out latest trace file and edit the trace file
  • SID to desired SID (PROD to TEST)
  • Change dbf path accordingly.
• Shutdown database (Shutdown normal both production & test server by using prod_stop.sh , your PROD stop script, and test_stop.sh your TEST stop script, respectively )
• Now ftp to your server as applprod user and download the file from PROD server to Test server in /appltest directory .
• Rename /appltest/testdata directory to /appltest/testdatabk
• Create a new testdata directory in /appltest
• Copy all datafiles, control file etc.of to new /appltest/testdata from PROD
  • (E.g rcp applprod @ ebsnode2:/applprod/proddata/*.dbf) and rename cntrl*.dbf to cntrl*.bak on test server
• Run TEST instance .env file from /appltest/testdb/9.2.0 .
• Now run the edited trace file (this will recreate the TEST database with PROD configuration) (sqlplus /nolog @edited script.sql)
• This will start up TEST oracle database

Change profile_option_values from FND_profile_option_values table

Select * from fnd_concurrent_queues
Where control_code=’N’
Update fnd_concurrent_queues
set target_node=NULL
Table ICX_Parameters set session_cookie= null

Clear concurrent Manager table from toad by using the following commands:

UPDATE fnd_concurrent_processes
SET process_status_code = ‘K’
WHERE process_status_code not in (‘K’, ‘S’);

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in (‘E’, ‘R’, ‘X’) AND control_code IS NOT NULL;

UPDATE fnd_concurrent_queues
SET target_node = null;

UPDATE fnd_concurrent_requests
SET phase_code = ‘C’, status_code = ‘E’
WHERE status_code =’T’ OR phase_code = ‘R’

• Copy the Server_ID from $FND_TOP/secure/ebsnode1_test.dbc and update the server_id column of FND_NODES table with that. Also change the NODE_NAME = EBSNODE1
• Run the . APPSORA.env from /appltest/testappl directory
• Now run following Java commands from command prompt:

java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/TEST_ebsnode1/test.dbc
java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1.<>_test.dbc
java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1_test.dbc

• Shutdown the Oracle database.
• Start application by using test_start.sh from /appltest dir
• If anything goes wrong check at http://:8000/OA_HTML/jsp/fnd/aoljtest.jsp
• Login to Oracle Applications
• Change Profile options from Application –
• System Administrator(Responsibility) Profile-> System-> concurrent: GSM enabled = ‘N’

Oracle account: SYSTEM, SYS, SYSMAN,...

SYS
The SYS user owns all base tables and user-accessable view of the data dictionary (Oracle configuration information). No Oracle user should ever alter (update, delete, or insert) any rows or schema objects conatained in the SYS schema, because such activity can compromise data integrety. The security administrator must keep strict control of this central account.

SYSTEM
The SYSTEM user is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools.

SYSMAN
The SYSMAN user represents the Enterprise Manager super admin account. This EM admin can create and modify other EM admin accounts as well as admin the database instance itself.

DBSNMP
The DBSNMP user is used by EM to monitor the database. EM uses this account to access performance stats about the database. The DBSNMP credentials sometimes referred to as the monitoring credentials.

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

SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This enables an administrator who is granted one of these privileges to connect to the database instance to start the database.

SYSDBA privileges [Oracle]

There are five operations on Oracle that require the user to have SYSDBA privileges in order to perform them:

* startup a database,
* shutdown a database,
* backup a database,
* recover a database and
* create a database

v$pwfile_users lists all users who have been granted sysdba or sysoper privileges.
The sysdba privilege can not be granted to public.

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

Startup options in Oracle

Oracle has several options for startup.

1.Standard startup

> startup;
This command actually takes Oracle through three distinct startup phases automatically which are
nomount
mount
open
, or you could also choose to explicitly step through these phases:

2.nomount phase

> startup nomount;
In the nomount phase, the database reads the spfile/pfile and starts up the Oracle Instance, but the database is not yet associated with the newly started instance. This is usually used in cases where you need to re-create the controlfile. The command to perform this is

3.mount phase

> startup mount;
> alter database mount;
In order to associate a database with the instance, the instance ''mounts" the database. This is done in the mount phase. The previously read parameter file is used to find those controlfiles, which contain the name of the data files and redo logs. The database is then mounted to allow some maintenance activities to be performed. Datafiles and redo logs are not opened when the database is in mount mode, so the database is not yet accessible by end users for normal tasks.

4.open phase

> Startup {open} {resetlogs};
> alter database open;
When Oracle opens the database in the open phase, it opens the data files and redo logs, making the database available for normal operations. Your redo logs must exist in order for the database to open. If they do not, the resetlogs command must be used to create new redo logs in the location specified in the control files.
Other Ways to Open the Database

5.Read only option.

> alter database open read only;
So that no database changes (inserts, updates, or deletes) can be performed.

6.Restrict Option

> startup restrict;
Only users with both the create session and restricted session privileges will be able to use the database.

7.Quiesce option

> alter system quiesce restrict;
> alter system unquiesce;
only the sys and system users can query the database without stopping the database and performing a subsequent startup restrict. The activities of other users continue until they become inactive.

8.Force Option

> startup force
Over time, you will run into situations where Oracle has not shutdown properly and you are unable to restart it. In these rare instances, you will need to use the force option of the startup command. This will first perform a ''shutdown abort" that forces the database to shutdown followed by a database startup.
This command actually takes Oracle through three distinct startup phases automatically which are nomount mount open, or you could also choose to explicitly step through these phases:In the nomount phase, the database reads the spfile/pfile and starts up the Oracle Instance, but the database is not yet associated with the newly started instance. This is usually used in cases where you need to re-create the controlfile. The command to perform this isIn order to associate a database with the instance, the instance ''mounts" the database. This is done in the mount phase. The previously read parameter file is used to find those controlfiles, which contain the name of the data files and redo logs. The database is then mounted to allow some maintenance activities to be performed. Datafiles and redo logs are not opened when the database is in mount mode, so the database is not yet accessible by end users for normal tasks. When Oracle opens the database in the open phase, it opens the data files and redo logs, making the database available for normal operations. Your redo logs must exist in order for the database to open. If they do not, the resetlogs command must be used to create new redo logs in the location specified in the control files.So that no database changes (inserts, updates, or deletes) can be performed.Only users with both the create session and restricted session privileges will be able to use the database.only the sys and system users can query the database without stopping the database and performing a subsequent startup restrict. The activities of other users continue until they become inactive. Over time, you will run into situations where Oracle has not shutdown properly and you are unable to restart it. In these rare instances, you will need to use the force option of the startup command. This will first perform a ''shutdown abort" that forces the database to shutdown followed by a database startup.

Basic alter database commands

alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile

ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;

alter database: ARCHIVELOG Mode Commands

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/opt/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/logfile_backup/backup_logfile.ctl';

alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4 AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS NEW;

alter database: Datafile Offline/Online

See alter database: Alter a Data File

alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER '/opt/oracle/logfiles/redo02c.rdo' to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

alter database: Mount and Open the Database

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

alter database: Move or Rename a Database File or Online Redo Log

NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move database data files.

ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;

HowTo Remove parameters from the spfile

HowTo: Remove parameters from the spfile

How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?

Answer:

Use "ALTER SYSTEM RESET ..." (For database versions 9i and up)

Syntax:

ALTER SYSTEM RESET PARAMETER SID='SID|*'
ALTER SYSTEM RESET "_TRACE_FILES_PUBLIC" SCOPE=SPFILE SID='*';

NOTE: The "SID='SID|*'" argument is REQUIRED!