SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 20 11:40:51 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

STEP -1)

CONNECT WITH THE USER IN WHICH YOU WANT TO CREATE A TABLE
SQL> CONN HR/HR
Connected.

I'LL CREATE A TABLE NAMED CALLED EXAMPLE_SCN

SQL> Create table  EXAMPLE_SCN
  2  AS
  3  SELECT * FROM HR.EMPLOYEES;

Table created.

INSERT SOME VALUE IN IT I'LL CREATE 3424 ROW'S IN EXAMPLE_SCN

SQL> INSERT INTO EXAMPLE_SCN
  2  SELECT * FROM HR.EXAMPLE_SCN;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> /

1712 rows created.

COMMIT THE TRANSACTION TO SAVE THE CHANGES

SQL> COMMIT;

Commit complete.


SQL> SELECT COUNT(*) FROM EXAMPLE_SCN;

  COUNT(*)
----------
      3424

STEP =2)

LOGIN WITH THE SYSDBA TO CHECK OUT WHICH SCN NO.. YOU HAVE

SQL> CONN SYS/AS SYSDBA
Enter password: *****
Connected.
SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE#
          FROM V$LOG;


    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         1          0 UNUSED           YES             0
         2          0 UNUSED           YES             0
         3          1 CURRENT          NO         678956

NOW SWITCH THE LOG FILE TO CHANGE YOUR LOG SEQUENCE

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

NOW CHECK AGAIN
AND LOOK AT THE CURRENT SCN NUMBER AND NOTE IT

SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE#
  2  FROM V$LOG;

    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         1          2 ACTIVE           YES        689996
         2          3 CURRENT          NO         689998
         3          1 ACTIVE           YES        678956



STEP -3) TAKE A FULL BACKUP ON RMAN

C:\>RMAN TARGET SYS/TIGER@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 15 22:31:18 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1303083464, not open)

RMAN> BACKUP DATABASE PLUS ARCHIVELOG ALL;

STEP -4) NOW CONNECT WITH THE SQL PROMT

ASSUME THAT THE TABLE EXAMPLE_SCN WE LOST ACCIDENTLY

SQL> DROP TABLE HR.EXAMPLE_SCN PURGE;

Table dropped.

IMMEDIATELY SHUTDOWN THE DATABASE

STEP -5)

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

TAKE DATABASE AT MOUNT STAGE

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  633339904 bytes
Fixed Size                  1250572 bytes
Variable Size             176163572 bytes
Database Buffers          448790528 bytes
Redo Buffers                7135232 bytes
Database mounted.

CONNECT WITH RMAN TO RECOVER THE TABLE NAMED EXAMPLE_SCN WHICH WE LOST ACCIDENTLY

STEP -6) C:\>RMAN TARGET SYS/TIGER@ORCL

NOW TAKE A DATABASE AT THAT STAGE WHERE THE TABLE NAMED EXAMPLE_SCN EXIST
REMEMBER WE HAVE COPIED THE CURRENT SCN NUMBER

RMAN> RESTORE DATABASE UNTIL SCN 689998;

Starting restore at 15-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece F:\ORACLE\PRODUCT\10.2.0\FLASH_REC
OVERY_AREA\ORCL\BACKUPSET\2012_02_15\O1_MF_NNNDF_TAG20120215T213715_7MQP1MRF_.BK
P
channel ORA_DISK_1: restored backup piece 1
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_02
_15\O1_MF_NNNDF_TAG20120215T213715_7MQP1MRF_.BKP tag=TAG20120215T213715
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 15-FEB-12

RECOVER THE DATABASE AND USED THE SAME SCN NUMBER WE USE ABOVE WHILE WE PERFORM RESORE DATABASE COMMAND

RMAN> RECOVER DATABASE UNTIL SCN 689998;

