Oracle Monitoring Index Usage

Index is a associated structure with table, index is extra overhead statement with table, so if index is not utilized or unnessaccary index is created then drop that indexes.

Oracle provide a means of monitoring indexes to determine whether they are being used.

Let see in practical view.

SQL> drop table test purge;
Table dropped.

SQL> create table test as select * from all_objects;
Table created.

SQL> create index testidx on test(object_id);
Index created.

SQL> --now enable monitoring on index

SQL> alter index TESTIDX MONITORING usage;
Index altered.

SQL> ---execute query where index is created.

SQL> select count(*) from test where object_id=77204;
COUNT(*)

----------

1

SQL>---disable index mornitoring

SQL> alter index TESTIDX NOmonitoring usage;
Index altered.

SQL> ---now check the V$OBJECT_USAGE for index utilization. if we found nothing then drop index is better option

SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORIN END_MONITORING

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

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

TESTIDX TEST NO YES 07/31/2008 19:51:20

07/31/2008 19:52:04

NOTE: Used column is indicate "index" is utilize or not.

Oracle Index with key + ORA-01408

SQL> create table test ( no number);
Table created.

SQL> alter table test add constraint pkno primary key (no);
Table altered.

NOTE: Primary key or Unique key create Unique index automatically, so there is not user intervention required.

So If we trying to create index which already primary key or unque key created the we get following error.

SQL> create index testidx on test(no);

create index testidx on test(no)

*

ERROR at line 1:

ORA-01408: such column list already indexed

There is no requirement to create explicit index but if we want to more control over index then we can create index over primary /unique constraint column.

SQL> alter table test drop constraint pkno;
Table altered.

SQL> alter table test add constraint pkno primary key(no) using index (create index idxtest on test(no));

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME

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

IDXTEST

See also: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#sthref2025

How to find Oracle Version?

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

There is many way to find out.
1. Through SQLPLUS.
C:\app\m.taj\product\11.1.0\db_1\BIN>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 12:23:54 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

2. Through Data dictionary view
SQL> select * from v$version;
BANNER
------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - ProductionCORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
3. opatch lsinventory
1. Set ORACLE_HOME variable
2. go to OPATCH directory
3. run opatch lsinventory
C:\>set oracle_home=c:\app\m.taj\product\11.1.0\db_1
C:\>cd %ORACLE_HOME%\opatch
C:\app\m.taj\product\11.1.0\db_1\OPatch>opatch lsinventory
Invoking OPatch 11.1.0.6.0
[output cut]
Installed Top-level Products (1):
Oracle Database 11g 11.1.0.6.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.

OPatch succeeded.
4. check SQLNET.LOG file
check SQLNET.LOG file in ORACLE_HOME\network\log
you will find below entries
VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit
Windows: Version 11.1.0.6.0 - Production

Oracle Flashback Troubleshooting

Oracle flashback troubleshooting

1.

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled.

Cause: Flashback log file are created only in FLASH RECOVERY AREA then must be DB_RECOVERY_FILE_DEST parameter for flashback recovery area

Solution:

SQL> alter system set db_recovery_file_dest='location';
SQL> alter database flashback on;

2.

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38714: Instance recovery required.

Cluase: Database is not cleaning shutdown, database shutdown must be NORMAL, IMMEDIATE or TRANSACTIONAL;

Solution:

SQL> alter database open; (becuase currently database is in mount stage)

SQL> SHUTDOWN NORMAL

SQL> STARTUP MOUNT

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

3.

SQL> startup

ORACLE instance started.
[output cut]

Database mounted.

ORA-38760: This database instance failed to turn on flashback database

Clause: Flashback file is not accessiable or missing or lost

NOTE: check alertlog file for more info

Solution:

SQL> alter database flashback off;

SQL> alter database flashback on;

SQL> alter database open;

Oracle Data Pump 11g

Data Pump utility enhanced in Oracle 11g.

Oracle 11g introduce some new features for Data Pump utility, I tested some and want to share with you all.

