Search My Oracle Blog

Custom Search

30 November, 2011

Constraints and Indexes

Here is an interesting discussion on Constraints and Indexes.

.
.
.

25 November, 2011

SSDs for Oracle

Gwen Shapira has investigated SSDs and Flash and published her findings as "De-Confusing SSD (for Oracle Databases)".   The post *and* the comments are worth a read.

.
.
.

22 November, 2011

ROWIDs from an Index

Earlier today there was a question : "can i use rowid as a column in index columns ?"

It's clear that you cannot index the ROWID :

SQL> connect hemant/hemant
Connected.
SQL> create table obj_list_tbl as select * from dba_objects;

Table created.

SQL> select rowid from obj_list_tbl where object_id between 1001 and 1005;

ROWID
------------------
AAAU1bAAEAAAEU+ABG
AAAU1bAAEAAAEU+ABH
AAAU1bAAEAAAEU+ABI
AAAU1bAAEAAAEU+ABJ
AAAU1bAAEAAAEU+ABK

SQL> create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid);
create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid)
                                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create index obj_list_tbl_oid_ndx on obj_list_tbl(object_id);

Index created.

SQL> 
Although we can retrieve the ROWID for each row in the table, we cannot index it as it is a pseudo-column.

However, can we use an Index to retrieve *all* the ROWIDs of a table, without having to do a FullTableScan ?
Yes  Since an Index is essentially a list of KeyValues and matching ROWIDs, we can read ROWIDs from an Index.
SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1005;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     5 |   125 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |     5 |   125 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1005)

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

17 rows selected.

SQL> 
SQL> exec dbms_stats.gather_table_stats('HEMANT','OBJ_LIST_TBL',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1011;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |    11 |   187 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |    11 |   187 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1011)

13 rows selected.

SQL> 
Thus, Oracle could do an Index Range Scan to retrieve ROWIDs. Why would we want ROWIDs ? Probably to identify the datafiles that contain the rows of interest :
SQL> l
  1  select file_name from dba_data_files
  2  where tablespace_name = 'USERS'
  3  and relative_fno in
  4  (select distinct row_file_relative_no
  5  from (select dbms_rowid.rowid_relative_fno(rowid) as row_file_relative_no
  6        from obj_list_tbl where object_id between 1001 and 1011)
  7* )
SQL> /

FILE_NAME
----------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf

SQL>
Can we retrieve all the ROWIDs of the table from the Index ?
SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4249882908

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 76967 |   901K|   307   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| OBJ_LIST_TBL | 76967 |   901K|   307   (1)| 00:00:04 |
----------------------------------------------------------------------------------

8 rows selected.

SQL> 
Why did Oracle choose to do a FullTableScan ? In the absence of a WHERE clause, the requirement is for all the rows of the table. However, an Index can be used only if it is known to have captured all the rows of the table. If the Index is on a column that is NULLABLE (i.e. is not a NOT NULL), the Optimizer cannot be sure that it captures all the rows as NULLs are not indexed (in a regular B-Tree index). I need to confirm that all the rows are indexed.
SQL> alter table obj_list_tbl modify (object_id not null);

Table altered.

SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2780527016

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      | 76967 |   901K|    48   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| OBJ_LIST_TBL_OID_NDX | 76967 |   901K|    48   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

8 rows selected.

SQL> 
Now that the column has been validated to be a NOT NULL, the Optimizer can be certain that every row has been indexed by an Index on this column. So, it can now use the Index.

.
.
.

09 November, 2011

Grid and RAC Notes

This is a place-holder for miscellaneous notes on 11gR2 Grid and RAC.

09-Nov-11 : Location of Voting disk

