1.PURPOSE AND SCOPE
In this article, we are going to take user managed backup and try to restore it until spesific time.
2.PRACTICE
2.1. Lets start with backup side of user managed backup/restore.Before starting to copy tablespaces we need to changing the status of tablespaces.By the way you dont need to change the status of tablespaces like me by one by one, you can use “ALTER DATABASE BEGIN BACKUP;” command to take all of them backup mode.
[oracle@sb]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 10:21:38 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> select 'alter tablespace '||TABLESPACE_NAME||' begin backup;' from SYS.DBA_DATA_FILES; 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace USERS begin backup; alter tablespace UNDOTBS1 begin backup; alter tablespace SYSAUX begin backup; alter tablespace SYSTEM begin backup; alter tablespace EXAMPLE begin backup; alter tablespace MGMT_ECM_DEPOT_TS begin backup; alter tablespace MGMT_TABLESPACE begin backup; alter tablespace MGMT_AD4J_TS begin backup; 8 rows selected. SQL> alter tablespace USERS begin backup; Database altered. SQL> alter tablespace UNDOTBS1 begin backup; Database altered. SQL> alter tablespace SYSAUX begin backup; Database altered. SQL> alter tablespace SYSTEM begin backup; Database altered. SQL> alter tablespace EXAMPLE begin backup; Database altered. SQL> alter tablespace MGMT_ECM_DEPOT_TS begin backup; Database altered. SQL> alter tablespace MGMT_TABLESPACE begin backup; Database altered. SQL> alter tablespace MGMT_AD4J_TS begin backup; Database altered.
2.2. Now time to create cp scripts, so I can copy tablespaces to other location
SQL> select 'cp '|| FILE_NAME || ' /cloud/backup'||FILE_NAME from SYS.DBA_DATA_FILES; 'CP'||FILE_NAME||'/CLOUD/BACKUP'||FILE_NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- cp /oracle/app/oracle/oradata/dbf12c/users01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/users01.dbf cp /oracle/app/oracle/oradata/dbf12c/undotbs01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/undotbs01.dbf cp /oracle/app/oracle/oradata/dbf12c/sysaux01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/sysaux01.dbf cp /oracle/app/oracle/oradata/dbf12c/system01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/system01.dbf cp /oracle/app/oracle/oradata/dbf12c/example01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/example01.dbf cp /oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf cp /oracle/app/oracle/oradata/dbf12c/mgmt.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt.dbf cp /oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf
2.3. Now we can start to copy tablespaces.
[oracle@sb]$ cp /oracle/app/oracle/oradata/dbf12c/users01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/users01.dbf [oracle@sb]$ cp /oracle/app/oracle/oradata/dbf12c/undotbs01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/undotbs01.dbf [oracle@sb]$ cp /oracle/app/oracle/oradata/dbf12c/sysaux01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/sysaux01.dbf [oracle@sb]$ cp /oracle/app/oracle/oradata/dbf12c/system01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/system01.dbf [oracle@sb]$cp /oracle/app/oracle/oradata/dbf12c/example01.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/example01.dbf [oracle@sb]$cp /oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf [oracle@sb]$cp /oracle/app/oracle/oradata/dbf12c/mgmt.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt.dbf [oracle@sb]$cp /oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf
2.4. After copying all tablespaces its time to close backup mode for tablespaces.
SQL> select 'alter tablespace '||TABLESPACE_NAME||' end backup;' from SYS.DBA_DATA_FILES; 'ALTER TABLESPACE'||TABLESPACE_NAME||' end backup;' --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- alter tablespace USERS end backup; alter tablespace UNDOTBS1 end backup; alter tablespace SYSAUX end backup; alter tablespace SYSTEM end backup; alter tablespace EXAMPLE end backup; alter tablespace MGMT_ECM_DEPOT_TS end backup; alter tablespace MGMT_TABLESPACE end backup; alter tablespace MGMT_AD4J_TS end backup; 8 rows selected. SQL> alter tablespace USERS end backup; Database altered. SQL> alter tablespace UNDOTBS1 end backup; Database altered. SQL> alter tablespace SYSAUX end backup; Database altered. SQL> alter tablespace SYSTEM end backup; Database altered. SQL> alter tablespace EXAMPLE end backup; Database altered. SQL> alter tablespace MGMT_ECM_DEPOT_TS end backup; Database altered. SQL> alter tablespace MGMT_TABLESPACE end backup; Database altered. SQL> alter tablespace MGMT_AD4J_TS end backup; Database altered.
2.5. We need to empty redo logs so they can written to archivelogs.
SQL> alter system archive log current ; System altered.
2.6. We need to take controlfile backup with this step our backup operation will be finished.
SQL> alter database backup controlfile to '/cloud/backup/oracle/app/oracle/oradata/dbf12c/control.ctl'; Database altered.
2.7. Before closing database , i want to create another script it would be handy to copy backup tablespaces to orginal place.
SQL> select 'cp /cloud/backup'|| FILE_NAME || ' '||FILE_NAME from SYS.DBA_DATA_FILES; 'CP /CLOUD/BACKUP '||FILE_NAME||' '||FILE_NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/users01.dbf /oracle/app/oracle/oradata/dbf12c/users01.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/undotbs01.dbf /oracle/app/oracle/oradata/dbf12c/undotbs01.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/sysaux01.dbf /oracle/app/oracle/oradata/dbf12c/sysaux01.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/system01.dbf /oracle/app/oracle/oradata/dbf12c/system01.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/example01.dbf /oracle/app/oracle/oradata/dbf12c/example01.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf /oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt.dbf /oracle/app/oracle/oradata/dbf12c/mgmt.dbf cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf /oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf
2.8. And now time to start our restore process. I stop database ,delete all original tablespaces and cp backuped tablespaces&controlfiles to original places.
SQL> Shutdown abort; ORACLE instance shut down. SQL>exit; [oracle@sb]$ rm -f /oracle/app/oracle/oradata/dbf12c/* [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/users01.dbf /oracle/app/oracle/oradata/dbf12c/users01.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/undotbs01.dbf /oracle/app/oracle/oradata/dbf12c/undotbs01.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/sysaux01.dbf /oracle/app/oracle/oradata/dbf12c/sysaux01.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/system01.dbf /oracle/app/oracle/oradata/dbf12c/system01.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/example01.dbf /oracle/app/oracle/oradata/dbf12c/example01.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf /oracle/app/oracle/oradata/dbf12c/mgmt_depot.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt.dbf /oracle/app/oracle/oradata/dbf12c/mgmt.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf /oracle/app/oracle/oradata/dbf12c/mgmt_ad4j.dbf [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/control.ctl /oracle/app/oracle/oradata/dbf12c/control01.ctl [oracle@sb]$ cp /cloud/backup/oracle/app/oracle/oradata/dbf12c/control.ctl /oracle/app/oracle/oradata/dbf12c/control02.ctl
2.9. Now lets try to open database
SQL> startup ORACLE instance started. Total System Global Area 2142679040 bytes Fixed Size 1337720 bytes Variable Size 671090312 bytes Database Buffers 1442840576 bytes Redo Buffers 27410432 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/dbf12c/system01.dbf'
2.10. It didnt let us open database, also i wanted to restore it spesific time so lets try to restore it using backuped controlfile.
SQL> recover database using backup controlfile until time '2013-03-16:10:00:00';
ORA-00279: change 1237645 generated at 03/16/2013 9:25:27 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/DBF12C/archivelog/2013_03_16/o1_mf_1_60_8
o06tyom_.arc
ORA-00280: change 1237645 for thread 1 is in sequence #60
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1239277 generated at 03/16/2013 9:39:42 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/DBF12C/archivelog/2013_03_16/o1_mf_1_61_8
o071fh4_.arc
ORA-00280: change 1239277 for thread 1 is in sequence #61
ORA-00278: log file
'/oracle/app/oracle/flash_recovery_area/DBF12C/archivelog/2013_03_16/o1_mf_1_60_
8o06tyom_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/oracle/app/oracle/flash_recovery_area/DBF12C/archivelog/2013_03_16/o1_mf_1_62_
%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
2.11. Time to try open it again with resetlogs.
SQL> alter database open resetlogs; Database altered. SQL> select status from gv$instance ; STATUS ------------ OPEN
Leave a Reply