For EXPDP utility

1. compression parameter

If we have issue with export dumpfile size then in 11g it can be gone through compression parameter.

so we don't need to any other third party tool to compress exported dumpfile because compression parameter dumpfile automatically compress during export.

Syntax:

COMPRESSION=[all , metadata_only, data_only, NONE]

Default: metadata_only

I tested compression option in same schema, first time i export schema without compress and second time with compress parameter see the difference. both dumpfile exported with COMPRESSION=ALL parameter

29-07-2008 07:10 PM 1,169,989,632 A1withoutCMPR.DMP

29-07-2008 07:15 PM 317,968,384 A2withCMPR.DMP

2. encryption & encryption_password parameter

This is most useful parameter becuase through this we can provide better security to exported dumpfile from unauthorized access.

Syntax:

ENCRYPTION=[all, data_only, encrypted_columns_only, metadata_only, NONE]

Default: NONE

ENCRYPTION_PASSWORD=there is no default; Value must be user provided

3. remap_data parameter

This is another useful options for export utility.

remap_data parameter allow us to specify a remap function that takes as a source the original value of the designated column and returns a remapped value that will replace the original value in the dump file.

For example: Data move from production server to test server, in data there is one table named employee which contents all employee bank account number. for security we don't disclose this value to test server team, so through remap_function we can prevent this.

For this we need to create package which content one function we replace or change bankac value for the column.

Syntax:

REMAP_DATA=schema.table_name.column_name:schema.pkg.function

For example:

REMAP_DATA=hr.employee.bankac:hr.package.function

4. reuse_dumpfiles parameter

This is another useful parameter, suppose we daily export dumpfile with somename but suppose is samename file already exist then export terminated with the following message

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "c:\test.dmp"

ORA-27038: created file already exists

OSD-04010: option specified, file already exists

To prevent this error we can use reuse_dumpfiles=y which overwrite any existing file with samename.

Syntax:

REUSE_DUMPFILES=y

Default: N

For IMPDP utility

1. encryption_password parameter

If dumpfile exported with encryption_password parameter then for import we must use password for import otherwise import terminated with the following message.

ORA-39002: invalid operation

ORA-39174: Encryption password must be supplied.

NOTE: Password is case-sensitive.

2. partition_options parameter

This is another cool feature which i come across.

Syntax:

PARTITION_OPTIONS=[none, departition,merge]

Default: none / departition (as per condition)

If NONE is specified means no change during import, table created as they exist in source database.

If DEPARTITION specified means each partitions created as a separate table in source database.

If MERGE specified means all partitions created as a one simple table.

For example: when partition_options=DEPARTITION

1. Create One table with 4 partition

CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL )
PARTITION BY RANGE (sale_year)
( PARTITION sales_q1 VALUES LESS THAN (1999),
PARTITION sales_q2 VALUES LESS THAN (2000),
PARTITION sales_q3 VALUES LESS THAN (2001),
PARTITION sales_q4 VALUES LESS THAN (2002));

2. check the partitions

SQL> select partition_name from user_tab_partitions where table_name='SALES';
PARTITION_NAME

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

SALES_Q1

SALES_Q2

SALES_Q3

SALES_Q4

3. expdp the table

host expdp scott/tiger dumpfile=sales.dmp directory=data tables=SALES

4. drop the table

SQL> drop table sales purge;
Table dropped.

5. Import the table with PARTITION_OPTIONS=DEPARTITION

SQL> host impdp scott/tiger dumpfile=p.dmp directory=data partition_options=DEPARTITION

This time 4 table created for each partition.

SQL> select * from tab where tname like 'S%';
TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------SALES_SALES_Q1 TABLE

SALES_SALES_Q2 TABLE

SALES_SALES_Q3 TABLE

SALES_SALES_Q4 TABLE

check the partitions view

SQL> select partition_name from user_tab_partitions;

no rows selected

Again we import same exported with with PARTITION_OPTIONS=MERGE

