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.
Subscribe to:
Posts (Atom)