Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Thursday, June 23, 2011

Oracle hidden parameters

Oracle database has some parameters related to the database configuration, some of them which are hidden and some of them are unhidden.

To list the unhidden parameters you can connect to the database "as sysdba" and directly query the "v$parameter" view as below.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 23 08:41:55 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column name format a30
SQL> column value format a50 
SQL> column description format a50
SQL> set lines 132
SQL> set pause on
SQL> select name, value, description from v$parameter order by name;

NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY    FALSE                                              Version 7 Dictionary Accessibility Support
active_instance_count                                                             number of active instances in the cluster database
aq_tm_processes                0                                                  number of AQ Time Managers to start
archive_lag_target             0                                                  Maximum number of seconds of redos the standby cou
                                                                                  ld lose

asm_diskgroups                                                                    disk groups to mount automatically
asm_diskstring                                                                    disk set locations for discovery
asm_power_limit                1                                                  number of parallel relocations for disk rebalancin
                                                                                  g



asm_preferred_read_failure_gro                                                    preferred read failure groups

............
SQL> 

To list the hidden parameters you need to use an SQL like below, by switching commented where conditions, you can query different set of parameters hidden or unhidden. The following SQL only brings the hidden parameter called "_disable_logging" as an example.

SQL> SELECT x.ksppinm name,
       y.ksppstvl VALUE,
       x.ksppdesc description
  FROM x$ksppi x,
       x$ksppcv y
 WHERE x.inst_id = userenv('Instance')
   AND y.inst_id = userenv('Instance')
   AND x.indx = y.indx
--   AND x.ksppinm LIKE '%pga%'; --list hidden and unhidden all parameters like "pga"
   AND x.ksppinm LIKE '\_disable\_logging%' escape '\' --list hidden parameters like "_disable_logging"
--   AND x.ksppinm NOT LIKE '\_%' escape '\' --list all unhidden parameters
--   AND x.ksppinm LIKE '\_%' escape '\' --list all hidden parameters
    order by 1;  2    3    4    5    6    7    8    9   10   11   12   13  


NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
_disable_logging               FALSE                                              Disable logging

All the Oracle hidden parameters start with an underscore (_). They are not documented parameters by Oracle. Oracle does not advise customers to change them without the knowledge of Oracle Support.

For example, if you bump into an Oracle database bug and there is no published One-off patch to solve this bug yet, Oracle support can advise you to change an hidden Oracle parameter as a workaround until the patch will be released for this bug. Otherwise do not try to play with them since that can result to unexpected results in your databases.

How to change the command prompt on Unix

Below is how I change the command prompt on IBM AIX Unix type.

It will set the command prompt on AIX to
"connected username"@"hostname of the server":"current folder full path"

[oracle@srvdb01]:/oracle > more .profile | grep PS
export PS1="[`whoami`@`hostname`]:\$PWD \> "
[oracle@srvdb01]:/oracle >

How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases

First try it in an Oracle 10g database on an AIX server.

