Category Archives: Oracle

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Starting backup at 04-JUN-12
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/04/2012 10:36:16
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/oradata/airman/archive/1_8592_628791162.arc
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Cause: This can happen for a variety of reasons; the file has been manually moved or deleted,
the archive log destination has recently been changed,
the file has been compressed, etc

Soluution:

Your options are either to restore the missing file(s), or to perform a crosscheck.
To perform a crosscheck, run the following command from within RMAN:

change archivelog all crosscheck;

Perform a full backup of the database

Note:
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure
that it exists on disk (or tape). Those that are missing are marked as unavailable.
If you have got missing logs, this won’t bring them back. It will allow you to get past this error
and back-up the database though.

Oracle Database Physical standby Starting and Shutting down

Leave a reply

Starting Up a Physical Standby Database
========================================

1.Start and mount the physical standby database:
————————————————-

SQL> STARTUP MOUNT;

2.Start Redo Apply or real-time apply:
————————————–

To start Redo Apply, issue the following statement:
—————————————————

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To start real-time apply, issue the following statement:
——————————————————–

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Note: On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view,
which displays the standby database’s operation as MANAGED_RECOVERY for Redo Apply
and MANAGED REAL TIME APPLY for real-time apply.

Shutting Down a Physical Standby Database
==========================================

1.Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.
If the MRP0 or MRP process exists, then the standby database is applying redo.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

2.If Redo Apply is running, cancel it as shown in the following example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.Shut down the standby database.

SQL> SHUTDOWN IMMEDIATE;

ORA-09817 IBM AIX RISC System/6000 Error: 89

Leave a reply

ORA-09817 IBM AIX RISC System/6000 Error: 89

Oracle 11.2.0.2
AIX 5.3

Related Errors:-

Mon Nov 05 18:33:51 2012
ARC3 started with pid=30, OS id=954622
ARC1: Becoming the ‘no FAL’ ARCH
ARC1: Becoming the ‘no SRL’ ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file /clmst01/UAT/oracode/app/oracle/diag/rdbms/clmst/clmst/trace/clmst_ora_2514960.trc (incident=17053):
ORA-00600: internal error code, arguments: [kccugg_end], [], [], [], [], [], [], [], [], [], [], []

Errors in file /clmst01/UAT/oracode/app/oracle/diag/rdbms/clmst/clmst/trace/clmst_m000_2105586.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/clmst02/UAT/oradata/redo/redo03A.log’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory

CREATE CONTROLFILE REUSE DATABASE “CLMST” RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: ‘/clmst02/UAT/oradata/control/control01.ctl’
ORA-27044: unable to write the header block of file
IBM AIX RISC System/6000 Error: 89: Invalid file system control data detected
Additional information: 3
Additional information: -1
Additional information: 1

Cause:-
Your /clmst02/ mount point might be corrupted.

Check:-
fsck /clmst02
The current volume is: /dev/fslv00
File system is currently mounted.
Primary superblock is valid.
fsck: Performing read-only processing does not produce dependable results.
*** Phase 1 – Initial inode scan
*** Phase 2 – Process remaining directories
*** Phase 3 – Process remaining files
*** Phase 4 – Check inode allocation map
File system inode map is corrupt (NOT FIXED)
fsck: 0507-278 Cannot continue.
File system is currently mounted.
fsck: Performing read-only processing does not produce dependable results.

Fix:-
Either you can change the file location to another mount points or shutdown the database and run the fsck on /clmst02 mount point to fix the corrupted one & start the database.

Fix:-
Recreate the affected Redolog files

alter database add logfile group 3 (
‘/clmst02/UAT/oradata/redo/redo03A.log’,
‘/clmst03/UAT/oradata2/redo/redo03B.log’,
‘/clmst04/UAT/oradata3/redo/redo03C.log’) size 50m reuse;

ORA-1652

Leave a reply

The quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile and then find root cause of temp tablespace issue.

Extent – An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks.One or more extents make up segment.
Segment – A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
Temporary Tablespace – contains transient data that persists only for the duration of the session.

Quick Fix for ORA-1652
=======================

1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
—–
/u01/oradata/VIS11i/temp01.dbf TEMP

2. Check if there is any space available in temporary tablespace (segment)

SQL> SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

