11g Release 2 RMAN Backup Compression

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed by ..

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size: 276.55M
time: 00:00:50

To summarise we can conclude:

LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

11g Release 2 Tablespace Point In Time Recovery – recover from dropped tablespace

One of the good new features in 11g Release 2 is that it enables us to recover from a case of a dropped tablespace. TSPITR (tablespace point in time recovery) has been around in earlier releases, but we could not recover a dropped tablespace.

What 11gR2 does is that it peforms a fully automated RMAN managed TSPITR. It creates and starts the auxiliary instance and restores just the datafiles it requires - SYSTEM,SYSAUX,UNDO and the files pertaining to the dropped tablespace – in this case datafiles 1,2,3 and 7- in the location which we specify as the ‘Auxiliary Destination’. It will first perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database.

To illustrate this example we create a new tablespace MYEXAMPLE and create two tables MYSALES and MYCOSTS in this tablespace. We take a database backup and then drop the tablespace and then perform tablespace point in time recovery using RMAN.

Before dropping the tablespace we first note the SCN as we will use this SCN when we do the TSPITR. Note also that we are connected to a RMAN catalog as well although the documentation does state that we can perform TSPITR without a recovery catalog.

SQL> create tablespace myexample datafile '+DATA' size 200m;

Tablespace created.

SQL> create table mysales
  2  tablespace myexample
  3  as select * from sales;

Table created.

SQL> create table mycosts
  2  tablespace myexample
  3   as select * from costs;

Table created.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1043322

SQL> drop tablespace myexample including contents and datafiles;

Tablespace dropped.

RMAN> run {
2> set newname for datafile 7 to '+DATA';
3> recover tablespace myexample
4> until scn 1043322
5> auxiliary destination '+DATA';
6> }

executing command: SET NEWNAME

Starting recover at 27-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

Creating automatic instance, with SID='qyBo'

initialization parameters used for automatic instance:
db_name=TESTDB
db_unique_name=qyBo_tspitr_TESTDB
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=+DATA
log_archive_dest_1='location=+DATA'
#No auxiliary parameter file used

starting up automatic instance TESTDB

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2212736 bytes
Variable Size                100666496 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4849664 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 27-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA/testdb/backupset/2009_11_27/ncsnf0_tag20091127t104307_0.502.
704025815
channel ORA_AUX_DISK_1: piece handle=+DATA/testdb/backupset/2009_11_27/ncsnf0_tag20091127t104307_0.502.
704025815 tag=TAG20091127T104307
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/testdb/controlfile/current.506.704033345
Finished restore at 27-NOV-09

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  7 to
 "+DATA";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 7;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

Starting restore at 27-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +DATA/testdb/backupset/2009_11_27/nnndf0_tag20091127t104307 .....
channel ORA_AUX_DISK_1: piece handle=+DATA/testdb/backupset/2009_11_27/nnndf0_tag20091127t104307_0.501.
704025789 tag=TAG200 .........
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 27-NOV-09

datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=704033380 file name=+DATA/testdb/datafile/myexample.512.
704033355
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=704033380 file name=+DATA/testdb/datafile/system.511.
704033355
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=704033380 file name=+DATA/testdb/datafile/undotbs1.513.
704033355
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=704033380 file name=+DATA/testdb/datafile/sysaux.509.
704033355

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "MYEXAMPLE", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  7 online

Starting recover at 27-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file +DATA/testdb/archivelog/2009_11_27/.....
archived log for thread 1 with sequence 11 is already on disk as file +DATA/testdb/archivelog/2009_11_27/.....
archived log for thread 1 with sequence 12 is already on disk as file +DATA/testdb/archivelog/2009_11_27/ ......
archived log file name=+DATA/testdb/archivelog/2009_11_27/ ....
archived log file name=+DATA/testdb/archivelog/2009_11_27/....
archived log file name=+DATA/testdb/archivelog/2009_11_27/....
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-09

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  MYEXAMPLE read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+DATA''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+DATA''";
}
executing Memory Script

sql statement: alter tablespace  MYEXAMPLE read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_qyBo":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_qyBo" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_qyBo is:
   EXPDP>   +DATA/tspitr_qybo_53647.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace MYEXAMPLE:
   EXPDP>   +DATA/testdb/datafile/myexample.512.704033355
   EXPDP> Job "SYS"."TSPITR_EXP_qyBo" successfully completed at 12:51:34
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_qyBo" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_qyBo":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_qyBo" successfully completed at 12:51:49
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  MYEXAMPLE read write';
sql 'alter tablespace  MYEXAMPLE offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  MYEXAMPLE read write

sql statement: alter tablespace  MYEXAMPLE offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file +DATA/testdb/tempfile/temp.518.704033385 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_3.517.704033385 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_2.515.704033383 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_1.514.704033383 deleted
auxiliary instance file +DATA/testdb/datafile/sysaux.509.704033355 deleted
auxiliary instance file +DATA/testdb/datafile/undotbs1.513.704033355 deleted
auxiliary instance file +DATA/testdb/datafile/system.511.704033355 deleted
auxiliary instance file +DATA/testdb/controlfile/current.506.704033345 deleted
Finished recover at 27-NOV-09

