Wednesday, 21 November 2012

Oracle apps: Cancel a concurrent request manually database side

First get the sid and serial# from the v$session view:
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 983785
AND a.phase_code = 'R';

Issue the kill session:
alter system kill session '1143,7' immediate;

Update the concurrent request status:
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE request_id = '983785';

Monday, 19 November 2012

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

We will write about resolving the Primary/Standby log gap in case of we deleted some archive
log files from primary. Suppose that we don’t have the backup of the deleted archive files.
Normally we (DBAs) should not allow such a situation but such a situation can happen to us.
In this case,  we need to learn the current SCN number of Primary and standby databases.

1- let’s learn current SCN number with the following query on the Primary.

SQL> select current_scn from v$database;


2- let’s learn current SCN number with the following query on the Standby

SQL> select current_scn from v$database;


using the function scn_to_timestamp(SCN_NUMBER) you can check the time difference
between primary and standby.

3- Stop apply process on the Standby database.

SQL> alter database recover managed standby database cancel;

4- Shutdown the Standby database.

SQL> shutdown immediate;

5- Take incremental backup from the latest SCN number of the Standby database on the
Primary database. And copy backup to the standby server.

RMAN> backup incremental from scn 1289359962 database;

# scp /backup_ISTANBUL/dun52q66_1_1 oracle@

6- Create new standby control file on the Primary database. And copy this file to standby server.

SQL> alter database create standby controlfile as ‘/oracle/ora11g/standby.ctl’;

# scp /oracle/ora11g/standby.ctl oracle@

7- Open the Standby database on NOMOUNT state to learn control files location.

SQL> startup nomount

SQL> show parameter control_files

8- Replace new standby control file with old files.

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data1/control01.ctl

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data2/control02.ctl

9- Open the Standby database on MOUNT state.

SQL> alter database mount standby database;

10- Connect to the RMAN and register backup to catalog.

# rman target /

RMAN> catalog start with ‘/oracle/ora11g’;

It will ask for confirmation. Click “y” .

11- Now, you can recover the Standby database. Start recover database.

RMAN> recover database;

When recover of database is finished, it searches the latest archive file. And it
gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start
apply process on the standby database.

SQL> alter database recover managed standby database disconnect from session;

We solved the Primary/Standby log gap with RMAN incremental backup . When we faced with
such a situation we don’t need to think about re-installing standby database.
Because time is very valuable for us.

Reference: Talip Hakan Ozturk's

Number of Visitors