Our Database Environment became Famous

Recently Oracle has created a customer snapshot for our database environment and recently published it on Oracle Website

RAC & ASM on Linux Forum

Tomorrow, I will be attending on RAC & ASM on Linux Forum as a speaker. I will be presenting the best practices we had throughout our data warehouse’s reengineering project. My presentation is mainly about the use of ASM, backup & recovery strategies and other issues pointing out the VLDBs. You can download my and other speaker’s presentations from Alejandro Vargas’s blog.

Finally, I also want to thank him and his managers for their kind invitation.

Migrating Data Using Transportable Tablespace(TTS)

Introduction

There are various ways of migrating data in Oracle 10g. Datapump, moving over link, RMAN duplicate, and transportable tablespace are the most common ones. According to my experiences I can do the following classification

Method	Hardness	Best Suitable For	Availability
Database Link	Easy	Less than 50 GB	High
Data Pump	Moderate	Over 50 GB	High
TTS	Moderate	Terabytes of Data	Low
RMAN Duplicate	Hard	Full Database	High

In this post I will be explaining the TTS way of doing the job. Although we define TTS as suitable for terabytes of data, when the system resources on the source system is limited, TTS is again the best option. That’s because all other techniques except the database link requires a copy of original data and this requires resource (CPU,disk etc.).

Preparing the Data Before Move (Optional)

Preparing the data before moving is important in two sense:

1. Usually you don’t need to move the whole data so processing and reducing the amount of data prior to move will reduce your transfer time to some extent.
2. One problem about TTS is that it requires tablespaces to be moved to be taken to read only mode during the move. If you copy the subset of the data to another tablespace, you will be increasing the availability of the master data source. You will be taking the copy tablespace to read only mode only.

SQL> CREATE TABLESPACE STAGING DATAFILE

‘/data08/asndb/staging1.dbf’ SIZE 4G AUTOEXTEND ON NEXT 4G MAXSIZE 16G,

‘/data08/asndb/staging2.dbf’ SIZE 4G AUTOEXTEND ON NEXT 4G MAXSIZE 16G,

‘/data08/asndb/staging3.dbf’ SIZE 4G AUTOEXTEND ON NEXT 4G MAXSIZE 16G

BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;

SQL> CREATE TABLE STAGING_TAB PCTFREE 1 PARALLEL 3 TABLESPACE STAGING AS

SELECT <list of required columns> FROM ASN_USER.SMSC_LOG WHERE <filter predicate>;

For our case by this filtering we reduce the 330 GB total data to 35 GB migration data. As a result, remember that filtering on the source site is a best practice for any migration activity.

TODO List on Source Database

There are a number of steps to be done on the source site. Respectively,

1. Validating the self containing property of the migration tablespace.
2. Altering the migration tablespace to read only mode.
3. Exporting the meta data

Validating Self Containing Property

TTS requires to be self contained. This means that the segments within the migration tablespace set can not have dependency to a segment in a tablespace out of the transportable tablespace set. Although it seems a complex task, it is simply done by a PL/SQL procedure in DBMS_TTS package.

SQL> begin
2    sys.dbms_tts.transport_set_check(’STAGING’, TRUE);
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

As you see STAGING tablespace is self contained. If it were not self contained you should either remove the dependencies by dropping them or include the tablespaces of segments into TTS set to which migration set is depended.

ALTER Tablespaces to READ ONLY Mode

This requirement must be obvious. In the next steps you will see that we will perform a file system copy to tablespace datafiles. In order those datafiles to not to require a recovery they need to be in consistent during the copy activity. That is to say it is required that redo log generation has to stop for those datafiles.

SQL> alter tablespace STAGING read only;

Export the Metadata

Final step on source side is to extract the metadata export. For this purpose you need a directory object. For example you can use built-in ADMIN_DIR or you can create  a new one.