If the voting disk exists on ASM you cannot add CFS votedisks. You must replace your ASM voting disk with the CFS votedisks. Apparently, both location types cannot be used concurrently ?
[root@node1 crs]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   5e25b31afc8e4fcabf3af0462e71ada9 (/crs/ocr.configuration) [DATA]
Located 1 voting disk(s).
[root@node1 crs]#
[root@node1 crs]# crsctl add css votedisk /data1/votedisk.1 /data2/votedisk.2
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM
[root@node1 crs]# crsctl replace votedisk /data1/votedisk.1 /data2/votedisk.2
Now formatting voting disk: /data1/votedisk.1.
Now formatting voting disk: /data2/votedisk.2.
CRS-4256: Updating the profile
Successful addition of voting disk 0ba633a673684fc0bf95cfbf188c399b.
Successful addition of voting disk 5784bae373ba4fcfbfb5c89b7136a7ea.
Successful deletion of voting disk 5e25b31afc8e4fcabf3af0462e71ada9.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
[root@node1 crs]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   0ba633a673684fc0bf95cfbf188c399b (/data1/votedisk.1) []
 2. ONLINE   5784bae373ba4fcfbfb5c89b7136a7ea (/data2/votedisk.2) []
Located 2 voting disk(s).
[root@node1 crs]# ls -l /data*/*otedi*
-rw-r----- 1 grid oinstall 21004800 Nov  9 22:52 /data1/votedisk.1
-rw-r----- 1 grid oinstall 21004800 Nov  9 22:52 /data2/votedisk.2
[root@node1 crs]#

So, I have now "moved" the voting disk from ASM (+DATA) to CFS (two separate files on two separate mount-points). (Note : /crs/ocr.configuration is actually an ASM disk).


09-Nov-11 :  OCR Backups are going to node1 only ?

I have a two-node RAC.  "Automatic"  *and* Manual OCR Backups are being created on node1.  I expect the backups to be spread out to different nodes.
[root@node2 ~]# uname -a
Linux node2.mydomain.com 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
[root@node2 ~]# ocrconfig -showbackup

node1     2011/10/22 03:09:03     /u01/app/grid/11.2.0/cdata/rac/backup00.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/backup01.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/day.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/week.ocr

node1     2011/11/09 23:09:16     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230916.ocr

node1     2011/11/09 22:47:25     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_224725.ocr

node1     2011/11/09 22:29:41     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_222941.ocr
[root@node2 ~]# ocrconfig -manualbackup

node1     2011/11/09 23:09:40     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230940.ocr

node1     2011/11/09 23:09:16     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230916.ocr

node1     2011/11/09 22:47:25     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_224725.ocr

node1     2011/11/09 22:29:41     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_222941.ocr
[root@node2 ~]# 
Commands fired from node2 show that all backups, even manual backups are on node1.


09-Nov-11 :  Shutdown of services from a node

crsctl can be used to shutdown all services.  Here I shutdown the local node :
[root@node2 log]# crsctl stop  cluster
CRS-2673: Attempting to stop 'ora.crsd' on 'node2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA1.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'node2'
CRS-2677: Stop of 'ora.scan1.vip' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'node1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.node2.vip' on 'node2'
CRS-2677: Stop of 'ora.node2.vip' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.node2.vip' on 'node1'
CRS-2677: Stop of 'ora.registry.acfs' on 'node2' succeeded
CRS-2676: Start of 'ora.node2.vip' on 'node1' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'node1'
CRS-2677: Stop of 'ora.FRA.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.DATA1.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'node2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'node1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'node2'
CRS-2677: Stop of 'ora.ons' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'node2'
CRS-2677: Stop of 'ora.net1.network' on 'node2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'node2' has completed
CRS-2677: Stop of 'ora.crsd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node2'
CRS-2673: Attempting to stop 'ora.evmd' on 'node2'
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.evmd' on 'node2' succeeded
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node2'
CRS-2677: Stop of 'ora.cssd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'node2'
CRS-2677: Stop of 'ora.diskmon' on 'node2' succeeded
[root@node2 log]# 
[root@node2 log]# ps -fugrid
UID        PID  PPID  C STIME TTY          TIME CMD
grid      3169     1  0 22:24 ?        00:00:32 /u01/app/grid/11.2.0/bin/oraagent.bin
grid      3183     1  0 22:24 ?        00:00:00 /u01/app/grid/11.2.0/bin/mdnsd.bin
grid      3194     1  0 22:24 ?        00:00:06 /u01/app/grid/11.2.0/bin/gpnpd.bin
grid      3207     1  2 22:24 ?        00:01:46 /u01/app/grid/11.2.0/bin/gipcd.bin
[root@node2 log]# 
Only the basic services are running now.

