Monthly Archives: April 2013

ORA-00600: How to recover and open the database if the archive log required for recovery is missing.

2 Replies

How to recover and open the database if the archive log required for recovery is missing.

ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue OCT 16 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340
——————————————————————————————

To resolve undo corruption issue, change undo_management to “Manual” in init.ora. Now it allowed to open database successfully.

Once database was up and running, create new undo tablespace and dropped old corrupted undo tablespace

and change back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

1. Create Pfile

2. Change undomanagement auto to manual

UNDO_MANAGEMENT = MANUAL

3. Up the db with Pfile

Note: due to undo segment corruption we need to create a new undo file

5. create New undofile with same specifcation of old one.

CREATE UNDO TABLESPACE undotbs_02
DATAFILE ‘/u01/oracle/rbdb1/undo0201.dbf’ SIZE 2M;

6. Change undomanagement to manual to auto.

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=BOTH;

Note: If it not works,change the undo_management parameter in pfile.

UNDO_MANAGEMENT = AUTO

7. Switch the new unod tablespace with new one.

ALTER SYSTEM SET UNDO_TABLESPACE = ”;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

8. Create SPfile from pfile.

9. Up the databse.

——————————————
Monitoring:
———-

Undo information can be queried using the following views.

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS

— Display the rollback segments

select segment_name
, status
from dba_rollback_segs
/

— Is anything rolling back at the moment?

Look for the used_ublk value decreasing. If it is, the session connected with it is rolling back.
When it reaches zero, rollback is complete.

set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/

—————————————
Maintenance:
————

— Add a datafile.
ALTER TABLESPACE undotbs_01
ADD DATAFILE ‘C:\Oracle\Ordata\TSH1\undo0102.dbf’
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

— Resize an undo datafile.
ALTER DATABASE DATAFILE ‘C:\Oracle\Ordata\TSH1\undo0102.dbf’
RESIZE 10M;

— Perform backup operations
ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

— Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Oracle Database Standby Database Failover for Oracle 10g

Leave a reply

Step 1. If the standby database is in managed recovery mode, it will need to be cancelled and the instance shutdown using the IMMEDIATE option:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> shutdown immediate
======================================================

Step 2. Startup the instance and then MOUNT the DB in standby mode:

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount

SQL> alter database mount standby database;

Database altered.

====================================================

Step3. Apply the primary database’s archived logs by recovering the standby database:

SQL> recover standby database until cancel;

A CANCEL will need to be performed as this is Incomplete Recovery.

=====================================================
Step4. Activate the standby database.

In issuing the ALTER DATABASE ACTIVATE STANDBY DATABASE command, the standby bit in the control file is reset. This process takes the database back to nomount mode:

SQL> alter database activate standby database;

Database altered.

NOTE: This process can take several minutes to complete so be patient!

============================================================

Step 7. Shutdown and restart the new primary database. This step ensures that all file headers are reset and clears all buffers.

SQL> shutdown immediate

SQL> startup open

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

8. Check Listener

9.Try to connect “sqlplus username/passwd@pr1

ORA-04031,ORA-06508,RMAN-00554,RMAN-06429

Leave a reply

ORA-04031_ORA-06508_RMAN-00554_RMAN-06429

ORACLE error from target database:
ORA-04031: unable to allocate 41152 bytes of shared memory (“shared pool”,”DBMS_RCVMAN”,”PL/SQL MPCODE”,”BAMIMA: Bam Buffer”)
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_RCVMAN”

error executing package DBMS_RCVMAN in TARGET database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN

SQL> show parameter share pool;

SQL> show parameter SGA

SQL> select dbms_rcvman.getpackageversion from dual;
If getting an error

I tried flushing my shared_pool and then checked to see how large my shared_pool was:

SQL> alter system flush shared_pool;

System altered.

SQL> select dbms_rcvman.getpackageversion from dual;
select dbms_rcvman.getpackageversion from dual
*
ERROR at line 1:
ORA-04031: unable to allocate 41152 bytes of shared memory (“shared
pool”,”DBMS_RCVMAN”,”PL/SQL MPCODE”,”BAMIMA: Bam Buffer”)

SQL> show parameter shared_pool

NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_reserved_size big integer 10M
shared_pool_size big integer 200M

Shared_pool size was small .. so i increased it and that fixed the problem:

SQL> alter system set shared_pool_size=300m;

System altered.

SQL> select dbms_rcvman.getpackageversion from dual;

GETPACKAGEVERSION
——————————————————————————–
08.00.04.00

backup is now running ok.

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

1 Reply

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;

or

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

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

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

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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