SQL> host impdp scott/tiger dumpfile=sales.dmp directory=data partition_options=MERGE

This time only one table created for all partitions.

SQL> select * from tab where tname like 'S%';
TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------SALES TABLE

Check the partitions view

SQL> select partition_name from user_tab_partitions where table_name='SALES';

no rows selected

Oracle Block Change Tracking

RMAN block change tracking

With Oracle 10gr1 Enterprise Edition Oracle introduce Block change tracking feature which use to FAST / Speed up RMAN Incremental Backup.

What is BLOCK CHANGE TRACKING?

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Source: Oracle documentation

1. Enable Block Change tracking and set location

Before enabling we must to set/specify block change tracking location.There is two way to specify block change tracking location1. using DB_CREATE_FILE_DEST parameter which use OMF file name.

SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;

SQL> alter database enable block change tracking;

2. Manually specify location for the block change tracking

SQL>alter database enable block change tracking using file 'location';

2. Disable block change tracking

SQL> alter database disable block change tracking;

NOTE: BCT files deleted automatically by oracle when BCT is disabled.

3. Reset the location of Block change tracking file

There are two options for this

1. shutdown the database and set the new location

SQL> shutdown database

SQL> move BCT file to new location

SQL> startup mount

SQL> alter database rename file 'old_location' TO 'new_location';

SQL> alter database open;

or

2. disable the block change tracking / set the new location / enable BCT

SQL> alter database DISABLE block change tracking;

SQL> alter database enable block change tracking using FILE 'NEW_LOCATION';

After that RMAN use new location for the BCT.

4. Checking the information about block change tracking enable or disable

Check v$BLOCK_CHANGE_TRACKING view

5. BCT file is important for restore and recovery of database.

Answer: No, it is doesn't require for Database Recovery of database

6. What happen if BCT file is lost and corrupted.

Answer: That is very interesting case here, suppose oracle found if BCT file is corrupted or missing then oracle will automatically recreate new BCT file.

Let see some practial view...

1. Enable BCT

SQL> alter database enable block change tracking using file 'e:\bct.dbf';
Database altered.

2. Check the status and default size.

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES

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

ENABLED E:\BCT.DBF 11599872

NOTE: Filesize created is 11 mb by default.

3. Now check alertsid log file what entries we found.

Mon Jul 28 20:06:53 2008

alter database enable block change tracking using file 'e:\bct.dbf'

Block change tracking file is current.

Starting background process CTWR

Mon Jul 28 20:06:54 2008

CTWR started with pid=20, OS id=464

Block change tracking service is active.

Completed: alter database enable block change tracking using file 'e:\bct.dbf'

4. Disable BCT

SQL> alter database disable block change tracking;
Database altered.

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES

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

DISABLED

5. Check alertsid log file

Mon Jul 28 20:12:41 2008

alter database disable block change tracking

Mon Jul 28 20:12:41 2008

Block change tracking service stopping.

Stopping background process CTWR

Deleted file E:\BCT.DBF

Completed: alter database disable block change tracking

6. Suppose BCT file lost or missing then what oracle will do...

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host del c:\bct.dbf

SQL> startup

ORACLE instance started.
[output cut]

Database mounted.

Database opened.

Now check the alertlog file for more information about how oracle create new BCT file when missing or lost.

Mon Jul 28 20:16:54 2008

ALTER DATABASE OPEN

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

See Also: Incremental Backup Algorithm

Oracle DBMS_REPAIR

Oracle Database provides different methods for detecting and correcting data block corruption. another way to manage data block corruption is to use the dbms_repair package.

we can use dbms_repair to detect and repair corrupt blocks in tables and indexes.

There is some Limitation and Restrictions for dbms_repair package.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/repair002.htm#sthref2281

Let see the step how to use dbms_repair package to detect and repair corrupt block in the tables and indexes.

There is two tables which must be created before using dbms_repair package.
1. Repair_Table
2. Orphan_Key_Table

NOTE: Both table must be created in SYS schema.