First create the sample export folder which export files will be written.
This folder should be created by unix user which is the owner of Oracle database (here "oracle" Unix OS user) or if created by another OS user, the directory permissions should be arranged so that Oracle owner OS user "oracle" can write to this folder.
[oracle@srvdb01]:/oracle > mkdir -p /transfer/uural/datapumpdemo
[oracle@srvdb01]:/oracle > cd /transfer/uural
[oracle@srvdb01]:/transfer/uural > ls -ld datapumpdemo
drwxr-xr-x    2 oracle   dba            4096 Jun 22 15:06 datapumpdemo
[oracle@srvdb01]:/transfer/uural > cd datapumpdemo
[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 0
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now create a new directory in the database pointing to the above export folder.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 22 15:28:09 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL AS SYSDBA> create or replace directory uural_datapumpdemo as '/transfer/uural/datapumpdemo';

Directory created.

SYS@ORCL AS SYSDBA> desc dba_directories
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SYS@ORCL AS SYSDBA> column directory_path format a50
SYS@ORCL AS SYSDBA> set lines 200
SYS@ORCL AS SYSDBA> r
  1* select * from dba_directories where directory_name = 'UURAL_DATAPUMPDEMO'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            UURAL_DATAPUMPDEMO             /transfer/uural/datapumpdemo


Lets create a sample schemas with some of empty tables in them.
SYS@ORCL AS SYSDBA> create user u0001 identified by "u0001_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0001 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> GRANT READ, WRITE ON DIRECTORY SYS.UURAL_DATAPUMPDEMO TO U0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> create user u0002 identified by "u0002_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0002 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> conn u0001/u0001_pass
Connected.
U0001@ORCL > create table t0001 (col1 number);

Table created.

U0001@ORCL > conn u0002/u0002_pass
Connected.
U0002@ORCL > create table t0001 (col1 number);

Table created.

Lets try to export some tables from only a single schema in the database.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp u0001/u0001_pass directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001_tables logfile=u0001_tables tables=T0001

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:29:05

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "U0001"."SYS_EXPORT_TABLE_01":  u0001/******** directory=UURAL_DATAPUMPDEMO dumpfile=u0001_tables logfile=u0001_tables tables=T0001
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "U0001"."T0001"                                 0 KB       0 rows
Master table "U0001"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U0001.SYS_EXPORT_TABLE_01 is:
  /transfer/uural/datapumpdemo/u0001_tables.dmp
Job "U0001"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:39

[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 136
-rw-r-----    1 oracle   dba           61440 Jun 23 15:32 u0001_tables.dmp
-rw-r--r--    1 oracle   dba            1043 Jun 23 15:32 u0001_tables.log
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now try to export tables from two different schemas, since we are going to export tables from two schemas we will use "SYS" as the userid parameter in expdp command.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001                                                     

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:35:01

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema

[oracle@srvdb01]:/transfer/uural/datapumpdemo >

As you can see you can not export tables from different schemas by using "tables" parameter in expdp in Oracle 10g.
What you should do is create a table and insert the names of the tables to be exported to this table and use the "schemas" and "include" parameter in expdp command.

U0001@ORCL > create table expdp_tables (table_name varchar2(30));

Table created.

U0001@ORCL > insert into expdp_tables values ('T0001');

1 row created.

U0001@ORCL > commit;

Commit complete.

You only need to insert the distinct names of all the tables into "expdp_tables" schema. Put the names of the all the distinct schemas whose tables will be exported in the "schemas" parameter.
In that method you export all the tables in "expdp_tables" table from both schemas, this means if a specific table name exist in both schemas and you only want to export one of them, you can not do that.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                                             

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:50:35

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=UURAL_DATAPUMPDEMO dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 INCLUDE=TABLE:"IN (SELECT table_name FROM u0001.expdp_tables)"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /transfer/uural/datapumpdemo/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:58:01

[oracle@srvdb01]:/transfer/uural/datapumpdemo >


Lets now try to export tables from different schemas in Oracle 11g database on a Linux server.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 23 07:07:24 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column directory_path format a50
SQL> select * from dba_directories where directory_name = 'DATA_PUMP_DIR'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            DATA_PUMP_DIR                  /home/oracle/app/oracle/admin/orcl/dpdump/

SQL> 

Check the number of files in the destination folder before the export
[oracle@localhost ~]$ ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 4
-rw-r----- 1 oracle oracle 116 Oct  2  2010 dp.log
[oracle@localhost ~]$ 

Prepare the sample schema and objects
SQL> create user u0001 identified by "u0001_pass";

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by "u0002_pass";

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> grant create table to u0002;

Grant succeeded.

SQL> conn u0001/u0001_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.

SQL> conn u0002/u0002_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.


Try the export in 11g
[oracle@localhost ~]$ expdp '"/ as sysdba"' directory=DATA_PUMP_DIR dumpfile=u0001-u0002_tables 
logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001

Export: Release 11.2.0.2.0 - Production on Thu Jun 23 07:17:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/app/oracle/admin/orcl/dpdump/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 07:18:48

[oracle@localhost ~]$  ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 96
-rw-r----- 1 oracle oracle   116 Oct  2  2010 dp.log
-rw-rw---- 1 oracle oracle 86016 Jun 23 07:18 u0001-u0002_tables.dmp
-rw-rw-r-- 1 oracle oracle  1167 Jun 23 07:18 u0001-u0002_tables.log
[oracle@localhost ~]$ 

As you can see, in Oracle 11g, you can export tables from different schemas by using "tables" parameter in Export Data Pump Utility and this is not possible in Oracle 10g.

Tuesday, June 21, 2011

How to enable and disable archivelog mode in an Oracle database

Although this is a very basic info, I just wanted to express it again and give some tips about it.

1-) Enable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:15:49 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Current log sequence           429

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             373295800 bytes
Database Buffers           75497472 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Enable archivelog in the database
SQL> alter database archivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Next log sequence to archive   429
Current log sequence           429

Do you know where the newly generated archivelogs are going to from now on, if there is any "log_archive_dest_n" parameter set and its "log_archive_des_n_state" is "enable" then all the newly generated archivelogs goes to that destination.


SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL> 

As you can see above there is no explicitly set "log_archive_dest_n", then archivelogs will go to the destination set in Oracle database parameter "db_recovery_file_dest" because "log_archive_dest_10" parameter implicitly set to "db_recovery_file_dest" parameter value, if there is no explicitly set "log_archive_dest_n" parameter. This is also the reason why we see in the above output "Archive destination USE_DB_RECOVERY_FILE_DEST", means the destination set in the "db_recovery_file_dest" parameter value.

Lets check that "db_recovery_file_dest" parameter value and see that it points to flash recovery area.

SQL> sho parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M

Check that destination
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL
[oracle@localhost ORCL]$ ls -l
total 4
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$

There is a folder named "ORCL"(value of $ORACLE_SID) below that folder.There is no "archivelog" folder yet, since there is no archivelogs generated yet.

Lets force generation of some archivelogs
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

Check the archivelog destination again
[oracle@localhost ORCL]$ ls -l
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 21 06:37 archivelog
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$ cd archivelog/
[oracle@localhost archivelog]$ ls -l
total 4
drwxrwx--- 2 oracle oracle 4096 Jun 21 06:37 2011_06_21
[oracle@localhost archivelog]$ cd 2011_06_21/
[oracle@localhost 2011_06_21]$ ls -l
total 2756
-rw-rw---- 1 oracle oracle 2808320 Jun 21 06:37 o1_mf_1_429_7017nnrw_.arc
-rw-rw---- 1 oracle oracle    1024 Jun 21 06:37 o1_mf_1_430_7017nq5w_.arc
-rw-rw---- 1 oracle oracle    2560 Jun 21 06:37 o1_mf_1_431_7017nrxo_.arc
[oracle@localhost 2011_06_21]$
As you can see Oracle created a folder named "archivelog" and a subfolder with today's date "2011_06_21" and put the newly generated archivelogs there.

If you want, you can explicitly set a new archivelog destination instead of implicitly set flash recovery area like below.

First create the new destination on the server
[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle
[oracle@localhost oracle]$ mkdir arc2
[oracle@localhost oracle]$ cd arc2
[oracle@localhost arc2]$ mkdir ORCL
[oracle@localhost arc2]$ cd ORCL
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ 

Now set "log_archive_dest_1" parameter to this new destination, you can do this while the database is in open mode.
SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/arc2/ORCL';

System altered.

--Check the "log_archive_dest_1" parameter
SQL> sho parameter log_archive_dest_1 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
                                                 le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string

--Now check the archivelog mode state, as you can see we see the new destination as "Archive destination            /home/oracle/app/oracle/arc2/ORCL"
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     430
Next log sequence to archive   432
Current log sequence           432
SQL> 

From now on, all the newly generated archivelogs will go to that new destination.

There is no archivelog in the new destination for now
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 0
[oracle@localhost ORCL]$ 

Generate some new archivelogs
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

Check the new destination and see the newly generated archivelogs

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf
[oracle@localhost ORCL]$

2-) Disable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:56:38 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Next log sequence to archive   435
Current log sequence           435

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Disable archivelog in the database
SQL> alter database noarchivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Current log sequence           435

As you can see we disabled the archivelog mode in the database, there will not be any newly generated archivelogs in the archivelog destination although it is still set in the database.

Switch some logfiles
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

As you can see there is no newly generated archivelogs in the archivelog destination.
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf

3-) Additional info :

Do not close the database in a way that it will require instance recovery(e.g. shutdown abort) before you will mount it for archivelog mode change otherwise you will get the below error "ORA-00265", always try to shutdown the database gracefully by using "shutdown immediate".

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:07:41 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> 

If there is a little chance that you can close the database with "shutdown immediate" because of the heavy activity on the database, you can use the below method to bring the database to the mount mode without causing any instance recovery.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:11:07 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     438
Next log sequence to archive   440
Current log sequence           440
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ 

When you open the database with "startup restrict", only the users with "Restricted Session" system privilege (SYS user is one of them) can connect to database, since all the other client sessions will not be able to connect to the database, you will be able to shutdown the database faster with "shutdown immediate".

Monday, June 20, 2011

How to set Oracle SQL*Plus sqlprompt on Unix

Sometimes it is very confusing to realize in which database sqlprompt we are, writing and executing the SQL commands. If we write an SQL command in a production database instead of development or test database, the results can be catastrophic. In the below example, I will try to demonstrate my way of setting the sqlprompt in Oracle SQl*Plus. My example Unix version is IBM AIX.

Oracle SQL*Plus, always, first runs an sql script called "glogin.sql" when you try to connect to an Oracle database.
First take the backup of this script, if you need it later you can use the original version.
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > ls -l
total 40
-rw-r-----    1 oracle   dba            1525 Aug 30 2004  glogin.sql
drwxr-x---    2 oracle   dba             256 May 23 2008  help
drwxr-x---    3 oracle   dba             256 May 23 2008  iplus
-rw-r-----    1 oracle   dba            5363 Mar 02 2008  libsqlplus.exp
-rw-r-----    1 oracle   dba             813 Mar 26 2006  plustrce.sql
-rw-r-----    1 oracle   dba            2118 Feb 16 2003  pupbld.sql
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > cp glogin.sql glogin.sql-201106141153

Add the following line to the end of "glogin.sql" script.
set sqlprompt "&_USER'@'&_CONNECT_IDENTIFIER &_PRIVILEGE> "

Now try to login to database by using SQL*Plus in a telnet session.
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 14 12:53:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL AS SYSDBA>

As you can see, we set the Oracle SQL*Plus sqlprompt to the username@instance_name the privilege like " as sysdba".

If you apply this in your Unix Oracle database servers, when you have multiple databases in the same server or when you have multiple telnet sessions connected to different database servers and you are always switching between them, you realize in which database you are connected to and it will help a lot not to write the wrong SQL in especially in production databases instead of development or test databases.

How to set the userid parameter in Oracle 10g impdp command line on IBM AIX

You can set the userid parameter in Oracle 10g impdp command line on IBM AIX OS like in the below example. It can sometimes be confusing to remember the placement of ' and " characters around / as sysdba, in below example it is ' "/ as sysdba" ' with no spaces between ' and ".

[oracle@srvdb01]:/oracle > impdp '"/ as sysdba"' 
directory=DPUMP_DIR  dumpfile=SRVDB01_PROD_PROD_201105101132_tables 
logfile=SRVDB01_PROD_PROD_201105101132_tables_log tables=T0001 remap_schema=U0001:U0002

Sunday, June 19, 2011

Oracle 11g New Feature "SQL Result Cache"

I want to demonstrate one of the new features of Oracle 11g called "SQL Result Cache" which will directly effect the database performance .

I am going to use the same sample schemas that I created in my previous blog post.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:13:02 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user u0001 quota 2G on users;

User altered.

SQL> conn u0001/u0001
Connected.
SQL> create table t0002 as select * from all_objects;

Table created.

SQL> select count(*) from t0002;

COUNT(*)
----------
56100

Populate the records in table t0002
SQL> begin
for c in (select rownum from t0002 where rownum <= 5) loop
    insert into t0002 select * from t0002;
    commit;
  end loop;
end;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL> select count(*) from t0002;

COUNT(*)
----------
1795200

SQL> 

Run a GROUP BY query on this table
SQL> set timing on
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:03.11
SQL> 

Query took 03.11 seconds
Now run the query so that results of the query will be cached in "SQL Result Cache", we accomplish this by using the "/*+ RESULT_CACHE */" hint in the query.

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:02.87
SQL>

First run of the query took 02.87 seconds

SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:00.05
SQL> 

Second run of the query took only 00.05 seconds because the query results has been fetched from "SQL Result Cache" in database server memory without going to disk to execute the SQL.

We can also see from the explain plan that the results of the query is read from SQL Result Cache.
SQL> set autotrace traceonly explain
SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4234960643

--------------------------------------------------------------------------------
------------------

| Id  | Operation           | Name                       | Rows  | Bytes | Cost
(%CPU)| Time     |

--------------------------------------------------------------------------------
------------------

|   0 | SELECT STATEMENT    |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   1 |  RESULT CACHE       | 44uuyvrukrh7w7w2m48aqb12tk |       |       |
|          |

|   2 |   SORT GROUP BY     |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   3 |    TABLE ACCESS FULL| T0002                      |    57M|   603M|  7612
(6)| 00:01:32 |

--------------------------------------------------------------------------------
------------------


Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(U0001.T0002); parameters=(nls); name="selec
t /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type orde
r by object_type"


Note
-----
- dynamic sampling used for this statement (level=2)

SQL> 

We can also compare the SQL statisticts and see that SQL with RESULT_CAHCHE hint has no "consistent gets" or "physical reads".
SQL> set autotrace traceonly statistics;
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

23 rows selected.

Elapsed: 00:00:01.83

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
37542  consistent gets
25909  physical reads
0  redo size
1041  bytes sent via SQL*Net to client
430  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
23  rows processed

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

23 rows selected.

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
1041  bytes sent via SQL*Net to client
430  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
23  rows processed

SQL> 

Lets now monitor the SQL Result Cache by using related dynamic views.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:41:23 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT name
,      type
,      cache_id
,      row_count
FROM   v$result_cache_objects
ORDER  BY
creation_timestamp;    

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
U0001.T0002
Dependency
U0001.T0002
0


NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type
order by object_type
Result
44uuyvrukrh7w7w2m48aqb12tk
23

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------


When there will be any DML operation on the dependent object u0001.t0002, the result cache is invalidated and generated again with the next run of SQL.

Monitor the SQL Result Cache statistics
SQL> column name format a30
SQL> column value format a30
SQL> select name, value from v$result_cache_statistics order by id;

NAME                           VALUE
------------------------------ ------------------------------
Block Size (Bytes)             1024
Block Count Maximum            1120
Block Count Current            32
Result Size Maximum (Blocks)   56
Create Count Success           1
Create Count Failure           0
Find Count                     2
Invalidation Count             0
Delete Count Invalid           0
Delete Count Valid             0
Hash Chain Length              1

NAME                           VALUE
------------------------------ ------------------------------
Find Copy Count                2

12 rows selected.

SQL> 

"WITH GRANT OPTION" and Cascading Revoke

Most of you probably already know, but I want to demonstrate again one of the important features of Oracle database called "Cascading Revoke". I will use Oracle 11g as an example database for this demonstration but this feature is also available in the older versions of Oracle databases.

First connect to the database as SYS user.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 12:05:59 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Create sample schemas
SQL> create user u0001 identified by u0001;

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by u0002;

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> create user u0003 identified by u0003;

User created.

SQL> grant create session to u0003;

Grant succeeded.

Create a sample table in u0001 schema and give select privilege on this table to u0002 user with grant option.
SQL> conn u0001/u0001
Connected.
SQL> create table t0001 (col1 number, col2 varchar2(100));

Table created.

SQL> grant select on t0001 to u0002 with grant option;

Grant succeeded.

SQL> column grantee format a7
SQL> column owner format a5
SQL> column table_name format a10
SQL> column grantor format a7
SQL> column privilege format a9
SQL> column grantable format a9
SQL> select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0002   U0001 T0001      U0001   SELECT    YES       NO

Connect with u0002 and grant select privilege on u0001.t0001 table to user u0003
SQL> conn u0002/u0002
Connected.
SQL> grant select on u0001.t0001 to u0003;

Grant succeeded.

Check grants on table u0001.t0001
SQL> conn u0001/u0001
Connected.
SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0003   U0001 T0001      U0002   SELECT    NO        NO
U0002   U0001 T0001      U0001   SELECT    YES       NO

Now revoke select privilege from only user u0002
SQL> revoke select on t0001 from u0002;

Revoke succeeded.

List privileges on table u0001.t0001
SQL> select * from user_tab_privs;

no rows selected

As you can see, although we revoked the select privilege only from user u0002, select privilege given to user u0003 by u0002 user was also revoked, because a "Cascading Revoke" occured.

In summary, any privilege given to any user(u0003) by another grantor user(u0002) having this privilege "WITH GRANT OPTION" will automatically be revoked when this privilege revoked by object owner(u0001) from this grantor user(u0002).

Problem starting the second node of RAC after OS upgrade from IBM AIX v5.3 to v6.1

We decided to test the IBM AIX OS upgrade operation in a test Oracle RAC environment. We wanted to do this test in a rolling fashion not to cause any downtime in production environment which is two node Oracle 10gR2 RAC database.

What happened was that, after Unix team upgraded the OS on second node of RAC from IBM AIX v5.3 to v6.1, we restarted the second node and we saw that Oracle crs processes did not start successfully on the second node.

We checked the CRS services
[root@srvdb01]:/home/root > crsstat
HA Resource                       Target  State
-----------                       ------  -----
ora.ORCL.ORCL1.inst               ONLINE  ONLINE on srvdb01
ora.ORCL.db                       ONLINE  ONLINE on srvdb01
ora.srvdb01.ASM1.asm              ONLINE  ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr ONLINE  ONLINE on srvdb01
ora.srvdb01.gsd                   ONLINE  ONLINE on srvdb01
ora.srvdb01.ons                   ONLINE  ONLINE on srvdb01
ora.srvdb01.vip                   ONLINE  ONLINE on srvdb01
ora.ORCL.ORCL2.inst               OFFLINE OFFLINE
ora.srvdb02.ASM2.asm              OFFLINE OFFLINE
ora.srvdb02.LISTENER_SRVDB02.lsnr OFFLINE OFFLINE
ora.srvdb02.gsd                   OFFLINE OFFLINE
ora.srvdb02.ons                   OFFLINE OFFLINE
ora.srvdb02.vip                   ONLINE  ONLINE on srvdb02

As you can see from the above output, "ora.srvdb02.vip" did not automatically moved to node1, so first we relocated that vip service to node1. Because client sessions trying to connect to the database by using this vip address was getting connection errors.
[root@srvdb01]:/home/root > $CRS_HOME/bin/crs_relocate ora.srvdb02.vip

Status after relocate,
[root@srvdb01]:/home/root > crsstat
HA Resource                       Target State
-----------                       ------  -----
ora.ORCL.ORCL1.inst               ONLINE  ONLINE on srvdb01
ora.ORCL.db                       ONLINE  ONLINE on srvdb01
ora.srvdb01.ASM1.asm              ONLINE  ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr ONLINE  ONLINE on srvdb01
ora.srvdb01.gsd                   ONLINE  ONLINE on srvdb01
ora.srvdb01.ons                   ONLINE  ONLINE on srvdb01
ora.srvdb01.vip                   ONLINE  ONLINE on srvdb01
ora.ORCL.ORCL2.inst               OFFLINE OFFLINE
ora.srvdb02.ASM2.asm              OFFLINE OFFLINE
ora.srvdb02.LISTENER_SRVDB02.lsnr OFFLINE OFFLINE
ora.srvdb02.gsd                   OFFLINE OFFLINE
ora.srvdb02.ons                   OFFLINE OFFLINE
ora.srvdb02.vip                   ONLINE  ONLINE on srvdb01

By the way if you do not have "crsstat" command on your server, you can use "crs_stat -t" command instead or you can put the following script in a folder and add its path to the "PATH" environment variable in "oracle" user profile on your server. "crs_stat -t" command does not display the full name of "HA Resource" but "crsstat" command below will display full names of "HA Resource" column.
[oracle@srvdb01]:/oracle/uural > more crsstat
#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
#AWK=/usr/xpg4/bin/awk    # if not available use /usr/bin/awk
AWK=/usr/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

[oracle@srvdb01]:/oracle/uural >

Anyway we checked the reason why CRS services was down on the second node after OS upgrade. We checked the CRS related logfiles on node2.
We saw the following error.
[root@srvdb02]:/oracle/crshome1/log/srvdb02/client > vi ocrcheck_327920.log
Oracle Database 10g CRS Release 10.2.0.4.0 Production Copyright 1996, 2008 Oracle.  All rights reserved.
2011-06-14 16:13:48.455: [OCRCHECK][1]ocrcheck starts...
2011-06-14 16:13:48.457: [  OCROSD][1]utopen:7:failed to open OCR file/disk /dev/ocr_disk1 /dev/ocr_disk2, errno=19, os err string=No such device
2011-06-14 16:13:48.457: [  OCRRAW][1]proprinit: Could not open raw device
2011-06-14 16:13:48.457: [ default][1]a_init:7!: Backend init unsuccessful : [26]
2011-06-14 16:13:48.457: [OCRCHECK][1]Failed to access OCR repository: [PROC-26: Error while accessing the physical storage Operating System error [No such device] [19]]
2011-06-14 16:13:48.457: [OCRCHECK][1]Failed to initialize ocrchek2
2011-06-14 16:13:48.457: [OCRCHECK][1]Exiting [status=failed]...

We thought that node2 could not access the OCR disk anyway. We checked the OCR disk configuration on node2.
[root@srvdb02]:/home/root > ls -l /dev/ocr*
crw-r-----    1 root     dba          23, 27 Jun 08 15:14 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jun 08 15:16 /dev/ocr_disk2

When we first installed Oracle RAC, we created the OCR disks as below
mknod /dev/ocr_disk1 c 23 27
mknod /dev/ocr_disk2 c 23 5

Querying the corresponding hdisks by using the disk access path "major, minor" numbers gave no results
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23, 27"
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23,  5"

We realized that major numbers of hdisks have changed after OS upgrade.
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "21, 27"
brw-------    1 root     system       21, 27 Jun 14 15:34 /dev/hdisk25
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23,  5"
brw-------    1 root     system       21,  5 Jun 14 15:34 /dev/hdisk3

But how could we be sure that the minor numbers did not change after OS upgrade. To check this we need to get one level deeper and compare the LUN ids of those hdisks with the ones on the node1 which has stil IBM AIX v5.3 OS.

We checked the ocr disk configuration on node1
[root@srvdb01]:/home/root > ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  4 Jun 19 15:52 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jun 19 15:52 /dev/ocr_disk2
[root@srvdb01]:/home/root > ls -l /dev/hdisk* | grep "23,  4"
brw-------    1 root     system       23,  4 May 16 2008  /dev/hdisk2
[root@srvdb01]:/home/root > ls -l /dev/hdisk* | grep "23,  5"
brw-------    1 root     system       23,  5 May 16 2008  /dev/hdisk3

Find the LUN ids of those hdisks on node1
[root@srvdb01]:/home/root > for i in 2 3
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk2 -> lun_id = 0x0001000000000000 / reserve_policy = no_reserve / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = no_reserve / pvid = none

Find the LUN ids of hdisks on node2 which were configured as ocr disks
[root@srvdb02]:/home/root > for i in 25 3
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk25 -> lun_id = 0x0018000000000000 / reserve_policy = single_path / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = single_path / pvid = none

As we compare the LUN ids of those disks on both nodes, we saw that they did not match, which means not only the major numbers but also the minor numbers of hdisks had been changed on node2 after the OS upgrade on this node.
Since we could not find so much information about this problem on the Internet, we discussed with the Unix guys about the problem and we decided that the problem of changing disk access path major minor number change could be the result of IBM AIX v6.1 started to use MPIO to manage storage although IBM AIX v5.3 was using RDAC. Although the Unix guys said they tried to changed the MPIO to RDAC after the upgrade on node2, they said this corrected only the major number change and set the major numbers to the old original values but still the minor numbers were scrambled causing the Oracle RAC node2 to fail to start CRS services.

In any case like that if the Unix administrators are not able to bring back the original major,minor numbers of the hdisks, what you can do is find the corresponding hdisks numbers by matching the LUN ids on node2, remove ocr disks and create them againg with the correct hdisk major, minor numbers and try to start the OCR on node2.

I will demonstrate how to find the matching hdisks on node2 by using LUN ids on node1.
List the LUN ids on node2
[root@srvdb02]:/home/root > for i in 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk1 -> lun_id = 0x0000000000000000 / reserve_policy = single_path / pvid = none
hdisk2 -> lun_id = 0x0001000000000000 / reserve_policy = single_path / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = single_path / pvid = none
hdisk4 -> lun_id = 0x0003000000000000 / reserve_policy = single_path / pvid = none
hdisk5 -> lun_id = 0x0004000000000000 / reserve_policy = single_path / pvid = none
hdisk6 -> lun_id = 0x0005000000000000 / reserve_policy = single_path / pvid = none
hdisk7 -> lun_id = 0x0006000000000000 / reserve_policy = single_path / pvid = none
hdisk8 -> lun_id = 0x0007000000000000 / reserve_policy = single_path / pvid = none
hdisk9 -> lun_id = 0x0008000000000000 / reserve_policy = single_path / pvid = none
hdisk10 -> lun_id = 0x0009000000000000 / reserve_policy = single_path / pvid = none
hdisk11 -> lun_id = 0x000a000000000000 / reserve_policy = single_path / pvid = none
hdisk12 -> lun_id = 0x000b000000000000 / reserve_policy = single_path / pvid = none
hdisk13 -> lun_id = 0x000c000000000000 / reserve_policy = single_path / pvid = none
hdisk14 -> lun_id = 0x000d000000000000 / reserve_policy = single_path / pvid = none
hdisk15 -> lun_id = 0x000e000000000000 / reserve_policy = single_path / pvid = none
hdisk16 -> lun_id = 0x000f000000000000 / reserve_policy = single_path / pvid = none
hdisk17 -> lun_id = 0x0010000000000000 / reserve_policy = single_path / pvid = none
hdisk18 -> lun_id = 0x0011000000000000 / reserve_policy = single_path / pvid = none
hdisk19 -> lun_id = 0x0012000000000000 / reserve_policy = single_path / pvid = none
hdisk20 -> lun_id = 0x0013000000000000 / reserve_policy = single_path / pvid = none
hdisk21 -> lun_id = 0x0014000000000000 / reserve_policy = single_path / pvid = none
hdisk22 -> lun_id = 0x0015000000000000 / reserve_policy = single_path / pvid = none
hdisk23 -> lun_id = 0x0016000000000000 / reserve_policy = single_path / pvid = none
hdisk24 -> lun_id = 0x0017000000000000 / reserve_policy = single_path / pvid = none
hdisk25 -> lun_id = 0x0018000000000000 / reserve_policy = single_path / pvid = none
hdisk26 -> lun_id = 0x0019000000000000 / reserve_policy = single_path / pvid = none
hdisk27 -> lun_id = 0x001a000000000000 / reserve_policy = single_path / pvid = none
hdisk28 -> lun_id = 0x001b000000000000 / reserve_policy = single_path / pvid = none
hdisk29 -> lun_id = 0x001c000000000000 / reserve_policy = single_path / pvid = none
hdisk30 -> lun_id = 0x001d000000000000 / reserve_policy = single_path / pvid = none
hdisk31 -> lun_id = 0x001e000000000000 / reserve_policy = single_path / pvid = none
hdisk32 -> lun_id = 0x001f000000000000 / reserve_policy = single_path / pvid = none
hdisk33 -> lun_id = 0x0028000000000000 / reserve_policy = single_path / pvid = 000c84c103c1d4480000000000000000
hdisk34 -> lun_id = 0x0020000000000000 / reserve_policy = single_path / pvid = none
hdisk35 -> lun_id = 0x0021000000000000 / reserve_policy = single_path / pvid = none
hdisk36 -> lun_id = 0x0022000000000000 / reserve_policy = single_path / pvid = none
hdisk37 -> lun_id = 0x0023000000000000 / reserve_policy = single_path / pvid = none
hdisk38 -> lun_id = 0x0024000000000000 / reserve_policy = single_path / pvid = none
hdisk39 -> lun_id = 0x0025000000000000 / reserve_policy = single_path / pvid = none
hdisk40 -> lun_id = 0x0026000000000000 / reserve_policy = single_path / pvid = none
hdisk41 -> lun_id = 0x0027000000000000 / reserve_policy = single_path / pvid = none

As you can see from the following output, if we match hdisk2 and hdisk3 LUN ids on node1 to LUN ids on node2 we found that they correspond again to the hdisk2 and hdisk3 on node2. This time LUN ids and hdisk names matches on both nodes but this is not always like that, hdisk names does not necessarily have to match on both servers everytime, the important thing is LUN ids should match, hdisk2 on node1 could match to hdisk18 on node2 in any other occasion.

In our case, hdisk2 and hdisk3 on node1 are the same disks as hdisk2 and hdisk3 on node2.
We need to find major, minor numbers of those disks on node2.
[root@srvdb02]:/home/root > ls -l /dev/hdisk[2,3]
brw-------    1 root     system       21,  4 Jun 14 15:34 /dev/hdisk2
brw-------    1 root     system       21,  5 Jun 14 15:34 /dev/hdisk3

Delete old ocr disk device definitions
[root@srvdb02]:/home/root > rm /dev/ocr_disk1
[root@srvdb02]:/home/root > rm /dev/ocr_disk2

Recreate ocr disks with the correct disk major,minor numbers
[root@srvdb02]:/home/root > mknod /dev/ocr_disk1 c 21 4
[root@srvdb02]:/home/root > mknod /dev/ocr_disk2 c 21 5

After completing this configuration you can first check the status and then try to restart CRS services
[root@srvdb02]:/home/root > crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
[root@srvdb02]:/home/root > crsctl start crs

How to delete and then add the same Oracle RAC node to an Oracle 10gR2 cluster

If you will need this kind of operation any time, you can use the following steps to remove and then add the same Oracle RAC node to an Oracle 10gR2 Cluster.

1-) Remove node2 from cluster :

