OraFAQ Forum: Backup & Recovery » Problems while Restoring Hot Backup

Home » RDBMS Server » Backup & Recovery » Problems while Restoring Hot Backup

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problems while Restoring Hot Backup [message #203120]

Mon, 13 November 2006 23:33

ashok1784
Messages: 3
Registered: November 2006

Junior Member

Hi All,

I am new to Oracle, so i am doing lot of R&D in oracle.I face a problem while restoring from backup. i will list the steps which i followed to take backup and restore it.

There is a tablespace called as ashok.

Inside the tablespace there is a table called as Test.

Step 1:
alter tablespace ashok begin backup;
Step 2:
select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 ACTIVE 1916776 14-NOV-06
4 NOT ACTIVE 0
5 ACTIVE 1916776 14-NOV-06
Step 3:
Used os command to copy the datafile used by the tablespace ashok.Note tablespace ashok uses only one datafile.
Step 4.1:

SQL> select TABLESPACE_NAME,TABLE_NAME from dba_tables where TABLESPACE_NAME='ASHOK';

TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
ASHOK TEST

Step 4.2:
alter tablespace ashok end backup;
Step 5:
Shut immediate
Step 6: startup mount;
Step 7: alter database rename file '/local-prd/u01/ashok.dbf' to /local-prd/u02/ashokbackup.dbf'
Step 8: recover tablespace ashok;
Step 9: alter database open;
Step 10:

SQL> select TABLESPACE_NAME,TABLE_NAME from dba_tables where TABLESPACE_NAME='ASHOK';

no rows selected

Please Guide me where i have gone wrong.

Thanks in advance.

My maid id is ashok1784@gmail.com

Report message to a moderator

Re: Problems while Restoring Hot Backup [message #203148 is a reply to message #203120]

Tue, 14 November 2006 01:50

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi ashok

SQL> select file_name from dba_data_files where tablespace_name = 'USERS';

FILE_NAME
--------------------------------------------------------------------------------

C:\USERS01.DBF

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 ACTIVE                2811256 14-NOV-06
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0

7 rows selected.

SQL> --using OS copy cmd copy user01.dbf file to bkp location
SQL> alter tablespace users end backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE            2811256 14-NOV-06
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0

7 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\USERS01.DBF'


SQL> alter database rename file 'c:\users01.dbf' to  'D:\oracle\product\10.1.0\o
radata\orcl\users01.dbf' ;

Database altered.

SQL> recover tablespace users;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select tablespace_name, table_name from dba_tables where tablespace_name =
'USERS';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
USERS                          DEPT
USERS                          EMP
USERS                          BONUS
USERS                          SALGRADE
USERS                          STYLESHEET_TAB
USERS                          PRODUCT_REF_LIST_NESTEDTAB
USERS                          SUBCATEGORY_REF_LIST_NESTEDTAB
USERS                          EMPSAL
USERS                          TEST
USERS                          WAGES
USERS                          C

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
USERS                          EMP
USERS                          SMS
USERS                          BIG_TABLE
USERS                          BIG_TABLE1
USERS                          A
USERS                          EMPLOYEES

17 rows selected.

SQL> select file_name from dba_data_files where tablespace_name = 'USERS';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------

USERS
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

7 rows selected.

SQL>

what actully you did. i hv not problem with recovery..

hope this helps
Mohammad Taj.

Report message to a moderator

Re: Problems while Restoring Hot Backup [message #203155 is a reply to message #203148]

Tue, 14 November 2006 02:24

ashok1784
Messages: 3
Registered: November 2006

Junior Member

Hi Mohamad,

I actually created a table inside the tablespace and took a backup of that tablespace. After taking the backup of the tablespace i dropped the table which i had created earlier.

So backed up datafile should contain the table and the existing datafile which is under use by the tablespace should not contain the table.

Correct me if iam wrong.

Problem which i am facing is that after renaming the file for the tablespace still then i dont find table.

Note i have followed the same steps as mentioned by you in the previous post.

Thanks
Ashok.R

Report message to a moderator

Re: Problems while Restoring Hot Backup [message #203156 is a reply to message #203155]

Tue, 14 November 2006 02:28

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi ashok

Oracle Version ?
if 10g. so use "FLASHBACK TABLE "TABLE_NAME" TO before drop;
or prior to 10g.

use point and time recovery...

hope this helps.
MOhammad Taj.

Report message to a moderator

Re: Problems while Restoring Hot Backup [message #203157 is a reply to message #203156]

Tue, 14 November 2006 02:31

ashok1784
Messages: 3
Registered: November 2006

Junior Member

sorry mohamad

the version of oracle is 9i.

can u help me why is it not happening in this case

Report message to a moderator

Re: Problems while Restoring Hot Backup [message #204206 is a reply to message #203157]

Sun, 19 November 2006 00:20

parthokonar
Messages: 17
Registered: September 2006
Location: india

Junior Member

I think when you create a table and enter some data in the table before making the tablespace into begin backup mode and copy the concerned datafiles with o/s, you go for some 'alter system checkpoint','alter system switch logfile'command so that the data is been recorded in the redolog files, thus in the archive logs. Well this may help you

Report message to a moderator

Previous Topic:	Password file errors after RMAN duplication
Next Topic:	Recovery from a different location rather than from backup location

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 03:40:38 CST 2025