Invalid Objects on SYS and ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

Some Objects invalid on SYS, then used catalog.sql, catproc.sql, that made error:

SELECT dbms_registry_sys.time_stamp('PATCH_BGN') AS timestamp FROM DUAL
*

ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

BEGIN dbms_registry.check_server_instance; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 1

Some Objects invalid, we can recreate with scripts at ORACLE_HOME/rdbms/admin PATH
Example Object invalid:
DBMS_STATS_INTERNAL PACKAGE BODY ,DBMS_STATS PACKAGE BODY
We can recreate dbms_stats_internal & dbms_stats:

SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

But Our Problem, we found many objects invalid on SYS, after used "catalog.sql", "catproc.sql"

SQL> startup migrate -- "backup database" before & if use RAC, should -- alter system set cluster_database=false scope=spfile -- before
SQL> spool /tmp/catalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> spool off
SQL> spool /tmp/catproc.log
SQL> @?/rdbms/admin/catproc.sql
SQL> spool off

Below Error after used catproc.sql:

ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"

Checked in spool file.

Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY DBMS_REGISTRY:

95/17 PL/SQL: Item ignored
95/21 PLS-00302: component 'REGISTRY$' must be declared

Checked objects invalid(Found many objects invalid):

select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME
/

What should we do ?
- recover database until time (if have backup & if necessary)
- check inconsistencies in the data dictionary(metalink 136697.1) hcheck package, then verify the reported inconsistency data dictionary + fix(manual)... and then run "catalog.sql", "catproc.sql" and "utlrp.sql" again.

Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hout as described in Note:101468.1
3. Create package hcheck in SYS schema.
4. execute hcheck.full
5. verify the reported inconsistency

Then:

SQL> startup migrate
SQL> set serverout on
SQL> set echo on
SQL> spool report.txt
SQL> exec hcheck.full
SQL> spool off

Verifed(metalink 456468.1) the reported inconsistency data dictionary and fixed(manual), then:

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

No Error about DBMS_REGISTRY package.

Actually, we should solve a real problem, before use catalog/catproc.
The SYS.DBMS_REGISTRY problem, that made database couldn't do something (expdmp/exp, run utlrp.sql or ...)

By the way, Thank You Oracle Support for hcheck package.

DATAPUMP with SYS$SYS.* service_names

While we using DATAPUMP, we will find ALTER SYSTEM SET service_names='SYS$SYS.*...' in alert log file(RAC).

$ expdp directory=TMP logfile=full.log dumpfile=full.dmp full=y

In alert log file:
Mon Nov 30 01:14:22 2009
ALTER SYSTEM SET service_names='service2','service1','SYS$SYS.KUPC$C_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20091130011411.ORCL','service2','service1','SYS$SYS.KUPC$S_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
Mon Nov 30 01:14:29 2009
DM00 started with pid=56, OS id=26976, job SYS.SYS_EXPORT_SCHEMA_01

At "SERVICE_NAMES" parameter:

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string SYS$SYS.KUPC$C_1_20091130011411.ORCL, service2, service1, SYS$SYS.KUPC$S_1_20091130011411.ORCL

After DATAPUMP finished. In alert log file:

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20091130011602.ORCL','service2','service1' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='service2','service1' SCOPE=MEMORY SID='orcl1';

At SERVICE_NAMES parameter:

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service2, service1

DATAPUMP doesn't change existing service name and no impact to existing services.
DATAPUMP just adds new services for its own queue operation.

read more metalink 363396.1

Disable/Enable Automatic startup Oracle HAS

On 11gR2, Oracle Clusterware consists of two separate stacks: an upper stack anchored by the Cluster Ready Services (CRS) daemon (crsd) and a lower stack anchored by the Oracle High Availability Services daemon (ohasd).

So.. How to disable/enable Oracle HAS.
Use the crsctl disable has command to disable automatic startup of the Oracle High Availability Services stack when the server boots up.

# crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

