Home » RDBMS Server » Backup & Recovery » manually backup without using RMAN
manually backup without using RMAN [message #536011] Sun, 18 December 2011 23:49 Go to next message
ashishs
Messages: 40
Registered: May 2011
Member

Sir,
How i can take manually backup without using RMAN,i tried it using os command "copy".
Re: manually backup without using RMAN [message #536012 is a reply to message #536011] Sun, 18 December 2011 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I refuse to provide a loaded gun to an infant.
Re: manually backup without using RMAN [message #536013 is a reply to message #536011] Sun, 18 December 2011 23:54 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Why not using RMAN? It's so easy!
If you want to take cold backup without RMAN, you need to shutdown the database, copy Redo Log files, Datafiles, controlfiles, parameter file and start the instance
Re: manually backup without using RMAN [message #536021 is a reply to message #536013] Mon, 19 December 2011 00:26 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

Sir, i want perform user managed backup,i tried
1)shutdown immediate;
2)startup mount;
3)cp '/home/oracle/oracle/../oradate/test' '/home/oracle/oracle/newfolder/test01'
4)rm '/home/oracle/oracle/../test'
3)mv '/home/oracle/oracle/newfolder/test01' '/home/oracle/oracle/oradata/test'
5)recover tablespace test;
but at this time it generate about to restore datafile..?
why....
can u tell me how do this..
Is this right step...
Re: manually backup without using RMAN [message #536022 is a reply to message #536021] Mon, 19 December 2011 00:27 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

You can't perform recovery operation having cold backup
Take RMAN Backup
drop tablespace
recover it
Re: manually backup without using RMAN [message #536024 is a reply to message #536022] Mon, 19 December 2011 00:34 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

it is possible with rman,but i want user managed...ok
can you tell how i take manually backup..
Re: manually backup without using RMAN [message #536025 is a reply to message #536024] Mon, 19 December 2011 00:34 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

There's no way to recover tablespace with user managed backups
Re: manually backup without using RMAN [message #536028 is a reply to message #536025] Mon, 19 December 2011 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This wrong but you must first restore the file from your backup.

Regards
Michel
Re: manually backup without using RMAN [message #536029 is a reply to message #536021] Mon, 19 December 2011 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ashishs wrote on Mon, 19 December 2011 07:26
Sir, i want perform user managed backup,i tried
1)shutdown immediate;
2)startup mount;
3)cp '/home/oracle/oracle/../oradate/test' '/home/oracle/oracle/newfolder/test01'
4)rm '/home/oracle/oracle/../test'
3)mv '/home/oracle/oracle/newfolder/test01' '/home/oracle/oracle/oradata/test'
5)recover tablespace test;
but at this time it generate about to restore datafile..?
why....
can u tell me how do this..
Is this right step...


Once again do NOT (try to) explain what you did, SHOW US.