bash-2.05$ expdp userid=temp_usr/****** directory=ADMIN_DIR dumpfile=staging_asn.dmp logfile=ADMIN_DIR:asn.log transport_tablespaces=staging transport_full_check=y

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 11 July, 2008 13:02:42

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting “TEMP_USR”.”SYS_EXPORT_TRANSPORTABLE_01″:  userid=temp_usr/******** directory=DIR1 dumpfile=staging_asn.dmp logfile=DIR1:asn.log transport_tablespaces=staging transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “TEMP_USR”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for TEMP_USR.SYS_EXPORT_TRANSPORTABLE_01 is:
/data08/asndb/staging_asn.dmp
Job “TEMP_USR”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 13:03:29

TODO List on Target Database

The task list for target database is simply,

1. Copy the datafiles and export file to target server via FTP
2. Create the necessary schemas
3. Modify the db_n_cache_size parameter if necessary.
4. Import the export file

Copy the datafiles via FTP

The very first step is to copy the datafiles and metadata export file to target server.

himalaya@oracle $ cd /export/home/oracle/hsensoy/datafile

himalaya@oracle $ ftp 10.x.x.x
…
Name (10.200.171.16:oracle): root
…
Password:
…
ftp> cd /data08/asndb
…
ftp> ls
…
asn.log
dump.log
dump.par
nohup.out
staging1.dbf
staging2.dbf
staging3.dbf
staging_asn.dmp
…
ftp> binary
…
ftp> get staging_asn.dmp
…
ftp> prompt
ftp> mget *.dbf
…
ftp> qui
…

Create Required Schemas

Remember that the metadata export is taken for the tablespace. While you are importing into a new database it will be looking for the schemas of the objects taken from the source system. So you need to create the required schemas in the new database.

SQL> CREATE USER TEMP_USR PROFILE DEFAULT IDENTIFIED BY ******
2 DEFAULT TABLESPACE USERS_BTS
3 TEMPORARY TABLESPACE PRIM_TMP_GRP
4 QUOTA UNLIMITED ON USERS_BTS QUOTA UNLIMITED ON STAGING_BTS
5 ACCOUNT UNLOCK;

User created.

Modify the db_n_cache_size (Optional)

If the source and target databases have different database block sizes. Then Oracle will not allow you to import the metadata

himalaya@oracle $ impdp userid=ddsbase/****** parfile=import.par

Import: Release 10.2.0.3.0 - 64bit Production on Friday, 11 July, 2008 14:38:56

Copyright (c) 2003, 2005, 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
Master table “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_01″:  userid=ddsbase/******** parfile=import.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29339: tablespace block size 8192 does not match configured block sizes

Job “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_01″ stopped due to fatal error at 14:38:59

Fortunately Oracle allows the usage of different block sized tablespaces within the same database. The only thing to do is to explicitly set the buffer cache size for different tablespaces

himalaya@oracle $ srv

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 11 14:40:59 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> alter system set db_8k_cache_size=16M;

System altered.

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

Import the Metadata

The final step on the target site is to import the tablespace metada. For this pupose you may simply create a data pump parameter file using your favorite text editor.

himalaya@oracle $ vi import.par
“import.par” [New file]

directory=ADMIN_DIR
dumpfile=staging_asn.dmp
logfile=ADMIN_DIR:asn.log
transport_datafiles=’/export/home/oracle/hsensoy/datafile/staging1.dbf’,'/export
/home/oracle/hsensoy/datafile/staging2.dbf’,'/export/home/oracle/hsensoy/datafil
e/staging3.dbf’
keep_master=y
~
~
~
“import.par” [New file] 5 lines, 262 characters

Then using this file you can perform the import

himalaya@oracle $ impdp userid=ddsbase/****** parfile=import.par

Import: Release 10.2.0.3.0 - 64bit Production on Friday, 11 July, 2008 14:41:14

Copyright (c) 2003, 2005, 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
Master table “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_02″ successfully loaded/unloaded
Starting “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_02″:  userid=ddsbase/******** parfile=import.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “DDSBASE”.”SYS_IMPORT_TRANSPORTABLE_02″ successfully completed at 14:41:48

himalaya@oracle $

Your tablespace is ready to use in the target database.

High Speed Network Implementation for SQL*Loader Performance

Abstract

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.

Read Full Article

Moving any File between ASM Diskgroups 2

Caveat about RMAN RUN{} Form

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;
}

Moving UNDO Tablespace

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.

Steps

1. Create a new UNDO tablespace
2. Assign UNDO tablespace as the instance tablespace
3. Wait for Oracle to switch-out tablespace before dropping the old one.

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).

Example

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.

Special Note

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.

…

Moving any File between ASM Diskgroups 1

Introduction

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:

• 154 data files including files of SYSTEM and SYSAUX tablespace
• 17 temporary files
• 1 change tracking file
• 1 control file copy
• 1 spfile

In a series of post, I will be explaining the different ways of doing the same jobs and writing about the possible pitfalls.

Moving Datafiles

Online Datafile Move

Steps

1. Copy datafile of the tablespace to the new diskgroup
2. Bring tablespace to offline mode
3. Switch datafile to its new location
4. Recover the tablespace
5. 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>

Online Tablespace Move(Multiple Datafiles)

Steps

1. Copy tablespace (allfiles) to the new diskgroup
2. Bring tablespace to offline mode
3. Switch tablespace (all datafiles) to its new location
4. Recover the tablespace
5. 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

1. Bring tablespace to offline mode
2. Copy tablespace (allfiles) to the new diskgroup
3. Switch tablespace (all datafiles) to its new location
4. 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

1. Copy all tablespaces (allfiles) to the new diskgroup
2. 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

1. shutdown your database
2. startup it in mount mode
3. copy datafile to new diskgroup
4. switch datafiles to new diskgroup
5. 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>

That’s all for tonight. See you…

Removing EM Scheduled Backup

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.

MGMT_JOB vs DBA_SCHEDULER_JOBS

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.

Getting rid of the Oracle Suggested Backup Job

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;

 

Using Oracle Table Compression

In a data warehouse database it is always likely to find some static data portion (usually a large portion) . This means that this portion of data will not be updated any more but rather it will be queried. In this post you will find the benefit of compressing your historical segments in a data warehouse environment.

Cost of creating a compressed table

It is usually known that creating compressed tables requires more time. Let me show why this is the case. We prepare a source table called big_table_new using Thomas Kyte’s infamous script (http://www.apress.com/book/downloadfile/2351). Then we create two identical tables, except that one is compressed and the other is not. Moreover we use runstats_pkg package to compare two executions of CTAS:

hsensoy@dds> set timing onhsensoy@dds> set autot tracehsensoy@dds> exec runstats_pkg.rs_start;PL/SQL procedure successfully completed. Elapsed: 00:00:00.73 hsensoy@dds> create table big_table_nocomp pctfree 1 nologging tablespace LMT_AUTO_NEW parallel 32 as select * from big_table_new; Table created. Elapsed: 00:00:59.49 hsensoy@dds> exec runstats_pkg.rs_middle; PL/SQL procedure successfully completed. Elapsed: 00:00:00.73 hsensoy@dds> create table big_table_comp compress pctfree 1 nologging tablespace LMT_AUTO_NEW parallel 32 as select * from big_table_new; Table created. Elapsed: 00:01:49.07 hsensoy@dds> exec runstats_pkg.rs_stop; Run1 ran in 9974 hsecs Run2 ran in 14599 hsecs run 1 ran in 68,32% of the time Name Run1 Run2 Diff STAT…Elapsed Time 10,046 14,670 4,624 STAT…user I/O wait time 57,814 8,085 -49,729 STAT…CPU used by this sessio 117,697 333,567 215,870 STAT…recursive cpu usage 117,615 333,497 215,882 STAT…physical writes direct 2,653,136 956,400 -1,696,736 STAT…undo change vector size 6,478,648 4,326,472 -2,152,176 STAT…redo size 33,033,884 18,905,292 -14,128,592

You see only a small portion of runstats_pkg results. As we look at the results, we immediately understand that the reason of compressed table creation to take longer time than uncompressed table creation. It is due to CPU time. Compression algorithm requires a significant computation time (x2 CPU time). But notice that since it creates a smaller data segment, it generates less redo, consumes less undo space, and performs less I/O requests.

How much to compress?

This is directly related to level of REDUNDANCY within a data block. If the rows with in a data block contains many common value columns this means the benefit you get from compression will be higher. Let’s show this. To make a redundancy prediction, we first analyze the BIG_TABLE_NEW from which we load the data. This is for filling the necessary columns of dba_tab_cols view, which we use later on.

hsensoy@dds> begindbms_stats.gather_table_stats(ownname => ‘HSENSOY’, tabname => ‘BIG_TABLE_NEW’, estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE, degree => 32); end; / PL/SQL procedure successfully completed.

Remember that we will make only a rough prediction(later you will see that it works pretty good) based on the fact that sortting data such according to most redundant columns (least distinct ones) will create table whose blocks contains highly redundant data. As we look at the dba_tab_cols table statistics we immediately see that some columns are significantly redundant.

hsensoy@dds> select dtc.column_name, dtc.num_distinct, dtc.avg_col_lenfrom dba_tab_cols dtc where dtc.owner = ‘HSENSOY’ and dtc.table_name = ‘BIG_TABLE_NEW’ order by dtc.num_distinct,dtc.avg_col_len desc; COLUMN_NAME NUM_DISTINCT AVG_COL_LEN ————— —————- ———– STATUS 1 6 SECONDARY 1 2 GENERATED 2 2 TEMPORARY 2 2 OWNER 17 6 OBJECT_TYPE 31 10 SUBOBJECT_NAME 650 2 CREATED 756 8 TIMESTAMP 761 20 LAST_DDL_TIME 767 8 DATA_OBJECT_ID 5398 2 OBJECT_NAME 27059 24 OBJECT_ID 50581 5 ID 200043516 7 14 rows selected.

Then we create the table using CTAS and we sort the data according to column order we obtain in previous step.

hsensoy@dds> create table big_table_careful_comp compress pctfree 1 nologging tablespace LMT_AUTO_NEW parallel 32 asselect * from big_table_new order by status, secondary, generated, temporary, owner, object_type, subobject_name, created, timestamp, last_ddl_time, data_object_id, object_name, object_id; Table created.

And ta taa!!! Same data and same compression algorithm but only 5 GB with compared to randomly ordered data.

hsensoy@dds> column segment_name format a30 hsensoy@dds> select segment_name, trunc(bytes / 1024 / 1024 / 1024,2) gigabytes, blocks, extents from dba_segments where owner = ‘HSENSOY’ and segment_name in (’BIG_TABLE_COMP’, ‘BIG_TABLE_CAREFUL_COMP’); SEGMENT_NAME GIGABYTES BLOCKS EXTENTS —————————— ———- ———- ———- BIG_TABLE_COMP 14,67 961944 4129 BIG_TABLE_CAREFUL_COMP 5,27 345744 2793

So remember that in a data warehouse database, in contrast to an operational one, it may be possible to load data sorted into tables in ETL step. You have to take into consideration of the fact that sorted data is much more compressible.

Query Performance

In the previous section we see that compressed table (or partition, subpartition,index) segments require less space on disk. One may infer that this will improve query performance. It’s true. Let’s see how

hsensoy@dds> select count(*) from big_table_nocomp; Elapsed: 00:00:28.99 hsensoy@dds> select count(*) from big_table_comp; Elapsed: 00:00:11.75 hsensoy@dds> select count(*) from big_table_careful_comp; Elapsed: 00:00:06.18

Remember that timing may be misleading. To see that compression really works let’s look closer:

	No Compression	Compress Unsorted Data	Compress Sorted Data
Elapsed Time	873.15	371.51	169.13
CPU Time	432.50	200.30	92.99
Wait Time	440.64	171.21	76.14
Disk Reads	2653175	928355.67	342878.00

Yes, querying a compressed table really works well in terms of I/O and CPU usage.

Conclusion

To sum up, we may conclude that data warehouse database may utilize Oracle table compression option by making a correct physical database design. That’s because their fundamental characteristics is being redundant/denormalized. So in order to improve your query performance, storage efficiency and in return shorten your backup & recovery timings you HAVE TO carefully think on using compression option.

Finally, I introduce some practical practics on using compression:

• Separate your OLD data from the NEW one in terms of tablespace whenever it’s possible.
• Instead of loading data into compressed tables. You load the data swiftly into a uncompressed segment and then recreate segment periodically as compressed using alter table statements clause.
• Keep your compressed segments in readonly tablespaces whenever it is possible to prevent compressed blocks from update operations. Update cause block to be decompressed decreasing the effect of compression.
• Tables range partitioned according to a date column are usually best candidates for partition/subpartition compression. Write scheduled scripts to perform segment compression.
• Talk with your business requirement, development and administration team to identify which partition of data is read-only and suitable for compression (For example, six month old data is static)

Compressing Subpartition Segments

Introduction

As far as I have recently read, Oracle doesn’t allow you to set some physical features of sub-partitions such as compression clause. I have been mistaken that composite partitioned tables can not be compressed at all. However in data warehouse environments, a general requirement is being able to compress segments of historic partitions of RANGE + HASH partitioned tables. I have tried a few scenarios and finally found a solution. Yes, Oracle allows it.

Naïve Approaches

One approach can be to compress all subsegments by trying recreating partition with COMPRESS clause in parallel. However this approach will not work:

ALTER TABLE X MOVE PARTITION 2007JAN NOLOGGING COMPRESS PARALLEL 16;

You will immediately face with ORA-14257: cannot move partition other than a Range or Hash partition exception. This means Oracle can not recreate all subpartition segments at a time.

Another approach is to try recreating compressed subpartition segments by using COMPRESS option one by one:

ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 NOLOGGING COMPRESS PARALLEL 16;

This time you will come up with ORA-14160: this physical attribute may not be specified for a table subpartition. That’s because Oracle doesn’t allow you to set some physical attributes of subpartitions. NOLOGGING and COMPRESS are two of those attributes. Those attributes inherit from partition of subpartition.

Solution

Change the physical attribute at partition level

The first thing you have to do is to change physical attribute (compression attribute) of the partition which owns the subpartition by using MODIFY clause:

ALTER TABLE X MODIFY PARTITION 2007JAN COMPRESS;

 

This statement will modify partition parameter in such a way that all new data blocks created by direct path inserts will be compressed. Remember that the table has composite partitioning. This means subpartition segment blocks are the compressed blocks.

Recreate subpartition segments

“ALTER TABLE…” statement that we have used above affects only the blocks that are created after the statement has been issued. In order to compress existing subpartition blocks, we need to recreate them:

ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_02 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_03 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_04 PARALLEL 16;

All these four subpartition segments will be recreated in compressed form within the tablespace(s) in which they have been currently residing. That is because they inherit the compression attribute from their parents (partition of subpartitions).

Check *_SEGMENTS view to see the effect

As you execute the following command you will see that your segments have already been compressed:

SELECT partition_name, bytes / 1024 / 1024 / 1024 gigabytes

FROM user_segments

WHERE segment_name = ‘X’;

In order to test this scenario I have used one of the summary tables of our data warehouse. Table is RANGE+HASH partitioned and one of these partitions is ~85 GB before compression and ~22 GB after all sixteen subpartitions have been compressed.

Conclusion

Remember that Oracle unfolds, considering that the term is correct, the compressed blocks if you update the data in compressed ones, which decreases the benefit you obtain by using compression. So Oracle advises using compression option with read only tablespaces to guarantee the gain.

COMPEC Oracle Show: Day 3: Final

Wow too short. 3 weeks and you know how to use Oracle Yes the final menu is here. We first introduce the basics then present the methods of putting some data into little boxes. And now it’s time to pull the desired ones from the box:

• Fundamental structure of a SQL query sentence (SELECT …)
  • Aliasing
  • Predicates (WHERE…) =, <>, !=, is null,is not null
  • Sorting (ORDER BY…)
  • Aggragation (GROUP BY…)
  • Limiting Aggragation(HAVING…)
• Joins
  • Equi-join
  • Left-join/Right join
  • Full join
  • IN/NOT IN
  • EXIST/NOT EXIST
• Set operation
  • MINUS
  • UNION
  • UNION ALL
  • INTERSECT
• Index
  • Concept of selectivity
  • Which operations use index
  • Unique vs. Non-unique
  • Oracle can’t use index ?!? Bug or Not

Let’s take a seat and enjoy with Oracle…