How to know Oracle HAS is enabled(if doesn't use "crsctl config has")

# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enable

# crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

# crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
disable

Use the crsctl enable has command to enable automatic startup of the Oracle High Availability Services stack when the server boots up.

# crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.

# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enable

If We just check HAS Disable/Enable status, that uses "crsctl config has" command, it's easier than "ohasdstr" file checking.

How about "crsctl disable/enable crs" on 11gR2?
They disable/enable automatic startup of Oracle HAS.

I posted "check enable/disable the startup of CRS".. that show Oracle Clusterware version <= 11gR1, we can check from "crsstart" file. On 11gR2, crsstart file is not used ???

Use the crsctl disable crs command to prevent the automatic startup of Oracle High Availability Services when the server boots.

Use the crsctl enable crs command to enable automatic startup of Oracle High Availability Services when the server boots.

# crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

# crsctl config crs
CRS-4622: Oracle High Availability Services autostart is enabled.

# ls -ltr /etc/oracle/scls_scr/rhel5-test/root/
-rw-r--r-- 1 root root 7 Sep 7 00:56 crsstart
-rw-r--r-- 1 root oinstall 5 Nov 22 17:04 ohasdrun
-rw-r--r-- 1 root oinstall 7 Nov 22 17:10 ohasdstr

# cat /etc/oracle/scls_scr/rhel5-test/root/crsstart
enable

# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enable

# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.

# crsctl config crs
CRS-4621: Oracle High Availability Services autostart is disabled.

# crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

# ls -ltr /etc/oracle/scls_scr/rhel5-test/root/
-rw-r--r-- 1 root root 7 Sep 7 00:56 crsstart
-rw-r--r-- 1 root oinstall 5 Nov 22 17:04 ohasdrun
-rw-r--r-- 1 root oinstall 8 Nov 22 17:12 ohasdstr

# cat /etc/oracle/scls_scr/rhel5-test/root/crsstart
enable

# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
disable

However, check CRSCTL Utility Reference

LOCK_SGA can not use with AMM or ASMM

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space.

On 11gR2:

SQL> show parameter lock_sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE

SQL> show parameter memory_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776M

SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together

On 10gR2:

SQL> show parameter lock_sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M

SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory

If need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.

V$SGA_RESIZE_OPS.STATUS ERROR

In 10G version, the ASMM(Automatic Shared Memory Management) has been introduced to relieve DBAs from sizing some parts of the SGA by themselves.

ASMM can be configured by using the SGA_TARGET initialization parameter.

when set > 0, the ASMM is enabled

when set to 0, the ASMM is disabled

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';

SET PAGESIZE 900

SET LINESIZE 255

COL COMPONENT FORMAT A25

COL INITIAL_SIZE FORMAT A10

COL TARGET_SIZE FORMAT A10

COL FINAL_SIZE FORMAT A10

COL OPER_TYPE FORMAT A10

select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME

from v$sga_resize_ops

order by start_time, component;

START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME

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

16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:24

16/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:24

16/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:35

16/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35

My result found "ERROR" (sizing operation was unable to complete) status.

Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.

So, I checked v$sga_target_advice ...

select * from v$sga_target_advice order by sga_size;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads

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

2560 .25 68538927 2.6068 5,648,312,410

5120 .5 34072278 1.2959 1,863,613,370

7680 .75 28290588 1.076 1,204,264,183

10240 1 26292359 1 1,020,822,398

12800 1.25 25737600 .9789 973,149,992

15360 1.5 25416834 .9667 973,149,992

17920 1.75 25377404 .9652 847,180,508

20480 2 25377406 .9652 767,045,950

Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!

Investigate ROW CACHE LOCK

My Oracle RAC had the problem, so I investigated... I found:

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

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

row cache lock 509,761 1,259,315 2470 71.8 Concurrenc

ROW CACHE LOCK (DDL statements require "row cache lock" and session will wait "row cache lock" to lock the data dictionary)

And then check V$SESSION

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';

P1TEXT P1 P2TEXT P2 P3TEXT P3

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

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

What I see? I need "cache id" to find on V$ROWCACHE (to check enqueue type)

Example: Enqueue Type

DC_TABLESPACES

Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

DC_SEQUENCES

Check for appropriate caching of sequences for the application requirements.

DC_USERS

Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

DC_OBJECTS

Look for any object compilation activity which might require an exclusive lock and thus block online activity.

DC_SEGMENTS

This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

To check on V$ROWCACHE

SQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER COUNT GETS GETMISSES MODIFICATIONS

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

dc_sequences 13 746449 210320 746449

SQL> column pct_succ_gets format 999.9

SQL> column updates format 999,999,999

SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;

PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES

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

dc_constraints 4288 1455 66.1 4,288

dc_object_ids 8161040 118929 98.5 5,482

outstanding_alerts 2737095 2722712 .5 200

dc_awr_control 31108 526 98.3 457

dc_objects 21160173 145159 99.3 266,627

dc_usernames 9387743 1349 100.0 0

dc_table_scns 4658 4658 .0 0

dc_users 94113064 2174 100.0 16

dc_histogram_defs 7702201 783888 89.8 97,472

kqlsubheap_object 153 36 76.5 0

dc_profiles 1266752 13 100.0 0

dc_object_grants 44530796 7547 100.0 0

dc_histogram_data 2431665 377265 84.5 147,793

dc_segments 1658801 347470 79.1 17,402

dc_files 67249 6477 90.4 0

dc_sequences 748386 210954 71.8 748,386

dc_database_links 2552640 89 100.0 0

dc_global_oids 850330 3091 99.6 0

global database name 175908 174 99.9 0

dc_tablespaces 42863352 137296 99.7 0

dc_tablespace_quotas 14299 5277 63.1 576

dc_rollback_segments 149604805 2673 100.0 949

After check at v$rowcache, my point to SEQUENCE (DC_SEQUENCES). then I check ALL_SEQUENCES and then investigate how to solve...

SQL> select * from all_sequences order by last_number;

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

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

APPUSER SEQ_XYZ 1 1.0000E+27 1 N N 0 34015364

This case, I solved it by "alter sequence ... cache ..."

Refer: RAC and Sequence

DUPLICATE Without Connection to Target Database

Backup-Based: Duplication Without a Target Connection













Oracle DUPLICATE Without Connection to Target Database, that's a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.

Start...
- Create password file

$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwAUX entries=5

Enter password for SYS:

- Create pfile (use ORACLE_SID=AUX)

SQL> create pfile='/oracle/product/11.2.0/dbhome_1/dbs/initAUX.ora' from spfile;

File created.

Modify initAUX.ora file.

*.audit_file_dest='/oracle/product/admin/AUX/adump'
*.compatible='11.2.0.0.0'
*.control_files='+DISK01/AUX/controlfile/current.263.696964775','+DISK02/AUX/controlfile/current.492.696964779'
*.db_block_size=8192
*.db_create_file_dest='+DISK01'
*.db_domain=''
*.db_name='AUX'
*.db_recovery_file_dest='+DISK02'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXXDB)'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=33554432
*.memory_target=813694976
*.open_cursors=300
*.processes=150
*.remote_listener='RHEL5-T-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'

