Changing the characterset – CSSCAN/CSALTER could be the answer!

Tags

characterset

A customer recently attempted to change the characterset of their Oracle RAC database on their own, after receiving some questionable advice, and ended up somehow deleting the database from their cluster. While working on recovering the database, it occurred to me that to most DBAs, working with charactersets and ‘NLS_’ parameters is pretty much a dry subject, which they would prefer to not do.
Prior versions of the Oracle database made it mandatory to re-create the database if there were any changes required to the default characterset. But we have (since at least Oracle 9i) the CSSCAN utility that will help us (in many situations) make this transition. If you’re like me, the first thing you would think of when someone talks of an utility/tool to administer the database is ‘That’s nice, but does it work with RAC?’
So for our test environment, we will be using a 10g Release 2 RAC database (with 2 nodes) running on Oracle Linux 5, with ASM for the storage. Let’s start by looking at the current settings for the characterset.
From the alert log:
Thu Jan 28 20:07:10 2010
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan

So now we know what is being used, lets change the characterset to WE8ISO8859P1. Before beginning, make sure:
i. There is a full backup of the database taken available
ii. There is a backup of the current spfile (especially for RAC databases)

Now we can start the CSSCAN utility, and start scanning the database. From the command line, run the following to start it as the SYS user, and specify a full database scan, with the intended characterset(WE8ISO8859P1). Additional options available can be seen by running ‘csscan –help’

If this is the first time you are running the csscan utility, you may see the following error:

CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.

In order to resolve this, you need to run the csminst.sql script (located in $ORACLE_HOME/rdbms/admin) as the SYS user, which will create the objects required by CSSCAN.

$ csscan \”sys/syspassword as sysdba\” full=y tochar= WE8ISO8859P1

You will then be able to specify a size for the array fetch buffer size (in bytes), as well as the degree of parallelism for the operation. Depending on the resources available on the server, higher values could be used for your setup.

Sample output:
Character Set Scanner v2.1 : Release 10.2.0.3.0 – Production on Thu Jan 28 11:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 4
Enumerating tables to scan…
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
. process 3 scanning SYS.METHOD$[AAAAC1AABAAAAURAAA]
——– some output truncated for brevity
Creating Database Scan Summary Report…
Creating Individual Exception Report…
Scanner terminated successfully.

Once the CSSCAN utility has completed, you can review the results in the current directory (scan.txt file), and more importantly any exceptions that might have occurred (scan.err file). Some of the useful information found in these reports include:
i. Any data truncation that might occur, on application data as well as the data dictionary
ii. Loss of data on both application tables and the data dictionary
iii. Indexes that need to be rebuilt after the characterset conversion
iv. Rows in tables that contain data that will be affected by the conversion

By reviewing these files, you will be able to better decide on whether or not to proceed with the method outlined here. If there are a significant number of objects that will be affected negatively, this approach is not advisable. Luckily, in my environment there was no significant data loss or truncation, so it’s safe to proceed.

The next part will involve using the CSALTER.PLB package (again, found in $ORACLE_HOME/rdbms/admin) to actually carry out the alteration of the characterset. The intended characterset will be the same as used when generating the CSSCAN report earlier. Also, it’s worth noting that CSALTER will need to be run within 7 days of running CSSCAN, or the results will not be valid, and CSSCAN will have to be re-run.
* For RAC databases only – The instance must not be started in clustered mode, and all other RAC instances must be shutdown when running the CSALTER package.

First, ensure the ‘job_queue_processes’ and ‘aq_tm_processes’ parameters are both set to 0, to avoid any scheduled jobs running during the characterset migration.

Next, startup the database in restricted mode:
SQL> shutdown immediate;
SQL> startup restrict

