Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / User managed backup and restore

User managed backup and restore

08:37 AM By Bugra Canbakal Leave a Comment

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 PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[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

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
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 PL/SQL
1
2
3
4
5
6
7
8
[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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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.

Oracle PL/SQL
1
2
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.

Oracle PL/SQL
1
2
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.

Oracle PL/SQL
1
2
3
4
5
6
SQL> alter database open resetlogs;
Database altered.
SQL> select status from gv$instance ;
STATUS
------------
OPEN

Share this:

  • LinkedIn
  • Twitter

Filed Under: Oracle Tagged With: resetlog, user managed backup, user managed restore

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.