First we backed up the OCR configuration (in case we need it)
[root@srvdb01]:/home/root > ocrconfig -export beforeupgrade -s online

Then we got RAC network configuration on first node.
[root@srvdb01]:/home/root > oifcfg getif
en8  10.1.11.0  global  public
en9  192.168.1.0  global  cluster_interconnect

List nodeapps
[root@srvdb01]:/home/root > crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.ORCL.ORCL1.inst                           ONLINE     ONLINE on srvdb01
ora.ORCL.db                                   ONLINE     ONLINE on srvdb01
ora.srvdb01.ASM1.asm                          ONLINE     ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr             ONLINE     ONLINE on srvdb01
ora.srvdb01.gsd                               ONLINE     ONLINE on srvdb01
ora.srvdb01.ons                               ONLINE     ONLINE on srvdb01
ora.srvdb01.vip                               ONLINE     ONLINE on srvdb01
ora.ORCL.ORCL2.inst                           ONLINE     ONLINE on srvdb02
ora.srvdb02.ASM2.asm                          ONLINE     ONLINE on srvdb02
ora.srvdb02.LISTENER_SRVDB02.lsnr             ONLINE     ONLINE on srvdb02
ora.srvdb02.gsd                               ONLINE     ONLINE on srvdb02
ora.srvdb02.ons                               ONLINE     ONLINE on srvdb02
ora.srvdb02.vip                               ONLINE     ONLINE on srvdb02