Starting recover at 15-FEB-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file F:\ORACLE\PRODUCT\10.
2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_15\O1_MF_1_8_7MQP3T9R_.ARC
archive log thread 1 sequence 9 is already on disk as file F:\ORACLE\PRODUCT\10.
2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_15\O1_MF_1_9_7MQQ00TV_.ARC
archive log thread 1 sequence 1 is already on disk as file F:\ORACLE\PRODUCT\10.
2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_15\O1_MF_1_1_7MQS1R2X_.ARC
archive log thread 1 sequence 2 is already on disk as file F:\ORACLE\PRODUCT\10.
2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_15\O1_MF_1_2_7MQS1TM1_.ARC
archive log filename=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2012_02_15\O1_MF_1_8_7MQP3T9R_.ARC thread=1 sequence=8
archive log filename=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2012_02_15\O1_MF_1_9_7MQQ00TV_.ARC thread=1 sequence=9
media recovery complete, elapsed time: 00:00:09
Finished recover at 15-FEB-12

STEP -7) REMEMBER THIS THING WHEN EVER YOU TAKE YOUR DATABASE IN PAST MUST OPEN WITH RESETLOGS

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

TAKE EXIT FROM THE RMAN

RMAN> EXIT;

CONNECT WITH SQL AND LOGIN WITH THE HR USER

SQL> CONN HR/HR
Connected.

CHECK THE TABLE NAMED EXAMPLE_SCN

SQL> SELECT COUNT(*) FROM EXAMPLE_SCN;

  COUNT(*)
----------
      3424

DONE :-)

####### WE RESTORE THE TABLE EXAMPLE_SCN SUCCESSFULLY ##########

Thanx For Reading My Blog Give Comment's & Suggesstions At The End of The Blog :-)
---------------------------------------------------------------------------------
SEND ME SUGGESTIONS & YOUR ORACLE 9I,10G ERROR'S AND SNAPSHOT'S AT MY
 EMAIL ID = rohitkhurana.k@gmail.com , rohitkhurana.oracle@gmail.com


Regards - Rohit khurana

( ORACLE CERTIFIED ASSOCIATE )

Views: 22834

Comment by Mahir M. Quluzade on February 22, 2012 at 4:22am

Very nice !

Comment by Andriy Dmytrenko on February 22, 2012 at 5:28am

You've killed my eyes!

Comment by Rohit Khurana on February 22, 2012 at 8:22am

Thank's Alot  Mahir M. Quluzade 

Comment by Rohit Khurana on February 22, 2012 at 8:26am

Andriy Dmytrenko Means It's complicated ?? OR It's Easy ??

Comment by abdul qadir on February 26, 2012 at 6:02am

gud one .its helping me alot to over come my db problem.

Comment by Peter Ephanga on January 12, 2015 at 6:08pm

Why would you shutdown your database to restore one tablespace that does not belong to sys or sysaux? 

Comment

You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Streamline Your Business Process with Real Estate Data Entry Services

People in the real estate business have to deal with huge volumes of data on a regular basis. It includes the data that is generated from legal documents, property valuation statements, latest commercial and non-commercial property information, etc. This database should be regularly updated and processed properly to help you in your business procedures. With access to relevant real-time data, your business organization can thrive in the commercial real estate market. In…

Continue

Posted by Alvaro Dee on March 27, 2017 at 2:48am

Augment the Speed and Efficiency of Website Development with Joomla

Over the past few years, Joomla has emerged out as one of the greatest platforms for designing, developing, and deploying websites and web applications worldwide. Joomla scales up extremely well, uses less memory and gives a brilliant performance. Hire skilled and experienced Joomla developers to use Joomla CMS for developing simple and complex corporate websites. You can even enhance the functioning of your existing website by using this open source platform.

By outsourcing Joomla…

Continue

Posted by Alvaro Dee on March 20, 2017 at 4:00am

Top 10 Interview Questions & Answers for Java Developers

An organization hiring a Java developer is looking for someone who can code the applications flawlessly. For the period after Java training, the following is a very important list of ten Java interview questions. Make sure you tap the benefits of this comprehensive list of questions shared by our Java counterparts to crack your next interview.

1. What is immutable…

Continue

Posted by Michael Warne on March 17, 2017 at 3:00am

Get the Best Website for Your Business in a Set Time Frame

If you sell products online, you must be knowing how time-consuming and mundane it is to upload products. There are chances of you looking for someone to handle your product data entry tasks. A company that has a wealth of experience and is known for providing its customers with data entry services for a variety of eCommerce solutions can deal efficiently with high volumes of work without any hassle.…

Continue

Posted by Alvaro Dee on March 17, 2017 at 2:47am

© 2017   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service