- Check parameters and create some directories

$ mkdir -p /oracle/product/admin/AUX/adump

- Check Target Database in Catalog

$ sqlplus rman/password@catalog

SQL> select * from rc_database ;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 501 1224745511 ORCL 3626383 03-NOV-09

- STARTUP NOMOUNT (AUXILIARY) database

$ export ORACLE_SID=AUX
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 3 19:27:19 2009

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

- Duplicate Database by RMAN

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 4 12:38:14 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> CONNECT AUXILIARY /

connected to auxiliary database: AUX (not mounted)

RMAN> CONNECT CATALOG rman/password@catalog

connected to recovery catalog database

RMAN> DUPLICATE DATABASE orcl TO AUX ;

Starting Duplicate Db at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

Starting restore at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473
channel ORA_AUX_DISK_1: piece handle=+DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473 tag=TAG20091103T210428
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DISK01/aux/controlfile/current.282.702045937
output file name=+DISK02/aux/controlfile/current.349.702045941
Finished restore at 04-NOV-09

database mounted

contents of Memory Script:
{
set until scn 3657467;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DISK01
channel ORA_AUX_DISK_1: reading from backup piece /oracle/RMAN/12ktf0ar_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle/RMAN/12ktf0ar_1_1 tag=TAG20091103T210011
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:25
Finished restore at 04-NOV-09

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=702046260 file name=+DISK01/aux/datafile/system.283.702045995
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=702046260 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=702046261 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=702046261 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=702046261 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=702046261 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=25 STAMP=702046261 file name=+DISK01/aux/datafile/tbs_fda.288.702046001

contents of Memory Script:
{
set until scn 3657467;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355
archived log for thread 1 with sequence 2 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355 thread=1 sequence=1
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:33
Finished recover at 04-NOV-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DISK01/aux/datafile/system.283.702045995'
CHARACTER SET TH8TISASCII


contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DISK01/aux/datafile/sysaux.284.702045997",
"+DISK01/aux/datafile/undotbs1.287.702045999",
"+DISK01/aux/datafile/users.289.702046003",
"+DISK01/aux/datafile/example.286.702045999",
"+DISK01/aux/datafile/tbs1.285.702045999",
"+DISK01/aux/datafile/tbs_fda.288.702046001";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DISK01 in control file

cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/sysaux.284.702045997 RECID=1 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/undotbs1.287.702045999 RECID=2 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/users.289.702046003 RECID=3 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/example.286.702045999 RECID=4 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs1.285.702045999 RECID=5 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs_fda.288.702046001 RECID=6 STAMP=702046345

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=702046345 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=702046345 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=702046345 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=702046345 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=702046345 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=702046345 file name=+DISK01/aux/datafile/tbs_fda.288.702046001

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-NOV-09

- Check

SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
1555302662 AUX

Fun ;) to duplicate database without connection to target database.

Just Fun with RMAN Recovery...

My Test Database (ARCHIVELOG MODE) lost:

Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist

It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:

run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}