Now, we can run the CSALTER package itself:
SQL> ?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER(‘&conf’) ‘Y’) then
new 6: if (UPPER(‘Y’) ‘Y’) then
Checking data validility…
begin converting system objects
1 row in table SYS.SQL$TEXT is converted
2 rows in table SYS.WRI$_ADV_OBJECTS are converted
38 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
6395 rows in table SYS.WRH$_SQL_PLAN are converted
19 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
935 rows in table SYS.WRH$_SQLTEXT are converted
80 rows in table SYS.METASTYLESHEET are converted
86 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
1 row in table SYS.RULE$ is converted
PL/SQL procedure successfully completed.
Alter the database character set…
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

Once the procedure completes, you can now restart the database, and verify if the characterset changes have been applied. Additionally, you can revert the ‘job_queue_processes’ and ‘aq_tm_processes’ initialization parameters to their original values.
* For RAC database only, you will also need to change the CLUSTER_DATABASE parameter again to enable all the RAC instances to be started.

Depending on what changes you have made (default database characterset, or NLS characterset) you will be able to verify the current database characterset from either the initialization parameters or the alert log. In this case, from the alert log:

Thu Jan 28 12:21:04 2010
Database Characterset is WE8ISO8859P1

By Niradj Selvam,
Oracle 10g, 11g OCP, 10g RAC Expert

How to setup 11g active dataguard

Tags

active dataguard, active dataguard 11g, dataguard 11g, snaphost standby database

Overview

Main advantage of 11g dataguard is

• Ability to open the standby database in read-only mode while the database is still in managed recovery mode. This means 11g active dataguard database is bi-functional, provides disaster recovery and at the same time can be used for reporting purpose
• Able to open standby database in read-write. This is very useful to turn our standby database into application testing / development purposes. This is achieved through snapshot standby database

Steps

Setting up active dataguard is very easy and straight forward. Initial steps to create active dataguard are as same as setting up 10g dataguard. The steps are almost similar with only few different commands. In this scenario, I have named my production database as ‘prod’ and standby database as ‘stdby’ :

1. First of all, parameter file (with appropriate dataguard parameters) and standby control file must be created from the production database.

2.Setup listener.ora and tnsnames.ora for connectivity between production and standby.

3. Connect to rman

rman target sys/oracle@prod auxiliary sys/oracle@stdby

4. With 11g active dataguard, 1 simple command will create standby without even backing up prod database and placing it in a staging area.

RMAN>duplicate target database for standby from active database;

5. Standby database successfully created with the db is in mount state.

Active Dataguard

1. Let me use dataguard broker to enable active dataguard. Once setup the dataguard broker, you can see the database configuration as below :

2.  If you try to open database during this time, you will get error as below :

3.  In order to open the standby database, connect to dgmrl and change the apply state.

4.   Open the standby database now.

5.   Re-enable the apply state so that redo transferred and applied to standby database while the database is open for querying.

6.  To disable the active dataguard, the steps are the same :

I.        DGMGRL> edit database stdby set state=apply-off;

II.        Shutdown the stdby database

III.        Startup mount the stdby database

IV.        DGMGRL> edit database stdby set state=apply-on;

Snapshot standby database

1. In order to create snapshot standby database :

• standby database must be a physical standby database
• Flashback logging must be enabled on both production and standby database

2. After enabling flashback mode, connect to dgmrl utility in the primary database :

3. Convert the physical standby to snapshot standby with a single command :

4. From now on, we can do any testing (like creating new schemas, tables and so on) on our standby database. Please note that at this point of time, all the redo generated in our production database will be still shipped to standby database. But it is just that not going to be applied until the database is converted into physical standby mode.

5. Once the testing done, you can convert the snapshot standby database to physical standby with just 1 command :

DGMGRL>convert database stdby to physical standby;

6. Note that when the above command executed

• All the changed made to the snapshot standby database ( such as creating new schemas, tables) has been terminated
• Previous physical standby database state is initiated
• Physical standby database is mounted and MRP process is initiated. MRP will apply all the logs which was shipped yet applied during the snapshot standby database state.

7. Duration for this process is depends on few factors :

• The amount of changes made to the database during the snapshot standby database state. More changes will lead to more time to rewind the changes via flashback database option.
• The amount of archived logs generated during the snapshot standby database state. More archive logs will cause more time to apply it when the database is converted to physical standby database.