There is two main procedure of dbms_repair package.

1. check_object procedure

which checks and reports block corruptions for a specified objects

2. Admin_table procedure

This procedure create two tables which populate by check_object and store information about block corruption.

Table Creation through ADMIN_TABLE procedure

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> BEGIN

2 DBMS_REPAIR.ADMIN_TABLES (

3 TABLE_NAME => 'REPAIR_TABLE',

4 TABLE_TYPE => dbms_repair.repair_table,

5 ACTION => dbms_repair.create_action,

6 TABLESPACE => 'USERS');

7 END;

8 /
PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPAIR.ADMIN_TABLES (

3 TABLE_NAME => 'ORPHAN_KEY_TABLE',

4 TABLE_TYPE => dbms_repair.orphan_table,

5 ACTION => dbms_repair.create_action,

6 TABLESPACE => 'USERS');

7 END;

8 /
PL/SQL procedure successfully completed.

1. Detect corruptions

SQL> set serveroutput on

SQL> declare num_corrupt int;

2 begin

3 num_corrupt :=0;

4 dbms_repair.check_object (

5 schema_name => 'SCOTT',

6 object_name => 'DEPT',

7 repair_table_name => 'REPAIR_TABLE',

8 corrupt_count => num_corrupt);

9 dbms_output.put_line ('number corrupt: ' to_char(num_corrupt));

10 end;

11 /

number corrupt: 0

PL/SQL procedure successfully completed.

After that query in repair_table table for check more information

Oracle RMAN TSPITR

Oracle RMAN TSPITR

Oracle RMAN TSPITR through we can recover one or more tablespace or other database objects without any trouble or affect rest of database objects.

We can use RMAN TSPITR in the following conditions

1. Recovering data lost after an erroneous TRUNCATE TABLE statement;
2. Recovering from logical corruption of a table;
3. Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database;
4. Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

Limitations of TSPITR

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit001.htm#ABC1041015
Choosing the Right Target Time for TSPITR

It is extremely important that we choose the right target time or SCN for TSPITR. becuase once we bring tablespace ONLINE after TSPITR, we can't use any backup from a time earlier than the moment we brought the tablespace online.

Basic RMAN TSPITR

There are three options available for TSPITR

1. Fully automated TSPITR

2. Customized TSPITR with an automatic auxiliary instance

3. TSPITR with our own auxiliary instance

NOTE: Oracle recommended to use FULLY AUTOMATED TSPITR.

In Fully automated TSPITR oracle done entire process, there are two requirement which we need to prepare.

1. Specify auxiliary destination

2. Manually configure rman channel for auxiliary instance

Let see how RMAN TSPITR help us to recover one or more objects in

database with few commands

Deni working as an Oracle DBA in ABC bank, there developement team by mistake run wrong script which update all tables with wrong entries in particular schema.

Here I am using "scott" schema or "USERS" tablespace

NOTE: Must You have valid database backup & DB run in Archivelog mode to perform TSPITR.

Wrong script truncate all tables in Scott Schema, Now deni have task to recover all tables.

17:53:33 SQL> truncate table SCOTT.SALGRADE;
Table truncated.

For example above salgrade table is truncated by mistake from development team.

17:53:44 SQL> host rman target /
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: TEST (DBID=1959343326)

RMAN> recover tablespace USERS until time "to_date('27-JUL-2008 17:53:23','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION='f:\auxiliary';

Starting recover at 27-JUL-08

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 devtype=DISK

Creating automatic instance
initialization parameters used for automatic instance:db_name=TEST

compatible=10.1.0.2.0

db_block_size=8192

db_unique_name=tspitr_TEST_rmzx

large_pool_size=1M

#No auxiliary parameter file used

db_create_file_dest=f:\auxiliary

control_files=f:\auxiliary/cntrl_tspitr_TEST_rmzx.f

starting up automatic instance TEST
Oracle instance started

Total System Global Area 117440512 bytes
Fixed Size 787728 bytes

