 What is wrong? Open DB recover [message #335483] |
Tue, 22 July 2008 05:29  |
in.lukfai
Messages: 8
Registered: July 2008
Location: India
|
Junior Member |
|
|
What is wrong?
I was doing practice about User-Manages Restore and Recovery. The scenario is:
- I have full backup(close database)
- I ran database in Archive mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\arc
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
- I have 2 tables:
SQL> select table_name,tablespace_name from user_tables where tablespace_name <> 'SYSTEM';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES USERDATA2
USER1 USER2_TPSP1
SQL> select t.name,d.name from v$tablespace t,v$datafile d where t.ts#=d.ts# and t.name like '%USER%';
NAME NAME
---------------------------------------------------------------------------------------------------------------
USERDATA2 C:\ORACLE\ORADATA\KATIE3\USERDATA2.DBF
USER2 C:\ORACLE\ORADATA\KATIE3\USER2.DBF
USER2_TPSP1 C:\ORACLE\ORADATA\KATIE3\U2_TBSP1.DBF
3 rows selected.
So the summary table would be:
TABLE_NAME TABLESPACE_NAME DATAFILE
------------------------------ ------------------------------
EMPLOYEES USERDATA2 C:\ORACLE\ORADATA\KATIE3\USERDATA2.DBF
USER1 USER2_TPSP1 C:\ORACLE\ORADATA\KATIE3\U2_TBSP1.DBF
- table employees has contents;
SQL> select employee_id from employees;
EMPLOYEE_ID
-----------
1
2
3
4
5
6
7
8
9
10
11
EMPLOYEE_ID
-----------
12
12 rows selected.
- I would like to drop table employees and then wanted to perform Recovering Open database so I did as the step below:
1. drop table employees:
SQL> drop table employees;
Table dropped.
SQL> select table_name,tablespace_name from user_tables where tablespace_name <> 'SYSTEM';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
USER1 USER2_TPSP1
2. Bring tablespace userdata2 offline:
SQL> alter tablespace userdata2 offline;
Tablespace altered.
3. Restored userdata2.dbf from backup.
4. Issued recover command:
SQL> recover automatic tablespace userdata2;
Media recovery complete.
5. Bring tablespace userdata2 online
SQL> alter tablespace userdata2 online;
Tablespace altered.
6. Checked whether table employees has been restored:
SQL> select * from employees;
select * from employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
That is the problem. I expected that table employees should be there but I could not find it in database. What is wrong in my part? Or I misconcept.
However, then I tried again by shutdown the database and then restore all files from backup and then open database. Everything is fine. The table employees
is in database. I don't understand. The purpose of this practice was for Recovery an Open database in archive mode. Could you please tell me what is wrong?
I read about TSPITR or the scenario is different I have to do TSPITR instead. Please advise..
|
|
|
|
|
|
| Re: What is wrong? Open DB recover [message #335496 is a reply to message #335490] |
Tue, 22 July 2008 05:57  |
in.lukfai
Messages: 8
Registered: July 2008
Location: India
|
Junior Member |
|
|
[quote title=Michel Cadot wrote on Tue, 22 July 2008 16:13]| Quote: | You recover the tablespace INCLUDING the drop statement.
|
I do think so. So what is the best way to recover this scenario?
I spinned around like anything.
Regards,
Katie
|
|
|
|
|
|
| Re: What is wrong? Open DB recover [message #335899 is a reply to message #335504] |
Thu, 24 July 2008 02:15 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
When u drop table accidently then u can recove it from recycle bin in oracle 10g .... 
Unless and until u have not purged your recycle bin ...
Note :- its recycle bin of oracle not windows o/s 
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE
Now, we accidentally drop the table:
SQL> drop table recycletest;
Table dropped.
Let's check the status of the table now.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE
=============================================================
PURGE RECYCLEBIN;
But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:
DROP TABLE RECYCLETEST PURGE;
This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.
[Updated on: Thu, 24 July 2008 02:19] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
