OC4J Components exiting with status 204,3 and 150

R12-oafm, forms and oacore exiting with status 204, status 3 and status 150 after IP address change:

Recently, I had gone through a problem while, I changed the server IP and started the oracle application with adstrtal.sh script.

adstrtal.sh: Exiting with status 3 and adoafmctl.sh: exiting with status 204

Solution to fix this issue:

Problem: IP address is present in OC4J lock files, Once IP address is changed - lock files contain the wrong IP address which causes the problem to fail OC4J.

Steps to be followed to exit with 0:

1. Open a new session and set the environment with apps user.
2. Go to $ADMIN_SCRIPTS_HOME ( cd $INST_TOP/ADMIN/SCRIPTS)
3. adopmnctl.sh stop
4. Verify the staus once: ps -ef grep grep opmn
5. Delete the persistence directory with below commands:

• rm -r $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
• rm -r $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
• rm -r $INST_TOP/ora/10.1.3/j2ee/forms/persistence/* 

6. adopmnctl.sh start
7. Check if the issue has been resolved: adapcctl.sh status

Change or switch UNDO tablespace

Change or switch UNDO tablespace in Oracle database and issues of switching UNDO Tablespace (How to increase size of UNDO tablespace?):

Before I proceed with the topic, I would like to have an overview first. The Undo tablespace is used for several purposes: ROLLBACK, READ CONSISTENCY and FLASHBACK technology.

I have already discussed about Undo Tablespace in my earlier post and if you want to flashback, here is the URL:

http://appsdbaraz.blogspot.com/2013/12/tablespaces.html

It is very important to know, how to switch Undo Tablesspaces as it is required when we need to increase the size of undo tablespace. But sometimes, we face issues if we try to drop the old undo tablespace after switching to new undo tablespace and it says "Tablespace is in use". This is because of  pending transactions in your old undo tablespace and get "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.

How to switch to a new UNDO tablespace and drop the old one in oracle database?

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS01

SQL>

We can see the current undo tablespace as UNDOTBS01 and its displayed by initialization parameter undo_tablespace. Leave this session as its and cosider it as session1.

Start a new session (session2) and log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

SQL>update emp1 set salary = salary + 1000 where empno=83795;

1 row updated.

We have started a transaction by updating on emp1 table and undo data is written to a segment in the UNDOTBS01 tablespace. Now leave session2 as it is and go to session2 as sysdba console. Just to notice, We have not issued COMMIT or ROLLBACK,

UNDOTBS02 is a new UNDO tablespace created:

SQL> CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/u01/apps/oracle/db/UNDOTBS02.dbf'

         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

Switching the database to the new UNDO tablespace UNDOTBS02:

SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH;

System altered.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

-- Try to drop the tablespace but failed.

SQL>

We have chaged the undo tablespace to UNDOTBS02 with alter system set undo_tablespace=UNDOTBS02 and any new transaction will go into new undo tablespace i.e. UNDOTBS02. But undo transaction for already pending transaction(with session2 - commit or rollback was not issued) is still in the old undo tablespace with status as PENDING OFFLINE. Until or unless those data are there, it is not possible to drop the existing tablespace.

We can get the status of undo segment and name of UNDOTBS01 tablespace from rollname, rollstat , dba_segments tables.

We can get the SID and SERIAL# and go to the user and request to end the transaction by issuing ROLLBACK or COMMIT. If in case this is not possible (user will not be available for long time), we may go ahead and kill the session to release the old UNDO segment in UNDOTBS01 tablespace.

SQL> alter system kill session '$SID,$SERIAL#';

Now we can go ahead and drop UNDOTBS01 tablespace.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

If UNDO_RETENTION time is not exceeded, then we will get above error and need to wait till the UNDO_RETENTION time is over. After that we can drop this UNDO tablespace easily. By default UNDO_RETENTION is 15 minutes or 900 seconds.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Control File in Database

Control file in oracle database:

As I have already discussed about the database startup process and steps involved for database to open. You can also get the same from below URL:

http://appsdbaraz.blogspot.com/2013/12/database-startup-options.html

While we start database, first it uses the parameter file and nomount the database, then using control file mount the database and after checking if recovery is required and performs the required step and then opens the database.

Control file is compulsory for every database to mount. Control file is a binary file which records the physical structure of database. While we think of control file one question arises in our mind i.e.

What control file contains?

Control file contains information like:

• The Database name.
• Names and locations of data files and redo log files.
• The timestamp of database creation.
• The current log sequence number.
• Checkpoint information.
• Backup set and backup pieces information.

Control file is the critical file, which is required for database to startup. So to avoid loss of control file, we manage to have multiple copies of control file.

When we create database at least one control file is created with default values and for some operating systems, by default multiple copies of control files are created.

How to get the location of control file?

SQL> Show Parameter Control_Files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string     /u02/oracle/prod/control01.ctl
                                                      ,  /u02/oracle/prod/control02.ctl
                                                      ,  /u02/oracle/prod/control03.ctl

             
Create control file:

Creating Additional Copies, Renaming, and Relocating Control Files:

Multiplexing copy of control file or renaming a control file steps:

1.     Shut down the database.

2.     Using operating system command, copy the existing control file to new location.

3.     Edit the CONTROL_FILES parameter in the initialization parameter file to add new control file name or to change the existing control file name.

4.     Restart the database.

Manually Creating New Control Files:

Create Control file statement can be used to create a new control file for a database and also we can use the parameter values as per our requirement. We can give the database name whatever we want.

CREATE CONTROLFILE
 SET DATABASE prod
 LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
 '/u01/oracle/prod/redo01_02.log'),
 GROUP 2 ('/u01/oracle/prod/redo02_01.log',
 '/u01/oracle/prod/redo02_02.log'),
 GROUP 3 ('/u01/oracle/prod/redo03_01.log',
 '/u01/oracle/prod/redo03_02.log')
 RESETLOGS
 DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
 '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
 '/u01/oracle/prod/users01.dbs' SIZE 5M,
 '/u01/oracle/prod/temp01.dbs' SIZE 5M
 MAXLOGFILES 50
 MAXLOGMEMBERS 3
 MAXLOGHISTORY 400
 MAXDATAFILES 200
 MAXINSTANCES 6
 ARCHIVELOG;

  

Back Up Control Files:

Back up of control file is very important and It happens by default when you change your physical structure of your database. Some examples of structure changes are:

1.     Adding, dropping, or renaming datafiles.

2.     Adding or dropping a tablespace.

3.     Altering the read-write state of the tablespace.

4.     Adding or dropping redo log files or groups.

Back up control file using the below command from SQL prompt and stores it into binary format:
SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

We can also backup our control file to trace location:

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

When to create control file?

1. When you have lost your all control files.
2. When you want to rename your database.

Dropping Control Files:

Sometimes the location of cotrol file is changed, in such situation need to drop the existing control file and use the correct one. Control file can be dropped or renamed very easily as below steps.

1.     Edit the CONTROL_FILES parameter in the initialization parameter file to delete old control file name.

2.     Shut down the database.

3.     Start the database.

Reusable section of control file (control_file_record_keep_time) :

Control file has reusable section and it tracks ARCHIVE logs and BACKUP sets. control_file_record_keep_time is the initialization parameter which determines the reussable data to be stored for number of days. Default value of control_file_record_keep_time is 7 days. It can also be increased to any value whatever we want.

We can check the reusable period from SQL prompt as below:
SQL> show parameter Keep_time;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

Changing the value of control_file_record_keep_time using SQL command:
SQL> alter system set control_file_record_keep_time=14 scope=spfile;

System altered

Now RMAN can keep records of BACKUPS and ARCHIVE logs for 14 days.

Also need to configure control file autobackup on.
CONFIGURE CONTROLFILE AUTOBACKUP ON;