Problem Description
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS;
drop Tablespace UNDOTBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace
Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.
Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.
SQL>Create PFILE from SPFILE;
2)Edit pfile and set undo management to manual.
undo_management = manual
3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)
4)Mount the database using new pfile.
Startup mount pfile='fullpath to pfile'
5)Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;
6)Open the database.
sql>Alter Database Open;
7)Drop the undo segments,
sql>Drop Rollback Segment "_SYSSMU1$";
......
8)Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;
9)Add the new undo tablespace.
10) Shutdown Immediate;
11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.
12) Startup the Database.
Theoretical solution
So, why would anyone be dropping the current UNDO tablespace? Most likely to create a new UNDO tablespace to replace it, so why go through all of your listed gyrations when one can:
SQL> create undo tablespace undotbs2
2 datafile '/u01/data/undotbs2_01.dbf' size 2048M,
3 '/u02/data/undotbs2_02.dbf' size 2048M
3 blocksize 8192
4 extent management local autoallocate;
Tablespace created.
SQL> alter system set undo_tablespace = 'UNDOTBS2';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
...
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL>
As you need an UNDO tablespace present it only makes sense to create the new UNDO tablespace before dropping the old one. And, as you need to shutdown the database to clear all old transactions from the current, defective UNDO tablespace you're assured of having no active segments if you set the new UNDO tablespace as THE UNDO tablespace before you shutdown and restart the database.
I can't understand why you make simple topics complicated.
Friday, January 29, 2010
Wednesday, January 27, 2010
Moving datafiles when asm is involved
Moving Datafiles
Online Datafile Move
Steps
Copy datafile of the tablespace to the new diskgroup
Bring tablespace to offline mode
Switch datafile to its new location
Recover the tablespace
Bring tablespace to online mode
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> copy datafile 169 to ‘+DGRP’;
4> release channel disk1;
5> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
Starting backup at 12-JUN-08 21:10:19
channel disk1: starting datafile copy
input datafile fno=00169 name=+DGROUP1/dds/datafile/cmp_default.463.645917347
output filename=+DGRP/dds/datafile/cmp_default.1093.657234623 tag=TAG20080612T211021 recid=7533 stamp=657234623
channel disk1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JUN-08 21:10:25
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:10:25
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657234626.7000.657234627 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:10:30
released channel: disk1
RMAN> sql ‘alter tablespace cmp_default offline‘;
sql statement: alter tablespace cmp_default offline
RMAN> switch datafile 169 to copy;
datafile 169 switched to datafile copy “+DGRP/dds/datafile/cmp_default.1093.657234623″
RMAN> recover tablespace cmp_default;
Starting recover at 12-JUN-08 21:13:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUN-08 21:13:26
RMAN> sql ‘alter tablespace cmp_default online‘;
sql statement: alter tablespace cmp_default online
RMAN>
Move tablespace (Multiple datafiles)
Online Tablespace Move(Multiple Datafiles)
Steps
Copy tablespace (allfiles) to the new diskgroup
Bring tablespace to offline mode
Switch tablespace (all datafiles) to its new location
Recover the tablespace
Bring tablespace to online mode
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;
5> ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;
6> backup as copy tablespace USERS format ‘+DGRP’;
7> release channel disk1;
8> release channel disk2;
9> release channel disk3;
10> release channel disk4;
11> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
allocated channel: disk2
channel disk2: sid=383 devtype=DISK
allocated channel: disk3
channel disk3: sid=232 devtype=DISK
allocated channel: disk4
channel disk4: sid=68 devtype=DISK
Starting backup at 12-JUN-08 21:38:03
channel disk1: starting datafile copy
input datafile fno=00112 name=+DGROUP1/dds/datafile/users.322.644072045
channel disk2: starting datafile copy
input datafile fno=00113 name=+DGROUP1/dds/datafile/users.321.644072157
channel disk3: starting datafile copy
input datafile fno=00114 name=+DGROUP1/dds/datafile/users.320.644072323
channel disk4: starting datafile copy
input datafile fno=00004 name=+DGROUP1/dds/datafile/users.261.643638281
output filename=+DGRP/dds/datafile/users.1097.657236289 tag=TAG20080612T213804 recid=7535 stamp=657236396
channel disk4: datafile copy complete, elapsed time: 00:01:55
output filename=+DGRP/dds/datafile/users.1094.657236285 tag=TAG20080612T213804 recid=7536 stamp=657236494
channel disk2: datafile copy complete, elapsed time: 00:03:31
output filename=+DGRP/dds/datafile/users.1095.657236285 tag=TAG20080612T213804 recid=7538 stamp=657236497
channel disk1: datafile copy complete, elapsed time: 00:03:34
output filename=+DGRP/dds/datafile/users.1096.657236289 tag=TAG20080612T213804 recid=7537 stamp=657236496
channel disk3: datafile copy complete, elapsed time: 00:03:34
Finished backup at 12-JUN-08 21:41:38
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:41:38
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236498.6997.657236501 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:41:46
released channel: disk1
released channel: disk2
released channel: disk3
released channel: disk4
RMAN> sql ‘alter tablespace users offline’;
sql statement: alter tablespace users offline
RMAN> switch tablespace users to copy;
datafile 4 switched to datafile copy “+DGRP/dds/datafile/users.1097.657236289″
datafile 112 switched to datafile copy “+DGRP/dds/datafile/users.1095.657236285″
datafile 113 switched to datafile copy “+DGRP/dds/datafile/users.1094.657236285″
datafile 114 switched to datafile copy “+DGRP/dds/datafile/users.1096.657236289″
RMAN> recover tablespace users;
Starting recover at 12-JUN-08 21:44:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 12-JUN-08 21:44:40
RMAN> sql ‘alter tablespace users online’;
sql statement: alter tablespace users online
RMAN>
Less High Available Online Tablespace Move(Multiple Datafiles)
Steps
Bring tablespace to offline mode
Copy tablespace (allfiles) to the new diskgroup
Switch tablespace (all datafiles) to its new location
Bring tablespace to online mode
Different from the previous one this solution is the one explained in Metalink Note:390274.1
Taking datafiles offline before starting the copy operation makes recovery unnecessary. However if the tablespaces are too large (Big File tablespaces are heavily used in our datawarehouse. A single file of size 5 TB), tablespace will be unavailable till the end of copy operation.
Example
RMAN> sql ‘alter tablespace TRF_BIG_B_2006M05 offline’;
sql statement: alter tablespace TRF_BIG_B_2006M05 offline
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> backup as copy tablespace TRF_BIG_B_2006M05 format ‘+DGRP’;
4> release channel disk1;
5> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
Starting backup at 12-JUN-08 21:49:17
channel disk1: starting datafile copy
input datafile fno=00030 name=+DGROUP1/dds/datafile/trf_big_b_2006m05.388.644681639
output filename=+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959 tag=TAG20080612T214918 recid=7543 stamp=657236968
channel disk1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 12-JUN-08 21:49:33
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:49:33
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236973.6993.657236975 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:49:37
released channel: disk1
RMAN> switch tablespace TRF_BIG_B_2006M05 to copy;
datafile 30 switched to datafile copy “+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959″
RMAN> sql ‘alter tablespace TRF_BIG_B_2006M05 online’;
sql statement: alter tablespace TRF_BIG_B_2006M05 online
RMAN>
Online More than One Tablespace Move
Steps
Copy all tablespaces (allfiles) to the new diskgroup
Do for all tablespaces you move
Bring tablespace to offline mode
Switch tablespace (all datafiles) to its new location
Recover the tablespace
Bring tablespace to online mode
If you move more than one tablespace using this approach will decrease the manual work you do.
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4> backup as copy tablespace COMPRESSED_BTS,COMPRESS_BTS_02 format ‘+DGRP’;
5> release channel disk1;
6> release channel disk2;
7> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
allocated channel: disk2
channel disk2: sid=383 devtype=DISK
Starting backup at 12-JUN-08 21:56:17
channel disk1: starting datafile copy
input datafile fno=00163 name=+DGROUP1/dds/datafile/compressed_bts.298.644699643
channel disk2: starting datafile copy
input datafile fno=00164 name=+DGROUP1/dds/datafile/compress_bts_02.297.644708473
output filename=+DGRP/dds/datafile/compress_bts_02.1100.657237379 tag=TAG20080612T215617 recid=7545 stamp=657237431
channel disk2: datafile copy complete, elapsed time: 00:00:55
output filename=+DGRP/dds/datafile/compressed_bts.1099.657237379 tag=TAG20080612T215617 recid=7546 stamp=657238158
channel disk1: datafile copy complete, elapsed time: 00:13:10
Finished backup at 12-JUN-08 22:09:28
Starting Control File and SPFILE Autobackup at 12-JUN-08 22:09:29
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657238169.6991.657238171 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 22:09:33
released channel: disk1
released channel: disk2
RMAN> sql ‘alter tablespace COMPRESSED_BTS offline’;
sql statement: alter tablespace COMPRESSED_BTS offline
RMAN> sql ‘alter tablespace COMPRESS_BTS_02 offline’;
sql statement: alter tablespace COMPRESS_BTS_02 offline
RMAN> switch tablespace COMPRESSED_BTS,COMPRESS_BTS_02 to copy;
datafile 163 switched to datafile copy “+DGRP/dds/datafile/compressed_bts.1099.657237379″
datafile 164 switched to datafile copy “+DGRP/dds/datafile/compress_bts_02.1100.657237379″
RMAN> recover tablespace COMPRESS_BTS_02,COMPRESSED_BTS;
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUN-08 22:25:25
RMAN> sql ‘alter tablespace COMPRESS_BTS_02 online’;
sql statement: alter tablespace COMPRESS_BTS_02 online
RMAN> sql ‘alter tablespace COMPRESSED_BTS online’;
sql statement: alter tablespace COMPRESSED_BTS online
RMAN>
Offline Tablespace Move
Unfortunately, if you need to move SYSTEM or SYSAUX tablespaces to some other diskgroup, you are unlucky. You need to shutdown your database for a while.
Steps
shutdown your database
startup it in mount mode
copy datafile to new diskgroup
switch datafiles to new diskgroup
Open your database
Sample
himalaya@oracle $ srv
SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jun 13 00:07:00 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 2402240 bytes
Variable Size 2843793472 bytes
Database Buffers 1.3237E+10 bytes
Redo Buffers 22708224 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
himalaya@oracle $ rman target /
Recovery Manager: Release 10.2.0.3.0 – Production on Fri Jun 13 00:13:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DDS (DBID=1378915377, not open)
RMAN> copy datafile 1 to ‘+DGRP’;
Starting backup at 13-JUN-08 00:51:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=77 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DGROUP1/dds/datafile/system.258.643638267
output filename=+DGRP/dds/datafile/system.1102.657247927 tag=TAG20080613T005205 recid=7551 stamp=657247953
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 13-JUN-08 00:52:40
Starting Control File and SPFILE Autobackup at 13-JUN-08 00:52:41
piece handle=+RGRP/dds/autobackup/2008_06_13/s_657247505.6986.657247963 comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-08 00:52:49
RMAN> switch datafile 1 to copy;
datafile 1 switched to datafile copy “+DGRP/dds/datafile/system.1102.657247927″
RMAN> alter database open;
database opened
RMAN>
Online Datafile Move
Steps
Copy datafile of the tablespace to the new diskgroup
Bring tablespace to offline mode
Switch datafile to its new location
Recover the tablespace
Bring tablespace to online mode
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> copy datafile 169 to ‘+DGRP’;
4> release channel disk1;
5> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
Starting backup at 12-JUN-08 21:10:19
channel disk1: starting datafile copy
input datafile fno=00169 name=+DGROUP1/dds/datafile/cmp_default.463.645917347
output filename=+DGRP/dds/datafile/cmp_default.1093.657234623 tag=TAG20080612T211021 recid=7533 stamp=657234623
channel disk1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JUN-08 21:10:25
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:10:25
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657234626.7000.657234627 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:10:30
released channel: disk1
RMAN> sql ‘alter tablespace cmp_default offline‘;
sql statement: alter tablespace cmp_default offline
RMAN> switch datafile 169 to copy;
datafile 169 switched to datafile copy “+DGRP/dds/datafile/cmp_default.1093.657234623″
RMAN> recover tablespace cmp_default;
Starting recover at 12-JUN-08 21:13:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUN-08 21:13:26
RMAN> sql ‘alter tablespace cmp_default online‘;
sql statement: alter tablespace cmp_default online
RMAN>
Move tablespace (Multiple datafiles)
Online Tablespace Move(Multiple Datafiles)
Steps
Copy tablespace (allfiles) to the new diskgroup
Bring tablespace to offline mode
Switch tablespace (all datafiles) to its new location
Recover the tablespace
Bring tablespace to online mode
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;
5> ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;
6> backup as copy tablespace USERS format ‘+DGRP’;
7> release channel disk1;
8> release channel disk2;
9> release channel disk3;
10> release channel disk4;
11> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
allocated channel: disk2
channel disk2: sid=383 devtype=DISK
allocated channel: disk3
channel disk3: sid=232 devtype=DISK
allocated channel: disk4
channel disk4: sid=68 devtype=DISK
Starting backup at 12-JUN-08 21:38:03
channel disk1: starting datafile copy
input datafile fno=00112 name=+DGROUP1/dds/datafile/users.322.644072045
channel disk2: starting datafile copy
input datafile fno=00113 name=+DGROUP1/dds/datafile/users.321.644072157
channel disk3: starting datafile copy
input datafile fno=00114 name=+DGROUP1/dds/datafile/users.320.644072323
channel disk4: starting datafile copy
input datafile fno=00004 name=+DGROUP1/dds/datafile/users.261.643638281
output filename=+DGRP/dds/datafile/users.1097.657236289 tag=TAG20080612T213804 recid=7535 stamp=657236396
channel disk4: datafile copy complete, elapsed time: 00:01:55
output filename=+DGRP/dds/datafile/users.1094.657236285 tag=TAG20080612T213804 recid=7536 stamp=657236494
channel disk2: datafile copy complete, elapsed time: 00:03:31
output filename=+DGRP/dds/datafile/users.1095.657236285 tag=TAG20080612T213804 recid=7538 stamp=657236497
channel disk1: datafile copy complete, elapsed time: 00:03:34
output filename=+DGRP/dds/datafile/users.1096.657236289 tag=TAG20080612T213804 recid=7537 stamp=657236496
channel disk3: datafile copy complete, elapsed time: 00:03:34
Finished backup at 12-JUN-08 21:41:38
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:41:38
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236498.6997.657236501 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:41:46
released channel: disk1
released channel: disk2
released channel: disk3
released channel: disk4
RMAN> sql ‘alter tablespace users offline’;
sql statement: alter tablespace users offline
RMAN> switch tablespace users to copy;
datafile 4 switched to datafile copy “+DGRP/dds/datafile/users.1097.657236289″
datafile 112 switched to datafile copy “+DGRP/dds/datafile/users.1095.657236285″
datafile 113 switched to datafile copy “+DGRP/dds/datafile/users.1094.657236285″
datafile 114 switched to datafile copy “+DGRP/dds/datafile/users.1096.657236289″
RMAN> recover tablespace users;
Starting recover at 12-JUN-08 21:44:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 12-JUN-08 21:44:40
RMAN> sql ‘alter tablespace users online’;
sql statement: alter tablespace users online
RMAN>
Less High Available Online Tablespace Move(Multiple Datafiles)
Steps
Bring tablespace to offline mode
Copy tablespace (allfiles) to the new diskgroup
Switch tablespace (all datafiles) to its new location
Bring tablespace to online mode
Different from the previous one this solution is the one explained in Metalink Note:390274.1
Taking datafiles offline before starting the copy operation makes recovery unnecessary. However if the tablespaces are too large (Big File tablespaces are heavily used in our datawarehouse. A single file of size 5 TB), tablespace will be unavailable till the end of copy operation.
Example
RMAN> sql ‘alter tablespace TRF_BIG_B_2006M05 offline’;
sql statement: alter tablespace TRF_BIG_B_2006M05 offline
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> backup as copy tablespace TRF_BIG_B_2006M05 format ‘+DGRP’;
4> release channel disk1;
5> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
Starting backup at 12-JUN-08 21:49:17
channel disk1: starting datafile copy
input datafile fno=00030 name=+DGROUP1/dds/datafile/trf_big_b_2006m05.388.644681639
output filename=+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959 tag=TAG20080612T214918 recid=7543 stamp=657236968
channel disk1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 12-JUN-08 21:49:33
Starting Control File and SPFILE Autobackup at 12-JUN-08 21:49:33
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657236973.6993.657236975 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:49:37
released channel: disk1
RMAN> switch tablespace TRF_BIG_B_2006M05 to copy;
datafile 30 switched to datafile copy “+DGRP/dds/datafile/trf_big_b_2006m05.1098.657236959″
RMAN> sql ‘alter tablespace TRF_BIG_B_2006M05 online’;
sql statement: alter tablespace TRF_BIG_B_2006M05 online
RMAN>
Online More than One Tablespace Move
Steps
Copy all tablespaces (allfiles) to the new diskgroup
Do for all tablespaces you move
Bring tablespace to offline mode
Switch tablespace (all datafiles) to its new location
Recover the tablespace
Bring tablespace to online mode
If you move more than one tablespace using this approach will decrease the manual work you do.
Example
RMAN> run{
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4> backup as copy tablespace COMPRESSED_BTS,COMPRESS_BTS_02 format ‘+DGRP’;
5> release channel disk1;
6> release channel disk2;
7> }
allocated channel: disk1
channel disk1: sid=450 devtype=DISK
allocated channel: disk2
channel disk2: sid=383 devtype=DISK
Starting backup at 12-JUN-08 21:56:17
channel disk1: starting datafile copy
input datafile fno=00163 name=+DGROUP1/dds/datafile/compressed_bts.298.644699643
channel disk2: starting datafile copy
input datafile fno=00164 name=+DGROUP1/dds/datafile/compress_bts_02.297.644708473
output filename=+DGRP/dds/datafile/compress_bts_02.1100.657237379 tag=TAG20080612T215617 recid=7545 stamp=657237431
channel disk2: datafile copy complete, elapsed time: 00:00:55
output filename=+DGRP/dds/datafile/compressed_bts.1099.657237379 tag=TAG20080612T215617 recid=7546 stamp=657238158
channel disk1: datafile copy complete, elapsed time: 00:13:10
Finished backup at 12-JUN-08 22:09:28
Starting Control File and SPFILE Autobackup at 12-JUN-08 22:09:29
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657238169.6991.657238171 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 22:09:33
released channel: disk1
released channel: disk2
RMAN> sql ‘alter tablespace COMPRESSED_BTS offline’;
sql statement: alter tablespace COMPRESSED_BTS offline
RMAN> sql ‘alter tablespace COMPRESS_BTS_02 offline’;
sql statement: alter tablespace COMPRESS_BTS_02 offline
RMAN> switch tablespace COMPRESSED_BTS,COMPRESS_BTS_02 to copy;
datafile 163 switched to datafile copy “+DGRP/dds/datafile/compressed_bts.1099.657237379″
datafile 164 switched to datafile copy “+DGRP/dds/datafile/compress_bts_02.1100.657237379″
RMAN> recover tablespace COMPRESS_BTS_02,COMPRESSED_BTS;
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUN-08 22:25:25
RMAN> sql ‘alter tablespace COMPRESS_BTS_02 online’;
sql statement: alter tablespace COMPRESS_BTS_02 online
RMAN> sql ‘alter tablespace COMPRESSED_BTS online’;
sql statement: alter tablespace COMPRESSED_BTS online
RMAN>
Offline Tablespace Move
Unfortunately, if you need to move SYSTEM or SYSAUX tablespaces to some other diskgroup, you are unlucky. You need to shutdown your database for a while.
Steps
shutdown your database
startup it in mount mode
copy datafile to new diskgroup
switch datafiles to new diskgroup
Open your database
Sample
himalaya@oracle $ srv
SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jun 13 00:07:00 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 2402240 bytes
Variable Size 2843793472 bytes
Database Buffers 1.3237E+10 bytes
Redo Buffers 22708224 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
himalaya@oracle $ rman target /
Recovery Manager: Release 10.2.0.3.0 – Production on Fri Jun 13 00:13:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DDS (DBID=1378915377, not open)
RMAN> copy datafile 1 to ‘+DGRP’;
Starting backup at 13-JUN-08 00:51:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=77 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DGROUP1/dds/datafile/system.258.643638267
output filename=+DGRP/dds/datafile/system.1102.657247927 tag=TAG20080613T005205 recid=7551 stamp=657247953
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 13-JUN-08 00:52:40
Starting Control File and SPFILE Autobackup at 13-JUN-08 00:52:41
piece handle=+RGRP/dds/autobackup/2008_06_13/s_657247505.6986.657247963 comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-08 00:52:49
RMAN> switch datafile 1 to copy;
datafile 1 switched to datafile copy “+DGRP/dds/datafile/system.1102.657247927″
RMAN> alter database open;
database opened
RMAN>
TO_DATE FUNCTION
Examples of the to_date function might include:
to_date('10-12-06','MM-DD-YY')
to_date('jan 2007','MON YYYY')
to_date('2007/05/31','YYYY/MM/DD')
to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
to_date('022002','mmyyyy')
to_date('12319999','MMDDYYYY')
to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)
to_date('10-12-06','MM-DD-YY')
to_date('jan 2007','MON YYYY')
to_date('2007/05/31','YYYY/MM/DD')
to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
to_date('022002','mmyyyy')
to_date('12319999','MMDDYYYY')
to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)
Redo Log File Status
To see the status of the redo log file there are two dynamic views are there.
A)V$LOG
B)V$LOGFILE
A)V$LOG:
----------
V$LOG displays redo log file information from the control file.
Status:
--------
1)UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2)CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3)ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4)CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
5)CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
6)INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
B)V$LOGFILE:
------------------
V$LOGFILE view contains information about redo log files.
1)INVALID - The file is corrupted or missing.
2)STALE -This redo log file member is new and has never been used.
3)DELETED -The file is no longer being used.
4) -The redo log file is in use and is not corrupted.
A)V$LOG
B)V$LOGFILE
A)V$LOG:
----------
V$LOG displays redo log file information from the control file.
Status:
--------
1)UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2)CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3)ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4)CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
5)CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
6)INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
B)V$LOGFILE:
------------------
V$LOGFILE view contains information about redo log files.
1)INVALID - The file is corrupted or missing.
2)STALE -This redo log file member is new and has never been used.
3)DELETED -The file is no longer being used.
4) -The redo log file is in use and is not corrupted.
Redo logs
Adding a New Redo Logfile Group
To add a new Redo Logfile group to the database give the following command
SQL>alter database add logfile group 3
‘/u01/oracle/ica/log3.ora’ size 10M;
Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.
Adding Members to an existing group
To add new member to an existing group give the following command
SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile
Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.
Dropping Members from a group
You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command
SQL>alter system switch logfile;
The following command can be used to drop a logfile member
SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Dropping Logfile Group
Similarly, you can also drop logfile group only if the database is having more than two groups and if it is not the current group.
SQL>alter database drop logfile group 3;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Resizing Logfiles
You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.
Renaming or Relocating Logfiles
To Rename or Relocate Logfiles perform the following steps
For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log1.ora’, then do the following
Steps
1. Shutdown the database
SQL>shutdown immediate;
2. Move the logfile from Old location to new location using operating system command
$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log1.ora
3. Start and mount the database
SQL>startup mount
4. Now give the following command to change the location in controlfile
SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;
5. Open the database
SQL>alter database open;
Clearing REDO LOGFILES
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
• If there are only two log groups
• The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover
Viewing Information About Logfiles
To See how many logfile groups are there and their status type the following query.
SQL>SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 20605 1048576 1 YES ACTIVE 61515628 21-JUN-07
2 1 20606 1048576 1 NO CURRENT 41517595 21-JUN-07
3 1 20603 1048576 1 YES INACTIVE 31511666 21-JUN-07
4 1 20604 1048576 1 YES INACTIVE 21513647 21-JUN-07
To See how many members are there and where they are located give the following query
SQL>SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 /U01/ORACLE/ICA/LOG1.ORA
2 /U01/ORACLE/ICA/LOG2.ORA
ADDED
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 5;
Database altered.
To add a new Redo Logfile group to the database give the following command
SQL>alter database add logfile group 3
‘/u01/oracle/ica/log3.ora’ size 10M;
Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.
Adding Members to an existing group
To add new member to an existing group give the following command
SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile
Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.
Dropping Members from a group
You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command
SQL>alter system switch logfile;
The following command can be used to drop a logfile member
SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Dropping Logfile Group
Similarly, you can also drop logfile group only if the database is having more than two groups and if it is not the current group.
SQL>alter database drop logfile group 3;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.
Resizing Logfiles
You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.
Renaming or Relocating Logfiles
To Rename or Relocate Logfiles perform the following steps
For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log1.ora’, then do the following
Steps
1. Shutdown the database
SQL>shutdown immediate;
2. Move the logfile from Old location to new location using operating system command
$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log1.ora
3. Start and mount the database
SQL>startup mount
4. Now give the following command to change the location in controlfile
SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;
5. Open the database
SQL>alter database open;
Clearing REDO LOGFILES
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
• If there are only two log groups
• The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover
Viewing Information About Logfiles
To See how many logfile groups are there and their status type the following query.
SQL>SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 20605 1048576 1 YES ACTIVE 61515628 21-JUN-07
2 1 20606 1048576 1 NO CURRENT 41517595 21-JUN-07
3 1 20603 1048576 1 YES INACTIVE 31511666 21-JUN-07
4 1 20604 1048576 1 YES INACTIVE 21513647 21-JUN-07
To See how many members are there and where they are located give the following query
SQL>SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 /U01/ORACLE/ICA/LOG1.ORA
2 /U01/ORACLE/ICA/LOG2.ORA
ADDED
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 5;
Database altered.
Linking NetBackup for Oracle with RMAN
Linking RMAN with a library provided by NetBackup for Oracle on UNIX is the only NetBackup requirement. Before writing to sequential devices such as tape, you must link the Oracle Server software with the media management API library installed by NetBackup for Oracle on UNIX. Oracle uses this library when it needs to write to, or read from devices supported by Media Manager.
To link Oracle with NetBackup for Oracle on UNIX
1. Become the Oracle user.
su -oracle
2. Shutdown all of the Oracle instances (SIDs). The user must connect as sysdba, either using OS Authentication or Oracle Authentication (set up in the Oracle password file). The following example is for Oracle9i.
Set the ORACLE_SID environment variable to the first SID.
Connect as sysdba using OS Authentication:
sqlplus /nolog
connect / as sysdba
shutdown immediate
exit
Connect as sysdba using Oracle Authentication (via password file):
sqlplus /nolog
connect userid/password as sysdba
shutdown immediate
exit
Change the ORACLE_SID environment variable to any other SID that uses this Oracle installation and repeat above commands.
3. Set up automatic linking.
a. Change directory to install_path/netbackup/bin/
normally is /usr/openv/netbackup/bin
b. Run the oracle_link script:
./oracle_link
This script determines the Oracle version level and then links Oracle to NetBackup accordingly. All output from this script will be captured in a /tmp/make_trace.pid file. To change the trace file location, change the MAKE_TRACE variable in the oracle_link script.
To link Oracle with NetBackup for Oracle on UNIX
1. Become the Oracle user.
su -oracle
2. Shutdown all of the Oracle instances (SIDs). The user must connect as sysdba, either using OS Authentication or Oracle Authentication (set up in the Oracle password file). The following example is for Oracle9i.
Set the ORACLE_SID environment variable to the first SID.
Connect as sysdba using OS Authentication:
sqlplus /nolog
connect / as sysdba
shutdown immediate
exit
Connect as sysdba using Oracle Authentication (via password file):
sqlplus /nolog
connect userid/password as sysdba
shutdown immediate
exit
Change the ORACLE_SID environment variable to any other SID that uses this Oracle installation and repeat above commands.
3. Set up automatic linking.
a. Change directory to install_path/netbackup/bin/
normally is /usr/openv/netbackup/bin
b. Run the oracle_link script:
./oracle_link
This script determines the Oracle version level and then links Oracle to NetBackup accordingly. All output from this script will be captured in a /tmp/make_trace.pid file. To change the trace file location, change the MAKE_TRACE variable in the oracle_link script.
Subscribe to:
Posts (Atom)
