This article describes how to implement a high speed network between two servers within a LAN to achieve
+1 TB/hour data loading rate by SQL*Loader tool. The article includes end-to-end details of the implementation starting from the customization of TCP/IP stack at operating system level (Solaris 10), and going up to the modification of database server (Oracle 10g Release 2) parameters.
In previous post, on Online Datafile Move subtitle, I have showed
RMAN> switch datafile 169 to copy;
command to switch datafile from one diskgroup to other one. However RMAN is not orthogonal and the form doesn’t work with the RUN command:
RMAN> run{
2> switch datafile 169 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “copy”: expecting one of: “datafilecopy”
RMAN-01007: at line 2 column 24 file: standard input
When you need to move hundereds of datafiles from one diskgroup to the other one, you may want to use RUN form. If you do so, use the following
run{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
copy datafile 111 to ‘+DGRP’ TAG ‘MOVE111′;
sql ‘alter tablespace STAGING_BTS offline’;
switch datafile 111 to DATAFILECOPY TAG MOVE111;
recover tablespace STAGING_BTS;
sql ‘alter tablespace STAGING_BTS online’;
copy datafile 112 to ‘+DGRP’ TAG ‘MOVE112′;
sql ‘alter tablespace USER_BTS offline’;
switch datafile 112 to DATAFILECOPY TAG MOVE112;
recover tablespace USER_BTS;
sql ‘alter tablespace USER_BTS online’;
release CHANNEL disk1;
}
Basically, UNDO tablespace is also a permanent tablespace. It is only specialized for a specific purpose. Although you can use the same method explained in previous sections to move UNDO tablespace, this is not the convenient way to do the job. Because most probably any time you try to offline the tablespace there will be an active transaction on the UNDO tablespace and Oracle will not allow you to offline it. So we will use a different technique to move UNDO tablespace.
Once you have assigned the new tablespace, you don’t need to bother to offline the old one. Oracle will try it for you periodically (Look at Special Notes section).
For this kind of simple staff, I always prefer to use Grid Control screens. Here is the path to do the job on Grid Control:
- Administration>Database Administration > Storage > Tablespaces
- Choose the radio button next to your current tablespace
- Pick “Create Like” from Actions list.
- Specify the size of your tablespace (since I usually use Big File tablespace for this purpose, I say tablespace size not datafile size).
- Administration>Database Administration > Database Configuration> Undo Management
- Undo Management>Undo Tablespace for this Instance>Change Tablespace
- Choose your new tablespace as the instance tablespace.
- After the completion of the switch-out, drop your old UNDO tablespace.
After you’ve issued the tablespace assignment, you will see the following messages on alert.log
Successfully onlined Undo Tablespace 967.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Fri Jun 13 11:29:14 2008
ALTER SYSTEM SET undo_tablespace=’UNDO_BTS’ SCOPE=BOTH;
Fri Jun 13 11:30:16 2008
drop tablespace undotbs1 including contents and datafiles
Fri Jun 13 11:30:16 2008
ORA-30013 signalled during: drop tablespace undotbs1 including contents and datafiles…
Fri Jun 13 11:33:16 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:38:16 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:40:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:45:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:50:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:55:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 12:00:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
That’s why Oracle is trying to move from the old tablespace to new one, and it is pending due to active transactions on old tablespace. Once the old UNDO tablespace become idle, you will see the following line in alert.log
…
Undo Tablespace 1 successfully switched out.
…
In our production environment we have created a data ASM diskgroup with inappropriate allocation unit (_asm_ausize) and stripe size (_asm_stripesize) and put everything into this diskgroup. Later on a new diskgroup with corrected parameters was created and a need to move anything in the old group to the new one has emerged. Following content has to be moved from old diskgroup to new one:
In a series of post, I will be explaining the different ways of doing the same jobs and writing about the possible pitfalls.
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>
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>
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.
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>
If you move more than one tablespace using this approach will decrease the manual work you do.
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>
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.
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>
That’s all for tonight. See you…
Recently, we have integrated our new DWH database with Oracle Grid Manager. We schedule and control the RMAN backups of our all databases from a centric location (Grid Control) for ease of management. However first I need to do is to remove the previously scheduled Oracle Suggested Backup from the EM Database control not to duplicate backup jobs. After searching for a while I couldn’t find a place in Database Control to remove my scheduled backup job by just clicking:). Then I choose the SQL*Plus way of solving the problem.
As all you may know, by 10g Oracle changes its built-in package used to create scheduled jobs from DBMS_JOBS to DBMS_SCHEDULER. Under the Administration tab, EM allows us to create/modify/remove scheduled jobs in a very easy way. Also EM allows us to create scheduled RMAN backups using the graphical EM interface(under Maintenance tab). Although both are scheduled jobs, you can not see a job for RMAN backup in DBA_SCHEDULER_JOBS view after you have scheduled it. But rather you need to look at SYSMAN.MGMT_JOB table.
In conclusion, Oracle just choose not to mix up its own EM jobs with any other user jobs.
Once you query the SYSMAN.MGMT_JOB, you will find out that there is a backup job with job_name something like ‘BACKUP_<SID>_<SOMEID>’. You can ensure this by checking the job_description column. Once you find it removing it is easy. Run the following lines from your SQL*Plus console
begin sysman.mgmt_jobs.stop_all_executions('BACKUP_DDS_000067','SYS'); sysman.mgmt_jobs.delete_job('BACKUP_DDS_000067','SYS'); end; / commit;
Steven Feuerstein