Hi there
Any assistance be greatly appreciated...
Details of environment:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
"CORE 9.2.0.8.0 Production"
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Background:
I am Oracle DBA (contractor), where there are 2 MS DBA's. Provided detail procedure ie scripts to move a few hundred datafiles between different mount points. Aim of the exercise was to save space.
Mentioned to DBA not to change individual lines but use WHERE statements. Instead, he changed one result of query, made a typo, result was, after the database bounced two days later, the datafile was not there.
Found the datafile, but could not do any recovery, had to take datafile offline, and bring database online. Retail business...
This database used for large warehouse.
Situation:
Come up with a solution that avoids taking the database offline. Recover in any way possible that partition.
Partition:
They are using range (date field) by calendar year. They have 1 tablespace with 1 datafile.
My solution:
They had a file-based backup of all the files (including Oracle database)(cold) for that server before the mishap.
Another server:
Database 1
Did restore of database using system, index, redo logs plus 1 individual file. (using trace controlfile)
Did export of that tablespace with partition(x12)
Database 2 (**For testing purposes***)
Created Same schema as LIVE database
Created same tablespaces used for partitioned table
Created partitioned table (using SQL statement)
Entered dummy data for calendar period 2009,2011,2012
Then took datafile offline, I get (For any query based on partition for period 2012)
ORA-00376:
ORA-01110:
Now I need to recover this situation:
So, to import the data for partition,
I did the following:
1.Drop tablespace (Including all contents - 12 partitions)
Select * From Dba_Tab_Partitions Where Table_Name =Upper( 'Wrk_Ibt_Matching_Historic') And Partition_Name LIKE 'P2012%' ORDER BY Partition_Name
2.Create tablespace with datafiles
3.Create manually partitions for 2012
4.Then do the import.
Using exp/imp options.
Stuck in 3.Create tablespace with datafiles.
Sample of partition table:
Partition By Range (Ibt_Date)
(
---
Partition P2012_01 Values Less Than (To_Date(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) Tablespace "WRK_IBT_MATCHING_HISTORIC_2012",
....
Partition P2012_12 Values Less Than (To_Date(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) Tablespace "WRK_IBT_MATCHING_HISTORIC_2012",
----
PARTITION P_MISC VALUES LESS THAN (MAXVALUE) Tablespace "WRK_IBT_MATCHING_HISTORIC"
);
So, need to add back 12 partitions dropped. Not sure about this. I tried this:
Attempt 1
Alter Table Wrk_Ibt_Matching_Historic
Split Partition P_Misc
At (To_Date(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
At (To_Date(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') Tablespace Wrk_Ibt_Matching_Historic_2012)
Into (Partition P2012_01,Partition P2012_02,Partition P2012_03,Partition P2012_04,Partition P2012_04,Partition P2012_05,Partition P2012_06,Partition P2012_07,Partition P2012_08,Partition P2012_09,
Partition P2012_10,Partition P2012_11,Partition P2012_12, Partition P_Misc);
But, get this error message:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Googled:
Maintaining Partitions
Attempt 2
ALTER TABLE orders
SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))
AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'))
INTO (PARTITION p_fh_may07,PARTITION p_sh_may2007);
NB TO_DATE('01-MAY-2007','dd-MON-yyyy') - used (MAXVALUE)
Both occasions get the same error message ?
What I'm I doing wrong ? Any ideas greatly appreciated.