while starting the Agent we faced the issue..which resulted in message "Failed to start HTTP listener"
Try to start the Agent
moesel(agent):/ora/product/agent/10203/agent10g/sysman/log>emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ....... failed.
Failed to start HTTP listener----------------message?
We checked the logs in
moesel(agent):/ora/product/agent/10203/agent10g/sysman/log>vi emagent.nohup
found somthing like this....
----- Sun Nov 15 12:52:01 2009::Checking status of EMAgent : 12151 -----
----- Sun Nov 15 12:52:31 2009::Checking status of EMAgent : 12151 -----
----- Sun Nov 15 12:52:52 2009::Checking status of EMAgent : 12151 -----
----- Sun Nov 15 12:52:52 2009::EMAgent exited at Sun Nov 15 12:52:52 2009 with signal 9 -----
A Quick fix
11/14/09 00:42:38 (PZSKNX):
moesel(agent):/export/home/oracle>emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
moesel(agent):/export/home/oracle>ps -ef|grep agent
root 16008 16007 0 Sep 04 ? 0:00 /bin/sh -c /bin/sh -c "./aex-runinvnow -c >../aex-invagentlastrun.log 2>&1"
root 2587 2219 0 Jun 20 ? 27:45 /opt/Tivoli/im/Solaris_agent/bin/SolarisAgent
root 16007 2219 0 Sep 04 ? 0:00 /bin/sh -c PATH="${PATH}:/opt/altiris/notification/nsagent/var/packages/{45F064
root 16077 16010 3 Sep 04 ? 101676:07 aex-invagent-configuration
root 14300 14299 0 Jul 17 ? 0:00 /bin/sh -c /bin/sh -c "./aex-runinvnow -c >../aex-invagentlastrun.log 2>&1"
root 16973 2219 0 20:00:06 ? 0:00 /opt/ctmagent/ctm/exe/p_ctmag -e /opt/ctmagent/ctm -i INETD
oracle 12129 2219 0 15:35:14 ? 0:09 /ora/product/agent/10203/agent10g/perl/bin/perl /ora/product/agent/10203/agent1
root 14301 14300 0 Jul 17 ? 0:00 /bin/sh -c ./aex-runinvnow -c >../aex-invagentlastrun.log 2>&1
root 14333 14302 3 Jul 17 ? 172204:55 aex-invagent-configuration
root 14299 2219 0 Jul 17 ? 0:00 /bin/sh -c PATH="${PATH}:/opt/altiris/notification/nsagent/var/packages/{45F064oracle 25627 23930 0 20:11:12 pts/2 0:00 grep agent
root 16009 16008 0 Sep 04 ? 0:00 /bin/sh -c ./aex-runinvnow -c >../aex-invagentlastrun.log 2>&1
oracle 12151 12129 0 15:35:16 ? 3:26 /ora/product/agent/10203/agent10g/bin/emagent
moesel(agent):/export/home/oracle>kill -9 12151
ahc565(agent):/export/home/oracle>emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ................ started.
ahc565(agent):/export/home/oracle>
Agent started....
but need to chk the other trace file as it is a bug...in 10203 agent..
Sunday, November 15, 2009
Monday, November 9, 2009
Block Corruption--RMAN From 9i onwards you can use RMAN to recover only blocks while database is up and running.
From 9i onwards we can use RMAN to recover only blocks while database is up and running.This could possibly save hours and hours of recovery time as a full database restore is not necessary.
Error reported by user pointing to block corruption.
An error encountered in Populate DATA process.
The Error - Source : DATA - ORA-01578: ORACLE data block corrupted (file # 48, block # 142713)
ORA-01110: data file 48: '/m006/ORACLE/DATA_2006_06.dbf'
ORA-02063: preceding 2 lines from .......
File name : /m006/ORACLE/DATA_2006_06.dbf
Check first if the there is only one(few) blocks corrupted or most of the blocks are corrupted.
doon:/opt/oracle/admin/doon/bdump>
Issue command below at UNIX prompt.
dbv file=/m006/ORACLE/DATA_2006_06.dbf BLOCKSIZE=8192 LOGFILE=blkcorpt.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBV-00200: Block, dba 201469305, already marked corrupted
doon:/opt/oracle/admin/doon/bdump> vi blkcorpt.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /m006/ORACLE/DATA_2006_06.dbf.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 21107
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2550111754 (1040.2550111754)
You can get the list of corrupted blocks from v$database_block_corruption
Select * from v$database_block_corruption;
You will get block number corrupt. ------Ex: block 142713.
After that LOGIN TO RMAN.
doon:/opt/oracle/admin/doon/bdump> rman target / catalog rman10/rman10@rman10p
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 4 14:33:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: db (DBID=1125502194)
connected to recovery catalog database
RMAN> blockrecover datafile 48 block 142713;
Starting blockrecover at 04-OCT-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=119 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00048
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.1.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
piece handle=MACSL.20061004.7379.1.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.2.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 2
piece handle=MACSL.20061004.7379.2.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:04:01
starting media recovery
media recovery complete, elapsed time: 00:00:46
Finished blockrecover at 04-OCT-09
Additional information:
V$database_block_corruption is the view to check the list of corrupted blocks.
If you have multiple block list as corrupt, You can use single command to recover them.
RMAN> BLOCKRECOVER corruption list;
Error reported by user pointing to block corruption.
An error encountered in Populate DATA process.
The Error - Source : DATA - ORA-01578: ORACLE data block corrupted (file # 48, block # 142713)
ORA-01110: data file 48: '/m006/ORACLE/DATA_2006_06.dbf'
ORA-02063: preceding 2 lines from .......
File name : /m006/ORACLE/DATA_2006_06.dbf
Check first if the there is only one(few) blocks corrupted or most of the blocks are corrupted.
doon:/opt/oracle/admin/doon/bdump>
Issue command below at UNIX prompt.
dbv file=/m006/ORACLE/DATA_2006_06.dbf BLOCKSIZE=8192 LOGFILE=blkcorpt.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBV-00200: Block, dba 201469305, already marked corrupted
doon:/opt/oracle/admin/doon/bdump> vi blkcorpt.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /m006/ORACLE/DATA_2006_06.dbf.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 21107
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2550111754 (1040.2550111754)
You can get the list of corrupted blocks from v$database_block_corruption
Select * from v$database_block_corruption;
You will get block number corrupt. ------Ex: block 142713.
After that LOGIN TO RMAN.
doon:/opt/oracle/admin/doon/bdump> rman target / catalog rman10/rman10@rman10p
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 4 14:33:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: db (DBID=1125502194)
connected to recovery catalog database
RMAN> blockrecover datafile 48 block 142713;
Starting blockrecover at 04-OCT-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=119 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00048
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.1.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
piece handle=MACSL.20061004.7379.1.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.2.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 2
piece handle=MACSL.20061004.7379.2.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:04:01
starting media recovery
media recovery complete, elapsed time: 00:00:46
Finished blockrecover at 04-OCT-09
Additional information:
V$database_block_corruption is the view to check the list of corrupted blocks.
If you have multiple block list as corrupt, You can use single command to recover them.
RMAN> BLOCKRECOVER corruption list;
Recovering Datafile for which no backup exist --ORACLE 10g
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP1
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/users01.dbf
/u02/ORACLE/opsdba/sysaux01.dbf
/u02/ORACLE/opsdba/undotbs01.dbf
/u02/ORACLE/opsdba/system01.dbf
......
10 rows selected.
Step 2: Create a new tablespace with 1 Datafile which will be used for recovery exercise.
SQL> create tablespace TST_TBS datafile '/u02/ORACLE/opsdba/TST_TBS1.dbf'
size 100M extent management local;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TST_TBS
TEMP1
6 rows selected.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
--------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
SQL> exit
Step 3: Take a full Backup of Database & Archive log.
RMAN> backup database plus archivelog;
Starting backup at 11-Nov-09
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=362 stamp=612901138
input archive log thread=1 sequence=2 recid=363 stamp=612901141
input archive log thread=1 sequence=3 recid=364 stamp=612901146
input archive log thread=1 sequence=4 recid=365 stamp=612943256
input archive log thread=1 sequence=5 recid=366 stamp=612976032
input archive log thread=1 sequence=6 recid=367 stamp=612976036
input archive log thread=1 sequence=7 recid=368 stamp=613049876
input archive log thread=1 sequence=8 recid=369 stamp=613049878
input archive log thread=1 sequence=9 recid=370 stamp=613049879
input archive log thread=1 sequence=10 recid=371 stamp=613049880
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.145.1.1.
613089429 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=372 stamp=613049882
input archive log thread=1 sequence=2 recid=373 stamp=613049884
input archive log thread=1 sequence=3 recid=374 stamp=613049885
input archive log thread=1 sequence=4 recid=375 stamp=613049887
input archive log thread=1 sequence=5 recid=376 stamp=613049888
input archive log thread=1 sequence=6 recid=377 stamp=613049889
input archive log thread=1 sequence=7 recid=378 stamp=613049890
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.146.1.1.
613089445 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=379 stamp=613089428
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.147.1.1.
613089453 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-Nov-09
Starting backup at 11-Nov-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/TST_TBS1.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455 tag=TAG20091111T223735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 11-Nov-09
Starting backup at 11-Nov-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=380 stamp=613089480
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.149.1.1.
613089480 tag=TAG20091111T223800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-Nov-09
Starting Control File and SPFILE Autobackup at 11-Nov-09
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20091111-03
comment=NONE
Finished Control File and SPFILE Autobackup at 11-Nov-09
RMAN>exit
Step 4: Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace. Also switch few log files just for confirmation.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
SQL> alter tablespace TST_TBS add datafile '/u02/ORACLE/opsdba/TST_TBS2.
dbf' size 100m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 5: Create a new Table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.
SQL> create table tst_t1(col1 number(10)) tablespace TST_TBS;
Table created.
SQL> insert into tst_t1 values (&a);
Enter value for a: 1
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(1)
1 row created.
SQL> /
Enter value for a: 2
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(2)
1 row created.
SQL> /
Enter value for a: 3
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(3)
1 row created.
SQL> /
Enter value for a: 4
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(4)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tst_t1;
COL1
----------
1
2
3
4
SQL> alter system switch logfile;
System altered.
Step 6: In the OS Level remove all files of that tablespace including the newly added one (whose backup does not exist).
opsdba:/opt/oracle>cd /u02/ORACLE/opsdba/
opsdba:/u02/ORACLE/opsdba>ls –lrt TST_TBS*.dbf
total 1441496
-rw-r----- 1 oracle dba 104865792 Jan 28 22:38 TST_TBS1.dbf
-rw-r----- 1 oracle dba 104865792 Jan 28 23:08 TST_TBS2.dbf
opsdba:/u02/ORACLE/opsdba>rm -r TST_TBS*.dbf
opsdba:/u02/ORACLE/opsdba>ls -lrt TST_TBS*.dbf
ls: TST_TBS*.dbf: No such file or directory
opsdba:/u02/ORACLE/opsdba>
Step 7: Try to bring the tablespace offline and we will get error message as follows.
opsdba:/u02/ORACLE/opsdba>sql
SQL> alter tablespace TST_TBS offline;
alter tablespace TST_TBS offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u02/ORACLE/opsdba/TST_TBS1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Step 8: Now bring the Tablespace offline with IMMEDIATE option and confirm.
SQL> alter tablespace TST_TBS offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM----------- ONLINE
UNDOTBS1-------- ONLINE
SYSAUX----------- ONLINE
USERS-------------- ONLINE
TEMP1------------- ONLINE
TST_TBS------------------ OFFLINE
6 rows selected.
Step 9: Now connect to RMAN and Confirm that No backup exist for the Newly added Datafile. Then try to restore the TABLESPACE and we will see that RMAN is creating that newly added Datafile as a part of the restore process...
SQL> select file_id, file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_ID- --------------------- FILE_NAME
11-------------------------/u02/ORACLE/opsdba/TST_TBS1.dbf
12-------------------------/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> exit;
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Nov 11 23:18:
11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> list backup of datafile 11;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 11-Nov-09
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20091111T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 11-Nov-09 /u02/ORACLE/opsdba/TST_TBS1.dbf
RMAN> list backup of datafile 12;
No output …
RMAN> restore tablespace TST_TBS;
Starting restore at 11-Nov-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
creating datafile fno=12 name=/u02/ORACLE/opsdba/TST_TBS2.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u02/ORACLE/opsdba/TST_TBS1.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/
opsdba/OPSDBA.20091111.148.1.1.613089455
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455 tag=TAG20091111T223735
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 11-Nov-09
opsdba:/u02/ORACLE/opsdba>rman target /
Step 10: Start Recovery of that Tablespace.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:49:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> recover tablespace TST_TBS;
Starting recover at 11-Nov-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
starting media recovery
un Nov 11 23:22:36 2009
alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Nov 11 23:22:36 2009
Media Recovery Log /u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo03.log
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 4 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo02.log
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo01.log
Sun Nov 11 23:22:36 2009
Media Recovery Complete (opsdba)
Completed: alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Nov 11 23:22:52 2009
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-Nov-09
RMAN> exit
Recovery Manager complete.
Step 11: Bring the Tablespace online and confirm .
SQL> alter tablespace TST_TBS online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM -------ONLINE
UNDOTBS1---- ONLINE
SYSAUX-------- ONLINE
USERS----------- ONLINE
TEMP1----------- ONLINE
TST_TBS-------- ONLINE
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from tst_t1;
COL1
----------
1
2
3
4
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> exit
Step 12: As a standard practice immediately after the recovery please take a FULL DATABASE BACKUP.
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP1
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/users01.dbf
/u02/ORACLE/opsdba/sysaux01.dbf
/u02/ORACLE/opsdba/undotbs01.dbf
/u02/ORACLE/opsdba/system01.dbf
......
10 rows selected.
Step 2: Create a new tablespace with 1 Datafile which will be used for recovery exercise.
SQL> create tablespace TST_TBS datafile '/u02/ORACLE/opsdba/TST_TBS1.dbf'
size 100M extent management local;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TST_TBS
TEMP1
6 rows selected.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
--------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
SQL> exit
Step 3: Take a full Backup of Database & Archive log.
RMAN> backup database plus archivelog;
Starting backup at 11-Nov-09
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=362 stamp=612901138
input archive log thread=1 sequence=2 recid=363 stamp=612901141
input archive log thread=1 sequence=3 recid=364 stamp=612901146
input archive log thread=1 sequence=4 recid=365 stamp=612943256
input archive log thread=1 sequence=5 recid=366 stamp=612976032
input archive log thread=1 sequence=6 recid=367 stamp=612976036
input archive log thread=1 sequence=7 recid=368 stamp=613049876
input archive log thread=1 sequence=8 recid=369 stamp=613049878
input archive log thread=1 sequence=9 recid=370 stamp=613049879
input archive log thread=1 sequence=10 recid=371 stamp=613049880
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.145.1.1.
613089429 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=372 stamp=613049882
input archive log thread=1 sequence=2 recid=373 stamp=613049884
input archive log thread=1 sequence=3 recid=374 stamp=613049885
input archive log thread=1 sequence=4 recid=375 stamp=613049887
input archive log thread=1 sequence=5 recid=376 stamp=613049888
input archive log thread=1 sequence=6 recid=377 stamp=613049889
input archive log thread=1 sequence=7 recid=378 stamp=613049890
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.146.1.1.
613089445 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=379 stamp=613089428
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.147.1.1.
613089453 tag=TAG20091111T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-Nov-09
Starting backup at 11-Nov-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/TST_TBS1.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455 tag=TAG20091111T223735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 11-Nov-09
Starting backup at 11-Nov-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=380 stamp=613089480
channel ORA_DISK_1: starting piece 1 at 11-Nov-09
channel ORA_DISK_1: finished piece 1 at 11-Nov-09
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.149.1.1.
613089480 tag=TAG20091111T223800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-Nov-09
Starting Control File and SPFILE Autobackup at 11-Nov-09
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20091111-03
comment=NONE
Finished Control File and SPFILE Autobackup at 11-Nov-09
RMAN>exit
Step 4: Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace. Also switch few log files just for confirmation.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
SQL> alter tablespace TST_TBS add datafile '/u02/ORACLE/opsdba/TST_TBS2.
dbf' size 100m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 5: Create a new Table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.
SQL> create table tst_t1(col1 number(10)) tablespace TST_TBS;
Table created.
SQL> insert into tst_t1 values (&a);
Enter value for a: 1
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(1)
1 row created.
SQL> /
Enter value for a: 2
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(2)
1 row created.
SQL> /
Enter value for a: 3
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(3)
1 row created.
SQL> /
Enter value for a: 4
old 1: insert into tst_t1 values(&a)
new 1: insert into tst_t1 values(4)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tst_t1;
COL1
----------
1
2
3
4
SQL> alter system switch logfile;
System altered.
Step 6: In the OS Level remove all files of that tablespace including the newly added one (whose backup does not exist).
opsdba:/opt/oracle>cd /u02/ORACLE/opsdba/
opsdba:/u02/ORACLE/opsdba>ls –lrt TST_TBS*.dbf
total 1441496
-rw-r----- 1 oracle dba 104865792 Jan 28 22:38 TST_TBS1.dbf
-rw-r----- 1 oracle dba 104865792 Jan 28 23:08 TST_TBS2.dbf
opsdba:/u02/ORACLE/opsdba>rm -r TST_TBS*.dbf
opsdba:/u02/ORACLE/opsdba>ls -lrt TST_TBS*.dbf
ls: TST_TBS*.dbf: No such file or directory
opsdba:/u02/ORACLE/opsdba>
Step 7: Try to bring the tablespace offline and we will get error message as follows.
opsdba:/u02/ORACLE/opsdba>sql
SQL> alter tablespace TST_TBS offline;
alter tablespace TST_TBS offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u02/ORACLE/opsdba/TST_TBS1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Step 8: Now bring the Tablespace offline with IMMEDIATE option and confirm.
SQL> alter tablespace TST_TBS offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM----------- ONLINE
UNDOTBS1-------- ONLINE
SYSAUX----------- ONLINE
USERS-------------- ONLINE
TEMP1------------- ONLINE
TST_TBS------------------ OFFLINE
6 rows selected.
Step 9: Now connect to RMAN and Confirm that No backup exist for the Newly added Datafile. Then try to restore the TABLESPACE and we will see that RMAN is creating that newly added Datafile as a part of the restore process...
SQL> select file_id, file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_ID- --------------------- FILE_NAME
11-------------------------/u02/ORACLE/opsdba/TST_TBS1.dbf
12-------------------------/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> exit;
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Nov 11 23:18:
11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> list backup of datafile 11;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 11-Nov-09
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20091111T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 11-Nov-09 /u02/ORACLE/opsdba/TST_TBS1.dbf
RMAN> list backup of datafile 12;
No output …
RMAN> restore tablespace TST_TBS;
Starting restore at 11-Nov-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
creating datafile fno=12 name=/u02/ORACLE/opsdba/TST_TBS2.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u02/ORACLE/opsdba/TST_TBS1.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/
opsdba/OPSDBA.20091111.148.1.1.613089455
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20091111.148.1.1.
613089455 tag=TAG20091111T223735
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 11-Nov-09
opsdba:/u02/ORACLE/opsdba>rman target /
Step 10: Start Recovery of that Tablespace.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:49:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> recover tablespace TST_TBS;
Starting recover at 11-Nov-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
starting media recovery
un Nov 11 23:22:36 2009
alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Nov 11 23:22:36 2009
Media Recovery Log /u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo03.log
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 4 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo02.log
Sun Nov 11 23:22:36 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo01.log
Sun Nov 11 23:22:36 2009
Media Recovery Complete (opsdba)
Completed: alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Nov 11 23:22:52 2009
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-Nov-09
RMAN> exit
Recovery Manager complete.
Step 11: Bring the Tablespace online and confirm .
SQL> alter tablespace TST_TBS online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM -------ONLINE
UNDOTBS1---- ONLINE
SYSAUX-------- ONLINE
USERS----------- ONLINE
TEMP1----------- ONLINE
TST_TBS-------- ONLINE
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from tst_t1;
COL1
----------
1
2
3
4
SQL> select file_name from dba_data_files where tablespace_name='TST_TBS';
FILE_NAME
----------------------------------------------------------------------
/u02/ORACLE/opsdba/TST_TBS1.dbf
/u02/ORACLE/opsdba/TST_TBS2.dbf
SQL> exit
Step 12: As a standard practice immediately after the recovery please take a FULL DATABASE BACKUP.
Saturday, October 3, 2009
Lock Info in Database..Session Blocking Other Sessions..
Here are few steps with the help of this we can find the Blocking session and the Query executed by those sesson...
Step 1) Checking Blocking Session..
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
order by l1.sid; -------[ Added later to Sort the query ]
The Result will be like this...
SID 'ISBLOCKING' SID
---------- ------------- ----------
204 IS BLOCKING 3
387 IS BLOCKING 29
88 IS BLOCKING 38
387 IS BLOCKING 41
506 IS BLOCKING 55
387 IS BLOCKING 60
387 IS BLOCKING 80
80 IS BLOCKING 88
204 IS BLOCKING 116
387 IS BLOCKING 139
387 IS BLOCKING 174
387 IS BLOCKING 204
387 IS BLOCKING 229
204 IS BLOCKING 233
80 IS BLOCKING 245
204 IS BLOCKING 260
204 IS BLOCKING 279
204 IS BLOCKING 294
387 IS BLOCKING 310
204 IS BLOCKING 332
204 IS BLOCKING 344
387 IS BLOCKING 345
80 IS BLOCKING 359
506 IS BLOCKING 363
387 IS BLOCKING 400
387 IS BLOCKING 407
387 IS BLOCKING 412
204 IS BLOCKING 451
387 IS BLOCKING 470
204 IS BLOCKING 491
387 IS BLOCKING 506
387 IS BLOCKING 517
387 IS BLOCKING 518
33 rows selected.
Step 2 getting more Blocking info..i.e which user bocking whome..
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.sid;--------[ Added later for Sorting ]
You will get a result like this...to check the user info..
BLOCKING_STATUS
-------------------------------------------------------------------------------------------------------------
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=3 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=29 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=38 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=41 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=55 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=60 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=70 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=88 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=115 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=116 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=138 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=139 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=174 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=229 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=233 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=245 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=260 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=261 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=279 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=294 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=310 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=332 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=344 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=345 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=359 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMSBAT@ahc055 ( SID=363 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=400 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=407 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=412 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=451 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=470 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=491 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=517 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMSBAT@ahc055 ( SID=518 )
37 rows selected.
SQL>
Step 3 To get the Query fired by the session's
select l.sid sid,s.username username,s.program program,t.sql_text,u.name owner,o.name object,
l.type type,lmode,
decode (lmode,1,'NULL',2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row',6,'Exclusive') mode_desc,
request,
decode (request,1,'NULL',2,'Row Share',3,'RowExclusive',4,'Share',5,'Share Row',6,'Exclusive')
request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW','TM','TX','UL')
and l.sid=s.sid(+)
and l.id1 = o.obj# (+)
and o.owner#=u.user#(+)
and s.sql_hash_value = t.hash_value
and lmode > 0;
will put the result of this ...
Vijay Kumar
Step 1) Checking Blocking Session..
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
order by l1.sid; -------[ Added later to Sort the query ]
The Result will be like this...
SID 'ISBLOCKING' SID
---------- ------------- ----------
204 IS BLOCKING 3
387 IS BLOCKING 29
88 IS BLOCKING 38
387 IS BLOCKING 41
506 IS BLOCKING 55
387 IS BLOCKING 60
387 IS BLOCKING 80
80 IS BLOCKING 88
204 IS BLOCKING 116
387 IS BLOCKING 139
387 IS BLOCKING 174
387 IS BLOCKING 204
387 IS BLOCKING 229
204 IS BLOCKING 233
80 IS BLOCKING 245
204 IS BLOCKING 260
204 IS BLOCKING 279
204 IS BLOCKING 294
387 IS BLOCKING 310
204 IS BLOCKING 332
204 IS BLOCKING 344
387 IS BLOCKING 345
80 IS BLOCKING 359
506 IS BLOCKING 363
387 IS BLOCKING 400
387 IS BLOCKING 407
387 IS BLOCKING 412
204 IS BLOCKING 451
387 IS BLOCKING 470
204 IS BLOCKING 491
387 IS BLOCKING 506
387 IS BLOCKING 517
387 IS BLOCKING 518
33 rows selected.
Step 2 getting more Blocking info..i.e which user bocking whome..
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.sid;--------[ Added later for Sorting ]
You will get a result like this...to check the user info..
BLOCKING_STATUS
-------------------------------------------------------------------------------------------------------------
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=3 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=29 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=38 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=41 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=55 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=60 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=70 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=88 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=115 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=116 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=138 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=139 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=174 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=229 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=233 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=245 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=260 )
WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=261 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=279 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=294 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=310 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=332 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=344 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=345 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=359 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMSBAT@ahc055 ( SID=363 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=400 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=407 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=412 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=451 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=470 )
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=491 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=517 )
WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMSBAT@ahc055 ( SID=518 )
37 rows selected.
SQL>
Step 3 To get the Query fired by the session's
select l.sid sid,s.username username,s.program program,t.sql_text,u.name owner,o.name object,
l.type type,lmode,
decode (lmode,1,'NULL',2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row',6,'Exclusive') mode_desc,
request,
decode (request,1,'NULL',2,'Row Share',3,'RowExclusive',4,'Share',5,'Share Row',6,'Exclusive')
request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW','TM','TX','UL')
and l.sid=s.sid(+)
and l.id1 = o.obj# (+)
and o.owner#=u.user#(+)
and s.sql_hash_value = t.hash_value
and lmode > 0;
will put the result of this ...
Vijay Kumar
Thursday, October 1, 2009
Checking Explain Plan of Query Consuming High CPU...
Normally faced....this issue..
This is how we can proceed with this..issue to drill down.
1) using TOP command
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
3202 intf 1 10 0 36G 36G cpu 6:46 99.78% oracle
11999 oracle 1 10 0 36G 36G cpu 114:59 98.09% oracle
8944 oracle 11 20 0 38G 38G cpu 371:33 96.28% oracle
25210 oracle 1 59 0 36G 36G sleep 0:46 32.78% oracle
27249 intf 32 60 0 36G 36G cpu 35:04 19.30% oracle
26601 oracle 1 60 0 36G 36G sleep 0:14 17.44% oracle
23417 oracle 11 49 0 36G 36G sleep 0:17 15.69% oracle
26916 oracle 1 60 0 36G 36G sleep 0:08 12.22% oracle
27685 cftprd1 1 60 0 70M 8688K sleep 63:26 7.50% CFTMAIN
8128 oracle 1 39 0 36G 36G sleep 208:31 5.44% oracle
27500 cftadmin 1 59 0 1480K 1312K sleep 0:01 5.17% ksh
425 oracle 1 59 0 36G 36G sleep 0:00 2.50% oracle
427 oracle 1 59 0 36G 36G sleep 0:00 2.48% oracle
442 oracle 1 59 0 36G 36G sleep 0:00 2.46% oracle
444 oracle 1 53 0 36G 36G sleep 0:00 2.34% oracle
2) Lets get the SID of these..using the query
select s.sid, p.spid FROM v$session s, v$process p where s.paddr = p.addr and p.spid in (3202,11999,8944);
SID SPID
---------- ------------
364 11999
1041 8944
1087 3202
3) Lets get the Query ...
select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=1087;
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
---------------- ---------- ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
000000042F9C2090 872866612 0 1579831869
select DEPT_EANADRES , RETC_EANADRES , COLLODRG_EAN , MIN_AANT_TERUGLEVER_DAGEN
, DATUM , AANTAL from p1i005.intf_ff_s_ccpool order by dept_eanadres , retc_ean
adres , COLLODRG_EAN , DATUM
4) Lets Check the Explain Plan of this Query
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' || options operation, object_name
FROM (SELECT id, parent_id, operation, options, object_name FROM v$sql_plan WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
you have the explain plan here after putting few info
Enter value for address: 000000042F9C2090
old 6: WHERE address = '&address'
new 6: WHERE address = '000000042F9C2090'
Enter value for hash_value: 872866612
old 7: AND hash_value = &hash_value
new 7: AND hash_value = 872866612
Enter value for child_number: 0
old 8: AND child_number = &child_number
new 8: AND child_number = 0
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 VIEW
3 2 SORT UNIQUE
4 3 UNION-ALL
5 4 FILTER
6 5 HASH JOIN
7 6 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
8 6 HASH JOIN
9 8 HASH JOIN
10 9 MERGE JOIN CARTESIAN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
11 10 TABLE ACCESS FULL L0RETOURENCENTRUM
12 10 BUFFER SORT
13 12 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
14 9 TABLE ACCESS FULL L0DEPT_CLLO_TEGOED_PER_RC
15 8 TABLE ACCESS FULL L0AFROEP_DEPOTS
16 5 FILTER
17 16 SORT GROUP BY NOSORT
18 17 INDEX RANGE SCAN DCTR_PK
19 5 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
20 19 INDEX RANGE SCAN CAPD_PK
21 4 HASH JOIN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
22 21 NESTED LOOPS
23 22 NESTED LOOPS
24 23 NESTED LOOPS
25 24 NESTED LOOPS
26 25 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
27 25 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
28 27 INDEX UNIQUE SCAN SRCD_PK
29 24 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
30 29 INDEX UNIQUE SCAN DEPT_PK
31 23 TABLE ACCESS BY INDEX ROWID L0DEPT_CLLO_TEGOED_PER_RC
32 31 INDEX RANGE SCAN DCTR_PK
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
33 32 FILTER
34 33 SORT GROUP BY NOSORT
35 34 INDEX RANGE SCAN DCTR_PK
36 22 TABLE ACCESS BY INDEX ROWID L0RETOURENCENTRUM
37 36 INDEX UNIQUE SCAN RETC_PK
38 21 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
39 4 FILTER
40 39 HASH JOIN
41 40 HASH JOIN
42 41 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
43 41 HASH JOIN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
44 43 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
45 43 TABLE ACCESS FULL L0DEPT_COLLO_TEGOED
46 40 TABLE ACCESS FULL L0AFROEP_DEPOTS
47 39 FILTER
48 47 SORT GROUP BY NOSORT
49 48 INDEX RANGE SCAN DCTG_PK
50 39 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
51 50 INDEX RANGE SCAN CAPD_PK
52 4 HASH JOIN
53 52 TABLE ACCESS BY INDEX ROWID L0DEPT_COLLO_TEGOED
54 53 NESTED LOOPS
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
55 54 NESTED LOOPS
56 55 NESTED LOOPS
57 56 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
58 56 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
59 58 INDEX UNIQUE SCAN SRCD_PK
60 55 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
61 60 INDEX UNIQUE SCAN DEPT_PK
62 54 INDEX RANGE SCAN DCTG_PK
63 62 FILTER
64 63 SORT GROUP BY NOSORT
65 64 INDEX RANGE SCAN DCTG_PK
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
66 52 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
67 rows selected.
SQL>
We can move further from here...
Vijay Kumar
This is how we can proceed with this..issue to drill down.
1) using TOP command
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
3202 intf 1 10 0 36G 36G cpu 6:46 99.78% oracle
11999 oracle 1 10 0 36G 36G cpu 114:59 98.09% oracle
8944 oracle 11 20 0 38G 38G cpu 371:33 96.28% oracle
25210 oracle 1 59 0 36G 36G sleep 0:46 32.78% oracle
27249 intf 32 60 0 36G 36G cpu 35:04 19.30% oracle
26601 oracle 1 60 0 36G 36G sleep 0:14 17.44% oracle
23417 oracle 11 49 0 36G 36G sleep 0:17 15.69% oracle
26916 oracle 1 60 0 36G 36G sleep 0:08 12.22% oracle
27685 cftprd1 1 60 0 70M 8688K sleep 63:26 7.50% CFTMAIN
8128 oracle 1 39 0 36G 36G sleep 208:31 5.44% oracle
27500 cftadmin 1 59 0 1480K 1312K sleep 0:01 5.17% ksh
425 oracle 1 59 0 36G 36G sleep 0:00 2.50% oracle
427 oracle 1 59 0 36G 36G sleep 0:00 2.48% oracle
442 oracle 1 59 0 36G 36G sleep 0:00 2.46% oracle
444 oracle 1 53 0 36G 36G sleep 0:00 2.34% oracle
2) Lets get the SID of these..using the query
select s.sid, p.spid FROM v$session s, v$process p where s.paddr = p.addr and p.spid in (3202,11999,8944);
SID SPID
---------- ------------
364 11999
1041 8944
1087 3202
3) Lets get the Query ...
select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=1087;
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
---------------- ---------- ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
000000042F9C2090 872866612 0 1579831869
select DEPT_EANADRES , RETC_EANADRES , COLLODRG_EAN , MIN_AANT_TERUGLEVER_DAGEN
, DATUM , AANTAL from p1i005.intf_ff_s_ccpool order by dept_eanadres , retc_ean
adres , COLLODRG_EAN , DATUM
4) Lets Check the Explain Plan of this Query
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' || options operation, object_name
FROM (SELECT id, parent_id, operation, options, object_name FROM v$sql_plan WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
you have the explain plan here after putting few info
Enter value for address: 000000042F9C2090
old 6: WHERE address = '&address'
new 6: WHERE address = '000000042F9C2090'
Enter value for hash_value: 872866612
old 7: AND hash_value = &hash_value
new 7: AND hash_value = 872866612
Enter value for child_number: 0
old 8: AND child_number = &child_number
new 8: AND child_number = 0
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 VIEW
3 2 SORT UNIQUE
4 3 UNION-ALL
5 4 FILTER
6 5 HASH JOIN
7 6 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
8 6 HASH JOIN
9 8 HASH JOIN
10 9 MERGE JOIN CARTESIAN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
11 10 TABLE ACCESS FULL L0RETOURENCENTRUM
12 10 BUFFER SORT
13 12 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
14 9 TABLE ACCESS FULL L0DEPT_CLLO_TEGOED_PER_RC
15 8 TABLE ACCESS FULL L0AFROEP_DEPOTS
16 5 FILTER
17 16 SORT GROUP BY NOSORT
18 17 INDEX RANGE SCAN DCTR_PK
19 5 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
20 19 INDEX RANGE SCAN CAPD_PK
21 4 HASH JOIN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
22 21 NESTED LOOPS
23 22 NESTED LOOPS
24 23 NESTED LOOPS
25 24 NESTED LOOPS
26 25 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
27 25 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
28 27 INDEX UNIQUE SCAN SRCD_PK
29 24 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
30 29 INDEX UNIQUE SCAN DEPT_PK
31 23 TABLE ACCESS BY INDEX ROWID L0DEPT_CLLO_TEGOED_PER_RC
32 31 INDEX RANGE SCAN DCTR_PK
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
33 32 FILTER
34 33 SORT GROUP BY NOSORT
35 34 INDEX RANGE SCAN DCTR_PK
36 22 TABLE ACCESS BY INDEX ROWID L0RETOURENCENTRUM
37 36 INDEX UNIQUE SCAN RETC_PK
38 21 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
39 4 FILTER
40 39 HASH JOIN
41 40 HASH JOIN
42 41 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
43 41 HASH JOIN
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
44 43 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
45 43 TABLE ACCESS FULL L0DEPT_COLLO_TEGOED
46 40 TABLE ACCESS FULL L0AFROEP_DEPOTS
47 39 FILTER
48 47 SORT GROUP BY NOSORT
49 48 INDEX RANGE SCAN DCTG_PK
50 39 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
51 50 INDEX RANGE SCAN CAPD_PK
52 4 HASH JOIN
53 52 TABLE ACCESS BY INDEX ROWID L0DEPT_COLLO_TEGOED
54 53 NESTED LOOPS
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
55 54 NESTED LOOPS
56 55 NESTED LOOPS
57 56 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
58 56 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
59 58 INDEX UNIQUE SCAN SRCD_PK
60 55 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
61 60 INDEX UNIQUE SCAN DEPT_PK
62 54 INDEX RANGE SCAN DCTG_PK
63 62 FILTER
64 63 SORT GROUP BY NOSORT
65 64 INDEX RANGE SCAN DCTG_PK
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
66 52 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
67 rows selected.
SQL>
We can move further from here...
Vijay Kumar
Wednesday, September 16, 2009
PART 1 :- RMAN with Oracle Data Guard in Oracle Database 10g
INTRODUCTION
==================
Data Guard enables and automates the management of a disaster recovery solution for Oracle databases located on the same campus or across the continent. Data Guard consists of a production database (also known as the primary database) and one or more standby database(s), which are transactionally consistent copies of the production database. As transactions occur in the primary database, redo data is generated and is written to the local redo logs. Data Guard automatically transfers this redo data to the standby sites and applies it to the standby databases, synchronizing them with the primary database
RMAN and Data Guard are part of the integrated Oracle High Availability technology stack, RMAN backups can be seamlessly offloaded to a physical standby database, allowing customers to gain more value out of their disaster recovery investment. Backups do not impact normal Data Guard operation – they can be taken while the standby database is in recovery or read-only mode. Backups can be used to recover either primary or standby database servers
Data Guard and RMAN are both fully supported features of the Oracle Database Enterprise Edition (RMAN is also provided with Oracle Database Standard Edition).
The assumptions for this setup are :-
•The standby database is a physical standby database and backups are only taken on the standby database.
•The data file directories on the primary and standby database are identical.This simplifies the RMAN backup and recovery operations no matter which host is used.
•RMAN Recovery Catalog is required so that backups taken on one database server can be restored onto another database server. Using just the control file as the RMAN repository is not sufficient, as the primary database will have no knowledge of backups taken on the standby database.
•Primary database does not use Oracle Managed Files (OMF). When using OMF, standby database filenames can vary from those on the primary. Refer to the Appendix for modifications to the restore procedures when standby database filenames are different than those on the primary.
Recommended Oracle Database Configuration
On primary and standby databases:
• Configure Flash Recovery Area :-
Configure the Flash Recovery Area, by setting the following init.ora
parameters:
DB_RECOVERY_FILE_DEST = <mount point or ASM Disk Group>
DB_RECOVERY_FILE_DEST_SIZE = <disk space quota>
•Use a system parameter file (SPFILE) so that it can be used with any database in the Data Guard configuration. This allows restoring of the SPFILE from a backup taken on another database.
•Uniquely name archived log and backup directories for each database.
For example, on a primary database named ‘PROD’, archived logs are written to ‘/arch/PROD’ directory, whereas on a standby database named ‘STDBY’, archived logs are written to ‘/arch/STDBY’ directory. This allows RMAN maintenance commands, such as CROSSCHECK and DELETE, to be used with the LIKE option to select the appropriate archived logs for a particular database.
•Uniquely tag backups,
If backups are taken on primary and standby databases. For example, primary database full backups can be tagged ‘BOS_FULL_BACKUP’, while standby database full backups can be tagged ‘SF_FULL_BACKUP’. This allows for proper selection of backups that were taken on a particular database when performing RMAN maintenance operations.
•Enable Flashback Database on primary and standby databases.
When Flashback Database is enabled, Oracle maintains flashback logs in the Flash Recovery Area. These logs can be used to ‘rewind’ the database back to an earlier point in time, without requiring a complete restore.
==================
Data Guard enables and automates the management of a disaster recovery solution for Oracle databases located on the same campus or across the continent. Data Guard consists of a production database (also known as the primary database) and one or more standby database(s), which are transactionally consistent copies of the production database. As transactions occur in the primary database, redo data is generated and is written to the local redo logs. Data Guard automatically transfers this redo data to the standby sites and applies it to the standby databases, synchronizing them with the primary database
RMAN and Data Guard are part of the integrated Oracle High Availability technology stack, RMAN backups can be seamlessly offloaded to a physical standby database, allowing customers to gain more value out of their disaster recovery investment. Backups do not impact normal Data Guard operation – they can be taken while the standby database is in recovery or read-only mode. Backups can be used to recover either primary or standby database servers
Data Guard and RMAN are both fully supported features of the Oracle Database Enterprise Edition (RMAN is also provided with Oracle Database Standard Edition).
The assumptions for this setup are :-
•The standby database is a physical standby database and backups are only taken on the standby database.
•The data file directories on the primary and standby database are identical.This simplifies the RMAN backup and recovery operations no matter which host is used.
•RMAN Recovery Catalog is required so that backups taken on one database server can be restored onto another database server. Using just the control file as the RMAN repository is not sufficient, as the primary database will have no knowledge of backups taken on the standby database.
•Primary database does not use Oracle Managed Files (OMF). When using OMF, standby database filenames can vary from those on the primary. Refer to the Appendix for modifications to the restore procedures when standby database filenames are different than those on the primary.
Recommended Oracle Database Configuration
On primary and standby databases:
• Configure Flash Recovery Area :-
Configure the Flash Recovery Area, by setting the following init.ora
parameters:
DB_RECOVERY_FILE_DEST = <mount point or ASM Disk Group>
DB_RECOVERY_FILE_DEST_SIZE = <disk space quota>
•Use a system parameter file (SPFILE) so that it can be used with any database in the Data Guard configuration. This allows restoring of the SPFILE from a backup taken on another database.
•Uniquely name archived log and backup directories for each database.
For example, on a primary database named ‘PROD’, archived logs are written to ‘/arch/PROD’ directory, whereas on a standby database named ‘STDBY’, archived logs are written to ‘/arch/STDBY’ directory. This allows RMAN maintenance commands, such as CROSSCHECK and DELETE, to be used with the LIKE option to select the appropriate archived logs for a particular database.
•Uniquely tag backups,
If backups are taken on primary and standby databases. For example, primary database full backups can be tagged ‘BOS_FULL_BACKUP’, while standby database full backups can be tagged ‘SF_FULL_BACKUP’. This allows for proper selection of backups that were taken on a particular database when performing RMAN maintenance operations.
•Enable Flashback Database on primary and standby databases.
When Flashback Database is enabled, Oracle maintains flashback logs in the Flash Recovery Area. These logs can be used to ‘rewind’ the database back to an earlier point in time, without requiring a complete restore.
Friday, September 11, 2009
Creating a Duplicate Database on a New Host
Duplicate database from server A to server B (Non ASM)
=============================================================
Assumed database names:
Primary Database SID: PROD
Duplicate Database SID: AUX
RMAN Catalog SID: RMAN
Steps
1. Backup the primary database.
2. Determine how much disk space will be required.
3. Ensuring you have enough space on your target server.
4. Making the backup available for the duplicate process.
5. Creating init.ora & admin directories for the duplicate database.
6. Checking connections to primary database and RMAN catalog.
7. Prepare RMAN duplicate script.
8. Execute the RMAN script.
=========================================================
Backup of the primary database
Host A (Target)
Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile.
If you are planning to duplicate a subset of the database refer to second script illustrates the RMAN command to backing up certain tablespaces.
[A]This command will perform a full database backup including archivelogs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
[B]This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' tablespace SYSTEM, SYSAUX, UNDO, USERS;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
2. Determine how much disk space will be required
[A] This query will calculte the size of the whole DB instead of subset of the Database...If you are going for Subset then pls check the Script..B
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this:-
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
13491 75 22.140625 13588.1406
[B] This script will calculate the Size of the subset of the database..only for the sapce required for these tablespaces ['SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS']
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
1062 75 22.140625 1159.14063
You can check the diff in the output of the script related to the size of files and Database..
3. Ensuring you have enough space on your target server [HOST B]
Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database
--Sample output showing the space available on your filesystem--
[oracle@linux] df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/vg01/root 9.9G 2.8G 6.6G 30% /
/dev/sda1 145M 16M 122M 12% /boot
none 999M 0 999M 0% /dev/shm
/dev/vg01/tmp 2.0G 383M 1.5G 20% /tmp
/dev/vg01/u01 20G 12G 7.0G 62% /u01
/dev/vg01/u02 4.9G 1010M 3.6G 22% /u02
/dev/vg01/backups 5.9G 1.2G 4.4G 22% /backups
/dev/vg01/oradata 15G 13G 2.0G 87% /oradata
4. Making the backup available for the duplicate process
If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B.
Also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery
RMAN> list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1
List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00
5. Creating the init.ora & administration directories for the duplicate database
Host B(AUX)
Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database with bare minimum settings.
Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)
# +----------------------------------------+
# | FILE : initAUX.ora |
# | DATABASE NAME : AUX |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
cluster_database=false (in case the production is a rac environment)
# Set the below to the location of the duplicate clone control file.
control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')
# Set the below for the from and to location for all data files / redo
# logs to be cloned.
db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0
------------EOF----------
Following the creation of the initAUX.ora startup nomount the auxiliary instance.
[oracle@linux]export ORACLE_SID=AUX
[oracle@linux] sqlplus '/as sysdba'
SQLPLUS> startup nomount;
6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.
Host B(AUX)
Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.
[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’
[oracle@linux]% sqlplus rman/rman@RMAN (not mandatory)
7. Prepare RMAN duplicate script.
In a working directory on Host B create an RMAN script file duplicate.rcv. The example below
[A] shows the command for a complete duplicate
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
[B] skips the tablespaces which are not required in the duplicate and
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX skip tablespace ABC, XYZ;
}
[C] provide the syntax required for a point in time duplicate.
run {
set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
8. Execute the RMAN script.
Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
[oracle@linux] export ORACLE_SID=AUX
[oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary /
RMAN> @duplicate.rcv
Vijay Kumar...
=============================================================
Assumed database names:
Primary Database SID: PROD
Duplicate Database SID: AUX
RMAN Catalog SID: RMAN
Steps
1. Backup the primary database.
2. Determine how much disk space will be required.
3. Ensuring you have enough space on your target server.
4. Making the backup available for the duplicate process.
5. Creating init.ora & admin directories for the duplicate database.
6. Checking connections to primary database and RMAN catalog.
7. Prepare RMAN duplicate script.
8. Execute the RMAN script.
=========================================================
Backup of the primary database
Host A (Target)
Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile.
If you are planning to duplicate a subset of the database refer to second script illustrates the RMAN command to backing up certain tablespaces.
[A]This command will perform a full database backup including archivelogs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
[B]This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' tablespace SYSTEM, SYSAUX, UNDO, USERS;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
2. Determine how much disk space will be required
[A] This query will calculte the size of the whole DB instead of subset of the Database...If you are going for Subset then pls check the Script..B
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this:-
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
13491 75 22.140625 13588.1406
[B] This script will calculate the Size of the subset of the database..only for the sapce required for these tablespaces ['SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS']
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
1062 75 22.140625 1159.14063
You can check the diff in the output of the script related to the size of files and Database..
3. Ensuring you have enough space on your target server [HOST B]
Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database
--Sample output showing the space available on your filesystem--
[oracle@linux] df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/vg01/root 9.9G 2.8G 6.6G 30% /
/dev/sda1 145M 16M 122M 12% /boot
none 999M 0 999M 0% /dev/shm
/dev/vg01/tmp 2.0G 383M 1.5G 20% /tmp
/dev/vg01/u01 20G 12G 7.0G 62% /u01
/dev/vg01/u02 4.9G 1010M 3.6G 22% /u02
/dev/vg01/backups 5.9G 1.2G 4.4G 22% /backups
/dev/vg01/oradata 15G 13G 2.0G 87% /oradata
4. Making the backup available for the duplicate process
If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B.
Also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery
RMAN> list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1
List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00
5. Creating the init.ora & administration directories for the duplicate database
Host B(AUX)
Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database with bare minimum settings.
Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)
# +----------------------------------------+
# | FILE : initAUX.ora |
# | DATABASE NAME : AUX |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
cluster_database=false (in case the production is a rac environment)
# Set the below to the location of the duplicate clone control file.
control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')
# Set the below for the from and to location for all data files / redo
# logs to be cloned.
db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0
------------EOF----------
Following the creation of the initAUX.ora startup nomount the auxiliary instance.
[oracle@linux]export ORACLE_SID=AUX
[oracle@linux] sqlplus '/as sysdba'
SQLPLUS> startup nomount;
6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.
Host B(AUX)
Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.
[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’
[oracle@linux]% sqlplus rman/rman@RMAN (not mandatory)
7. Prepare RMAN duplicate script.
In a working directory on Host B create an RMAN script file duplicate.rcv. The example below
[A] shows the command for a complete duplicate
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
[B] skips the tablespaces which are not required in the duplicate and
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX skip tablespace ABC, XYZ;
}
[C] provide the syntax required for a point in time duplicate.
run {
set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
8. Execute the RMAN script.
Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
[oracle@linux] export ORACLE_SID=AUX
[oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary /
RMAN> @duplicate.rcv
Vijay Kumar...
RMAN Stored Scripts in the Recovery Catalog
Stored scripts offer an alternative to command files for managing frequently used sequences of RMAN commands.
The advantage of a stored script over a command file is that a stored script is always available to any RMAN client that can connect to the target database and recovery catalog, whereas command files are only available if the RMAN client has access to the file system on which they are stored.
Stored scripts can be global or local. A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database. A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.
Note that to work with stored scripts, even global ones, you must be connected to both a recovery catalog and a target instance
Creating Stored Scripts: CREATE SCRIPT
Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE SCRIPT command, as shown in this example:
CREATE SCRIPT full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
Examine the output. If no errors are displayed, then the script was successfully created and stored in the recovery catalog
For a global script, the syntax is similar:
CREATE GLOBAL SCRIPT global_full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
You can also provide a COMMENT with descriptive information:
CREATE GLOBAL SCRIPT global_full_db_backup
COMMENT 'use only with ARCHIVELOG mode databases'
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
Finally, you can create a local or global script, reading its contents from a text file:
CREATE SCRIPT full_db_backup FROM FILE 'script_file.txt';
The file must begin with a { character, contain a series of commands valid within a RUN block, and end with a } character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard
Running Stored Scripts: EXECUTE SCRIPT
============================================
To run a stored script, connect to the target database and recovery catalog, and use EXECUTE SCRIPT. EXECUTE SCRIPT requires a RUN block, as shown:
RUN { EXECUTE SCRIPT full_db_backup; }
This command invokes a local script if one is with the name specified. If no local script is found, but there is a global script with the name specified, RMAN will execute the global script.
You can also use EXECUTE GLOBAL SCRIPT to control which script is invoked if a local and a global script have the same name
Assuming there is no local script called global_full_db_backup, the following two commands have the same effect:
RUN { EXECUTE GLOBAL SCRIPT global_full_db_backup; }
RUN { EXECUTE SCRIPT global_full_db_backup; }
Executing a global script only affects the connected target database; to run a global script across multiple databases, you must connect the RMAN client to each one separately and execute the script.
Your script will use the automatic channels configured at the time you execute the script. Use ALLOCATE CHANNEL commands in the script if you need to override the configured channels
Note If because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will not execute.
Displaying a Stored Script: PRINT SCRIPT
===============================================
The PRINT SCRIPT command displays a stored script or writes it out to a file. With RMAN connected to the target database and recovery catalog, use the PRINT SCRIPT command as shown here:
PRINT SCRIPT full_db_backup;
To send the contents of a script to a file, use this form of the command:
PRINT SCRIPT full_db_backup TO FILE 'my_script_file.txt';
For global scripts, the analogous syntax would be:
PRINT GLOBAL SCRIPT global_full_db_backup;
and
PRINT GLOBAL SCRIPT global_full_db_backup TO FILE 'script_file.txt';
Listing Stored Scripts: LIST SCRIPT NAMES
===============================================
Use the LIST SCRIPT NAMES command to display the names of scripts defined in the recovery catalog. This command displays the names of all stored scripts, both global and local, that can be executed for the currently connected target database:
LIST SCRIPT NAMES;
If RMAN is not connected to a target database when the LIST SCRIPT NAMES command is run, then RMAN will respond with an error.
To view only global script names, use this form of the command:
LIST GLOBAL SCRIPT NAMES;
To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use this form of the command:
LIST ALL SCRIPT NAMES;
The output will indicate for each script listed which target database the script is defined for (or whether a script is global).
Note:
LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES are the only commands that work when RMAN is connected to a recovery catalog without connecting to a target instance.
Updating Stored Scripts: REPLACE SCRIPT
==============================================
To update stored scripts, connect to the target database and recovery catalog and use the REPLACE SCRIPT command. If the script does not already exist, then RMAN creates it.
This command updates stored script script full_backup with new contents:
REPLACE SCRIPT full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Global scripts can be updated using the REPLACE GLOBAL SCRIPT command when connected to a recovery catalog, as follows:
REPLACE GLOBAL SCRIPT global_db_full_backup
COMMENT 'A script for full backup to be used with any database'
{
BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
}
Deleting Stored Scripts: DELETE SCRIPT
=============================================
To delete a stored script from the recovery catalog, connect to the catalog and a target database, and use the DELETE SCRIPT command:
DELETE SCRIPT 'full_backup';
To delete a global stored script, use DELETE GLOBAL SCRIPT:
DELETE GLOBAL SCRIPT 'global_full_db_backup';
If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists. So, if you were to enter the command
DELETE SCRIPT 'global_full_db_backup';
RMAN would look for a script 'global_full_db_backup' defined for the connected target database, and if it did not find one, it would search the global scripts for a script called 'global_full_backup' and delete that script.
Starting the RMAN Client and Running a Stored Script
===========================================================
To run the RMAN client and start a stored script in the recovery catalog on startup, use the SCRIPT argument when starting the RMAN client.
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb SCRIPT 'full_backup';
You must connect to a recovery catalog (which contains the stored script) and target database (to which the script will apply) when starting the RMAN client
Restrictions on Stored Script Names
==========================================
There are some issues to be aware of about how RMAN resolves script names, especially when a local and global script share the same name.
• RMAN permits but generally does not require that you use quotes around the name of a stored script. However, if the name begins with a digit or if the name is an RMAN reserved word, you will have to put quotes around the name to use it as a stored script name. Consider avoiding stored script names that begin with characters other than A-Z or that are the same as RMAN reserved words.
• When starting the RMAN client with a SCRIPT argument on the command line, if local and global scripts are defined with the same name, then RMAN will always execute the local script.
• For the EXECUTE SCRIPT, DELETE SCRIPT and PRINT SCRIPT commands, if the script name passed as an argument is not the name of a script defined for the connected target instance, RMAN will look for a global script by the same name to execute, delete or print. For example, if the a stored script global_full_backup is in the recovery catalog as a global script, but no local stored script global_full_backup is defined for the target database, the following command will delete the global script:
• DELETE SCRIPT global_full_db_backup;
Consider using some naming convention to avoid mistakes due to confusion between global stored scripts and local stored scripts
Vijay Kumar
The advantage of a stored script over a command file is that a stored script is always available to any RMAN client that can connect to the target database and recovery catalog, whereas command files are only available if the RMAN client has access to the file system on which they are stored.
Stored scripts can be global or local. A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database. A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.
Note that to work with stored scripts, even global ones, you must be connected to both a recovery catalog and a target instance
Creating Stored Scripts: CREATE SCRIPT
Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE SCRIPT command, as shown in this example:
CREATE SCRIPT full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
Examine the output. If no errors are displayed, then the script was successfully created and stored in the recovery catalog
For a global script, the syntax is similar:
CREATE GLOBAL SCRIPT global_full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
You can also provide a COMMENT with descriptive information:
CREATE GLOBAL SCRIPT global_full_db_backup
COMMENT 'use only with ARCHIVELOG mode databases'
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
Finally, you can create a local or global script, reading its contents from a text file:
CREATE SCRIPT full_db_backup FROM FILE 'script_file.txt';
The file must begin with a { character, contain a series of commands valid within a RUN block, and end with a } character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard
Running Stored Scripts: EXECUTE SCRIPT
============================================
To run a stored script, connect to the target database and recovery catalog, and use EXECUTE SCRIPT. EXECUTE SCRIPT requires a RUN block, as shown:
RUN { EXECUTE SCRIPT full_db_backup; }
This command invokes a local script if one is with the name specified. If no local script is found, but there is a global script with the name specified, RMAN will execute the global script.
You can also use EXECUTE GLOBAL SCRIPT to control which script is invoked if a local and a global script have the same name
Assuming there is no local script called global_full_db_backup, the following two commands have the same effect:
RUN { EXECUTE GLOBAL SCRIPT global_full_db_backup; }
RUN { EXECUTE SCRIPT global_full_db_backup; }
Executing a global script only affects the connected target database; to run a global script across multiple databases, you must connect the RMAN client to each one separately and execute the script.
Your script will use the automatic channels configured at the time you execute the script. Use ALLOCATE CHANNEL commands in the script if you need to override the configured channels
Note If because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will not execute.
Displaying a Stored Script: PRINT SCRIPT
===============================================
The PRINT SCRIPT command displays a stored script or writes it out to a file. With RMAN connected to the target database and recovery catalog, use the PRINT SCRIPT command as shown here:
PRINT SCRIPT full_db_backup;
To send the contents of a script to a file, use this form of the command:
PRINT SCRIPT full_db_backup TO FILE 'my_script_file.txt';
For global scripts, the analogous syntax would be:
PRINT GLOBAL SCRIPT global_full_db_backup;
and
PRINT GLOBAL SCRIPT global_full_db_backup TO FILE 'script_file.txt';
Listing Stored Scripts: LIST SCRIPT NAMES
===============================================
Use the LIST SCRIPT NAMES command to display the names of scripts defined in the recovery catalog. This command displays the names of all stored scripts, both global and local, that can be executed for the currently connected target database:
LIST SCRIPT NAMES;
If RMAN is not connected to a target database when the LIST SCRIPT NAMES command is run, then RMAN will respond with an error.
To view only global script names, use this form of the command:
LIST GLOBAL SCRIPT NAMES;
To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use this form of the command:
LIST ALL SCRIPT NAMES;
The output will indicate for each script listed which target database the script is defined for (or whether a script is global).
Note:
LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES are the only commands that work when RMAN is connected to a recovery catalog without connecting to a target instance.
Updating Stored Scripts: REPLACE SCRIPT
==============================================
To update stored scripts, connect to the target database and recovery catalog and use the REPLACE SCRIPT command. If the script does not already exist, then RMAN creates it.
This command updates stored script script full_backup with new contents:
REPLACE SCRIPT full_db_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Global scripts can be updated using the REPLACE GLOBAL SCRIPT command when connected to a recovery catalog, as follows:
REPLACE GLOBAL SCRIPT global_db_full_backup
COMMENT 'A script for full backup to be used with any database'
{
BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
}
Deleting Stored Scripts: DELETE SCRIPT
=============================================
To delete a stored script from the recovery catalog, connect to the catalog and a target database, and use the DELETE SCRIPT command:
DELETE SCRIPT 'full_backup';
To delete a global stored script, use DELETE GLOBAL SCRIPT:
DELETE GLOBAL SCRIPT 'global_full_db_backup';
If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists. So, if you were to enter the command
DELETE SCRIPT 'global_full_db_backup';
RMAN would look for a script 'global_full_db_backup' defined for the connected target database, and if it did not find one, it would search the global scripts for a script called 'global_full_backup' and delete that script.
Starting the RMAN Client and Running a Stored Script
===========================================================
To run the RMAN client and start a stored script in the recovery catalog on startup, use the SCRIPT argument when starting the RMAN client.
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb SCRIPT 'full_backup';
You must connect to a recovery catalog (which contains the stored script) and target database (to which the script will apply) when starting the RMAN client
Restrictions on Stored Script Names
==========================================
There are some issues to be aware of about how RMAN resolves script names, especially when a local and global script share the same name.
• RMAN permits but generally does not require that you use quotes around the name of a stored script. However, if the name begins with a digit or if the name is an RMAN reserved word, you will have to put quotes around the name to use it as a stored script name. Consider avoiding stored script names that begin with characters other than A-Z or that are the same as RMAN reserved words.
• When starting the RMAN client with a SCRIPT argument on the command line, if local and global scripts are defined with the same name, then RMAN will always execute the local script.
• For the EXECUTE SCRIPT, DELETE SCRIPT and PRINT SCRIPT commands, if the script name passed as an argument is not the name of a script defined for the connected target instance, RMAN will look for a global script by the same name to execute, delete or print. For example, if the a stored script global_full_backup is in the recovery catalog as a global script, but no local stored script global_full_backup is defined for the target database, the following command will delete the global script:
• DELETE SCRIPT global_full_db_backup;
Consider using some naming convention to avoid mistakes due to confusion between global stored scripts and local stored scripts
Vijay Kumar
Thursday, September 10, 2009
CRSCTL
Find below various commands which can be used to administer Oracle Clusterware using crsctl.
There are options for CRSCTL which can be seen using the fllowing command
#crsctl
Or
#crsctl help
To determine software version (binary version of the software/operating version on a particular cluster node) use
#crsctl query crs softwareversion [] - lists the version of CRS software installed
Oracle Clusterware version on node [Rac01] is [x.x.x.x.x]
#crsctl query crs activeversion - lists the CRS software operating version
Oracle Clusterware active version on the cluster is [x.x.x.x.x]
Start Oracle Clusterware --> #crsctl start crs
Stop Oracle Clusterware --> #crsctl stop crs
Enable Oracle Clusterware --> #crsctl enable crs
It enables automatic startup of Clusterware daemons
Disable Oracle Clusterware --> #crsctl disable crs
NOTE:- 1) This command disables OC from being started in
a subsequent restart
2) This Does not stop the currently running OC Stack
@===== Check CRS Status =====@
#crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
@===== To see particular daemon status =====@
$crsctl check cssd
Cluster Synchronization Services appears healthy
$crsctl check crsd
Cluster Ready Services appears healthy
$crsctl check evmd
Event Manager appears healthy
You can also check Clusterware status on the nodes using
$crsctl check cluster
Rac01 ONLINE
Rac02 ONLINE
..... ......
@===== Checking Voting disk Location =====@
$crsctl query css votedisk
0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).
Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.
Add Voting disk --> #crsctl add css votedisk path
Remove Voting disk --> #crsctl delete css votedisk path
@===== Enabling Debugging of Oracle Clusterware Components =====@
You can enable debugging for the Oracle Cluster daemons by running crsctl commands [debugging_level is a number from 1 to 5]
# crsctl debug log component module:debugging_level
To enable tracing for the EVMD module of the css component, you could use the following command:
# crsctl debug log css EVMD:1
@===== Locating the Oracle Clusterware Alert Log =====@
The location of the Oracle Clusterware log file is CRS_home/log/hostname/alerthostname.log, where CRS_home is the directory in which Oracle Clusterware was installed and hostname is the host name of the local node
To obtain a list of the resources available for debugging, use the following command:
# crs_stat
=====@@@@@@@@@@@@@@@@@@@=====
USAGE
============================
crsctl
Usage: crsctl check crs - checks the viability of the CRS stack
crsctl check cssd - checks the viability of CSS
crsctl check crsd - checks the viability of CRS
crsctl check evmd - checks the viability of EVM
crsctl set css - sets a parameter override
crsctl get css - gets the value of a CSS parameter
crsctl unset css - sets CSS parameter to its default
crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk - adds a new voting disk
crsctl delete css votedisk - removes a voting disk
crsctl enable crs - enables startup for all CRS daemons
crsctl disable crs - disables startup for all CRS daemons
crsctl start crs - starts all CRS daemons.
crsctl stop crs - stops all CRS daemons. Stops CRS resources in case of cluster.
crsctl start resources - starts CRS resources.
crsctl stop resources - stops CRS resources.
crsctl debug statedump evm - dumps state info for evm objects
crsctl debug statedump crs - dumps state info for crs objects
crsctl debug statedump css - dumps state info for css objects
crsctl debug log css [module:level]{,module:level} ...
- Turns on debugging for CSS
crsctl debug trace css - dumps CSS in-memory tracing cache
crsctl debug log crs [module:level]{,module:level} ...
- Turns on debugging for CRS
crsctl debug trace crs - dumps CRS in-memory tracing cache
crsctl debug log evm [module:level]{,module:level} ...
- Turns on debugging for EVM
crsctl debug trace evm - dumps EVM in-memory tracing cache
crsctl debug log res turns on debugging for resources
crsctl query crs softwareversion [] - lists the version of CRS software installed
crsctl query crs activeversion - lists the CRS software operating version
crsctl lsmodules css - lists the CSS modules that can be used for debugging
crsctl lsmodules crs - lists the CRS modules that can be used for debugging
crsctl lsmodules evm - lists the EVM modules that can be used for debugging
If necesary any of these commands can be run with additional tracing by
adding a "trace" argument at the very front.
Example: crsctl trace check css
Vijay Kumar....
There are options for CRSCTL which can be seen using the fllowing command
#crsctl
Or
#crsctl help
To determine software version (binary version of the software/operating version on a particular cluster node) use
#crsctl query crs softwareversion [
Oracle Clusterware version on node [Rac01] is [x.x.x.x.x]
#crsctl query crs activeversion - lists the CRS software operating version
Oracle Clusterware active version on the cluster is [x.x.x.x.x]
Start Oracle Clusterware --> #crsctl start crs
Stop Oracle Clusterware --> #crsctl stop crs
Enable Oracle Clusterware --> #crsctl enable crs
It enables automatic startup of Clusterware daemons
Disable Oracle Clusterware --> #crsctl disable crs
NOTE:- 1) This command disables OC from being started in
a subsequent restart
2) This Does not stop the currently running OC Stack
@===== Check CRS Status =====@
#crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
@===== To see particular daemon status =====@
$crsctl check cssd
Cluster Synchronization Services appears healthy
$crsctl check crsd
Cluster Ready Services appears healthy
$crsctl check evmd
Event Manager appears healthy
You can also check Clusterware status on the nodes using
$crsctl check cluster
Rac01 ONLINE
Rac02 ONLINE
..... ......
@===== Checking Voting disk Location =====@
$crsctl query css votedisk
0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).
Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.
Add Voting disk --> #crsctl add css votedisk path
Remove Voting disk --> #crsctl delete css votedisk path
@===== Enabling Debugging of Oracle Clusterware Components =====@
You can enable debugging for the Oracle Cluster daemons by running crsctl commands [debugging_level is a number from 1 to 5]
# crsctl debug log component module:debugging_level
To enable tracing for the EVMD module of the css component, you could use the following command:
# crsctl debug log css EVMD:1
@===== Locating the Oracle Clusterware Alert Log =====@
The location of the Oracle Clusterware log file is CRS_home/log/hostname/alerthostname.log, where CRS_home is the directory in which Oracle Clusterware was installed and hostname is the host name of the local node
To obtain a list of the resources available for debugging, use the following command:
# crs_stat
=====@@@@@@@@@@@@@@@@@@@=====
USAGE
============================
crsctl
Usage: crsctl check crs - checks the viability of the CRS stack
crsctl check cssd - checks the viability of CSS
crsctl check crsd - checks the viability of CRS
crsctl check evmd - checks the viability of EVM
crsctl set css
crsctl get css
crsctl unset css
crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk
crsctl delete css votedisk
crsctl enable crs - enables startup for all CRS daemons
crsctl disable crs - disables startup for all CRS daemons
crsctl start crs - starts all CRS daemons.
crsctl stop crs - stops all CRS daemons. Stops CRS resources in case of cluster.
crsctl start resources - starts CRS resources.
crsctl stop resources - stops CRS resources.
crsctl debug statedump evm - dumps state info for evm objects
crsctl debug statedump crs - dumps state info for crs objects
crsctl debug statedump css - dumps state info for css objects
crsctl debug log css [module:level]{,module:level} ...
- Turns on debugging for CSS
crsctl debug trace css - dumps CSS in-memory tracing cache
crsctl debug log crs [module:level]{,module:level} ...
- Turns on debugging for CRS
crsctl debug trace crs - dumps CRS in-memory tracing cache
crsctl debug log evm [module:level]{,module:level} ...
- Turns on debugging for EVM
crsctl debug trace evm - dumps EVM in-memory tracing cache
crsctl debug log res
crsctl query crs softwareversion [
crsctl query crs activeversion - lists the CRS software operating version
crsctl lsmodules css - lists the CSS modules that can be used for debugging
crsctl lsmodules crs - lists the CRS modules that can be used for debugging
crsctl lsmodules evm - lists the EVM modules that can be used for debugging
If necesary any of these commands can be run with additional tracing by
adding a "trace" argument at the very front.
Example: crsctl trace check css
Vijay Kumar....
Subscribe to:
Posts (Atom)