DDL TRIGGER

Tags

trigger

CREATE TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30));

create or replace trigger
sys.audit_ddl_trg after ddl on database
begin
if (ora_sysevent=’TRUNCATE’)
then
null; — I do not care about truncate
else
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
values(
sysdate,
sys_context(‘USERENV’,'OS_USER’) ,
sys_context(‘USERENV’,'CURRENT_USER’) ,
sys_context(‘USERENV’,'HOST’) ,
sys_context(‘USERENV’,'TERMINAL’) ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent
);
end if;
end;
/

By running the above script, a table and a trigger will be created. The trigger will practically updates the table on all the DDL takes place on the database by specifying which osuser is actually executed the DDL command and from which terminal. By selecting from this table, we may know who is the user/party/team actually dropped or altered any object in the database. Please see below for the example:

SQL> select * from AUDIT_DDL;

no rows selected

SQL> conn ctsadm/ctsadm
Connected.

SQL> CREATE TABLE test_ddl (
2 d date,
3 USERNAME varchar2(20),
4 TYPE varchar2(30),
5 STATUS varchar2(15));

Table created.

SQL> conn /as sysdba
Connected.
SQL> col OSUSER for a10
SQL> col CURRENT_USER for a10
SQL> col HOST for a15
SQL> col TERMINAL for a10
SQL> col OWNER for a20
SQL> col TYPE for a15
SQL> col NAME for a10
SQL> col SYSEVENT for a15
SQL> set lines 148
SQL> set pages 9000
SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——- ———- ——– ——— ——– ——–
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE

SQL> conn ctsadm/ctsadm
Connected.
SQL> alter table TEST_DDL modify (USERNAME varchar2(30));

Table altered.

SQL> conn /as sysdba
Connected.

SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——– ———– ——— ——— ——- ——-
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER

SQL> conn ctsadm/ctsadm
Connected.

SQL> drop table TEST_DDL;

Table dropped.

SQL> conn /as sysdba
Connected.

SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——— ———- ———– ——— ———- ———–
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL DROP

As you all can see, all the DDL executed is updated on the table. The osuser column show “oracle” and terminal column will show the terminal. From this I can determine that the changes is done by someone who connecting as “oracle” where usually in Client environment it will be the DBA team. From this output, we can prove to any other team (usually application team) who blame us (DBA) for any missing objects on the database.

By Manoj Devan

Convert Oracle RAC to Single Instance

Tags

oracle rac, Rac

This post is about Turn on/off RAC basically is to implement Converting/Migrating Oracle RAC to Single instance. You can find thousand of article on converting Single Instance to Oracle RAC but not the other way around. Why?

It does not makes any sense that you cannot convert from RAC to non-RAC environment. So, based on my knowledge on how Oracle RAC work, I’ve successfully done it.

This is based on Oracle 10G Release 2 and assumes:
1. Oracle RAC running with cluster file system
2. You have basic knowledge about Oracle RAC

Test Server:
OS : Red Hat Enterprise Linux Server release 5.4
Database Version : 10.2.0.4
File system: OCFS2

1. Stop database and CRS on both node
$ srvctl stop database -d mydb
# crsctl stop crs

2. Turn Off RAC

SQL> startup
ORA-29702 error occurred in Cluster Group Service operation

Relink with the RAC OFF.
$ cd $ORACLE_HOME/rdbms/lib
$ /usr/ccs/bin/make -f ins_rdbms.mk rac_off
Relinking oracle
$ make -f ins_rdbms.mk ioracle
## OR , both working fine
$ cd $ORACLE_HOME/bin
$ relink oracle

If ASM Instance Exist, run below command as root
# /oracle/product/10.2.0/db/bin/localconfig delete
# /oracle/product/10.2.0/db/bin/localconfig add

3.  Parameters (Pfile/spfile) & database changes
SQL> startup
SQL> alter database disable thread 2;
SQL> alter system set remote_listener='';

3a. Remove unwanted logfile
SQL> select thread#, group# from v$log;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;

3b. Remove unwanted tablespace
SQL> drop tablespace UNDOTBS2 including contents and datafiles;

3c.    Rename instance name.
SQL> alter system set instance_name=<new_name> scope=spfile;
SQL> shutdown immediate
SQL> startup
- Change your ORACLE_SID environment

4. Run $ORA_CRS_HOME/install/rootdelete.sh on both node
- This will stop and remove all CRS startup related file

5. Remove $ORA_CRS_HOME binary using Clusterware OUI installer
- Ignore any error if 2nd node already down
- rm -rf $ORA_CRS_HOME

6. Modify listener file
$ vi $ORACLE_HOME/network/admin/listener.ora

6a. Modify tnsname file
$ vi $ORACLE_HOME/network/admin/tnsnames.ora

By Hafiz Abdullah @ arPz

Transportable Tablespace

Tags

transportable tablespace

Previously, we encountered block corruption issue in one of the major Malaysian car distributors where the I_COL1 index was corrupt, in the SYS schema.

We’ve tried lot of thing such as drop and rebuild. All fail with same error “ORA-00701: object necessary for warmstarting database cannot be altered”.

The current production database was still running, but their application (SAP) was unable to access some of the function. Based on database size (1.8T), moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information.

What is transportable tablespace?

It is a feature to move a subset of an Oracle database and “plug” it in to another Oracle database, essentially moving tablespaces between the databases.

Limitations **

- The source and target database must be on the same hardware platform (< 9i). Oracle Database 10g adds the cross platform support for transportable tablespaces

- The source and target database must use the same character set and national character set.

- You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

- New data types – Binary Float and Binary Double.  These new Oracle 10g data types are only supported by Data Pump. Original Export will not handle them.

- Types opaque to Oracle, for example RAW, BLOB, SYS.ANYTYPE, SYS.ANYDATA, etc.  Interpretations of these data are application-specific and thus may affect cross-platform transportability.  Note the potential incompatibility can even occur when transporting between platforms with same endianness.

- User-defined function, user-defined operator, etc.  Oracle allows these definitions to appear in the following forms: SQL, PL/SQL, packages, C callouts.  Defining in SQL is always transportable. PL/SQL, packages and C callouts are external to tablespaces.  They need to be migrated or ported, in case of C callouts, in addition to the tablespace transport.

- SYSTEM, UNDO, SYSAUX, and TEMP tablespaces cannot be transported.

- If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.

** Source :

http://www.oracle.com/technology/deploy/availability/htdocs/xtts.htm

Example of Transportable Tablespace.

1. All the tablespaces needed to be transport must be in read only mode.

2. Check for any transport violation

a. execute SYS.DBMS_TTS.TRANSPORT_SET_CHECK (‘<tbs_name1>,<tbs_name2>’,TRUE,TRUE);

b. select * from sys.transport_set_violations;

3. Note down the violation object (tables, indexes or constraint). Export or get the script to recreate this object. Some ‘DBA_’ views such dba_indexes, dba_tables, dba_constraints, dba_objects can be helpful for this.

4. Take note of the objects with violations. Drop it if necessary. Re-run step 2 until there are no more violations.

5. Run the transportable tablespace command.

Example:

i.              SQL> alter tablespace PSAPBTABD read only;

ii.             SQL> alter tablespace PSAPBTABI read only;

iii.            SQL> execute sys.DBMS_TTS.TRANSPORT_SET_CHECK (‘PSAPBTABD,PSAPBTABI’,TRUE,TRUE);

PL/SQL procedure successfully completed.

iv.           SQL> select * from sys.transport_set_violations;

VIOLATIONS

———————————————————————————————————————–

Index SAPR3.SWP_SUSPEN~A in tablespace PSAPBTABI points to table SAPR3.SWP_SUSPEN in tablespace PSAPSTABD

Index SAPR3.SYS_C00152597 in tablespace PSAPBTABI enforces primary constraints of table SAPR3.VBAKTEST in tablespace PSAPSTABD

v.            Get metadata of index “SAPR3″.”SWP_SUSPEN~A”

vi.           SQL> drop index “SAPR3″.”SWP_SUSPEN~A”;

vii.          Get the tables’ constraint creation script(s)

viii.         SQL> alter table SAPR3.VBAKTEST drop constraints SYS_C00152597;

ix.           Export the tablespaces using below command: (Source Database)

exp userid=”‘system/manager@en1 as sysdba’” file=STAB_BTAB_exp.dmp log=STAB_BTAB_exp.log TRANSPORT_TABLESPACE=y TABLESPACES=PSAPBTABI,PSAPBTABD

x.            Import the tablespaces to EN2 (Target Database)

imp userid=”‘sys/oracle@en2 as sysdba’” file=STAB_BTAB_exp.dmp log=STAB_BTAB_imp.log TRANSPORT_TABLESPACE=y TABLESPACES= PSAPBTABI,PSAPBTABD datafiles=’/oracle/EN1/sapdata072/stabd_37/stabd.data37′,’/oracle/EN1/sapdata067/btabi_259/btabi.data259′

xi.           Re-create the violation objects if necessary.

by Hafiz Abdullah @ arPz

Recovering from deletion of Oracle index datafiles (with no backup available)

Tags

delete oracle datafile, oracle index, recover index

Recently, I had a scenario where a client reported that all the data files for a tablespace containing only indexes had been accidentally deleted from the database server, during a scheduled maintenance activity. More interestingly, for this particular soon to go live pre-production database, a backup strategy had not been implemented yet, so there was literally no options to perform restoration, either Oracle or OS-based.

So let’s just simulate the same scenario in a simple test environment first:

SQL> create tablespace INDEXES datafile '/u01/app/oracle/oradata/niradj/niradj/index01.dbf' size 100m;
Tablespace created.
SQL> create table test_obj as select * from dba_objects;
Table created.
SQL> create index testobj_idx on test_obj (object_name) tablespace "INDEX";
Index created.
SQL> alter table test_obj add constraint test_objpk primary key (object_id) using index tablespace "INDEX";
SQL> select index_name, tablespace_name,status from user_indexes where tablespace_name='INDEXES'
INDEX_NAME             TABLESPACE_NAME     STATUS
-----------------   ----------------------  --------
TESTOBJ_IDX             INDEXES              VALID
TEST_OBJPK             INDEXES               VALID

So with these few statements, we now have a tablespace INDEXES that only contains the indexes for our TEST_OBJ table. We are now ready to perform a delete of the datafile for this tablespace, after first shutting down the database. Note that at this point there is no backup of any sort done.

[root@olinux3 ~]# cd /u01/app/oracle/oradata/niradj/niradj/
[root@olinux3 niradj]# rm -rf index01.dbf

Now when we try to startup the database again, we will encounter the expected ORA-01157 error, with its related error message telling us a datafile is missing:

SQL> startup
ORACLE instance started.
Total System Global Area  205520896 bytes
Fixed Size                  1266584 bytes
Variable Size             125832296 bytes
Database Buffers           71303168 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/niradj/niradj/index01.dbf'

Now, here is where our story starts, as we have to somehow perform a recovery of the lost index tablespace without any form of backup, and bring up our “production” database. The first thing to note, is that if this really was a production system we’re seeing for the first time, we don’t really know that we’re only dealing with indexes in this tablespace. Second, we need to bring it up somehow, before we can even check on this.

So first, we spool a create controlfile script to help us bring up the database:

SQL> !pwd
/home/oracle10g
SQL> alter database backup controlfile to trace as '/home/oracle10g/control.txt';
Database altered.

Next, we edit the NORESETLOGS section of the newly-generated file to remove the datafile(s) that has been deleted in our index tablespace. We also can remove the ‘RECOVER DATABASE’ step, as we will be doing a clean shutdown of the database. It will look something like the sample below, when it’s done:

STARTUP NOMOUNT
CREATE CONTROLFILE DATABASE "NIRADJ" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/niradj/niradj/redo01.log'  SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/niradj/niradj/redo02.log'  SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/niradj/niradj/redo03.log'  SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/niradj/niradj/system01.dbf',
'/u01/app/oracle/oradata/niradj/niradj/undotbs01.dbf',
'/u01/app/oracle/oradata/niradj/niradj/sysaux01.dbf',
'/u01/app/oracle/oradata/niradj/niradj/users01.dbf',
CHARACTER SET WE8ISO8859P1;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;

Now, we can shutdown the database cleanly, and run our create control file script (make sure to move the existing control files first before running the script, or use the “REUSE” clause in the create controlfile statement) *Once the database is up, you will see that the tablespace name still exists in dba_tablespaces, and the datafile is missing in the view dba_data_files. We will also need to add a tempfile to the default temporary tablespace. *

But now we can verify if there are any tables that exist in this tablespace:

SQL> select table_name, owner, tablespace_name from dba_tables where tablespace_name='INDEXES';
no rows selected

Happily, there are in fact no tables residing in this tablespace, and there are only indexes or constraints that we can now recreate (if this wasn’t the case, there would be pretty much no way of recovering the data). First, let us run a simple export of the user schema(s) affected (in our example, only the NIRADJ schema, but we can verify by checking the dba_indexes and dba_constraints views)

exp niradj/niradj rows=n file=expniradj.dmp log=expniradj.log owner=NIRADJ

This will give us an export dumpfile of the schema definition for the NIRADJ schema, which of course also includes index creation statements. We now create an indexfile from this dumpfile:

imp niradj/niradj file=expniradj.dmp log=indexfileniradj.log indexfile=index_niradj.sql fromuser=NIRADJ

We now have a running database, a full set of index creation scripts for all indexes for our affected schema. Lastly, we just need to do a quick check of the created indexfile to ensure that any constraints that need to be enabled for the affected shcema is not commented out, e.g:

CREATE UNIQUE INDEX "NIRADJ"."TEST_OBJPK" ON "TEST_OBJ" ("OBJECT_ID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 983040 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDEXES" LOGGING ;
ALTER TABLE "NIRADJ"."TEST_OBJ" ADD CONSTRAINT "TEST_OBJPK" PRIMARY
KEY ("OBJECT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 983040 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "INDEXES" LOGGING ENABLE ;

Now we can go ahead with the final part, re-creating the INDEXES tablespace (after dropping any constraints existing in this tablespace), and consequently the relevant indexes. But note that it will be necessary to repeat the export/import steps if more than 1 schema was affected.

SQL> drop tablespace indexes including contents  and datafiles;
Tablespace dropped.
SQL> create tablespace indexes  datafile '/u01/app/oracle/oradata/niradj/niradj/index01.dbf' size 100m;
Tablespace created.
SQL> @index_niradj.sql
--- output trimmed for brevity ---
Index created.
Index created.
Table altered.
SQL> select index_name, tablespace_name,status from user_indexes where tablespace_name='INDEXES'
INDEX_NAME                    TABLESPACE_NAME     STATUS
------------------------------   ------------------------------  --------
TESTOBJ_IDX                    INDEXES                        VALID
TEST_OBJPK                     INDEXES                        VALID

So there we have it, after performing a few information-gathering steps, and preparing our index script, we are able to fully re-create all the lost datafiles for an index tablespace, with the relevant indexes, even without having any form of backup available.

By, Niradj Selvam
Oracle 10g, 11g OCP, 10g RAC Expert

Welcome to the Blog!

Tags

welcome

Howdy all,

Welcome to the launch of the new Oracle blog here at Innotiive Asia. With this blog, we hope to provide an informal forum for discussion and information sharing on all things Oracle-related. Please check by once in a while to see what new articles we’ve added, comment on things we’ve shared, or give us a few suggestions of your own.

More to come,

Cheers!