Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, October 12, 2012

EXADATA X2-2 Creating DBFS fliesystem/share on EXADATA X2-2

 Environment:
EXADATA X2-2
OS: Linux
Image version: 11.2.3.2.0.120713
DB - 11.2.0.3

For Linux database servers, there are several steps to perform as root. Solaris database servers do not require this step and can

skip it. First, add the oracle user to the fuse group on Linux.  Run these commands as the root user.

(root)# dcli -g ~/dbs_group -l root usermod -a -G fuse oracle

Create the /etc/fuse.conf file with the user_allow_other option. Ensure proper privileges are applied to this file.

(root)# dcli -g ~/dbs_group -l root "echo user_allow_other > /etc/fuse.conf"
(root)# dcli -g ~/dbs_group -l root chmod 644 /etc/fuse.conf


For all database servers, create an empty directory that will be used as the mount point for the DBFS filesystem.

(root)# dcli -g ~/dbs_group -l root mkdir /dbfs_direct


[root@exadb01 onecommand]# pwd
/opt/oracle.SupportTools/onecommand

[root@exadb01 onecommand]# dcli -g dbs_group -l root mkdir /dbfs_share
[root@exadb01 onecommand]# dcli -g dbs_group -l root chown oracle:dba /dbfs_share
[root@exadb01 onecommand]#


exadb01.(MYDB1)  /home/oracle
>dba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 12 23:41:06 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create bigfile tablespace dbfsts datafile '+DBFS_DG' size 32g autoextend on next 8g maxsize 300g NOLOGGING EXTENT MANAGEMENT

LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created.


QL>
SQL>  create user dbfs_user identified by default tablespace dbfsts quota unlimited on dbfsts;
SQL> grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;


SQL> conn dbfs_user/
Connected.
SQL> start dbfs_create_filesystem dbfsts FS1
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_FS1', tbl_name =>
'T_FS1', tbl_tbs => 'dbfsts', lob_tbs => 'dbfsts', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_FS1', provider_name =>
'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_FS1', store_mount=>'FS1');
end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/FS1', 16895); end;
No errors.


copy the script:

rw-r--r-- 1 oracle   oinstall 11592 Oct 13 00:09 mount-dbfs.sh

[root@exadb01 tmp]# dos2unix mount-dbfs.sh mount-dbfs.sh
dos2unix: converting file mount-dbfs.sh to UNIX format ...
dos2unix: converting file mount-dbfs.sh to UNIX format ...
[root@exadb01 tmp]#


Edit the variable settings in the top of the script for your environment. Edit or confirm the settings for the following

variables in the script. Comments in the script will help you to confirm the values for these variables.

    DBNAME
    MOUNT_POINT
    DBFS_USER
    ORACLE_HOME (should be the RDBMS ORACLE_HOME directory)
    LOGGER_FACILITY (used by syslog to log the messages/output from this script)
    MOUNT_OPTIONS
    DBFS_PASSWD (used only if WALLET=false)
    DBFS_PWDFILE_BASE (used only if WALET=false)
    WALLET (must be true or false)
    TNS_ADMIN (used only if WALLET=true)
    DBFS_LOCAL_TNSALIAS

After editing, copy the script (rename it if desired or needed) to the proper directory (GI_HOME/crs/script) on database nodes

and set proper permissions on it, as the root user:


[root@exadb01 onecommand]# dcli -g dbs_group -l root -d /u01/app/11.2.0.3/grid/crs/script -f /tmp/mount-dbfs.sh
[root@exadb01 onecommand]# dcli -g dbs_group -l root chown oracle:dba /u01/app/11.2.0.3/grid/crs/script/mount-dbfs.sh
[root@exadb01 onecommand]#  dcli -g dbs_group -l root chmod 750 /u01/app/11.2.0.3/grid/crs/script/mount-dbfs.sh



With the appropriate preparation steps for one of the two mount methods complete, the Clusterware resource for DBFS mounting can

now be registered. Register the Clusterware resource by executing the following as the RDBMS owner of the DBFS repository

database (typically "oracle") user. The ORACLE_HOME and DBNAME should reference your Grid Infrastructure ORACLE_HOME directory

and your DBFS repository database name, respectively. If mounting multiple filesystems, you may also need to modify the

