| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: rman recover/restore from backup
Hello Norm,
I just got back after two 15 hour days of work (nice weekend, I guess), so sorry for the delay.
Below are the steps involved in the logs included. Hope everything is clear and simple.
And Hope you are right and people you mentioned would be nice and brave to recognize that other things and methods exist outside of their world.
Regards,
Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Environment:
db1 - original database
tablespaces: system (contains additonal rollback segment and used as sort tablespace for simplicity)
users01 (contains demo table) - tablespace to be recovered and transferred
create database db1
controlfile reuse
archivelog
datafile '/oracle/data07/tdb/db1/db1_sys.dbf' size 300M
logfile group 1 '/oracle/data07/tdb/db1/db1_log1.dbf' size 100M
,group 2 '/oracle/data07/tdb/db1/db1_log2.dbf' size 100M
,group 3 '/oracle/data07/tdb/db1/db1_log3.dbf' size 100M
;
create tablespace users01 datafile '/oracle/data07/tdb/db1/db_users01.dbf'
size 10M
create rollback segment rbs_sys2 (for demo operations)
alter rollback segment rbs_sys2 online
db2 - new database
tablespaces: system (contains additonal rollback segment and used as sort tablespace for simplicity)
create database db2
controlfile reuse
archivelog
datafile '/oracle/data07/tdb/db2/db1_sys.dbf' size 300M
logfile group 1 '/oracle/data07/tdb/db2/db2_log1.dbf' size 100M
,group 2 '/oracle/data07/tdb/db2/db2_log2.dbf' size 100M
,group 3 '/oracle/data07/tdb/db2/db2_log3.dbf' size 100M
;
db1aux - clone database of db1 (created in a process)
init.ora contains some modified parameters (listed below).
Step #1:
in db1 - create demo user, demo table
Step #2
run rman backup of db1, incremental, level 0
Step #3
remove database db1 (or move to temp directory - shutdown before)
Step #4
create empty db1 directories
extract controlfile from db1 rman backupset to db1 directories (run PL/SQL block from db2)
Step #5
extract archivelogs from db1 rman backupset to db1aux archive dest (run PL/SQL block from db2).
Step #6
startup mount db1 database using recovered controlfile startup nomount db1aux clone instance
Step #7
rman connect to db1 , clone connect to db1aux
restore and replicate controlfile to clone (clone database mounted)
clone restore system and users01 datafiles
Step #8
db1aux: recover database until cancel
db1aux: alter database open resetlogs
Step #9
exp tts of user01 tablespace from db1aux (ts is in read only by default)
Step #10
copy users01 datafile to db2 directory
imp tts of users01 into db2
Step #11
verify data is accessabe
bring tablespace users01 read write
>>>> initdb1aux.ora
cat initdb1aux.ora
db_name = db1 compatible = 9.2.0.3#rollback_segments=(rbs01,rbs02,rbs03,rbs04,rbs05,rbs06,rbs07,rbs08,rbs09,rb s10,big,big2, big3)
db_files = 1000 db_block_size = 8192 db_cache_size=200m
recovery_parallelism=8 parallel_min_servers = 8 parallel_max_servers = 50
#log_archive_format= db1_%s.arc #log_archive_dest=/oracle/data07/tdb/db1/archive #log_checkpoint_timeout = 0
_sqlexec_progression_cost=0 _small_table_threshold=15000 _spin_count=5000 _sqlexec_progression_cost=0
>>>> END
>> demo setup on db1
SQL> create user demo identified by demo default tablespace users01;
User created.
SQL> grant connect, resource to demo;
Grant succeeded.
SQL> connect demo/demo
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> create table demo_test ( n number ) ;
Table created.
SQL> insert into demo_test values (20);
1 row created.
SQL> c/20/30/
1* insert into demo_test values (30)
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL>
>>>> Backup Steps
rman target sys/db0
Recovery Manager: Release 9.2.0.3.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DB1 (DBID=1112418489)
run {
sql "alter system archive log current";
backup incremental level 0 database
format '/oracle/data07/tdb/db1backup_%d_inc_0_%U'
archivelog all
current controlfile
tag=db1_inc_0;
}
Starting backup at 22-FEB-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupsetincluding current controlfile in backupset input datafile fno=00001 name=/oracle/data07/tdb/db1/db1_sys.dbf input datafile fno=00002 name=/oracle/data07/tdb/db1/db_users01.dbf channel ORA_DISK_1: starting piece 1 at 22-FEB-04 channel ORA_DISK_1: finished piece 1 at 22-FEB-04 piece handle=/oracle/data07/tdb/db1backup_DB1_inc_0_09feohho_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=1 recid=1 stamp=518555436 input archive log thread=1 sequence=2 recid=2 stamp=518555874 input archive log thread=1 sequence=3 recid=3 stamp=518710602input archive log thread=1 sequence=4 recid=4 stamp=518797909 input archive log thread=1 sequence=5 recid=5 stamp=518800951 channel ORA_DISK_1: starting piece 1 at 22-FEB-04 channel ORA_DISK_1: finished piece 1 at 22-FEB-04 piece handle=/oracle/reports/9203/dbs/0afeohhv_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupsetincluding current controlfile in backupset channel ORA_DISK_1: starting piece 1 at 22-FEB-04 channel ORA_DISK_1: finished piece 1 at 22-FEB-04 piece handle=/oracle/reports/9203/dbs/0bfeohi7_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 22-FEB-04
>>> Restore db1 control file from backupset (from db2 database)
DECLARE
v_dev varchar2(50); v_done boolean; v_backup varchar2 (255);
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/oracle/data07/tdb/db1
/cntrldb1.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_backup,
params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
PL/SQL procedure successfully completed.
>>> Restore db1 archive logs from backupset (from db2 database)
DECLARE
v_dev varchar2(50); v_done boolean:=false; v_backup varchar2(255);
sys.dbms_backup_restore.restoreSetArchivedLog(destination=>'/oracle/data07/t
db/db1aux/archive/arch_');
sys.dbms_backup_restore.restoreArchivedLog(thread=>1, sequence=>&1);
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_backup,
params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
Enter value for 1: 1
old 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>&1);
new 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>1);
PL/SQL procedure successfully completed.
SQL> /
Enter value for 1: 2
old 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>&1);
new 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>2);
PL/SQL procedure successfully completed.
SQL> /
Enter value for 1: 3
old 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>&1);
new 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>3);
PL/SQL procedure successfully completed.
SQL> /
Enter value for 1: 4
old 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>&1);
new 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>4);
PL/SQL procedure successfully completed.
SQL> /
Enter value for 1: 5
old 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>&1);
new 9: sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
sequence=>5);
PL/SQL procedure successfully completed.
>>> mount db1 database (using restored controlfile)
SQL> startup mount
ORACLE instance started.
Total System Global Area 404047768 bytes
Fixed Size 731032 bytes Variable Size 184549376 bytes Database Buffers 218103808 bytes Redo Buffers 663552 bytesDatabase mounted.
SQL> up nomount
ORACLE instance started.
Total System Global Area 404047768 bytes
Fixed Size 731032 bytes Variable Size 184549376 bytes Database Buffers 218103808 bytes Redo Buffers 663552 bytesSQL> >>>>> restore clone controlfile
rman target sys/db1 clone sys/oracle_at_db1aux
Recovery Manager: Release 9.2.0.3.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DB1 (DBID=1112418489) connected to auxiliary database: db1 (not mounted)
run {
restore clone controlfile to clone_cf;
replicate clone controlfile from clone_cf;
sql clone 'alter database mount clone database';
}
2> 3> 4> 5>
Starting restore at 22-FEB-04
using target database controlfile instead of recovery catalog allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring controlfileoutput filename=/oracle/data07/tdb/db1aux/cntrldb1.dbf channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/oracle/reports/9203/dbs/08feoej5_1_1 tag=DB1_INC_0 params=NULL channel ORA_AUX_DISK_1: restore complete Finished restore at 22-FEB-04
replicating controlfile
input filename=/oracle/data07/tdb/db1aux/cntrldb1.dbf
sql statement: alter database mount clone database
>>> restore db1 datafiles to clone database
run {
set until sequence = 4 thread = 1;
set newname for datafile 1 to '/oracle/data07/tdb/db1aux/db1_sys.dbf';
set newname for datafile 2 to '/oracle/data07/tdb/db1aux/db_users01.dbf';
restore clone datafile 1;
restore clone datafile 2;
switch clone datafile all;
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 2 online";
}
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-FEB-04
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/data07/tdb/db1aux/db1_sys.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/data07/tdb/db1backup_DB1_inc_0_02feld45_1_1
tag=DB1_INC_0 params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 22-FEB-04
Starting restore at 22-FEB-04
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oracle/data07/tdb/db1aux/db_users01.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/data07/tdb/db1backup_DB1_inc_0_02feld45_1_1
tag=DB1_INC_0 params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 22-FEB-04
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
>> recover clone database
SQL> recover database using backup controlfile until cancel
ORA-00279: change 198311 generated at 02/21/2004 14:18:18 needed for thread
1
ORA-00289: suggestion : /oracle/reports/9203/dbs/arch1_4.dbf
ORA-00280: change 198311 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/data07/tdb/db1aux/archive/arch_db2_4.arc ORA-00279: change 198930 generated at 02/22/2004 14:31:49 needed for thread 1
ORA-00289: suggestion : /oracle/reports/9203/dbs/arch1_5.dbf ORA-00280: change 198930 for thread 1 is in sequence #5 ORA-00278: log file '/oracle/data07/tdb/db1aux/archive/arch_db2_4.arc' nolonger needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/data07/tdb/db1aux/archive/arch_db2_5.arc ORA-00279: change 199509 generated at 02/22/2004 15:22:30 needed for thread 1
ORA-00289: suggestion : /oracle/reports/9203/dbs/arch1_6.dbf ORA-00280: change 199509 for thread 1 is in sequence #6 ORA-00278: log file '/oracle/data07/tdb/db1aux/archive/arch_db2_5.arc' nolonger needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>
>>> Verify user01 data is available:
setenv ORACLE_SID db1aux
ora si
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Feb 22 23:30:49 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.3.0 - Production
SQL> connect demo/demo
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEMO_TEST TABLE
SQL> select * from DEMO_TEST;
N
20
30
SQL> connect / as sysdba
Connected.
SQL> select status , tablespace_name from dba_tablespaces;
STATUS TABLESPACE_NAME
--------- ------------------------------ONLINE SYSTEM
exp userid=\'sys/oracle as sysdba\' TRANSPORT_TABLESPACE=y tablespaces=USERS01 file=users01.dmp
Export: Release 9.2.0.3.0 - Production on Sun Feb 22 15:52:26 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.3.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
erver uses US7ASCII character set (possible charset conversion)
ote: table data (rows) will not be exported
bout to export transportable tablespace metadata...
or tablespace USERS01 ...
exporting cluster definitions
exporting table definitions
. exporting table DEMO_TESTexporting referential integrity constraints exporting triggers
>> copy datafile and tts import of users01 to db2
cp db_users01.dbf ../db2/
imp userid =\'sys/db2 as sysdba\' TRANSPORT_TABLESPACE=y file=users01.dmp datafiles=/oracle/data07/tdb/db2/db_users01.dbf
Import: Release 9.2.0.3.0 - Production on Sun Feb 22 15:56:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set import server uses US7ASCII character set (possible charset conversion)
. importing SYS's objects into SYS . importing DEMO's objects into DEMO . . importing table "DEMO_TEST"Import terminated successfully without warnings.
db1aux oracle:/oracle/data07/tdb/db2 >
SQL> connect demo/demo
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from demo_test;
N
20
30
SQL> alter tablespace users01 read write;
Tablespace altered.
"Norman Dunbar" <norman.dunbar_at_lfs.co.uk> wrote in message news:3078e2fe.0402191028.50bce0ec_at_posting.google.com...
> Eee, by 'eck. I go away for a couple of monthsand when I come back > (briefly) there's all sorts of carnage going on. > > I'm with Richard on this one - it's been a hell of a thread and I love > it. > > It's a hard world behind the firewall. No news, b*gg*r all email most > of the time, and an 'interesting' situation of DBAing at this place. > :o) > > No offence Sybrand - I'd hate to fall out with you - but I especially > loved the bit where you gave advice on civil behaviour. Nice one :o) > > > Anyway, here's my £0.02 as it were : > > "Ron" <support_at_dbainfopower.com> wrote in messagenews:<GIudnVoTtMuB7a_d4p2dnA_at_comcast.com>...
> > Hello Daniel, > > > > Please, not like that ;-) > > Can you please expand on may lack on English skills? > > Should I send you my English literature scores to washington.edu? > > If ever someone was setting themselves up for a huge fall, this is it. > I look forward to see what happens next. > > There is of course another way to sort out the 'minor' differences > here, Ron, how about recreating a setup where you craete a test > database (full of useful data) and bollox it in exactly the same way > as the OP did, or in a similar way. Then, create a new one using dbca > as the OP did, and recover it using your point in time recovery > advice. > > Spool the exercise to a file and let us see it here. If it works, and > I very much doubt that it will, I'm almost 100% certain that Howard > will retract all he said about your advice, Sybrand will apologise > profusely and Richard will also gladly refute what he said. > > I have to admit that I *am* 100% sure that it will not work, or at > least not recover the database back to exactly the same pont in time > as Howard's advice did. > > Given the situation here, I'd have taken Howard's advice any day. But > then, I take advice from people I know have practiced (should that be > practised - I can't remeber?) and experimented with the procedures > they advise on. I've seen the results (and the hard work) that go into > Howard's advice in the past - have a quick Google and see for yourself > - so I know I can trust him and what he says. > > I have also seen him publicly apologise when he gave incorrect or > insufficient advice too. Something you might want to consider > sometime. > > If my english isn't up to scratch then don't attempt to correct me, I > don't care because I'm not English and English is not my first > language - I'm Scottish :o) > > (And on that note, I'm anticipating a huge defeat at the weekend when > we play England in the 6 Nations Rugby. Just like last night at the > hands of the Welsh in the footie. We are totally cr4p at the moment in > everything, except curling I suppose - we won the Olympics at that!) > > > Yours in anticipation, > > Norm. > > (Hopefully, I'll get to read some of the replies. This Googling is a > major PITA.) > > And by the way, those who know me will also know that the email > address is a spam trap - it no longer exists.Received on Mon Feb 23 2004 - 02:13:19 CST
 |
 |