—-
output like

TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP 2548 2536 12

(in above case out of 2 GB only 12 MB is free)

3. Temporary fix

a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

Root Cause Analysis
===================

1. Identify temp segment usages per session

–
– Temp segment usage per session.
–
SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Identify temp segment usages per statement
–
– Temp segment usage per statement.
–
SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.

.
References/Related

•793380.1 ORA-1652 Error Troubleshooting
•258941.1 RAC databases and ORA-1652: Unable To Extend Temp Segment By %s In Tablespace TEMP

=============================Real workaround========================

SQL> SELECT tablespace_name,SUM(bytes_used/1024/1024),SUM(bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 0
TIVOLI_TEMP 250 0

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
5000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> alter DATABASE TEMPFILE ‘/uat_u03/oradata/odrmu/data/temp_01.dbf’ resize 6000m;

Database altered.

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
6000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> SELECT tablespace_name, SUM (bytes_used/1024/1024),SUM (bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 1000
TIVOLI_TEMP 250 0

Oracle Database: ORA-00604 and ORA-20000

2 Replies

***************************************************************************************
* ORA-00604: error occurred at recursive SQL level 1
* ORA-20000: Trigger xdb_installation_trigger does not support object creation of type SNAPSHOT *
***************************************************************************************

Solution:

A trigger named xdb_installation_trigger that prevent you from creating your sequence.
Disable it before the import, you reeanable it after, if you really need it.

What is XDB ?

Oracle XML DB is a feature of the Oracle Database. It provides a high-performance,
native XML storage and retrieval technology. It fully absorbs the W3C XML data model
into the Oracle Database, and provides new standard access methods for navigating
and querying XML. With Oracle XML DB, you get all the advantages of relational
database technology plus the advantages of XML.

In Oracle Database 11g Release 2, new capabilities, such as XMLIndex structured
component and partitioning, as well as numerous enhancements of Oracle XML DB are
introduced to dramatically improve its performance, scalability,
XML schema management, and XML information lifecycle management.

$ su – orancmst
orancmst’s Password:

$ export ORACLE_SID=ctstrng
$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 27 17:22:29 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CTSTRNG READ WRITE

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CTSTRNG READ WRITE

SQL> desc dba_triggers
Name Null? Type
———————————————————————————– ——– ——————————————————–
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
CROSSEDITION VARCHAR2(7)
BEFORE_STATEMENT VARCHAR2(3)
BEFORE_ROW VARCHAR2(3)
AFTER_ROW VARCHAR2(3)
AFTER_STATEMENT VARCHAR2(3)
INSTEAD_OF_ROW VARCHAR2(3)
FIRE_ONCE VARCHAR2(3)
APPLY_SERVER_ONLY VARCHAR2(3)

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME
—————————— —————————— —————- ——————————
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT

SQL> desc dba_registry
Name Null? Type
———————————————————————————– ——– ——————————————————–
COMP_ID NOT NULL VARCHAR2(30)
COMP_NAME VARCHAR2(255)
VERSION VARCHAR2(30)
STATUS VARCHAR2(11)
MODIFIED VARCHAR2(20)
NAMESPACE NOT NULL VARCHAR2(30)
CONTROL NOT NULL VARCHAR2(30)
SCHEMA NOT NULL VARCHAR2(30)
PROCEDURE VARCHAR2(61)
STARTUP VARCHAR2(8)
PARENT_ID VARCHAR2(30)
OTHER_SCHEMAS VARCHAR2(4000)

SQL> select COMP_ID,STATUS from dba_registry;

COMP_ID STATUS
—————————— ———–
XDB LOADING
CATALOG VALID
CATPROC VALID
JAVAVM VALID
XML VALID
CATJAVA VALID
APS VALID
XOQ INVALID

8 rows selected.

SQL> alter trigger XDB_INSTALLATION_TRIGGER disable;

Trigger altered.

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME
—————————— —————————— —————- ——————————
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
—————————— —————————— —————- —————————— ——–
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT DISABLED

Oracle Database – Enableing Trace for a concurrent request

Leave a reply

Step 1 :
=======

Get Concurrent Request ID of long running concurrent request from Application(fronted).

Navigation : Application >> System administrator>> concurrent>request>

Step 2 :
========

Find session associated with concurrent request .i.e SID

By using the above Query we can get sid,serial#,spid of the concurrent Request..

SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;

For Example:
————
Output of above query returned

REQUEST_ID SID SERIAL# SPID
———- — ——- —-
678901 1973 89 1100

Here Conurrent Request id is 678901 & Asoociated Session is 1973.

SPID is the process that Running on DB node Because of This Request..

oracle@acer $ ps -ef|grep -i 1100

oracle1100 1 0 3:30:43 0:03 oracle (LOCAL=NO)

Note : before Using Oradebug make sure that spid shoud exist on DB node..

Step 3 :
========

Enable event 10046 trace with level 12 using oradebug .

Syntax : oradebug setospid SPID

Here SPID is the process id that we are getting from step 2

Coming to My Case

SQL> oradebug setospid 1100
—
Oracle pid: 79, Unix process pid: 1100, image: oracle@Acer
—

Step 3.3 :
==========

Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 :
==========

Locate Trace file as
SQL>oradebug tracefile_name

/ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc

Wait for 30 Min to get the Trace file to find the Root Cause

Step 4 :
========

Disable trace if u r thinking that trace is enough to find the root cause..

SQL> oradebug event 10046 trace name context off

Step 5 :
========

Convert raw trace To Understandable tracefile By using tkprof/Traceanalyzer

tkprof ‘ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc’ ’ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.txt ’ explain=apps/[apps_passwd] prsela.

Step 6 :
========

Check TKPROF out file to find root cause of slow concurrent request.

Oracle – Temporarily disabling the log shipping to standby database

Leave a reply

Temporarily disabling the log shipping to standby database

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jul 25 21:07:57 2012

Enter user-name: /as sysdba

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

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
AIRMAN READ WRITE

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8941

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8942

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = ‘VALID’;

STATUS
———
DEST_NAME
——————————————————————————–
DESTINATION
——————————————————————————–
VALID
LOG_ARCHIVE_DEST_1
/u01/oradata/airman/archive

VALID
LOG_ARCHIVE_DEST_2
airman_sj

STATUS
———
DEST_NAME
——————————————————————————–
DESTINATION
——————————————————————————–

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma
n_sj

SQL> show parameter log_archive

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string DG_CONFIG=(airman_kj,airman_sj
)
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/oradata/airman/a
rchive VALID_FOR=(ALL_LOGFILES
, ALL_ROLES) db_unique_name=ai
rman_kj
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma

NAME TYPE VALUE
———————————— ———– ——————————
n_sj
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string DEFER

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8942

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8943

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8944

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8945

Oracle Activating the Standby Database – Switchover Method

Leave a reply

Activating the Standby Database – Switchover Method

The main advantage of a graceful switchover is that it avoids the resetlogs operation. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In PRIMARY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In STANDBY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the PRIMARY database. (ABCD02 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the PRIMARY database in RESTRICTED mode. (ABCD02 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the PRIMARY database. (ABCD02 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the PRIMARY database. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the STANDBY database and issues the following command. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database and issue a shutdown and mount the NEW STANDBY database. (ABCD02 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the NEW PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD03 server)
SQL> alter database open;
Database opened.

13. Verify the new STANDBY’S status. (ABCD02 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the NEW STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD02)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD03 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the NEW STANDBY and also check the listener status.
17. On the NEW PRIMARY database instance, take a backup if possible. (ABCD03 server)
Switch back from SZB location (ABCD03) to KJ location orginal primary sever (ABCD02)
Switch back to Orginal Primary Database – Switchover Method
1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In New PRIMARY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

4. In New STANDBY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

5. Shutdown the New PRIMARY database. (ABCD03 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the New PRIMARY database in RESTRICTED mode. (ABCD03 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the New PRIMARY database. (ABCD03 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the New PRIMARY database. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the New STANDBY database/Orginal Primary and issues the following command. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database/Orginal STANDBY and issue a shutdown and mount the Orginal STANDBY database. (ABCD03 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the Orginal PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD02 server)
SQL> alter database open;
Database opened.

13. Verify the orginal STANDBY’S status. (ABCD03 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the Orginal STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD03)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD02 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the Orginal STANDBY and also check the listener status.
17. On the Orginal PRIMARY database instance, take a backup if possible. (ABCD02 server)

Oracle Database Health Check Script

Leave a reply

SPOOL OFF
CLEAR SCREEN
SPOOL /tmp/quickaudit.lst

–SELECT SYSDATE FROM DUAL;
–SHOW USER

PROMPT
PROMPT ———————————————————————–|
PROMPT

SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK ON

PROMPT
PROMPT Checking database name and archive mode, dbid
PROMPT

column NAME format A9
column LOG_MODE format A12

SELECT NAME,CREATED, LOG_MODE, DBID FROM V$DATABASE;

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Time since last RMAN backup
PROMPT

select (sysdate-min(t))*24 from
(
select max(b.CHECKPOINT_TIME) t
from v$backup_datafile b, v$tablespace ts, v$datafile f
where INCLUDED_IN_DATABASE_BACKUP=’YES’
and f.file#=b.file#
and f.ts#=ts.ts#
group by f.file#
);

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Tablespace name and status
PROMPT

column TABLESPACE_NAME format a30
column STATUS format a10
set pagesize 400

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space in tablespaces
PROMPT

column tablespace_name format a30

SELECT tablespace_name ,sum(bytes)/1024/1024 “MB Free” FROM dba_free_space WHERE
tablespace_name ‘TEMP’ GROUP BY tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking freespace by tablespace
PROMPT

column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a16 heading “Tablespace Name”
column bytes format 9,999,999,999,999 heading “Total Bytes”
column used format 99,999,999,999 heading “Used”
column free format 999,999,999,999 heading “Free”
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

set linesize 132
set termout off
select a.tablespace_name name,
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) –
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) –
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking Size and usage in GB of Flash Recovery Area
PROMPT

SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space In Flash Recovery Area
PROMPT

column FILE_TYPE format a20

select * from v$flash_recovery_area_usage;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking last sequence in v$archived_log
PROMPT

clear screen
set linesize 100

column STANDBY format a20
column applied format a10

–select max(sequence#), applied from v$archived_log where applied = ‘YES’ group by applied;

SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;

prompt
prompt—————-Last log on Primary————————————–|
prompt

select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking switchover status
PROMPT

select switchover_status from v$database;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for 20 Largest items in Database
PROMPT

column SEGMENT_NAME format A50

SELECT * FROM (
SELECT
OWNER, SEGMENT_NAME, BYTES/1024/1024 SIZE_MB
FROM
DBA_SEGMENTS
ORDER BY
BYTES/1024/1024 DESC ) WHERE ROWNUM <= 20;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for Failed jobs
PROMPT

SELECT
OWNER,
LOG_DATE,
JOB_NAME,
STATUS –,
— REQ_START_DATE,
— ACTUAL_START_DATE,
— RUN_DURATION
FROM
DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
STATUS ‘SUCCEEDED’
AND
LOG_DATE > SYSDATE -7;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for invalid objects
PROMPT

column owner format A15
column object_name format A30 heading ‘Object’
column object_id format 999999 heading “Id#”
column object_type format A15
column status format A8

select owner, object_name, object_id, object_type, status
from dba_objects where status != ‘VALID’ and object_type != ‘SYNONYM’;

PROMPT
PROMPT ————————————————————————|

PROMPT
PROMPT How large is the database
PROMPT

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking the recycle Bin
PROMPT

SELECT
OWNER, SUM(SPACE) AS TOTAL_BLOCKS
FROM
DBA_RECYCLEBIN GROUP BY OWNER
ORDER BY OWNER;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking database versions
PROMPT

column BANNER format A64
select * from v$version;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking control file(s)
PROMPT

column STATUS format a7
column NAME format a68
column IS_RECOVERY_DEST_FILE format a3
set linesize 132

SELECT * FROM V$CONTROLFILE;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking redo logs and group(s)
PROMPT

column member format a90

SELECT group#, member FROM v$logfile;

SPOOL OFF

exit

Oracle11G Data Pump By Using Database Link

1 Reply

Scenario:
Directly importing the TEST01 schema in the production database (oraodrmu) to test database oraodrmt, over
a network by using database link and data pump in Oracle 11g.

Note: When you perform an import over a database link, the import source is a database, not a dump file set, and the data is imported to the connected database instance.
Because the link can identify a remotely networked database, the terms database link and network link are used interchangeably.

=================================================================
STEP-1 (IN PRODUCTION DATABASE – oraodrmu)
=================================================================

[root@szoddb01]>su – oraodrmu

Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant resource to test01;

Grant succeeded.

SQL> grant imp_full_database to test01;

Grant succeeded.

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

OWNER OBJECT_TYPE STATUS COUNT(*)
—————————— ——————- ——- ———-
TEST01 PROCEDURE VALID 2
TEST01 TABLE VALID 419
TEST01 SEQUENCE VALID 3
TEST01 FUNCTION VALID 8
TEST01 TRIGGER VALID 3
TEST01 INDEX VALID 545
TEST01 LOB VALID 18

7 rows selected.

SQL>
SQL> set pages 999
SQL> col “size MB” format 999,999,999
SQL> col “Objects” format 999,999,999
SQL> select obj.owner “Owner”
2 , obj_cnt “Objects”
3 , decode(seg_size, NULL, 0, seg_size) “size MB”
4 from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
5 , (select owner, ceil(sum(bytes)/1024/1024) seg_size
6 from dba_segments group by owner) seg
7 where obj.owner = seg.owner(+)
8 order by 3 desc ,2 desc, 1
9 /

Owner Objects size MB
—————————— ———— ————
OND 8,097 284,011
SYS 9,601 1,912
TEST01 998 1,164

3 rows selected.

SQL> exit

=================================================================
STEP-2 (IN TEST DATABASE – oraodrmt)
=================================================================

[root@szoddb01]>su – oraodrmt

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 18:40:16 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
ODRMT READ WRITE

SQL> create tablespace test_test datafile ‘/trn_u04/oradata/odrmt/test01.dbf’ size 2048m;

Tablespace created.

SQL> create user test01 identified by test123 default tablespace test_test;

User created.

SQL> grant resource, create session to test01;

Grant succeeded.

SQL> grant EXP_FULL_DATABASE to test01;

Grant succeeded.

SQL> grant imp_FULL_DATABASE to test01;

Grant succeeded.

Note: ODRMU is the DNS hoste name.We can test the connection with: [oraodrmt@szoddb01]>sqlplus test01/test01@odrmu

SQL> create directory test_network_dump as ‘/dbdump/test_exp’;

Directory created.

SQL> grant read,write on directory test_network_dump to test01;

Grant succeeded.

SQL> conn test01/test123
Connected.

SQL> create DATABASE LINK remote_test CONNECT TO test01 identified by test01 USING ‘ODRMU’;

Database link created.

For testing the database link we can try the below sql:

SQL> select count(*) from OA_APVARIABLENAME@remote_test;

COUNT(*)
———-
59

SQL> exit

[oraodrmt@szoddb01]>impdp test01/test123 network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp__networklink_grms.log;
[oraodrmt@szoddb01]>

Import: Release 11.2.0.2.0 – Production on Mon Dec 3 19:42:47 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “TEST01”.”SYS_IMPORT_SCHEMA_01″: test01/******** network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp_grms_networklink.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 318.5 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TEST01″ already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “TEST01″.”SY_TASK_HISTORY” 779914 rows
. . imported “TEST01″.”JCR_JNL_JOURNAL” 603 rows
. . imported “TEST01″.”GX_GROUP_SHELL” 1229 rows
. . . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

Job “TEST01”.”SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at 19:45:19

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:46:04 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

8 rows selected.

Owner Objects size MB
—————————— ———— ————
OND 8,065 247,529
SYS 9,554 6,507
TEST01 999 1,164

13 rows selected.

=================================================================
STEP-3 FOR REMOVING THE DATABASE LINK
=================================================================

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:16:01 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop database link remote_test;

Database link dropped.

Shanoj

Learn.Share.Grow

Category Archives: Oracle

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Oracle Database Physical standby Starting and Shutting down

ORA-09817 IBM AIX RISC System/6000 Error: 89

ORA-1652

Oracle Database: ORA-00604 and ORA-20000

Oracle Database – Enableing Trace for a concurrent request

Oracle – Temporarily disabling the log shipping to standby database

Oracle Activating the Standby Database – Switchover Method

Oracle Database Health Check Script

Oracle11G Data Pump By Using Database Link