ACTION_SCRIPT and RESNAME. For more information, see section below regarding Creating and Mounting Multiple DBFS Filesystems.

Create this short script and run it as the RDBMS owner (typically "oracle") on only one database server in your cluster.

##### start script add-dbfs-resource.sh
#!/bin/bash
ACTION_SCRIPT=/u01/app/11.2.0/grid/crs/script/mount-dbfs.sh
RESNAME=dbfs_mount
haanDBNAME=fsdb
DBNAMEL=`echo $DBNAME | tr A-Z a-z`
ORACLE_HOME=/u01/app/11.2.0/grid
PATH=$ORACLE_HOME/bin:$PATH
export PATH ORACLE_HOME
crsctl add resource $RESNAME \
  -type local_resource \
  -attr "ACTION_SCRIPT=$ACTION_SCRIPT, \
         CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \
         START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\
         STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\
         SCRIPT_TIMEOUT=300"
##### end script add-dbfs-resource.sh

Change it to:
##### start script add-dbfs-resource.sh
#!/bin/bash
ACTION_SCRIPT=/u01/app/11.2.0.3/grid/crs/script/mount-dbfs.sh
RESNAME=dbfs_share
DBNAME=MYDB
DBNAMEL=`echo $DBNAME | tr A-Z a-z`
ORACLE_HOME=/u01/app/11.2.0.3/grid
PATH=$ORACLE_HOME/bin:$PATH
export PATH ORACLE_HOME
crsctl add resource $RESNAME \
  -type local_resource \
  -attr "ACTION_SCRIPT=$ACTION_SCRIPT, \
         CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \
         START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\
         STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\
         SCRIPT_TIMEOUT=300"
##### end script add-dbfs-resource.sh
~






>vi add-dbfs-resource.sh
exadb01.(MYDB1)  /home/oracle/sshaik
>sh ./add-dbfs-resource.sh



exadb01.(MYDB1)  /home/oracle/sshaik
>srvctl stop database -d MYDB -f
exadb01.(MYDB1)  /home/oracle/sshaik
>srvctl start database -d MYDB


>crsctl stat res dbfs_share -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_share
               OFFLINE OFFLINE      exadb01
               OFFLINE OFFLINE      exadb02
               OFFLINE OFFLINE      exadb03
               OFFLINE OFFLINE      exadb04

exadb01.(MYDB1)  /home/oracle/sshaik
> /u01/app/11.2.0.3/grid/bin/crsctl start resource dbfs_share
CRS-2672: Attempting to start 'dbfs_share' on 'exadb01'
CRS-2672: Attempting to start 'dbfs_share' on 'exadb02'
CRS-2672: Attempting to start 'dbfs_share' on 'exadb04'
CRS-2672: Attempting to start 'dbfs_share' on 'exadb03'
CRS-2676: Start of 'dbfs_share' on 'exadb01' succeeded
CRS-2676: Start of 'dbfs_share' on 'exadb04' succeeded
CRS-2676: Start of 'dbfs_share' on 'exadb03' succeeded
CRS-2676: Start of 'dbfs_share' on 'exadb02' succeeded


exadb01.(MYDB1)  /home/oracle/sshaik
> /u01/app/11.2.0.3/grid/bin/crsctl stat res dbfs_share -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_share
               ONLINE  ONLINE       exadb01
               ONLINE  ONLINE       exadb02
               ONLINE  ONLINE       exadb03
               ONLINE  ONLINE       exadb04




    To unmount DBFS on all nodes, run this as the oracle user:
    (oracle)$ /bin/crsctl stop res dbfs_mount

    Note the following regarding restarting the database now that the dependencies have been added between the dbfs_mount

resource and the DBFS repository database resource.

    Note: After creating the dbfs_mount resource, in order to stop the DBFS repository database when the dbfs_mount resource is

ONLINE, you will have to specify the force flag when using srvctl. For example: "srvctl stop database -d fsdb -f". If you do not

specify the -f flag, you will receive an error like this:

    (oracle)$ srvctl stop database -d fsdb
    PRCD-1124 : Failed to stop database fsdb and its services
    PRCR-1065 : Failed to stop resource (((((NAME STARTS_WITH ora.fsdb.) && (NAME ENDS_WITH .svc)) && (TYPE == ora.service.type))

&& ((STATE != OFFLINE) || (TARGET != OFFLINE))) || (((NAME == ora.fsdb.db) && (TYPE == ora.database.type)) && (STATE !=

OFFLINE)))
    CRS-2529: Unable to act on 'ora.fsdb.db' because that would require stopping or relocating 'dbfs_mount', but the force option