RMAN>

Note: After the recovery is complete, we need to bring the tablespace and datafile online as well.

SQL> alter tablespace myexample online;

Tablespace altered.

SQL> alter database datafile '+DATA/testdb/datafile/myexample.512.704033355' online;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='MYEXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/testdb/datafile/myexample.512.704033355

SQL> select table_name from dba_tables
  2  where tablespace_name='MYEXAMPLE';

TABLE_NAME
------------------------------
MYSALES
MYCOSTS

SQL> select count(*) from sh.mysales;

  COUNT(*)
----------
    918843

Oracle Restart – new in 11g R2

Similar to the clusterware processes in a RAC environment, in 11g R2 even for a standalone instance, functionality is now available to automatically restart components like the database, listener, ASM diskgroup, service etc in the event of their failure.

This feature is called Oracle Restart and it runs out of the Grid Infrastructure home which is separate from the database home.

Oracle Restart is managed by CRSCTL utility and it uses Oracle High Availability Services to start and stop the components managed by Oracle Restart.

Using Oracle Restart, we can stop or start all the components running out of a single Oracle Home with a single srvctl command as well as Oracle Restart will automatically start all components on machine reboot in the proper order taking into account dependencies like ASM instance and the managed database instances. So no more do we have to use the dbstart and dbstop scripts to manage this.

Oracle Restart can be extended to Data Guard configurations as well by integrating with Data Guard Broker to manage the start and stop of database services following a data guard role transition.

Let us examine some of the srvctl commands which can be executed in a single instance environment as well as see the use of the crsctl command – normally previously only used for RAC environments.

-bash-3.2$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA1.dg   ora....up.type ONLINE    ONLINE    redhat64
ora....ER.lsnr ora....er.type ONLINE    ONLINE    redhat64
ora....WARE.dg ora....up.type ONLINE    ONLINE    redhat64
ora.asm        ora.asm.type   ONLINE    ONLINE    redhat64
ora.cssd       ora.cssd.type  ONLINE    ONLINE    redhat64
ora.diskmon    ora....on.type ONLINE    ONLINE    redhat64
ora.eons       ora.eons.type  ONLINE    ONLINE    redhat64
ora.ons        ora.ons.type   ONLINE    ONLINE    redhat64

bash-3.2$ ./crsctl check has
CRS-4638: Oracle High Availability Services is online

-bash-3.2$ ./crsctl check css
CRS-4529: Cluster Synchronization Services is online

-bash-3.2$ ./srvctl start listener

-bash-3.2$ ps -ef |grep tns
oracle   14899     1  0 14:08 ?        00:00:00 /u02/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit

-bash-3.2$ ./srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): redhat64

-bash-3.2$ ./srvctl status asm
ASM is running on redhat64

-bash-3.2$ ./srvctl status diskgroup -g DATA1
Disk Group DATA1 is running on redhat64

-bash-3.2$ ./srvctl config asm
ASM home: /u02/app/oracle/product/11.2.0/grid
ASM listener: LISTENER
Spfile: +DATA1/asm/asmparameterfile/registry.253.700932479
ASM diskgroup discovery string: /dev/raw/raw*

In case a process dies unexpectedly, Oracle Restart will automatically start the process. In the example below, we kill the PMON process of the ora11gr2 instance and find that in less than 5 seconds the instance has been restarted by the Oracle High Availablity Service running in the background constantly monitoring the health of the managed components.

-bash-3.2$ ps -ef |grep pmon
oracle   12710     1  0 Oct22 ?        00:00:01 asm_pmon_+ASM
oracle   15752     1  0 14:14 ?        00:00:00 ora_pmon_ora11gr2
oracle   15975 18271  0 14:15 pts/2    00:00:00 grep pmon

-bash-3.2$ kill -9  15752

-bash-3.2$ ps -ef |grep pmon
oracle   12710     1  0 Oct22 ?        00:00:01 asm_pmon_+ASM
oracle   16679     1  0 14:22 ?        00:00:00 ora_pmon_ora11gr2
oracle   16775 18271  0 14:22 pts/2    00:00:00 grep pmon

In this example we stop all the components running out of a particular home by executing the srvctl stop home command. After stopping the database home, we find that only the ASM instance is running as it is managed by a seperate home which is the Grid Infrastructure home. After the database home is started, the database instance ora11gr2 also is automatically started.

[oracle@redhat346 ~]$ srvctl stop home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /tmp/state

[oracle@redhat346 ~]$ ps -ef |grep pmon
oracle   19841     1  0 Sep18 ?        00:01:24 asm_pmon_+ASM
oracle   31843 27855  0 15:19 pts/1    00:00:00 grep pmon

[oracle@redhat346 ~]$ srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /tmp/state