On node1 we removed the instance ORCL2 running on node2 by using $ORACLE_HOME/bin/dbca.
[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/dbca
Choose RAC database
Choose Instance Management
Choose Delete Instance

On node1 we stopped and removed the ASM2 instance running on node2.
[root@srvdb01]:/home/root > srvctl stop asm -n srvdb02
[root@srvdb01]:/home/root > srvctl remove asm -n srvdb02

On node2 we removed the instance LISTENER_SRVDB02 running on node2 by using $ORACLE_HOME/bin/netca.
[oracle@srvdb02]:/oracle > $ORACLE_HOME/bin/netca
Choose Cluster management
Choose Listener
Choose Remove
Confirm deletion of LISTENER_SRVDB02

On node2 update node list in $ORACLE_HOME
[oracle@srvdb02]:/oracle > $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="srvdb02" -local

On node1 update node list in $ORACLE_HOME
[oracle@srvdb01]:/oracle > $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="srvdb01"

Remove node2 from cluster
[oracle@srvdb01]:/oracle > $CRS_HOME/bin/racgons remove_config srvdb02:6201
( Replace port 6201 in the above command with the number that you see in the remote port section of the ons.config file in the CRS_HOME/opmn/conf diretory )

On node2
[root@srvdb02]:/home/root > $CRS_HOME/install/rootdelete.sh

List cluster nodes.
[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1
srvdb02       2

On node1
[root@srvdb01]:/home/root > $CRS_HOME/install/rootdeletenode.sh srvdb02,2

On node2 update node list in $CRS_HOME
[oracle@srvdb02]:/oracle > $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$CRS_HOME CLUSTER_NODES="srvdb02" CRS=TRUE -local

On node1 update node list in $CRS_HOME
[oracle@srvdb01]:/oracle > $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$CRS_HOME CLUSTER_NODES="srvdb01" CRS=TRUE

Check the nodeapps have been removed on node2
[root@srvdb01]:/home/root > srvctl status nodeapps -n srvdb02

List cluster nodes.
[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1

2-) Add node2 back to cluster :

Add Cluster Home
[oracle@srvdb01]:/oracle > $CRS_HOME/oui/bin/addNode.sh -nocopy

Add node2 to cluster
[oracle@srvdb01]:/oracle > $CRS_HOME/bin/racgons add_config srvdb02:6201
( Replace port 6201 in the above command with the number that you see in the remote port section of the ons.config file in the CRS_HOME/opmn/conf diretory )

Add Oracle Home
[oracle@srvdb01]:/oracle > $ORACLE_HOME/oui/bin/addNode.sh -nocopy

Configure VIP of node2
[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/vipca -nodelist srvdb02

On node2 we added the instance LISTENER_SRVDB02 running on node2 by using $ORACLE_HOME/bin/netca.
[oracle@srvdb02]:/oracle > $ORACLE_HOME/bin/netca
Choose Cluster management
Choose Listener
Choose Add
Choose the name as LISTENER
This will add a listener on node2 with the name LISTENER_SRVDB02

On node1 we added the instance ORCL2 running on node2 by using $ORACLE_HOME/bin/dbca with this step we also created the ASM2 instance.
[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/dbca
Choose RAC
Choose Instance Management
Choose Add Instance

List cluster nodes.
[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1
srvdb02       2

List nodeapps
[root@srvdb01]:/home/root > crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.ORCL.ORCL1.inst                           ONLINE     ONLINE on srvdb01
ora.ORCL.db                                   ONLINE     ONLINE on srvdb01
ora.srvdb01.ASM1.asm                          ONLINE     ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr             ONLINE     ONLINE on srvdb01
ora.srvdb01.gsd                               ONLINE     ONLINE on srvdb01
ora.srvdb01.ons                               ONLINE     ONLINE on srvdb01
ora.srvdb01.vip                               ONLINE     ONLINE on srvdb01
ora.ORCL.ORCL2.inst                           ONLINE     ONLINE on srvdb02
ora.srvdb02.ASM2.asm                          ONLINE     ONLINE on srvdb02
ora.srvdb02.LISTENER_SRVDB02.lsnr             ONLINE     ONLINE on srvdb02
ora.srvdb02.gsd                               ONLINE     ONLINE on srvdb02
ora.srvdb02.ons                               ONLINE     ONLINE on srvdb02
ora.srvdb02.vip                               ONLINE     ONLINE on srvdb02

Saturday, June 18, 2011

The difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup

Most probably most of you know but I wanted to demonstrate the difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup.

Lets first check the current archivelog destinations in the target database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 18 12:16:24 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      LOCATION=USE_DB_RECOVERY_FILE_
DEST
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL> 

As you can see there are two archivelog destinations defined "log_archive_dest_1" and "log_archive_dest_2".

Lets list the archivelogs in the target database by using RMAN tool.

[oracle@localhost ORCL]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 18 12:24:32 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

24      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

26      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc


RMAN> 

Lets now try to backup and delete backed up archivelogs by using "DELETE INPUT".

RMAN> backup archivelog all delete input;

Starting backup at 18-JUN-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=404 RECID=24 STAMP=754143819
input archived log thread=1 sequence=405 RECID=26 STAMP=754143861
input archived log thread=1 sequence=406 RECID=28 STAMP=754143995
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T122636_6zsyzf26_.bkp tag=TAG20110618T122636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc RECID=24 STAMP=754143819
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc RECID=26 STAMP=754143861
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_406_6zsyzcjd_.arc RECID=28 STAMP=754143995
Finished backup at 18-JUN-11

Now list the remaining archivelog files, since only one copy of archivelog files are deleted, we still see the first copies of archivelogs remaining in the first archivelog destination.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

27      1    406     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_406_701609923.dbf


RMAN> 

Now lets try to backup and delete the all the archivelogs backed up by using "DELETE ALL INPUT". We will start the demonstration with newly generated archivelogs.
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
29      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf

30      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc

31      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf

32      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc

Backup and delete all of them.

RMAN> backup archivelog all delete all input;

Starting backup at 18-JUN-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=407 RECID=30 STAMP=754144245
input archived log thread=1 sequence=408 RECID=32 STAMP=754144248
input archived log thread=1 sequence=409 RECID=34 STAMP=754144369
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T123249_6zszc1s6_.bkp tag=TAG20110618T123249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc RECID=30 STAMP=754144245
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf RECID=29 STAMP=754144245
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc RECID=32 STAMP=754144248
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf RECID=31 STAMP=754144248
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_409_6zszc108_.arc RECID=34 STAMP=754144369
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_409_701609923.dbf RECID=33 STAMP=754144369
Finished backup at 18-JUN-11

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> 

As a result, by using "DELETE INPUT" we made RMAN, backup only one copy of all the archivelogs in one archivelog destination and then delete only the backed up copies of all the archivelogs in only one archivelog destination, but by using "DELETE ALL INPUT", we made RMAN backup only one copy of all the archivelogs in one archivelog destination and then delete all the copies of all the archivelogs in all the archivelog destinations.

Monitoring last RMAN backup log in target Oracle database in Oracle 10g

You can use two simple SQL commands to monitor the last RMAN backup log in target Oracle database without using RMAN catalog database in Oracle 10g.

The information in "v$rman_status" comes from the target database controlfile, showing one record for every RMAN command executed in RMAN backup script.

select * 
from v$rman_status 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;

The information in "v$rman_output" comes from the target database memory and is not stored in target database controlfile, showing all the output as seperate records for every RMAN command executed in RMAN backup script.

In a single server database you should use "v$rman_output", if you have a RAC database you should use "gv$rman_output" dynamic view instead.

select * 
from v$rman_output 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid ;

Friday, June 17, 2011

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Today, we faced an error in some of our daily Oracle database export jobs as below.

;;;
Export: Release 10.2.0.3.0 - 64bit Production on Friday, 17 June, 2011 0:30:00

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/export/SRVDB01_DEV_DEV_201106170030_01.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 4

Working together with our Unix team, we tried to solve the problem.
First we checked the "/export" NFS file system mount options on AIX server.

[oracle@srvdb01]:/oracle > mount -p
node       mounted        mounted over    vfs       date        options
-------- ---------------  ---------------  ------ ------------ ---------------
srvnfs01 /export          /export          nfs3   Jun 17 14:02 bg,hard,intr,rsize=32768,wsize=32768,proto=udp,sec=sys,rw

We saw that the mount options are correct on the database server where the "/export" filesystem is mounted.

Then, one of our Unix team members realized that file access permissions on "/etc/filesystems" file does not have a read permission for the Unix user "oracle" and he gave this permission so that "oracle" Unix user can read this file.

--Before
[oracle@srvdb01]:/oracle > ls -ld /etc/filesystems
-rw-rw----    1 root     system         3107 Jun 17 14:38 /etc/filesystems

--After
[oracle@srvdb01]:/oracle > ls -ld /etc/filesystems
-rw-r--r--    1 root     system         3107 Jun 17 14:38 /etc/filesystems

And the Oracle export can not write to "/export" NFS filesystem problem is solved.

What I think Oracle Export Datapump Utility does is, it reads "/etc/filesystems" file when exporting to an NFS file system. This file contains an entry about the mounted "/export" NFS filesystem in it.

[oracle@srvdb01]:/oracle > more /etc/filesystems
...
/export:
dev             = "/export"
vfs             = nfs
nodename        = srvnfs01
mount           = true
options         = bg,hard,intr,rsize=32768,wsize=32768,proto=udp,sec=sys,rw
account         = false
...