was not specified

    Using the -f flag allows a successful shutdown and results in no output.

    Also note that once the dbfs_mount resource is started and then the database it depends on is shut down as shown above (with

the -f flag), the database will remain down. However, if Clusterware is then stopped and started, because the dbfs_mount resource

still has a target state of ONLINE, it will cause the database to be started automatically when normally it would have remained

down. To remedy this, ensure that dbfs_mount is taken offline (crsctl stop resource dbfs_mount) at the same time the DBFS

database is shutdown.








offnote:-

Note that the "crsctl stop cluster -all" syntax may not be used as it leaves ohasd running and Solaris database hosts require it

to be restarted for the workaround to take effect.

ORA-15204: database version is incompatible with diskgroup while creating the database.

While creating the database via DBCA you may see the below error:




or get the below error:
"ORA-15204: database version 11.2.0.0.0 is incompatible with diskgroup DBFS_DG" while creating the database.

Fix:

Change the compatible parameter value in the dbca templates:

go to $ORACLE_HOME/assistants/dbca/templates

>vi New_Database.dbt  or  General_Purpose.dbc <= depends on which template you are using.

Change the below from
         initParam name="compatible" value="11.2.0.0.0"
Change the below from
         initParam name="compatible" value="11.2.0.3.0"
        


ORA-01586 ORA-39701 database must be mounted EXCLUSIVE and not open for this operation


Dropping the database :
 
Mount the database in restrict mode:
 SQL> startup mount restrict;
ORACLE instance started.


Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

What if you get the below error:


SQL> startup mount  restrict;
ORACLE instance started.


Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

Even the startup upgrade will fail:

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             704646848 bytes
Database Buffers         2348810240 bytes
Redo Buffers              151146496 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 46501
Session ID: 652 Serial number: 3


Then mostly likely you are destroying a RAC database and you need to change the cluster_database=FALSE.

SQL> alter system set cluster_database=FALSE scope=spfile;


System altered.

SQL> startup mount restrict;
ORACLE instance started.


Redo Buffers              151146496 bytes
Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

Thursday, October 11, 2012

RAC gv$ view dynamic views


Gv$views... That are related in day to day troubleshooting