[oracle@redhat346 ~]$ ps -ef |grep pmon
oracle   19841     1  0 Sep18 ?        00:01:24 asm_pmon_+ASM
oracle   26690     1  0 Oct08 ?        00:00:41 ora_pmon_emrep
oracle   32054     1  0 15:20 ?        00:00:00 ora_pmon_testdb
oracle   32344     1  0 15:20 ?        00:00:00 ora_pmon_ora11gr2
oracle   32620 27855  0 15:22 pts/1    00:00:00 grep pmon

11g Release 2 Grid Infrastructure Installation

Let us have a look at the installation screenshots of 11g Release 2 on a Red Hat Linux platform. Currently the 11g R2 software has only been released for the Linux platform with release dates for other platforms like Solaris and AIX set for sometime end October to mid November.

There are quite a few new installation options available as can be seen from the screenshots below. What we are installing below is “Grid Infrastructure for a stand alone server”.

In 11g R2, ASM is now part of what is called the Grid Infrastructure (nothing to do with Grid Control). It is no longer an option available in dbca and there is a command line option asmca which is launched from the Grid Infrastructure home. The ASM instance will be running from the Grid Home and not the database Oracle Home.

Further, the Grid Infrastructure can be installed for a stand alone server or for a cluster deployment. The ASM and Clusterware are instaled in the same home directory and it should be noted that now in 11g R2, the clusterware files like the OCR and the Voting Disk can be located on ASM storage and raw devices is no longer supported.

Also, now there is separation between SYSASM and SYSDBA and if you want to connect to the ASM instance you need to do it as SYSASM. It is recommended to also create a separate group in addition to the DBA group specific for ASM administration and make SYSASM grantees members of this group. In one of the screenshots below we will see an alert being displayed when we have selected the dba group for the ASM installation.

11g Release 2 ACFS (Automatic Storage Management Cluster File System)

The following are some of the new ASM related features introduced in 11g R2:

In Oracle 11g Release 2, ASM and the Oracle Clusterware are now installed in a common home called the Grid Infrastructure Home.

The clusterware files like the Voting Disk and the Cluster Registry can no longer be installed on raw devices. They can now be installed on ASM Disk Groups

Need to connect as SYSASM to perform any administrative operations on the ASM instance or we will get an ORA-01031 error as shown below

[oracle@redhat346 stage]$ sqlplus sys/xxx as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 16 09:10:55 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> shutdown immediate;
ORA-01031: insufficient privileges

ACFS or Automatic Storage Management Cluster File System is an extension of the ASM technology to support storage of files like Oracle binaries and other application files related to text,video, audio etc

The example below will show how we can configure and mount a ACFS on Red HatLinux with 11g R2.

Note: ASM operations like creating and adding disk groups etc which were earlier performed via DBCA will now be performed via the GUI ASM Configuration Assistant which needs to be launched via the ‘asmca‘ command from the Grid Infrastructure Home.

We first create a disk group called ASMCFS and then create a volume called ASMCFS_VOL1 of 25 GB using the disk group that we just created.

We will then use a mount point /u03 to mount this file system. A configuration script is created which includes the commands to be run as root to mount this file system. After the script is run we will see the ACFS file system mounted on /u03.

/bin/mount -t acfs /dev/asm/asmcfs_vol1-44 /u03

chown oracle:dba /u03

chmod 775 /u03

[oracle@redhat346 stage]$ df -k

Filesystem           1K-blocks      Used Available Use% Mounted on
.....
......
/dev/asm/asmcfs_vol1-44
                      26214400     89112  26125288   1% /u03

11g Release 2 Edition-based Redefinition

11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any outage as the application was accessing the procedure or package which needed to be modified.

An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.

Let us see an example of this where we create a procedure, make some changes to the procedure in a new edition and then make those changes visible to other database users.

SQL>  GRANT CREATE ANY EDITION, DROP ANY EDITION to sh;

Grant succeeded.

SQL> conn sh/sh
Connected.

SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> set serverout on

SQL> BEGIN hello(); END;
/
  2  Hello, edition 1.

PL/SQL procedure successfully completed.

SQL> CREATE EDITION e2;

Edition created.

SQL> ALTER SESSION SET EDITION = e2;

Session altered.

SQL> CREATE OR REPLACE PROCEDURE hello IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
  END hello;
/
  2    3    4    5
Procedure created.

SQL> BEGIN hello(); END;
  2  /
Hello, edition 2.

PL/SQL procedure successfully completed.

Note, now if we change the edition to the default edition ‘ORA$BASE’, we will now see the original procedure and not the updated one

SQL> ALTER SESSION SET EDITION = ora$base;

Session altered.

SQL>  BEGIN hello(); END;
  2  /
Hello, edition 1.

Let us now see how this can be rolled out to database user HR

SQL> grant execute on hello to hr;

Grant succeeded.

SQL> GRANT USE ON EDITION e2 to public;

Grant succeeded.

We now make the default edition of the database, E2 so that all changes in the new edition E2 will be visible to all database users

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> ALTER DATABASE DEFAULT EDITION=e2;

Database altered.

SQL> conn hr/hr
Connected.

SQL> set serverout on

SQL>  exec sh.hello;
Hello, edition 2.