Regards
Michel
Re: manually backup without using RMAN [message #536033 is a reply to message #536025] Mon, 19 December 2011 01:02 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

Can you tell me user managed recovery...how i can do it.
Re: manually backup without using RMAN [message #536034 is a reply to message #536029] Mon, 19 December 2011 01:05 Go to previous messageGo to next message
nircarasso
Messages: 2
Registered: December 2011
Junior Member
Hi,
You can recover and restore a tablespace without Oracle RMAN!
But you must(!) be in archived-log mode.
Are you in archived log mode? ( from sqlplus: " archive log list")
Re: manually backup without using RMAN [message #536036 is a reply to message #536033] Mon, 19 December 2011 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ashishs wrote on Mon, 19 December 2011 08:02
Can you tell me user managed recovery...how i can do it.


Can you post what I asked?

Regards
Michel
icon4.gif  Re: manually backup without using RMAN [message #536050 is a reply to message #536034] Mon, 19 December 2011 02:19 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

Yes,my database in archivelog mode....
Sir, can you tell me how i can take manual backup..what is the mean of begin backup and end backup..i search on net but cant get proper answer..
i give you the script prepare by another person.

Re: manually backup without using RMAN [message #536064 is a reply to message #536050] Mon, 19 December 2011 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Regards
Michel
Re: manually backup without using RMAN [message #536068 is a reply to message #536064] Mon, 19 December 2011 02:52 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

BACKUP AND RECOVERY SCENARIOS
Complete Recovery With User-managed Backup.
you can perform complete recovery in the below 5 situations.

User Managed Recovery Scenarios of complete recovery.
1. Complete Closed Database Recovery. System datafile is missing(with recent backups)
2. Complete Open Database Recovery. Non system datafile is missing(with backups).
3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing(with backups)
4. Recovery of a Missing Datafile that has no backups.(Disk corrupted and no backups available)
restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
you cannot recover or create datafile without backup in the following situation:
view plainprint?
1. SQL> select CONTROLFILE_CREATED from v$database;
2. CONTROLFILE_CREATED
3. --------------------
4. 07-MAY-2010 01:23:43
view plainprint?
1. SQL> select creation_time,name from v$datafile;
2. CREATION_TIME NAME
3. -------------------- ---------------------------------------------
4. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
5. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
6. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
7. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf

5. Restore and Recovery of a Datafile to a different location.(Disk corrupted having recent backup and recover the datafile in new Disk location).

User Managed Recovery Scenarios
User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up.
Files to be copied:
select name from v$datafile;
select member from v$logfile; # optional
select name from v$controlfile;
1.Complete Closed Database Recovery. System tablespace is missing
If the system tablespace is missing or corrupted the database cannot be started up
so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location from recent backup, ie:
cp -p /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;
workshop1: system datafile recovery with recent backup

view plainprint?
1. SQL> create user rajesh identified by rajesh;
2. User created.
3. SQL> grant dba to rajesh;
4. Grant succeeded.
5. SQL> shutdown immediate
6. Database closed.
7. Database dismounted.
8. ORACLE instance shut down.
9. i manually deleted the datafile system01.dbf for testing purpose only
10. SQL> startup
11. ORACLE instance started.
12. Total System Global Area 444596224 bytes
13. Fixed Size 1219904 bytes
14. Variable Size 138412736 bytes
15. Database Buffers 301989888 bytes
16. Redo Buffers 2973696 bytes
17. Database mounted.
18. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
19. ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf'
20.
21.
22. SQL> shutdown immediate
23. ORA-01109: database not open
24.
25.
26. Database dismounted.
27. ORACLE instance shut down.
28. SQL> host cp /u01/app/oracle/oradata/backup/system01.dbf /u01/app/oracle/oradata/testdb/system01.dbf
29. system datafile restored from recent backup
30.
31. SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 7 12:51:16 2010
32.
33. Copyright (c) 1982, 2005, Oracle. All rights reserved.
34.
35. Enter user-name: sys as sysdba
36. Enter password:
37. Connected to an idle instance.
38.
39. SQL> startup mount
40. ORACLE instance started.
41.
42. Total System Global Area 444596224 bytes
43. Fixed Size 1219904 bytes
44. Variable Size 138412736 bytes
45. Database Buffers 301989888 bytes
46. Redo Buffers 2973696 bytes
47. Database mounted.
48. SQL> recover datafile 1;
49. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
50. ORA-00289: suggestion :
51. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
52. ORA-00280: change 454383 for thread 1 is in sequence #7
53.
54.
55. Specify log: {=suggested | filename | AUTO | CANCEL}
56. auto
57. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
58. ORA-00289: suggestion :
59. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
60. ORA-00280: change 456007 for thread 1 is in sequence #8
61. ORA-00278: log file
62. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
63. .
64. .
65. .
66. ORA-00279: change 456039 generated at 05/07/2010 12:46:22 needed for thread 1
67. ORA-00289: suggestion :
68. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
69. ORA-00280: change 456039 for thread 1 is in sequence #11
70. ORA-00278: log file
71. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7hl7dr_.arc' no longer needed for this recovery
72.
73.
74. Log applied.
75. Media recovery complete.
76. SQL> alter database open;
77.
78. Database altered.
79.
80. SQL> archive log list;
81. Database log mode Archive Mode
82. Automatic archival Enabled
83. Archive destination USE_DB_RECOVERY_FILE_DEST
84. Oldest online log sequence 12
85. Next log sequence to archive 14
86. Current log sequence 14
87. SQL> select username from dba_users
88. 2 where username='RAJESH';
89.
90. USERNAME
91. ------------------------------
92. RAJESH

2.Complete Open Database Recovery. Non system tablespace is missing
If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

2. alter tablespace offline immediate;
3. recover tablespace ;
4. alter tablespace online;
workshop2: Non-system datafile recovery from recent backup when database is open
view plainprint?
1. SQL> ALTER USER rajesh DEFAULT TABLESPACE users;
2.
3. User altered.
4.
5. SQL> conn rajesh/rajesh;
6. Connected.
7. SQL> create table demo(id number);
8.
9. Table created.
10.
11. SQL> insert into demo values(123);
12.
13. 1 row created.
14.
15. SQL> commit;
16.
17. Commit complete.
18.
19. SQL> select * from demo;
20.
21. ID
22. ----------
23. 123
24.
25. SQL> conn sys/oracle as sysdba;
26. Connected.
27. SQL> alter system switch logfile;
28.
29. System altered.
30.
31. SQL> /
32.
33. System altered.
34.
35. SQL> archive log list;
36. Database log mode Archive Mode
37. Automatic archival Enabled
38. Archive destination USE_DB_RECOVERY_FILE_DEST
39. Oldest online log sequence 14
40. Next log sequence to archive 16
41. Current log sequence 16
42. i manually deleted the datafile users01.dbf for testing purpose only
43. SQL> conn rajesh/rajesh;
44. Connected.
45. SQL> alter system flush buffer_cache;
46.
47. System altered.
48.
49. SQL> select * from demo;
50. select * from demo
51. *
52. ERROR at line 1:
53. ORA-00376: file 4 cannot be read at this time
54. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
55.
56.
57. SQL> conn sys/oracle as sysdba;
58. Connected.
59. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
60. restore the users01.dbf datafile from recent backup to the testdb folder
61.
62. SQL> alter tablespace users offline immediate;
63.
64. Tablespace altered.
65.
66. SQL> recover tablespace users;
67. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
68. ORA-00289: suggestion :
69. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
70. ORA-00280: change 454383 for thread 1 is in sequence #7
71.
72.
73. Specify log: {=suggested | filename | AUTO | CANCEL}
74. auto
75. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
76. ORA-00289: suggestion :
77. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
78. ORA-00280: change 456007 for thread 1 is in sequence #8
79. ORA-00278: log file
80. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
81. .....
82. ......
83. ORA-00279: change 456044 generated at 05/07/2010 12:46:28 needed for thread 1
84. ORA-00289: suggestion :
85. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_%u_.arc
86. ORA-00280: change 456044 for thread 1 is in sequence #13
87. ORA-00278: log file
88. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_12_5y7hldl2_.arc' no longer needed for this recovery
89.
90.
91. Log applied.
92. Media recovery complete.
93. SQL> alter tablespace users online;
94.
95. Tablespace altered.
96.
97. SQL> conn rajesh/rajesh;
98. Connected.
99. SQL> select * from demo;
100.
101. ID
102. ----------
103. 123
3.Complete Open Database Recovery (when the database is initially closed).Non system datafile is missing
If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2. alter database datafile3 offline; (tablespace cannot be used because the database is not open)
3. alter database open;
4. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
5. recover datafile 3;
6. alter tablespace online;
workshop 3:Non system datafile is missing

view plainprint?
1. SQL> conn sys/oracle as sysdba;
2. Connected.
3. SQL> alter system switch logfile;
4.
5. System altered.
6.
7. SQL> select username,default_tablespace from dba_users
8. 2 where username='RAJESH';
9.
10. USERNAME DEFAULT_TABLESPACE
11. ------------------------------ ------------------------------
12. RAJESH USERS
13.
14. SQL> conn rajesh/rajesh;
15. Connected.
16. SQL> create table testtbl (id number);
17.
18. Table created.
19.
20. SQL> insert into testtbl values(786);
21.
22. 1 row created.
23.
24. SQL> commit;
25.
26. Commit complete.
27.
28. SQL> select * from testtbl;
29.
30. ID
31. ----------
32. 786
33.
34. SQL> conn sys/oracle as sysdba;
35. Connected.
36. SQL> shutdown immediate;
37. Database closed.
38. Database dismounted.
39. ORACLE instance shut down.
40. SQL> --manually deleting the users01.dbf datafile from testdb folder
41. warning:for testing purpose only
42. SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf
43.
44. SQL> startup
45. ORACLE instance started.
46.
47. Total System Global Area 444596224 bytes
48. Fixed Size 1219904 bytes
49. Variable Size 142607040 bytes
50. Database Buffers 297795584 bytes
51. Redo Buffers 2973696 bytes
52. Database mounted.
53. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
54. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
55.
56.
57. SQL> alter database datafile 4 offline;
58.
59. Database altered.
60.
61. SQL> alter database open;
62.
63. Database altered.
64.
65. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
66. copying user01.dbf from the recent backup to the testdb folder
67. SQL> recover datafile 4;
68. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
69. ORA-00289: suggestion :
70. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
71. ORA-00280: change 454383 for thread 1 is in sequence #7
72.
73.
74. Specify log: { =suggested | filename | AUTO | CANCEL} auto
75. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
76. ORA-00289: suggestion :
77. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
78. ORA-00280: change 456007 for thread 1 is in sequence #8
79. ORA-00278: log file
80. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
81. ......
82. .........
83. ORA-00279: change 456046 generated at 05/07/2010 12:46:29 needed for thread 1
84. ORA-00289: suggestion :
85. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_%u_.arc
86. ORA-00280: change 456046 for thread 1 is in sequence #14
87. ORA-00278: log file
88. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_5y7hlfbc_.arc' no longer needed for this recovery
89.
90.
91. Log applied.
92. Media recovery complete.
93. SQL> alter database datafile 4 online;
94.
95. Database altered.
96.
97. SQL> conn rajesh/rajesh;
98. Connected.
99. SQL> select * from testtbl;
100.
101. ID
102. ----------
103. 786
4.Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing,

recovery can be performed if all archived logs since the creation
of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter tablespace offline immediate;
2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf';
3. recover tablespace ;
4. alter tablespace online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'

restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
workshop 4: Missing Non-system Datafile having no backups
view plainprint?
1. SQL> alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';
2.
3. Session altered.
4.
5. SQL> select controlfile_created from v$database;
6.
7. CONTROLFILE_CREATED
8. --------------------
9. 07-MAY-2010 16:27:22
10.
11. SQL> col name format a45
12. SQL> select creation_time,name from v$datafile;
13.
14. CREATION_TIME NAME
15. -------------------- ---------------------------------------------
16. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
17. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
18. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
19. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf
20. you cannot re-create the any one of the listed above datafile , without backup.
21. SQL> create tablespace testing datafile
22. 2 '/u01/app/oracle/oradata/testdb/test01.dbf' size 2m;
23.
24. Tablespace created.
25.
26. SQL> select creation_time,name from v$datafile;
27.
28. CREATION_TIME NAME
29. -------------------- ---------------------------------------------
30. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
31. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
32. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
33. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf
34. 07-MAY-2010 16:32:07 /u01/app/oracle/oradata/testdb/test01.dbf
35. we can re-create test01.dbf file without backup.
36. SQL> select controlfile_created from v$database;
37.
38. CONTROLFILE_CREATED
39. --------------------
40. 07-MAY-2010 16:27:22
41.
42. ---we can recover the datafile test01.dbf without backup using
view plainprint?
1. create datafile command in recovery
2. ---in this example i am going to create a table in testing tablespace
view plainprint?
1. and deleted the test01.dbf datafile and recover it without backup and
view plainprint?
1. create datafile recovery command.
2.
3. SQL> create user jay identified by jay
4. 2 default tablespace testing;
5.
6. User created.
7.
8. SQL> grant dba to jay;
9.
10. Grant succeeded.
11.
12. SQL> select username,default_tablespace from dba_users
13. 2 where username='JAY';
14.
15. USERNAME DEFAULT_TABLESPACE
16. ------------------------------ ------------------------------
17. JAY TESTING
18.
19. SQL> conn jay/jay;
20. Connected.
21. SQL> create table demo (id number);
22.
23. Table created.
24.
25. SQL> insert into demo values(321);
26.
27. 1 row created.
28.
29. SQL> commit;
30.
31. Commit complete.
32.
33. SQL> select * from demo;
34.
35. ID
36. ----------
37. 321
38.
39. SQL> conn sys/oracle as sysdba;
40. Connected.
41. SQL> host rm -rf /u01/app/oracle/oradata/testdb/test01.dbf
42. ---manually deleting datafile test01.dbf for testing purpose
43.
44. SQL> conn jay/jay;
45. Connected.
46. SQL> select * from demo;
47.
48. ID
49. ----------
50. 321
51.
52. SQL> alter system flush buffer_cache;
53.
54. System altered.
55.
56. SQL> select * from demo;
57. select * from demo
58. *
59. ERROR at line 1:
60. ORA-01116: error in opening database file 5
61. ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01.dbf'
62. ORA-27041: unable to open file
63. Linux Error: 2: No such file or directory
64. Additional information: 3
65.
66.
67. SQL> alter database datafile 5 offline;
68.
69. Database altered.
70. ----TO CREATE A NEW RECOVERED DATAFILE IN SAME LOCATION.
71. SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf';
72. Database altered.
73. ----TO CREATE A NEW RECOVERED DATAFILE IN DIFFERENT LOCATION.
74. SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf' as '/u03/oradata/test01.dbf';
75.
76. Database altered.
77.
78. SQL> recover datafile 5;
79. ORA-00279: change 454443 generated at 05/07/2010 16:32:07 needed for thread 1
80. ORA-00289: suggestion :
81. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
82. ORA-00280: change 454443 for thread 1 is in sequence #8
83.
84.
85. Specify log: {=suggested | filename | AUTO | CANCEL}
86. auto
87. ORA-00279: change 454869 generated at 05/07/2010 16:41:38 needed for thread 1
88. ORA-00289: suggestion :
89. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_9_%u_.arc
90. ORA-00280: change 454869 for thread 1 is in sequence #9
91. ORA-00278: log file
92. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_5y7xcbrm_.arc' no longer needed for this recovery
93. .....
94. .......
95. ORA-00279: change 454874 generated at 05/07/2010 16:41:45 needed for thread 1
96. ORA-00289: suggestion :
97. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
98. ORA-00280: change 454874 for thread 1 is in sequence #11
99. ORA-00278: log file
100. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7xck8j_.arc' no longer needed for this recovery
101.
102.
103. Log applied.
104. Media recovery complete.
105. SQL> alter database datafile 5 online;
106.
107. Database altered.
108.
109. SQL> conn jay/jay;
110. Connected.
111. SQL> select * from demo;
112.
113. ID
114. ----------
115. 321
116.
117. SQL>

5.Restore and Recovery of a Datafile to a different location.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.

1. Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. recover tablespace ;
5. alter tablespace online;
workshop 5:
view plainprint?
1. SQL> create user lachu identified by lachu
2. 2 default tablespace users;
3.
4. User created.
5.
6. SQL> grant dba to lachu;
7.
8. Grant succeeded.
9.
10. SQL> conn lachu/lachu;
11. Connected.
12. SQL> create table test_tb(id number);
13.
14. Table created.
15.
16. SQL> insert into test_tb values(123);
17.
18. 1 row created.
19.
20. SQL> commit;
21.
22. Commit complete.
23.
24. SQL> conn sys/oracle as sysdba;
25. Connected.
26. SQL> ---manually deleting users01.dbf datafile for testing purpose
27. SQL> host rm -rf '/u01/app/oracle/oradata/testdb/users01.dbf'
28.
29. SQL> conn lachu/lachu;
30. Connected.
31. SQL> select * from tab;
32.
33. TNAME TABTYPE CLUSTERID
34. ------------------------------ ------- ----------
35. TEST_TB TABLE
36. SQL> select * from test_tb;
37. select * from test_tb
38. *
39. ERROR at line 1:
40. ORA-00376: file 4 cannot be read at this time
41. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
42.
43.
44. SQL> conn sys/oracle as sysdba;
45. Connected.
46. SQL> alter database datafile 4 offline;
47.
48. Database altered.
49.
50. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u03/oradata/users01.dbf
51. --restore datafile user01.dbf to new disk from the recent backup of the database.
52.
53. SQL> alter tablespace users rename datafile
54. 2 '/u01/app/oracle/oradata/testdb/users01.dbf' to '/u03/oradata/users01.dbf';
55. Tablespace altered.
56.
57. SQL> recover datafile 4;
58. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
59. ORA-00289: suggestion :
60. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
61. ORA-00280: change 454383 for thread 1 is in sequence #7
62.
63.
64. Specify log: {=suggested | filename | AUTO | CANCEL}
65. auto
66. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
67. ORA-00289: suggestion :
68. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
69. ORA-00280: change 456007 for thread 1 is in sequence #8
70. ORA-00278: log file
71. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
72. ....
73. ......
74. ORA-00279: change 457480 generated at 05/07/2010 13:09:30 needed for thread 1
75. ORA-00289: suggestion :
76. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_15_%u_.arc
77. ORA-00280: change 457480 for thread 1 is in sequence #15
78. ORA-00278: log file
79. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_5y7jxlvg_.arc' no longer needed for this recovery
80.
81.
82. Log applied.
83. Media recovery complete.
84. SQL> alter database datafile 4 online;
85.
86. Database altered.
87.
88. SQL> select name from v$datafile;
89.
90. NAME
91. ---------------------------------------------
92. /u01/app/oracle/oradata/testdb/system01.dbf
93. /u01/app/oracle/oradata/testdb/undotbs01.dbf
94. /u01/app/oracle/oradata/testdb/sysaux01.dbf
95. /u03/oradata/users01.dbf ----------restored in new location (disk)
96.
97. SQL> conn lachu/lachu;
98. Connected.
99. SQL> select * from tab;
100.
101. TNAME TABTYPE CLUSTERID
102. ------------------------------ ------- ----------
103. TEST_TB TABLE
104.
105. SQL> select * from test_tb;
106.
107. ID
108. ----------
109. 123

Re: manually backup without using RMAN [message #536069 is a reply to message #536068] Mon, 19 December 2011 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I give up!

Regards
Michel
Re: manually backup without using RMAN [message #536073 is a reply to message #536069] Mon, 19 December 2011 03:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
OK, Michel, I'll have a go.
Ashish, you have posted a vast amount of information that appears to come from May 2010. You had better start again. But first:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read. If you don't format the code with [code] tags, it is really hard to read and people will not want to assist you.
Re: manually backup without using RMAN [message #536144 is a reply to message #536068] Mon, 19 December 2011 08:31 Go to previous messageGo to next message
nircarasso
Messages: 2
Registered: December 2011
Junior Member
Hi,

You can do your backups without RMAN, based on OS commands. Before year 2000 that was the only way to do it.
All you have to do is copy the datafiles, controlfiles, redo , archive from the oracle server to backup place. But Before executing the copy command you must "free" the oracle from managing the files. It is done be the commands: "Alter database/tablespace begin backup".
After the copy is done you must execute the command "alter database/tablespace end backup;




Re: manually backup without using RMAN [message #536150 is a reply to message #536144] Mon, 19 December 2011 09:53 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Copying online redo log files and the controlfile while the database is open will not work: backup mode will make it safe to copy the datafiles, but nothing else. You cannot backup online logs (not even with RMAN) if the database is open. You must backup an open controlfile with ALTER DATABASE BACKUP CONTROLFILE.
Previous Topic: backup level issue
Next Topic: recover issue
Goto Forum:
  


Current Time: Thu Mar 28 10:15:31 CDT 2024