But It could not "resetlogs"... and alert log showed:

alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist

Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :

$ rman target / catalog rman/password@catalog

RMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09

RMAN> shutdown

database dismounted
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09

RMAN> alter database mount;

Database altered.

RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}

executing command: SET until clause

Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09

Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527

using channel ORA_DISK_1

skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09

Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 03-NOV-09

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Just fun with RMAN (If A database has backup)... ;)

Learn "NOLOGGING" with Tablespace "force logging" mode

After I learned, with...

If the database, or that tablespace, is in "force logging" mode, the nologging will not work.

Check Database is Archive Mode:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103

Check REDO Size with "create table" logging:

SQL> @redo-new

OLD_VALUE
----------
1860

SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE

OLD_VALUE
----------
8622128

Check REDO Size with "create table" nologging:

SQL> @redo-new

OLD_VALUE
----------
784

SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE

OLD_VALUE
----------
105448

Check REDO Size with "create table" nologging on Tablespace "force logging" mode:

SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO

SQL> alter tablespace TBS1 force logging;

Tablespace altered.

SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES

SQL> @redo-new

OLD_VALUE
----------
788

SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE

OLD_VALUE
----------
8640936

That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.

redo-new.sql:

column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

redo-diff.sql:

select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

Learn "NOLOGGING" from forums

I often learn something(oracle) and find idea from OTN Forums.

And This Topic(Wait Events "log file parallel write" / "log file sync" during CREATE INDEX), It wrote about nologging option, “log file sync” and “log file parallel write” waits, and it's replied by Jonathan Lewis and Uwe Hesse.

Jonathan Lewis noted 2 points with "create index" nologging option:

it's "only" an index, so you could always rebuild it in the event of media corruption, but if you had lots of indexes created nologging this might cause an unreasonable delay before the system was usable again - so you should decide on a fallback option, such as taking a new backup of the tablespace as soon as all the nologging operatons had completed.

If the database, or that tablespace, is in "force logging" mode, the nologging will not work.