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

Cross Platform Transportable Tablespace using ASM with Oracle XML DB

In one of my earlier posts How To Transport a Tablespace from 10g ASM to 11g ASM on AIX we discussed how to transport a tablespace stored on ASM storage from Windows to AIX using the DBMS_FILE_TRANSFER package.

We will now look at how the same thing can be done using FTP with Oracle XML DB Repository and its virtual folders.

All we need to do is to run the following script and provide the port numbers which will be used for the FTP and HTTP protocols for accessing the repository.

@?/rdbms/admin/catxdbdbca 8080 7787

In this case we have assigned the port 8080 for the FTP protocol and the port 7787 is for HTTP access.

The ASM related directories on the Windows Oracle database are accessed via the sys/asm virtual folder and then we use FTP to copy the ASM files over the network and then use RMAN to convert it to AIX 64 bit format

The example below shows us how we are connecting to the Windows server via port 8080 using the FTP protocol. Note we are then connecting with a database user – SYSTEM in this case.

testdb:/u01/oracle > ftp cbdorca261 8080
Connected to cbdorca261.bankwest.com.
220- cbdorca261
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 cbdorca261 FTP Server (Oracle XML DB/Oracle Database) ready.
Name (cbdorca261:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
ftp> i
200  Type set to I.
ftp> cd sys/asm
250 CWD Command successful
ftp> cd DATA/genua1/datafile
250 CWD Command successful
ftp> get test_tts.376.699110247
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
104865792 bytes received in 2.25 seconds (4.551e+04 Kbytes/s)
local: test_tts.376.699110247 remote: test_tts.376.699110247
ftp> quit
221 QUIT Goodbye.

 testdb:/u01/oracle > rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Oct 1 13:50:02 2009

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

connected to target database: TESTDB (DBID=2482257163)

RMAN>  convert datafile2>
 '/u01/oracle/test_tts.376.699110247'
3> FORMAT '+DATA'
4> from platform 'Microsoft Windows IA (32-bit)';

Starting conversion at target at 01-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/oracle/test_tts.376.699110247
converted datafile=+DATA/testdb/datafile/test_tts.267.699112231
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 01-OCT-09

imp file=tts.dmp transport_tablespace=y tablespaces=TEST_TTS datafiles=+DATA/testdb/datafile/test_tts.267.699112231

Import: Release 11.1.0.7.0 - Production on Thu Oct 1 13:57:33 2009

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. . importing table                    "MYOBJECTS"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SQL> conn system/oracle
Connected.

SQL> select file_name from dba_data_files
  2  where tablespace_name='TEST_TTS';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/testdb/datafile/test_tts.267.699112231

SQL> select tablespace_name from user_tables
  2  where table_name='MYOBJECTS';

TABLESPACE_NAME
------------------------------
TEST_TTS

SQL> select count (*) from myobjects;

  COUNT(*)
----------
     56454