Home » RDBMS Server » Backup & Recovery » why the table does not exists? (Oracle 11g)
why the table does not exists? [message #543271] Mon, 13 February 2012 21:01 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
SQL> show user
USER is "STORE"
SQL>
SQL> create table test(name varchar2(20));
SQL> insert into test values('1111');
SQL>
SQL> insert into test values('4444');
SQL>
SQL> insert into test values('5555');
SQL>
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

SQL> select sysdate from dual;
SYSDATE
-------------------
2012-02-13 17:57:11

SQL> select sysdate from dual;
SYSDATE
-------------------
2012-02-13 17:57:30
SQL> commit;
SQL>
SQL> select * from test;
NAME
--------------------
1111
4444
5555
SQL> drop table test;
SQL>
SQL> conn / as sysdba;
SQL>
SQL> shutdown immediate;
SQL>
SQL>
SQL>

[b]Recover the table that it is dropped after time '2012-02-13 17:57:11[/b]' 

SQL> startup mount;
SQL>
SQL> recover database until time '2012-02-13 17:57:11';
SQL>
SQL> alter database open resetlogs;
SQL>
SQL> conn store/system
SQL>
SQL>SQL> show user
USER is "STORE"
SQL>
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist



why the table does not exists? recover fail ?

[Updated on: Mon, 13 February 2012 21:03]

Report message to a moderator

Re: why the table does not exists? [message #543272 is a reply to message #543271] Mon, 13 February 2012 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>recover fail ?
No "recovery" was required or executed since DB was cleanly SHUTDOWN IMMEDIATE;
but you already knew that.
Re: why the table does not exists? [message #543274 is a reply to message #543272] Mon, 13 February 2012 21:54 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
I Can't understand it clearly.
Re: why the table does not exists? [message #543275 is a reply to message #543274] Mon, 13 February 2012 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
post content of alert_SID.log file starting with the SHUTDOWN IMMEDIATE until end of file.
Re: why the table does not exists? [message #543287 is a reply to message #543275] Mon, 13 February 2012 23:53 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
>post content of alert_SID.log file starting with the SHUTDOWN IMMEDIATE until end of file.
--------------------------------------------------------------------------------------------
it's here.


Tue Feb 14 12:44:40 2012
Shutting down instance: further logons disabled
EMN0 started with pid=21, OS id=4089
Tue Feb 14 12:44:40 2012
Stopping background process CJQ0
Tue Feb 14 12:44:40 2012
Stopping background process QMNC
Tue Feb 14 12:44:42 2012
Stopping background process MMNL
Tue Feb 14 12:44:42 2012
Stopping background process MMON
Tue Feb 14 12:44:43 2012
Shutting down instance (immediate)
License high water mark = 5
Tue Feb 14 12:44:43 2012
Stopping Job queue slave processes
Tue Feb 14 12:44:43 2012
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Tue Feb 14 12:44:46 2012
ALTER DATABASE CLOSE NORMAL
Tue Feb 14 12:44:47 2012
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Feb 14 12:44:47 2012
Shutting down archive processes
Archiving is disabled
Tue Feb 14 12:44:52 2012
ARCH shutting down
ARC2: Archival stopped
Tue Feb 14 12:44:57 2012
ARCH shutting down
ARC1: Archival stopped
Tue Feb 14 12:45:02 2012
ARCH shutting down
ARC0: Archival stopped
Tue Feb 14 12:45:03 2012
Thread 1 closed at log sequence 13
Successful close of redo thread 1
Tue Feb 14 12:45:03 2012
Completed: ALTER DATABASE CLOSE NORMAL
Tue Feb 14 12:45:03 2012
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Feb 14 12:45:58 2012
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 71303168
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  sga_target               = 167772160
  control_files            = /u01/app/oracle/oradata/lonion/control01.ctl, 
/u01/app/oracle/oradata/lonion/control02.ctl, 
/u01/app/oracle/oradata/lonion/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 83886080
  compatible               = 10.2.0.1.0
  log_archive_format       = %t_%s_%r.dbf
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /u01/app/oracle/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = sz
  dispatchers              = (PROTOCOL=TCP) (SERVICE=lonionXDB)
  job_queue_processes      = 10
  background_dump_dest     = /u01/app/oracle/admin/lonion/bdump
  user_dump_dest           = /u01/app/oracle/admin/lonion/udump
  core_dump_dest           = /u01/app/oracle/admin/lonion/cdump
  audit_file_dest          = /u01/app/oracle/admin/lonion/adump
  db_name                  = lonion
  open_cursors             = 300
  pga_aggregate_target     = 16777216
PMON started with pid=2, OS id=4096
PSP0 started with pid=3, OS id=4098
MMAN started with pid=4, OS id=4100
DBW0 started with pid=5, OS id=4102
LGWR started with pid=6, OS id=4104
CKPT started with pid=7, OS id=4106
SMON started with pid=8, OS id=4108
RECO started with pid=9, OS id=4110
CJQ0 started with pid=10, OS id=4112
MMON started with pid=11, OS id=4114
MMNL started with pid=12, OS id=4116
Tue Feb 14 12:45:58 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Tue Feb 14 12:45:58 2012
ALTER DATABASE   MOUNT
Tue Feb 14 12:46:03 2012
Setting recovery target incarnation to 2
Tue Feb 14 12:46:03 2012
Successful mount of redo thread 1, with mount id 1232974982
Tue Feb 14 12:46:03 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Feb 14 12:46:27 2012
ALTER DATABASE RECOVER  DATABASE UNTIL TIME '2012-02-14 12:43:55'  
Tue Feb 14 12:46:27 2012
Media Recovery Start
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER  DATABASE UNTIL TIME '2012-02-14 12:43:55'  
Tue Feb 14 12:46:40 2012
ALTER DATABASE OPEN RESETLOGS
Tue Feb 14 12:46:40 2012
db_recovery_file_dest_size of 2048 MB is 13.04% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
RESETLOGS after complete recovery through change 722738
Resetting resetlogs activation ID 1218519536 (0x48a121f0)
Tue Feb 14 12:46:46 2012
Setting recovery target incarnation to 3
Tue Feb 14 12:46:46 2012
Assigning activation ID 1232974982 (0x497db486)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=4126
Tue Feb 14 12:46:46 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=4128
Tue Feb 14 12:46:46 2012
Thread 1 opened at log sequence 1
  Current log# 3 seq# 1 mem# 0: /u01/app/oracle/oradata/lonion/redo03.log
Successful open of redo thread 1
Tue Feb 14 12:46:46 2012
ARC0: STARTING ARCH PROCESSES
Tue Feb 14 12:46:46 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Feb 14 12:46:46 2012
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Tue Feb 14 12:46:46 2012
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=4130
Tue Feb 14 12:46:46 2012
SMON: enabling cache recovery
Tue Feb 14 12:46:47 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Tue Feb 14 12:46:47 2012
SMON: enabling tx recovery
Tue Feb 14 12:46:47 2012
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=4132
Tue Feb 14 12:46:52 2012
LOGSTDBY: Validating controlfile with logical metadata
Tue Feb 14 12:46:52 2012
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN RESETLOGS
[oracle@lonion ~]$ 

[Updated on: Tue, 14 February 2012 01:15] by Moderator

Report message to a moderator

Re: why the table does not exists? [message #543288 is a reply to message #543287] Mon, 13 February 2012 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>Media Recovery Start
>Media Recovery Not Required
QED!
Re: why the table does not exists? [message #543293 is a reply to message #543288] Tue, 14 February 2012 00:34 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
If i want to recover the dropped table called 'test',what should i do ?Does it must have a backup?
Re: why the table does not exists? [message #543302 is a reply to message #543271] Tue, 14 February 2012 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> startup mount;
SQL>
SQL> recover database until time '2012-02-13 17:57:11';
SQL>
SQL> alter database open resetlogs;
SQL>
SQL> conn store/system
SQL>


Why did not you post the actual session?

Regards
Michel

Re: why the table does not exists? [message #543304 is a reply to message #543302] Tue, 14 February 2012 01:29 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Hi,Michel Cadot,there are the same test.
Re: why the table does not exists? [message #543308 is a reply to message #543304] Tue, 14 February 2012 01:40 Go to previous messageGo to next message
John Watson
Messages: 7623
Registered: January 2010
Location: Global Village
Senior Member
You haven't restored the databas, so you need to use FLASHBACK DATABASE, not RECOVER DATABASE.
Re: why the table does not exists? [message #543324 is a reply to message #543308] Tue, 14 February 2012 02:51 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Thank u .But i just want to know why recover database like the steps doesn't work.
Re: why the table does not exists? [message #543325 is a reply to message #543324] Tue, 14 February 2012 02:53 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Because there is nothing to recover!
Quote:
>Media Recovery Start
>Media Recovery Not Required
Re: why the table does not exists? [message #543326 is a reply to message #543324] Tue, 14 February 2012 03:11 Go to previous messageGo to next message
John Watson
Messages: 7623
Registered: January 2010
Location: Global Village
Senior Member
Quote:
i just want to know why recover database like the steps doesn't work.

I've already told you:
Quote:
You haven't restored the databas

Recovery takes a database forward in time. You are trying to take it backwards. You need to do some reading,
http://www.oracle.com/pls/db112/to_toc?pathname=backup.112%2Fe10642%2Ftoc.htm&remark=portal+%28Books%29
Re: why the table does not exists? [message #543330 is a reply to message #543304] Tue, 14 February 2012 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lonion wrote on Tue, 14 February 2012 08:29
Hi,Michel Cadot,there are the same test.


Oracle ALWAYS answers something, your post shows nothing, so you did not post what you did and got, why?
You want us to diagnose a patient we can't see?
Hopefully, they are some smart guys here that can do this as their answers show it but are you able to understand what they say?

Regards
Michel
Re: why the table does not exists? [message #543357 is a reply to message #543330] Tue, 14 February 2012 06:47 Go to previous message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Thank u for Michel Cadot's teaching. i will post the content what i really do next one.

are you able to understand what they say?
-----------------------------------------
maybe i have a little understand it . i need some time to think.
Previous Topic: Restore from control files
Next Topic: rman restore on new server - set newname not working?
Goto Forum:
  


Current Time: Tue Oct 23 03:39:21 CDT 2018