Saturday, April 30, 2011

Warning: log write time

Symptoms:

Oracle 10.2.0.4 on Solaris sparc 64bit (Solaris 10)
===========================================================
Warning: log write time 930ms, size 1176KB
*** 2011-04-14 10:00:06.961
Warning: log write time 3170ms, size 3651KB
*** 2011-04-14 10:00:10.684
Warning: log write time 790ms, size 969KB


Explanation:


The above warning messages has been introduced in 10.2.0.4 patchset. This warning message

will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file.

These messages are very much expected in 10.2.0.4 database in case the log write is more than 500 ms.
This is a warning which means that the write process is not as fast as it intended to be.

ORA-16086: standby database does not contain available standby log files

Symptoms:
Dataguard physical standby redolog all become "active", and no redo can be received from primary database because of standby redolog full.
The setup previously was fine, the change is only a failover. (old primary becomes standby database through "reinstate database" command.)


RFS[214]: No standby redo logfiles of size 102400 blocks available
RFS[214]: No standby redo logfiles selected (reason:7)
Thu Apr 14 08:33:53 2011
Errors in file /oracle/admin/ORCL/udump/orcl_rfs_4658.trc:
ORA-16086: standby database does not contain available standby log files


And the flash recovery is not full:


SQL> select * from v$standby_log; <-- shows all the standby redo not being archived

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
------------- -------------------- ------------ --------------------
4 3335137383 1 4 52428800 252416 NO ACTIVE
107537956 2011-APR-13 14:40:45 107538463 2011-APR-13 14:44:25

5 3335137383 1 3 52428800 17413632 NO ACTIVE
107512877 2011-APR-13 07:04:46 107537956 2011-APR-13 14:40:45

6 3335137383 1 5 52428800 43436032 NO ACTIVE
107538463 2011-APR-13 14:44:25 107569782 2011-APR-13 19:08:06


GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
------------- -------------------- ------------ --------------------
7 3335137383 1 6 52428800 43436032 NO ACTIVE
107569782 2011-APR-13 19:08:06 107604733 2011-APR-13 22:01:38


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .4 .27 105
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 9.79 8.95 198



Workaround:


Restarted the standby database, and problem resolved.

Heap size 2238K exceeds notification threshold (2048K) in 10.2.0.1

Symptom:

Heap size 2238K exceeds notification threshold (2048K)
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_HTABLE_VIEW KU$ WHERE NOT (BITAND
(KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.NAME=:NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2

Solution/Explanation:

The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments.

In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate

SQL> startup

ORA-16820: Fast-Start Failover observer is no longer observing this database

One obvious possibility is the observer does not connect to the database again after the database restarted. In this case, restart the observer will solve the problem.

Below is another possibility apply to 10gR2. (MOS ID 730379.1)

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Goal
The Primary Database locked up or hangs (i.e. allows no connections)

The Observer stopped monitoring it with this Error Message:
" RSM0: HEALTH CHECK ERROR: ORA-16820: Fast-Start Failover observer is no longer observing this database "

Primary Database is hanging but not down, so no Fast-Start Failover was initiated although the Observer could not reach the Primary Database anymore.
Solution
In 10.2.0.x, Fast-Start Failover is only initiated if the Primary Database is not available because it is down. Here the Primary Database was locked. The Observer considered it as not reachable but still available, ie. the
Primary Database is not down. So no Fast-Start Failover was initiated.

This has been changed with Oracle 11.x where we can setup several Conditions (also User-defined) to initiate a Fast-Start Failover to handle such Cases, too.

Tuesday, April 19, 2011

Rename ASM Diskgroup Name in 11gR2


[grid@vmxavlab1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE vmxavlab1
ora.FRA.dg
ONLINE ONLINE vmxavlab1
ora.LISTENER.lsnr
ONLINE ONLINE vmxavlab1
ora.asm
ONLINE ONLINE vmxavlab1 Started
ora.ons
OFFLINE OFFLINE vmxavlab1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE vmxavlab1
ora.diskmon
1 ONLINE ONLINE vmxavlab1
ora.evmd
1 ONLINE ONLINE vmxavlab1
ora.orcl.db
1 ONLINE ONLINE vmxavlab1 Open


[grid@vmxavlab1 ~]$ crsctl stop res ora.orcl.db
CRS-2673: Attempting to stop 'ora.orcl.db' on 'vmxavlab1'
CRS-2677: Stop of 'ora.orcl.db' on 'vmxavlab1' succeeded
[grid@vmxavlab1 ~]$ ps -ef|grep agent
grid 4712 1 0 21:45 ? 00:00:01 /u01/app/11.2.0/grid/bin/oraagent.bin
grid 4784 1 0 21:45 ? 00:00:00 /u01/app/11.2.0/grid/bin/cssdagent
grid 4786 1 0 21:45 ? 00:00:00 /u01/app/11.2.0/grid/bin/orarootagent.bin
grid 5561 4863 0 21:48 pts/2 00:00:00 grep agent
[grid@vmxavlab1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE vmxavlab1
ora.FRA.dg
ONLINE ONLINE vmxavlab1
ora.LISTENER.lsnr
ONLINE ONLINE vmxavlab1
ora.asm
ONLINE ONLINE vmxavlab1 Started
ora.ons
OFFLINE OFFLINE vmxavlab1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE vmxavlab1
ora.diskmon
1 ONLINE ONLINE vmxavlab1
ora.evmd
1 ONLINE ONLINE vmxavlab1
ora.orcl.db
1 OFFLINE OFFLINE Instance Shutdown


[grid@vmxavlab1 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 9781 7926 0 7926 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 10691 10461 0 10461 0 N FRA/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
ASM/
ORCL/
ASMCMD [+data] > cd orcl
ASMCMD [+data/orcl] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileorcl.ora
ASMCMD [+data/orcl] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.256.744155141
ASMCMD [+data/orcl] > cp +DATA/ORCL/PARAMETERFILE/spfile.256.744155141 /tmp/spfile.ora
copying +DATA/ORCL/PARAMETERFILE/spfile.256.744155141 -> /tmp/spfile.ora

ASMCMD [+data/orcl] >
ASMCMD [+data/orcl] > exit

[grid@vmxavlab1 ~]$ strings /tmp/spfile.ora
orcl.__db_cache_size=230686720
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=260046848
orcl.__sga_target=390070272
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/orcl/controlfile/current.262.744154915','+F
RA/orcl/controlfile/current.256.744154915'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=8390705152
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=650117120
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[grid@vmxavlab1 ~]$ crsctl stat res ora.asm -p
NAME=ora.asm
TYPE=ora.asm.type
ACL=owner:grid:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=/dev/sdb*
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+DATA/asm/asmparameterfile/registry.253.744140725
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.2.0

[grid@vmxavlab1 dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 19 21:54:01 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option
[grid@vmxavlab1 dbs]$ crsctl stat res ora.asm -p
NAME=ora.asm
TYPE=ora.asm.type
ACL=owner:grid:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=/dev/sdb*
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+DATA/asm/asmparameterfile/registry.253.744140725
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.2.0

[grid@vmxavlab1 dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 19 21:54:19 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> create spfile from memory;
create spfile from memory
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance


SQL> create spfile='?/dbs/spfile+ASM.ora' from memory;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option
[grid@vmxavlab1 dbs]$ crsctl stat res ora.asm -p
NAME=ora.asm
TYPE=ora.asm.type
ACL=owner:grid:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=/dev/sdb*
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=?/dbs/spfile+ASM.ora
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.2.0

[grid@vmxavlab1 dbs]$ ls -l
total 32
-rw-rw---- 1 grid oinstall 998 Apr 19 21:45 ab_+ASM.dat
-rw-rw---- 1 grid oinstall 1544 Apr 19 21:45 hc_+ASM.dat
-rw-r--r-- 1 grid oinstall 208 Apr 19 21:54 init+ASM.ora
-rw-r--r-- 1 grid oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 grid oinstall 1536 Feb 26 17:45 orapw+ASM
-rw-r----- 1 grid oinstall 11776 Apr 19 21:54 spfile+ASM.ora


SQL> select name,path from v$asm_disk;

NAME PATH
------------------------------ --------------------
FRA_0001 /dev/sdb4
DATA_0001 /dev/sdb3
FRA_0000 /dev/sdb2
DATA_0000 /dev/sdb1


[grid@vmxavlab1 dbs]$ renamedg dgname=data newdgname=new_data asm_diskstring='/dev/sdb*' verbose=true
NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA
New DG name : NEW_DATA
Phases :
Phase 1
Phase 2
Discovery str : /dev/sdb*
Clean : TRUE
Raw only : TRUE
renamedg operation: dgname=data newdgname=new_data asm_diskstring=/dev/sdb* verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:/dev/sdb*
Identified disk UFS:/dev/sdb1 with disk number:0 and timestamp (32951121 -1260691456)
Identified disk UFS:/dev/sdb3 with disk number:1 and timestamp (32951121 -1260691456)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:/dev/sdb*
Identified disk UFS:/dev/sdb1 with disk number:0 and timestamp (32951121 -1260691456)
Identified disk UFS:/dev/sdb3 with disk number:1 and timestamp (32951121 -1260691456)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
KFNDG-00405: file not found; arguments: [DATA]

Terminating kgfd context 0xb7f40050

[grid@vmxavlab1 dbs]$ sqlplus '/ as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 19 22:01:53 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> alter diskgroup data dismount;

Diskgroup altered.

exit

[grid@vmxavlab1 dbs]$ renamedg dgname=data newdgname=new_data asm_diskstring='/dev/sdb*' verbose=true
NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA
New DG name : NEW_DATA
Phases :
Phase 1
Phase 2
Discovery str : /dev/sdb*
Clean : TRUE
Raw only : TRUE
renamedg operation: dgname=data newdgname=new_data asm_diskstring=/dev/sdb* verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:/dev/sdb*
Identified disk UFS:/dev/sdb1 with disk number:0 and timestamp (32951121 -1260691456)
Identified disk UFS:/dev/sdb3 with disk number:1 and timestamp (32951121 -1260691456)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:/dev/sdb*
Identified disk UFS:/dev/sdb1 with disk number:0 and timestamp (32951121 -1260691456)
Identified disk UFS:/dev/sdb3 with disk number:1 and timestamp (32951121 -1260691456)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Checking disk number:1
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/sdb1
Modifying the header
Looking for /dev/sdb3
Modifying the header
Completed phase 2
Terminating kgfd context 0xb7e9c050

[grid@vmxavlab1 dbs]$ sqlplus '/ as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 19 22:03:58 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option


SQL> select name,state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
FRA MOUNTED
NEW_DATA DISMOUNTED


SQL> alter diskgroup new_data mount;

Diskgroup altered.

SQL> col path for a20
SQL> select name,path from v$asm_disk;

NAME PATH
------------------------------ --------------------
FRA_0001 /dev/sdb4
DATA_0001 /dev/sdb3
FRA_0000 /dev/sdb2
DATA_0000 /dev/sdb1


[grid@vmxavlab1 dbs]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 10691 10461 0 10461 0 N FRA/
MOUNTED EXTERN N 512 4096 1048576 9781 7926 0 7926 0 N NEW_DATA/
ASMCMD [+] > cd NEW_DATA/
ASMCMD [+NEW_DATA] > ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
Y ASM/
Y ORCL/
ASMCMD [+NEW_DATA] > cd ORCL/
ASMCMD [+NEW_DATA/ORCL] > ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileorcl.ora => +NEW_DATA/ORCL/PARAMETERFILE/spfile.256.744155141
ASMCMD [+NEW_DATA/ORCL] >

[oracle@vmxavlab1 ~]$ srvctl modify db -d orcl -h

Modifies the configuration for the database.

Usage: srvctl modify database -d [-n ] [-o ] [-u ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s ] [-t ] [-y {AUTOMATIC | MANUAL}] [-a ""|-z] [-f]
-d Unique name for the database
-n Database name (DB_NAME), if different from the unique name given by the -d option
-o ORACLE_HOME path
-u Oracle user
-m Domain for database. Must be set if database has DB_DOMAIN set.
-p Server parameter file path
-r Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
-t Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
-y Management policy for the database (AUTOMATIC or MANUAL)
-a "" Comma separated list of disk groups
-z To remove database's dependency upon disk groups
-f Force the modify operation to change management policy of all services to match new database management policy
-h Print usage
[oracle@vmxavlab1 ~]$ srvctl modify db -d orcl -p +NEW_DATA/orcl/spfileorcl.ora
[oracle@vmxavlab1 ~]$ srvctl config db -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: grid
Spfile: +NEW_DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
[oracle@vmxavlab1 ~]$


[oracle@vmxavlab1 ~]$ srvctl modify db -d orcl -h

Modifies the configuration for the database.

Usage: srvctl modify database -d [-n ] [-o ] [-u ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s ] [-t ] [-y {AUTOMATIC | MANUAL}] [-a ""|-z] [-f]
-d Unique name for the database
-n Database name (DB_NAME), if different from the unique name given by the -d option
-o ORACLE_HOME path
-u Oracle user
-m Domain for database. Must be set if database has DB_DOMAIN set.
-p Server parameter file path
-r Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
-t Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
-y Management policy for the database (AUTOMATIC or MANUAL)
-a "" Comma separated list of disk groups
-z To remove database's dependency upon disk groups
-f Force the modify operation to change management policy of all services to match new database management policy
-h Print usage
[oracle@vmxavlab1 ~]$ srvctl modify db -d orcl -p +NEW_DATA/orcl/spfileorcl.ora
[oracle@vmxavlab1 ~]$ srvctl config db -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: grid
Spfile: +NEW_DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
[oracle@vmxavlab1 ~]$ srvctl modify db -d orcl -a "NEW_DATA,FRA"
[oracle@vmxavlab1 ~]$ rvctl config db -d orcl
-bash: rvctl: command not found
[oracle@vmxavlab1 ~]$ srvctl config db -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: grid
Spfile: +NEW_DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: NEW_DATA,FRA
Services:
[oracle@vmxavlab1 ~]$

[oracle@vmxavlab1 ~]$ srvctl start db -d orcl -o nomount
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5010: Update of configuration file "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora" failed: details at "(:CLSN00014:)" in "/u01/app/11.2.0/grid/log/vmxavlab1/agent/ohasd/oraagent_grid/oraagent_grid.log"
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
CRS-5010: Update of configuration file "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora" failed: details at "(:CLSN00014:)" in "/u01/app/11.2.0/grid/log/vmxavlab1/agent/ohasd/oraagent_grid/oraagent_grid.log"


CRS-2674: Start of 'ora.orcl.db' on 'vmxavlab1' failed

[oracle@vmxavlab1 ~]$ vi /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
[oracle@vmxavlab1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
SPFILE='+NEW_DATA/orcl/spfileorcl.ora'


[oracle@vmxavlab1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 19 22:12:05 2011

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


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

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/orcl/controlfile/current
.262.744154915, +FRA/orcl/cont
rolfile/current.256.744154915
control_management_pack_access string DIAGNOSTIC+TUNING

SQL> alter system set control_files='+NEW_DATA/orcl/controlfile/current.262.744154915','+FRA/orcl/controlfile/current.256.744154915' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 648654848 bytes
Fixed Size 1346132 bytes
Variable Size 411043244 bytes
Database Buffers 230686720 bytes
Redo Buffers 5578752 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.260.744154813
+DATA/orcl/datafile/sysaux.266.744154813
+DATA/orcl/datafile/undotbs1.264.744154815
+DATA/orcl/datafile/users.263.744154815
+DATA/orcl/datafile/example.257.744154937

SQL> alter database rename file '+DATA/orcl/datafile/system.260.744154813' to '+NEW_DATA/orcl/datafile/system.260.744154813';

Database altered.

SQL> alter database rename file '+DATA/orcl/datafile/sysaux.266.744154813' to '+NEW_DATA/orcl/datafile/sysaux.266.744154813';

Database altered.

SQL> alter database rename file '+DATA/orcl/datafile/undotbs1.264.744154815' to '+NEW_DATA/orcl/datafile/undotbs1.264.744154815';

Database altered.

SQL> alter database rename file '+DATA/orcl/datafile/users.263.744154815' to '+NEW_DATA/orcl/datafile/users.263.744154815';

Database altered.

SQL> alter database rename file '+DATA/orcl/datafile/example.257.744154937' to '+NEW_DATA/orcl/datafile/example.257.744154937';

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.258.744154929

SQL> alter database rename file '+DATA/orcl/tempfile/temp.258.744154929' to '+NEW_DATA/orcl/tempfile/temp.258.744154929';

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_3.259.744154919
+FRA/orcl/onlinelog/group_3.257.744154921
+DATA/orcl/onlinelog/group_2.265.744154917
+FRA/orcl/onlinelog/group_2.258.744154919
+DATA/orcl/onlinelog/group_1.261.744154915
+FRA/orcl/onlinelog/group_1.259.744154917

6 rows selected.

SQL> alter database rename file '+DATA/orcl/onlinelog/group_3.259.744154919' to '+NEW_DATA/orcl/onlinelog/group_3.259.744154919';

Database altered.

SQL> alter database rename file '+DATA/orcl/onlinelog/group_2.265.744154917' to '+NEW_DATA/orcl/onlinelog/group_2.265.744154917';

Database altered.

SQL> alter database rename file '+DATA/orcl/onlinelog/group_1.261.744154915' to '+NEW_DATA/orcl/onlinelog/group_1.261.744154915';

Database altered.

SQL> alter database open;

Database altered.

Sunday, April 10, 2011

Impact on Foreign key constraint and Index on FK column on DML occurred in parent table

create table tbl_p (pid number, col1 char(80));
create table tbl_c (cid number, clo1 char(80),pid number);
create unique index p_pk on tbl_p(pid);
create unique index c_pk on tbl_c(cid);
create index c_n1 on tbl_c(pid);
alter table tbl_p add constraint p_pk primary key (pid);
alter table tbl_c add constraint c_pk primary key (cid);
alter table tbl_c add constraint c_p_fk foreign key (pid) references tbl_p(pid);

SQL> insert into tbl_p select rownum,'x' from dual connect by rownum<=10000;
10000 rows created.
SQL> insert into tbl_c select rownum,'y',rownum from dual connect by rownum<=10000;
10000 rows created.
SQL> commit;


SQL> select segment_name,sum(blocks) from dba_extents where owner='DONGHUA' group by segment_name;

SEGMENT_NAME SUM(BLOCKS)
------------------------------ -----------
P_PK 24
TBL_P 256
C_PK 24
TBL_C 256
C_N1 32

SQL> set autotrace traceonly statistics;


-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAA');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAA';

1 row deleted.


Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
116 redo size
672 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> drop index c_n1;

Index dropped.

-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAB');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAB';

1 row deleted.


Statistics
----------------------------------------------------------
14 recursive calls
7 db block gets
232 consistent gets
0 physical reads
816 redo size
674 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

-- same effect as alter table tbl_c drop constraint c_p_fk;
SQL> alter table tbl_c disable constraint c_p_fk;


Table altered.

-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAC');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAC';

1 row deleted.


Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
1 consistent gets
0 physical reads
116 redo size
676 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Sunday, April 3, 2011

Export DISPLAY through SSH to local Linux/Unix Machine

Prerequisites

1) DISPLAY envrionment variables not set on remote host (vmxdb01 in this example)
2) X Windows installed on remote host (on vmxdb01, run 'yum groupinstall "X Window System"' to install the X-windows



donghua@rh6:/opt/vmware/repo$ ssh oracle@vmxdb01
oracle@vmxdb01's password:
Last login: Sun Apr 3 21:38:57 2011 from rh6.lab.dbaglobe.com
[oracle@vmxdb01 ~]$ env|grep DISPLAY
[oracle@vmxdb01 ~]$ exit
logout
Connection to vmxdb01 closed.



donghua@rh6:/opt/vmware/repo$ ssh -X oracle@vmxdb01
oracle@vmxdb01's password:
Last login: Sun Apr 3 21:48:30 2011 from rh6.lab.dbaglobe.com
[oracle@vmxdb01 ~]$ env|grep DISPLAY
DISPLAY=localhost:10.0
[oracle@vmxdb01 ~]$ exit
logout
Connection to vmxdb01 closed.
donghua@rh6:/opt/vmware/repo$

Friday, April 1, 2011

Oracle: After password expiry, need to reset password

Here is the summary:



If the account status already “expired” or “exired(grace)”, updating profile will not change account_status and expiry date.

If the account status is open (means not expired), updating profile will change the expiry date for users as well.



After user changing password, it will use new profile.



The difference between expired and expired(grace) is expired(grace) allow user to change the password after login.


SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';



Session altered.



SQL> create profile passwd_life_5_min limit password_life_time 5/1440; <- Create a profile to expire password in 5 minutes



Profile created.



SQL> create user donghua identified by donghua profile passwd_life_5_min; <- user Donghua has this profile



User created.





SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:49:54 2011-MAR-30 18:54:54 OPEN



SQL> create user donghua1 identified by donghua profile passwd_life_5_min;



User created.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA1';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:52:49 2011-MAR-30 18:57:49 OPEN



SQL> select sysdate from dual;



SYSDATE

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

2011-MAR-30 18:52:59



SQL> select sysdate from dual;



SYSDATE

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

2011-MAR-30 18:56:02







SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:49:54 2011-APR-06 18:56:18 EXPIRED(GRACE)



SQL> alter profile passwd_life_5_min limit password_life_time 180; <- change password policy to 180 days



Profile altered.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:49:54 2011-APR-06 18:56:18 EXPIRED(GRACE) <- Since it’s already expired(grace), the expiry date not updated.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA1';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:52:49 2011-SEP-26 18:52:49 OPEN <- because this account ‘DONGHUA1’ still open, so the new profile changes apply.





Only Donghua’s password change, it will use the new profile:



SQL> alter user donghua identified by newpassword; <- change password here



User altered.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

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

2011-MAR-30 18:49:54 2011-SEP-26 19:02:54 OPEN