Variable Size 65272560 bytes

Database Buffers 50331648 bytes

Redo Buffers 1048576 bytes

Automatic instance created
contents of Memory Script:{

# set the until clauseset until time "to_date('27-JUL-2008 17:53:23','DD-MON-YYYY HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log for tspitr to a resent until time

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';}executing Memory Script
executing command: SET until clause
Starting restore at 27-JUL-08

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=29 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring controlfile

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NCSNF_TAG20080727T175226_48RZFO1D_.BKP tag=TAG20080727T175226

channel ORA_AUX_DISK_1: restore complete

output filename=F:\AUXILIARY\CNTRL_TSPITR_TEST_RMZX.F

Finished restore at 27-JUL-08

sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:{

# generated tablespace point-in-time recovery script

# set the until clauseset until time "to_date('27-JUL-2008 17:53:23','DD-MON-YYYY HH24:MI:SS')";plsql <<<-- tspitr_2

declare sql

statement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539);begin sqlstatement := 'alter tablespace ' 'USERS' ' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement);exception when offline_not_needed then null;end; >>>;

# set an omf destination filename for restoreset newname for clone datafile 1 to new;

# set an omf destination filename for restoreset newname for clone datafile 2 to new;

# set a destination filename for restoreset newname for datafile 4 to "F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF";

# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 4;

switch clone datafile all;

#online the datafiles restored or flipped

sql clone "alter database datafile 1 online";

#online the datafiles restored or flipped

sql clone "alter database datafile 2 online";

#online the datafiles restored or flipped

sql clone "alter database datafile 4 online";

# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;alter clone database open resetlogs;

# PLUG HERE the creation of a temporary tablespace if export fails due to lack of temporary space.

# For example in Windows these two lines would do that:

#sql clone "create tablespace aux_tspitr_tmp

# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";

sql clone "alter tablespace temp

add tempfile ''f:/tmp/aux_tspitr_tmp.dbf'' size 5M";

}

executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 27-JUL-08using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_%U_.DBFrestoring datafile 00002 to F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_%U_.DBFrestoring datafile 00004 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBFchannel ORA_AUX_DISK_1: restored backup piece 1piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NNNDF_TAG20080727T175226_48RZDV59_.BKP tag=TAG20080727T175226channel ORA_AUX_DISK_1: restore completeFinished restore at 27-JUL-08
datafile 1 switched to datafile copyinput datafilecopy recid=3 stamp=661197349 filename=F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_48RZLD8P_.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=2 stamp=661197327 filename=F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_48RZLDB5_.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 27-JUL-08using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2008_07_27\O1_MF_1_5_48RZL9PD_.ARCarchive log filename=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2008_07_27\O1_MF_1_5_48RZL9PD_.ARC thread=1 sequence=5

media recovery complete

Finished recover at 27-JUL-08
database opened
sql statement: alter tablespace temp add tempfile ''f:/tmp/aux_tspitr_tmp.dbf''size 5M
contents of Memory Script:{

# export the tablespaces in the recovery set

host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0[/a]=oraclermzx)(ARGS=^'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=rmzx^'))(CONNECT_DATA=(SID=rmzx))) as sysdba\" point_in_time_recover=y tablespaces= USERS file=tspitr_a.dmp';

# shutdown clone before import

shutdown clone immediate

# import the tablespaces in the recovery set

host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=tspitr_a.dmp';

# online/offline the tablespace importedsql "alter tablespace USERS online";sql "alter tablespace USERS offline";

# enable autobackups in case user does open resetlogs from RMAN after TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';}executing Memory Script

Export: Release 10.1.0.2.0 - Production on Sun Jul 27 17:56:06 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character setNote: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...

For tablespace USERS ....

exporting cluster definitions.

exporting table definitions. .

exporting table DEPT. .

exporting table EMP. .

exporting table BONUS. .

exporting table SALGRADE.

exporting referential integrity constraints.

exporting triggers. end point-in-time recovery