GV$ACCESS
GV$ACTIVE_INSTANCES
GV$ACTIVE_SERVICES
GV$ACTIVE_SESSION_HISTORY
GV$ALERT_TYPES
GV$AQ
GV$ARCHIVE
GV$ARCHIVED_LOG
GV$ARCHIVE_DEST
GV$ARCHIVE_DEST_STATUS
GV$ARCHIVE_GAP
GV$ARCHIVE_PROCESSES
GV$ASH_INFO
GV$ASM_ALIAS
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_DISK_IOSTAT
GV$ASM_DISK_STAT
GV$ASM_FILE
GV$ASM_OPERATION
GV$BACKUP
GV$BGPROCESS
GV$BH
GV$BSP
GV$BUFFERED_PUBLISHERS
GV$BUFFERED_QUEUES
GV$BUFFERED_SUBSCRIBERS
GV$BUFFER_POOL
GV$BUFFER_POOL_STATISTICS
GV$CALLTAG
GV$CELL
GV$CELL_CONFIG
GV$CELL_REQUEST_TOTALS
GV$CELL_STATE
GV$CLUSTER_INTERCONNECTS
GV$CONFIGURED_INTERCONNECTS
GV$CONTEXT
GV$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION
GV$CR_BLOCK_SERVER
GV$CURRENT_BLOCK_SERVER
GV$DATABASE
GV$DATABASE_BLOCK_CORRUPTION
GV$DATABASE_INCARNATION
GV$DATAFILE
GV$DATAFILE_COPY
GV$DATAFILE_HEADER
GV$DATAGUARD_STATUS
GV$DATAPUMP_JOB
GV$DATAPUMP_SESSION
GV$DBLINK
GV$DB_CACHE_ADVICE
GV$DB_OBJECT_CACHE
GV$DB_PIPES
GV$DELETED_OBJECT
GV$DETACHED_SESSION
GV$DIAG_INFO
GV$DISPATCHER
GV$DISPATCHER_RATE
GV$DYNAMIC_REMASTER_STATS
GV$ENCRYPTED_TABLESPACES
GV$ENQUEUE_LOCK
GV$ENQUEUE_STAT
GV$ENQUEUE_STATISTICS
GV$EVENTMETRIC
GV$EVENT_NAME
GV$EXECUTION
GV$FILEMETRIC
GV$FILEMETRIC_HISTORY
GV$FILESPACE_USAGE
GV$FILESTAT
GV$FILE_CACHE_TRANSFER
GV$FILE_PING
GV$FIXED_TABLE
GV$FIXED_VIEW_DEFINITION
GV$FLASHBACK_DATABASE_LOG
GV$FLASHBACK_DATABASE_LOGFILE
GV$FLASHBACK_DATABASE_STAT
GV$GC_ELEMENT
GV$GC_ELEMENTS_WITH_COLLISIONS
GV$GES_BLOCKING_ENQUEUE
GV$GES_ENQUEUE
GV$GLOBALCONTEXT
GV$GLOBAL_BLOCKED_LOCKS
GV$GLOBAL_TRANSACTION
GV$HM_CHECK
GV$HM_FINDING
GV$HM_INFO
GV$HM_RECOMMENDATION
GV$INSTANCE
GV$INSTANCE_CACHE_TRANSFER
GV$INSTANCE_LOG_GROUP
GV$INSTANCE_RECOVERY
GV$IOFUNCMETRIC
GV$IOFUNCMETRIC_HISTORY
GV$IOSTAT_FILE
GV$IOSTAT_FUNCTION
GV$IOSTAT_FUNCTION_DETAIL
GV$IOSTAT_NETWORK
GV$IO_CALIBRATION_STATUS
GV$LATCH
GV$LATCHHOLDER
GV$LATCHNAME
GV$LATCH_CHILDREN
GV$LATCH_MISSES
GV$LATCH_PARENT
GV$LIBCACHE_LOCKS
GV$LIBRARYCACHE
GV$LIBRARY_CACHE_MEMORY
GV$LOCK
GV$LOCKED_OBJECT
GV$LOCKS_WITH_COLLISIONS
GV$LOCK_ACTIVITY
GV$LOCK_ELEMENT
GV$LOCK_TYPE
GV$LOG
GV$LOGFILE
GV$LOGHIST
GV$MANAGED_STANDBY
GV$MEMORY_CURRENT_RESIZE_OPS
GV$MEMORY_DYNAMIC_COMPONENTS
GV$MEMORY_RESIZE_OPS
GV$MEMORY_TARGET_ADVICE
GV$METRIC
GV$MUTEX_SLEEP
GV$MUTEX_SLEEP_HISTORY
GV$MVREFRESH
GV$MYSTAT
GV$OBSOLETE_PARAMETER
GV$OPEN_CURSOR
GV$OSSTAT
GV$PARALLEL_DEGREE_LIMIT_MTH
GV$PARAMETER
GV$PARAMETER2
GV$PARAMETER_VALID_VALUES
GV$PGASTAT
GV$PGA_TARGET_ADVICE_HISTOGRAM
GV$PGA_TARGET_ADVICE
GV$POLICY_HISTORY
GV$PQ_SESSTAT
GV$PQ_SLAVE
GV$PQ_SYSSTAT
GV$PQ_TQSTAT
GV$PROCESS
GV$PROCESS_MEMORY
GV$PROCESS_MEMORY_DETAIL
GV$PROCESS_MEMORY_DETAIL_PROG
GV$PWFILE_USERS
GV$PX_BUFFER_ADVICE
GV$PX_INSTANCE_GROUP
GV$PX_PROCESS
GV$PX_PROCESS_SYSSTAT
GV$PX_SESSION
GV$PX_SESSTAT
GV$QUEUE
GV$QUEUEING_MTH
GV$RECOVERY_FILE_STATUS
GV$RECOVERY_LOG
GV$RECOVERY_PROGRESS
GV$RECOVERY_STATUS
GV$RECOVER_FILE
GV$RESOURCE
GV$RESOURCE_LIMIT
GV$RESTORE_POINT
GV$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS
GV$ROWCACHE
GV$SCHEDULER_RUNNING_JOBS
GV$SEGMENT_STATISTICS
GV$SEGSTAT
GV$SEGSTAT_NAME
GV$SESSION
GV$SESSION_BLOCKERS
GV$SESSION_CURSOR_CACHE
GV$SESSION_EVENT
GV$SESSION_LONGOPS
GV$SESSION_WAIT
GV$SESSION_WAIT_CLASS
GV$SESSION_WAIT_HISTORY
GV$SESSTAT
GV$SESS_IO
GV$SGA
GV$SGAINFO
GV$SGASTAT
GV$SGA_DYNAMIC_COMPONENTS
GV$SGA_DYNAMIC_FREE_MEMORY
GV$SGA_TARGET_ADVICE
GV$SHARED_POOL_ADVICE
GV$SHARED_POOL_RESERVED
GV$SHARED_SERVER
GV$SORT_SEGMENT
GV$TEMPSEG_USAGE
GV$SORT_USAGE
GV$SPPARAMETER
GV$SQL
GV$SQLAREA
GV$SQLAREA_PLAN_HASH
GV$SQLCOMMAND
GV$SQLSTATS
GV$SQLSTATS_PLAN_HASH
GV$SQLTEXT
GV$SQL_BIND_CAPTURE
GV$SQL_BIND_DATA
GV$SQL_BIND_METADATA
GV$SQL_CURSOR
GV$SQL_FEATURE
GV$SQL_FEATURE_HIERARCHY
GV$SQL_HINT
GV$SQL_JOIN_FILTER
GV$SQL_MONITOR
GV$SQL_OPTIMIZER_ENV
GV$SQL_PLAN
GV$SQL_PLAN_MONITOR
GV$SQL_PLAN_STATISTICS
GV$SQL_PLAN_STATISTICS_ALL
GV$SQL_SHARED_CURSOR
GV$SQL_SHARED_MEMORY
GV$SQL_WORKAREA
GV$SQL_WORKAREA_ACTIVE
GV$SQL_WORKAREA_HISTOGRAM
GV$STATISTICS_LEVEL
GV$SYSAUX_OCCUPANTS
GV$SYSMETRIC
GV$SYSMETRIC_HISTORY
GV$SYSMETRIC_SUMMARY
GV$SYSSTAT
GV$SYSTEM_CURSOR_CACHE
GV$SYSTEM_EVENT
GV$SYSTEM_PARAMETER
GV$SYSTEM_PARAMETER2
GV$TABLESPACE
GV$TEMPFILE
GV$TEMPORARY_LOBS
GV$TEMPSTAT
GV$TRANSACTION
GV$TRANSACTION_ENQUEUE
GV$UNDOSTAT
GV$VERSION
GV$WAITSTAT
GV$WORKLOAD_REPLAY_THREAD