.
26-Nov-11 :  IP Addresses
On node 1 :


SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 RACDB_1

SQL> show parameter local_listener

NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
local_listener
string
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.149)(PORT=1
521))))
SQL> 
One node 2 :

SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              2 RACDB_2

SQL> show parameter local_listener

NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
local_listener
string
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.144)(PORT=1
521))))
SQL> 
The /var/messages/log file on node1 has :
Nov 26 22:12:22 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.127 on eth0.
Nov 26 22:12:27 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.148 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.146 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.147 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.144 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.149 on eth0.
Nov 26 22:18:46 node1 avahi-daemon[2572]: Withdrawing address record for 192.168.56.144 on eth0.
Nov 26 22:18:48 node1 avahi-daemon[2572]: Withdrawing address record for 192.168.56.148 on eth0.
while node2 has :
Nov 26 22:18:48 node2 avahi-daemon[2573]: Registering new address record for 192.168.56.144 on eth0.
Nov 26 22:18:50 node2 avahi-daemon[2573]: Registering new address record for 192.168.56.148 on eth0.
These are the assigned IP addressees. 1448 and 148 switched from node1 to node2 when node2 came up.
.
.
.

05 November, 2011

Tablespace Recovery in a NOARCHIVELOG database

Following up on my previous blog, I show a Tablespace Restore and Recovery for a NOARCHIVELOG mode database.
This is possible only if all the redo is still available in the online redo logs. !
Do not attempt this if the online redo logs have suffered enough switches that the log that was current during backup and the subsequent transaction(s) has been overwritten.

First, I create a new tablespace :

SQL> connect / as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL>
SQL> create tablespace TBS_RECOVERY ;

Tablespace created.

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

FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf

SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 213911176 bytes
Database Buffers 314572800 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace

Then I run an RMAN backup :
RMAN> backup tablespace TBS_RECOVERY;

Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00018 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-NOV-11

Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766409773_7c9czltz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11

RMAN>


Now, I proceed to put some data into the tablespace :
SQL> alter database open;

Database altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5482811

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41

SQL>
SQL> -- run regular insert
SQL> create table INSERT_TBL tablespace TBS_RECOVERY
2 as select * from dba_objects where 1=2;

Table created.

SQL> insert into INSERT_TBL select * from dba_objects;

76684 rows created.

SQL> -- run CTAS
SQL> create table CTAS_TBL tablespace TBS_RECOVERY as select * from dba_objects;

Table created.

SQL>
SQL> select segment_name, segment_type, bytes from user_segments
2 where tablespace_name = 'TBS_RECOVERY';

SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
INSERT_TBL TABLE 9437184
CTAS_TBL TABLE 9437184

SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2 from v$datafile, dba_data_files
3 where file#=file_id
4 and tablespace_name = 'TBS_RECOVERY'
5 /

UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
0

SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5482969

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41

SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace TBS_RECOVERY offline;

Tablespace altered.

SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile

I remove the datafile :
[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
-rw-rw---- 1 oracle oracle 104865792 Nov 5 11:41 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf'? yes
[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf: No such file or directory
[root@localhost ~]#

I then RESTORE and RECOVER the tablespace :
RMAN> restore tablespace TBS_RECOVERY;

Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00018 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 05-NOV-11

RMAN> recover tablespace TBS_RECOVERY;

Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 05-NOV-11

RMAN> sql 'alter tablespace TBS_RECOVERY online';

sql statement: alter tablespace TBS_RECOVERY online

RMAN>


I now verify the data in the two tables :
SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5483260

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:47

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_TBL;
select count(*) from CTAS_TBL
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 1283)
ORA-01110: data file 18: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>
SQL> select count(*) from INSERT_TBL;

COUNT(*)
----------
76684

SQL>


The table that was created as a CTAS operation is not readable as the rows were inserted as a NOLOGGING operation. (See my previous blog post : "CTAS in a NOARCHIVELOG database is a NOLOGGING operation").
However, the table that has data inserted by regular DML, using a normal INSERT has been recovered !
The RECOVER was able to read from the Online Redo Logs.

This case also shows how I did not have to do a Full Database Backup or a Full Database Restore+Recover. The single Tablespace was recovered to a consistent point in time with the rest of the database as evidenced below :

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5483376

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:51

SQL> alter system checkpoint;

System altered.

SQL> select file#, last_change#, checkpoint_change#, to_char(checkpoint_time,'DD-MON HH24:MI')
2 from v$datafile order by 1;

FILE# LAST_CHANGE# CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_TI
---------- ------------ ------------------ ---------------------
1 5483383 05-NOV 11:51
2 5483383 05-NOV 11:51
3 5483383 05-NOV 11:51
4 5483383 05-NOV 11:51
5 5483383 05-NOV 11:51
6 5483383 05-NOV 11:51
7 5483383 05-NOV 11:51
8 5483383 05-NOV 11:51
9 5483383 05-NOV 11:51
10 5483383 05-NOV 11:51
11 5483383 05-NOV 11:51
12 5483383 05-NOV 11:51
13 5483383 05-NOV 11:51
14 5483383 05-NOV 11:51
15 5483383 05-NOV 11:51
16 5483383 05-NOV 11:51
17 5483383 05-NOV 11:51
18 5483383 05-NOV 11:51

18 rows selected.

SQL>


These are the Log Sequence Numbers (selected lines from the alert.log):
Sat Nov 05 11:22:02 2011
ALTER DATABASE OPEN
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
...
Sat Nov 05 11:22:14 2011
QMNC started with pid=20, OS id=2378
Completed: ALTER DATABASE OPEN
...
Sat Nov 05 11:35:45 2011
create tablespace TBS_RECOVERY
Sat Nov 05 11:35:55 2011
Completed: create tablespace TBS_RECOVERY
...
Sat Nov 05 11:37:12 2011
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1294202344
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
...
Sat Nov 05 11:41:13 2011
alter database open
Sat Nov 05 11:41:13 2011
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
...
Sat Nov 05 11:41:59 2011
alter tablespace TBS_RECOVERY offline
Completed: alter tablespace TBS_RECOVERY offline
...
Sat Nov 05 11:45:46 2011
Full restore complete of datafile 18 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf. Elapsed time: 0:00:05
checkpoint is 5482691
Sat Nov 05 11:46:04 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace TBS_RECOVERY
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace TBS_RECOVERY
Sat Nov 05 11:46:23 2011
alter tablespace TBS_RECOVERY online
Completed: alter tablespace TBS_RECOVERY online


Therefore it is possible to recover a Tablespace in a NOARCHIVELOG database in a very limited frame -- enough redo being available in the Online Redo Logs.
.
.
.

CTAS in a NOARCHIVELOG database is a NOLOGGING operation

Normally, a CTAS (CREATE TABLE ... AS SELECT ....) operation generates redo and logging. However, if the database is in NOARCHIVELOG mode, Oracle silently converts it to a NOLOGGING operation.

This also means that a CTAS in a Test database (that is NOARCHIVELOG mode) will run faster than a CTAS in a Production database (that is ARCHIVELOG mode) , allowing for differences in server and I/O performance.

Here is a demonstration :
I first create a Tablespace and run a Backup, with the database in NOARCHIVELOG mode :

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL>
SQL> create tablespace CTAS_NOARCH ;

Tablespace created.

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

FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf

SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 213911176 bytes
Database Buffers 314572800 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace

The RMAN backup is executed :


RMAN> backup tablespace CTAS_NOARCH;

Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-NOV-11

Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766369695_7c84s6ks_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11

RMAN>


I then run a CTAS in the Tablespace :


SQL> alter database open;

Database altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5479486

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 00:31

SQL>
SQL> -- run CTAS
SQL> create table CTAS_NOARCH_TBL tablespace CTAS_NOARCH as select * from dba_objects;

Table created.

SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2 from v$datafile, dba_data_files
3 where file#=file_id
4 and tablespace_name = 'CTAS_NOARCH'
5 /

UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
0

SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace CTAS_NOARCH offline;

Tablespace altered.

SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile

SQL>


I remove the datafile and RESTORE and RECOVER the tablespace :
[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf'? yes
[root@localhost ~]#


RMAN> restore tablespace CTAS_NOARCH;

Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00017 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 05-NOV-11

RMAN> recover tablespace CTAS_NOARCH;

Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 05-NOV-11

RMAN> sql 'alter tablespace CTAS_NOARCH online';

sql statement: alter tablespace CTAS_NOARCH online

RMAN>


These are the alert.log messages :
Sat Nov 05 00:31:45 2011
alter tablespace CTAS_NOARCH offline
Completed: alter tablespace CTAS_NOARCH offline
Sat Nov 05 00:34:40 2011
Full restore complete of datafile 17 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf. Elapsed time: 0:00:08
checkpoint is 5479138
Sat Nov 05 00:34:41 2011
Checker run found 3 new persistent data failures
Sat Nov 05 00:34:59 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace CTAS_NOARCH
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace CTAS_NOARCH
Sat Nov 05 00:35:13 2011
alter tablespace CTAS_NOARCH online


I then attempt to access the table :

SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_NOARCH_TBL;
select count(*) from CTAS_NOARCH_TBL
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 17, block # 131)
ORA-01110: data file 17: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>
SQL>
SQL>
SQL> spool off
SQL> !oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//* redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.

SQL>


So, Oracle now finds that the blocks for the table have been loaded via a NOLOGGING operation. I did not create the tablespace explicitly as NOLOGGING, I did not create the table as NOLOGGING. Yet, the CTAS was (silently) executed as a NOLOGGING operation.

.
.
.

02 November, 2011

An ALTER USER to change password updates the timestamp of the password file

Apparently, upto 11.2.0.1, the password file is opened and verified and then closed when an ALTER USER ... IDENTIFIED BY ... is issued for any database account.

Here's a demo :
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl
[oracle@localhost dbs]$ date
Wed Nov 2 23:12:27 SGT 2011
[oracle@localhost dbs]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 2 23:12:47 2011

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


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

SQL> create user TEST_A identified by TEST_A;

User created.

SQL> !date
Wed Nov 2 23:13:04 SGT 2011

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl

SQL> alter user TEST_A identified by TEST_A_PWD;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov 2 23:13 orapworcl

SQL>

Thus, a CREATE USER did not change the timestamp of the password file. But an ALTER USER ... IDENTIFIED BY did change the timestamp. However, the file doesn't really get updated because this user (TEST_A) is not a SYSDBA or SYSOPER user.
SQL> !date
Wed Nov 2 23:16:59 SGT 2011

SQL> !cp -p orapworcl orapworcl.keep

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov 2 23:13 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov 2 23:13 orapworcl.keep

SQL> alter user TEST_A identified by TEST_A_NEW;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov 2 23:17 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov 2 23:13 orapworcl.keep

SQL> !diff orapworcl orapworcl.keep

SQL>

Thus, although the timestamp of the password file did get updated, there was no real change to the file.

See Oracle Support article "Timestamp on ORAPWD File Updated When Users' Password Changed [ID 312093.1]" for a description.

.
.
.

01 November, 2011

Handling Exceptions in PLSQL

Tom Kyte has repeatedly, repeatedly, repeatedly found fault with the usage of WHEN OTHERS -- particularly without a RAISE or RAISE_APPLICATION_ERROR.

See his article Why You Really Want to Let Exceptions Propagate (you need to scroll down the page) in the latest issue of Oracle Magazine.

.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com