Export terminated successfully without warnings.

host command complete
database closed

database dismounted

Oracle instance shut down

Import: Release 10.1.0.2.0 - Production on Sun Jul 27 17:56:21 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional pathAbout to import Tablespace Point-in-time Recovery objects...

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT. .

importing table "DEPT". .

importing table "EMP". .

importing table "BONUS". .

importing table "SALGRADE".

importing SYS's objects into SYS

Import terminated successfully without warnings.

host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instanceAutomatic instance removedauxiliary instance datafile F:\AUXILIARY\CNTRL_TSPITR_TEST_RMZX.F deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_48RZLD8P_.DBF deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_48RZLDB5_.DBF deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_1_48RZMKWQ_.LOG deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_2_48RZML59_.LOG deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_3_48RZMLDM_.LOG deletedFinished recover at 27-JUL-08

RMAN> backup tablespace USERS;

NOTE: Oracle Recommended to Take tablespace backup

Starting backup at 27-JUL-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBFchannel ORA_DISK_1: starting piece 1 at 27-JUL-08channel ORA_DISK_1: finished piece 1 at 27-JUL-08piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NNNDF_TAG20080727T175710_48RZOPXP_.BKP comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 27-JUL-08

RMAN> sql "alter tablespace USERS online";
sql statement: alter tablespace USERS online

NOTE: Now take tablespace ONLINE.

RMAN> exit
Recovery Manager complete.

17:57:45 SQL> select count(*) from scott.salgrade;
COUNT(*)

----------

5

User sysdba connect must be use Operating system authentication otherwise following error occurs.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/27/2008 18:11:19RMAN-04006: error from auxiliary database:

ORA-01031: insufficient privileges

If your getting below error

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_PITR", line 887
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

check solution: http://dbataj.blogspot.com/2008/07/rman-tspitr-ora-25153.html

RMAN TSPITR + ORA-25153

Today I come across one problem during testing RMAN TSPITR option.

Database Version: 10.1.0.2.0 (10gr1)

The problem during TSPITR operation RMAN session disconnected with the following errors

About to export Tablespace Point-in-time Recovery objects...

EXP-00008: ORACLE error 25153 encountered

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "SYS.DBMS_PITR", line 887

ORA-06512: at line 1

EXP-00000: Export terminated unsuccessfully

host command complete

Above error message we face till 10.1.0.4.0.

This problem fix in 10.1.0.5.0 or later version.

Above error occur becuase RMAN automatically not create tempfile. After searching on metalink I found below metalink note: Note:263483.1

Oracle Data Recovery Advisor

Oracle 11g come up with one new cool feature for Database Backup & Recovery. This is DRA (Data Recovery Advisor).

Which help us to recover the database without any trouble with few RMAN commands.

What is Data Recovery Advisor?

DRA is an oracle database tool that automatically diagnoses data failures, determines and presents appropriate repair options and executes repaires at user requrests.

The following RMAN commands are use to perform Data Recovery Advisor.

1. List Failure

2. Advise Failure

4. Repair Failure

5. Change Failure

1. List Failure

List failure command give us information regarding failures and the effect of these on database operations. Each failures uniquely identified by failure number.

2. Advise Failure

Advise failure give us advise for how to deal with failure against database means advise failure give us solution for particular failure.

3. Repair failure

Repair failure command give us "rman generated scrits" which restore and recover database from backup.

4. Change failure

Change failure is RMAN command which change the failure status or priority.

like there is two status : OPEN or CLOSED and Priority is HIGH or LOW.

If some failure is rman showing HIGH and we want to change it to LOW then using change failure command we can change it.

Let see some pratical

1. Suppose I lost my system datafile.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF'

2. Now I am not worry becuase I have database backup with 11g database.

3. I need to just connect with RMAN, after connect with RMAN I do the following task.

1. Using list failure command checking the failure description

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

After got the failure description we can get "advise" from oracle about failure through advise failure command.

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=152 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

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

1 Restore and recover datafile 1

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