Tuesday, October 9, 2012

RMAN switch database to copy

RMAN image copy .. switching database to image copy backup.


>r sql
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 23 15:09:10 2010

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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
Database mounted.


labrman01(MYDB)  /ora_backup/backups/MYDB
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 23 15:10:13 2010

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

RMAN> connect target /

connected to target database: MYDB (DBID=2563884143, not open)

RMAN> connect catalog rcat10g/rcat10g@rcat;

connected to recovery catalog database

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp"
datafile 2 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm"
datafile 3 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr"
datafile 4 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib"
datafile 5 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij"
datafile 6 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm"
datafile 8 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm"
datafile 9 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib"
datafile 10 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq"
starting full resync of recovery catalog
full resync complete


RMAN>  run{
2> set until time "to_date('08/23/2010 15:00:00','mm/dd/yyyy hh24:mi:ss')";
3> recover database;
4> }

executing command: SET until clause

Starting recover at 23-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1092 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc
archive log thread 1 sequence 7 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000007_0727545786.arc
archive log thread 1 sequence 8 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000008_0727545786.arc
archive log filename=/adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-AUG-10


RMAN> run{
2> sql 'alter database open resetlogs';
3> }

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
761     774     MYDB     2563884143       PARENT  1          29-OCT-07
761     762     MYDB     2563884143       PARENT  10513166137703 21-JUL-10
761     4636    MYDB     2563884143       PARENT  10513166276407 20-AUG-10
761     5187    MYDB     2563884143       PARENT  10513166280927 20-AUG-10
761     6336    MYDB     2563884143       PARENT  10513166285247 20-AUG-10
761     6786    MYDB     2563884143       CURRENT 10513166407407 23-AUG-10
761     1681    MYDB     2563884143       ORPHAN  10513167403561 20-AUG-10