Above is rman advise regarding particular failure if above suggested repair option is helpful and fix the current problem then ok otherwise need to call oracle support services.

now check oracle suggested repair options or scripts.

RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Above is suggested script from RMAN to restore and recover database for particular failure, if suppose we want to use above script then again run "repair failure" command without 'preview' keyword.

RMAN> repair failure ;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

using channel ORA_DISK_1
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 00001 to C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1

channel ORA_DISK_1: piece handle=C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1 tag=TAG20080726T124808

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

Finished restore at 26-JUL-08

Starting recover at 26-JUL-08

using channel ORA_DISK_1
starting media recovery

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

Finished recover at 26-JUL-08

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

Database is now recovered and open for normal operation. So it is very easy to restore and recover database using Data Recovery Advisor feature.

same like above procedure for the system datafile we can restore or recover "undo file" & other "application datafiles".

If we lost one of controlfile or all controlfiles then use the following procedure.

ORA-00205: error in identifying control file, check alert log for more info

I am getting above error during startup database then I connect to rman and check "list failure".

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

Then I check "advise" from rman regarding above error

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

732 CRITICAL OPEN 26-JUL-08 Control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=151 device type=DISK

RMAN-06495: must explicitly specify DBID with SET DBID command

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

no manual actions available
Automated Repair Options

========================

Option Repair Description

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

1 Use a multiplexed copy to restore control file C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

Then I check repair preview for the above failure.

RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

And finally I apply the above suggested script by RMAN.

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2234784495.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from 'C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL';

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL01.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL02.CTL

output file name=C:\APP\M.TAJ\ORADATA\TEST\CONTROL03.CTL

Finished restore at 26-JUL-08
sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

Do you want to open the database (enter YES or NO)? YES

database opened

Now if we lost all or one of redolog file then use the following procedure

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

During startup I am getting above error then after I connect to RMAN and perform the following task

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

After that I check advise from RMAN regarding above failure list.

RMAN> advise failure;
List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

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

781 CRITICAL OPEN 26-JUL-08 Redo log group 2 is unavailable

775 CRITICAL OPEN 26-JUL-08 Redo log group 1 is unavailable

784 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG is missing

778 HIGH OPEN 26-JUL-08 Redo log file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG was unintentionally renamed or moved, restore it

2. If file C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

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

1 Recover database

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_4228591735.hm

Then finally I apply the suggested RMAN advise means "repair failure".

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_4228591735.hm
contents of repair script:

# recover database until cancel and open resetlogs

sql 'alter database recover database until cancel';

alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
sql statement: alter database recover database until cancel
database opened

repair failure complete

So what you say, it is one of cool feature with 11g :)

now up to we see how to perform recovery against "controlfile" & "datafile" & "redolog" file suppose if we lost "tempfiles" then what Oracle suggest. let see

Oracle 11g

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host del C:\app\m.taj\oradata\test\TEMP*.dbf
SQL> --In above statement I deleted the TEMPORARY files from filesystem.

SQL> startup

ORACLE instance started.
Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 201327508 bytes

Database Buffers 327155712 bytes

Redo Buffers 5844992 bytes

Database mounted.

Database opened.

Database is open without any error but when i checked alertlog file then i found following entries.

Re-creating tempfile C:\APP\M.TAJ\ORADATA\TEST\TEMP01.DBF

Above line meaning is Oracle Automatically Created Missing Tempfile during startup but this is not happen with Oracle 10gr1 (tested), Don't know about 10gr2 (Didn't test)

When I deleted tempfile manually in 10gr1 then after database open without error during startup I found following entires in alertlog file

Oracle10gr1

Sat Jul 26 19:27:04 2008

Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_dbw0_3292.trc:

ORA-01186: file 202 failed verification tests

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\TEMPFILE01.DBF'

File 202 not verified due to error ORA-01157

Means in 10gr1 we need to manually RE-CREATE temporary tablespace if we lost tempfiles. but in 11g it is automatically done by ORACLE :)