ALTER TABLE MOVE

You can use dbms_redef for reorg:-

Redefining Tables Online

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
  • Improve the performance of queries or DML
  • Accommodate application changes
  • Manage storage
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.
Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

 but for people looking for quick fix or short cuts here it is..

Re-org or move objects around dirty way:-

To move TABLES:

SELECT'ALTER TABLE '||OWNER||'.'||TABLE_NAME ||' MOVE TABLESPACE APPS_TS_TX_DATA;'
 FROM DBA_TABLES WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'
 and owner='JTF'

To move indexes:
SELECT'ALTER INDEX '||OWNER||'.'||INDEX_NAME ||' REBUILD TABLESPACE APPS_TS_TX_IDX;'
 FROM DBA_INDEXES WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'


To move LOB SEGMENT:
SELECT 'ALTER TABLE '||OWNER ||'.'|| TABLE_NAME || ' MOVE LOB('|| COLUMN_NAME ||') STORE AS (TABLESPACE APPS_TS_TX_DATA);'
 FROM DBA_LOBS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'


To move TABLE SUBPARTITION:
 select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION '||SUBPARTITION_NAME ||' TABLESPACE APPS_TS_TX_DATA;'
    FROM DBA_TAB_SUBPARTITIONS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'



To move LOB SUB PARTITION:

SCRIPT:-
SELECT 'ALTER TABLE '||TABLE_OWNER ||'."'|| TABLE_NAME || '" MOVE SUBPARTITION '|| SUBPARTITION_NAME ||'  TABLESPACE APPS_TS_TX_DATA LOB ('||
COLUMN_NAME||') STORE AS (TABLESPACE APPS_TS_TX_DATA);'
 FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='APPS_TS_TX_DATA_OLD'

EX:-
   ALTER TABLE FPA."AW$FPAPJP" MOVE SUBPARTITION SYS_IL_SUBP127 TABLESPACE APPS_TS_TX_DATA  LOB(AWLOB) STORE AS (TABLESPACE APPS_TS_TX_DATA);


If there are many objects to move:

Datapump:

expdp \"/ as sysdba\" directory=TABPSLACE_exp dumpfile=exp_tablespace.dmp logfile=mydb_exp.log tablespaces='APPS_TS_TX_DATA_OLD'

Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=tablespace_exp dumpfile=mydb_tablespace.dmp logfile=mydb_exp.log tablespaces=APPS_TS_TX_DATA_OLD
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 179.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS



Import it with REMAP_TABLESPACE or as desired...

Tuesday, October 2, 2012

ORA-01502: index or partition of such index is in unusable state

Exploring ORA-01502 error and why we usually get this error message. I am not explaining why/how the index status changed to unusable( mostly due to the table move and alter index xxxx unusable ..)

You will get

ORA-01502: index  or partition of such index is in unusable state

If you have the parameter skip_unusable_indexes= false  then it makes sense that oracle reported this error during DML activity.

If you don't care about the unusable indexes during DML and you want the optimizer to choose different(expensive) execution plans during SELECT then you can set the parameter skip_unusable_indexes= true at the instance level or at the session level and move on.

what if you got this error even when you have the paramater skip_unusable_indexes set to true at the instance level..

i.e
SQL> show parameter skip

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE

SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result. (See Oracle Database Administrator's Guide for more information about using hints.)

Values:   true

    Disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.




and you still got the error during DML activity on this table, why?

SQL> insert into TEST_TABLE (salesrep_dim_pk) values (55555);
insert into test_table(salesrep_dim_pk) values (55555)
*
ERROR at line 1:
ORA-01502: index 'TEST_INDEX_UNIQUE' or partition of such index is in unusable state

Example:
SQL> create table TEST_TABLE as select * from my_table;

Table created.

SQL> CREATE UNIQUE INDEX TEST_INDEX_UNIQUE ON TEST_TABLE (SALESREP_DIM_PK);

Index created.

SQL> commit;

Commit complete.

SQL> select sum(bytes) from dba_segments where segment_name='TEST_INDEX_UNIQUE';

SUM(BYTES)
----------
    196608

SQL> select * from TEST_TABLE where salesrep_dim_pk =95056;

Execution Plan
----------------------------------------------------------
Plan hash value: 684699485

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |  1470 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE        |     1 |  1470 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TEST_INDEX_UNIQUE |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("SALESREP_DIM_PK"=95056)

Above explain plan shows the optimizer is using the index.

Now insert some data into the data..some DML...

SQL> insert into test_table (salesrep_dim_pk) values (55555);

1 row created.

SQL> delete from test_table where salesrep_dim_pk=55555;

1 row deleted.

SQL> commit;

Commit complete.


****** Now mark the index UNUSABLE *****
SQL> alter index TEST_INDEX_UNIQUE unusable;

Index altered.

SQL> select sum(bytes) from dba_segments where segment_name='TEST_INDEX_UNIQUE';

SUM(BYTES)
----------
    196608
***** This is bad even though the index is unusable the segments were not dropped for the index ***
****The default behaviour is oracle drops the segments for an unusable index *****



SQL>  select * from test_table where salesrep_dim_pk =95056;

Execution Plan
----------------------------------------------------------
Plan hash value: 74755328

----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |   151 |   216K|    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TEST_TABLE |   151 |   216K|    10   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - storage("SALESREP_DIM_PK"=95056)
       filter("SALESREP_DIM_PK"=95056)

****As expected the access path changed from INDEX to FULL TABLE SCAN as the index is unusable******

***** try doing some DML ********

SQL> insert into TEST_TABLE (salesrep_dim_pk) values (55555);
insert into TEST_TABLE (salesrep_dim_pk) values (55555)
*
ERROR at line 1:
ORA-01502: index 'TEST_INDEX_UNIQUE' or partition of such index is in unusable state

***Since the index is a non unique index oracle will not allows us to do any dml activity on the underlying table
on which the index is unusable and will not drop the index segments either ****

  Note:
    If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.



SQL> alter index TEST_INDEX_UNIQUE rebuild;

Index altered.

SQL> select sum(bytes) from dba_segments where segment_name='TEST_INDEX_UNIQUE';

SUM(BYTES)
----------
    196608

SQL> insert into TEST_TABLE (salesrep_dim_pk) values (55555);

1 row created.

SQL> commit;

Commit complete.


where as non unique unusable index doesn't throw this error.

Create table TEST_TABLE as select * from my_table;

****Created non-unique index here ********
create index TEXT_IDX on TEST_TABLE(customer_num,customer_name);

commit;

SQL> select customer_num from TEST_TABLE where customer_num='8.1502701000322E15';

Execution Plan
----------------------------------------------------------
Plan hash value: 3224766131

-----------------------------------------------------------------------------
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |    12 |    96 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEXT_IDX |    12 |    96 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("CUSTOMER_NUM"=8150270100032200)

SQL> select wo_num from TEST_TABLE where  wo_num='1.00037856314112E15';

Execution Plan
----------------------------------------------------------
Plan hash value: 3979868219

----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     7 |    49 |   317K  (2)| 00:16:09 |
|*  1 |  TABLE ACCESS STORAGE FULL| TEST_TABLE |     7 |    49 |   317K  (2)| 00:16:09 |
----------------------------------------------------------------------------------------

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

   1 - storage("WO_NUM"=1000378563141120)
       filter("WO_NUM"=1000378563141120)

SQL> ALTER INDEX TEXT_IDX unusable;

Index altered.



SQL> select index_name,status from dba_indexes where index_name='TEXT_IDX';

INDEX_NAME                     STATUS
------------------------------ --------
TEXT_IDX                       UNUSABLE


SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEXT_IDX';

SUM(BYTES)/1024/1024/1024
-------------------------



SQL> exec dbms_stats.gather_table_stats('MYSCHEMA','TEST_TABLE',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

PL/SQL procedure successfully completed.


SQL> update TEST_TABLE set customer_num='1234567890' where customer_num='8.1502701000322E15';

6 rows updated.

SQL> alter index TExt_idx rebuild;


Index altered.

SQL> SQL>
SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEXT_IDX';

SUM(BYTES)/1024/1024/1024
-------------------------
               2.00976563