Oracle Tips and Tricks: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
(Die Seite wurde neu angelegt: „Tipps ==Show non default settings== <source lang=sql> SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE'; </s…“) |
No edit summary |
||
| Line 1: | Line 1: | ||
[[Kategorie:Oracle|Tipps]] | [[Kategorie:Oracle|Tipps]] | ||
==Recover datafiles== | |||
Problem: | |||
<source lang=oracle11> | |||
ORA-00376: file 18 cannot be read at this time | |||
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf' | |||
</source> | |||
<source lang=oracle11> | |||
SQL> select * from v$recover_file; | |||
FILE# ONLINE ONLINE_ | |||
---------- ------- ------- | |||
ERROR CHANGE# | |||
----------------------------------------------------------------- ---------- | |||
TIME | |||
--------- | |||
18 OFFLINE OFFLINE | |||
5.8016E+12 | |||
22-JUL-15 | |||
</source> | |||
<source lang=oracle11> | |||
SQL> select ONLINE_STATUS from dba_data_files where file_id = 18; | |||
ONLINE_ | |||
------- | |||
RECOVER | |||
</source> | |||
Recover datafile: | |||
<source lang=oracle11> | |||
SQL> recover datafile 18; | |||
ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1 | |||
ORA-00289: suggestion : | |||
/data/oracle/arclog/ORACLESID_1946_1_882824275.ARC | |||
ORA-00280: change 5801623243148 for thread 1 is in sequence #1946 | |||
ORA-00278: log file | |||
'/data/oracle/arclog/ORACLESID_1945_1_882824275.ARC' no longer needed | |||
for this recovery | |||
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} | |||
AUTO | |||
Log applied. | |||
Media recovery complete. | |||
</source> | |||
Set file online: | |||
<source lang=oracle11> | |||
SQL> alter database datafile 18 online | |||
</source> | |||
Anything else? | |||
<source lang=oracle11> | |||
SQL> select * from v$recover_file; | |||
no rows selected | |||
</source> | |||
==Show non default settings== | ==Show non default settings== | ||
<source lang= | <source lang=oracle11> | ||
SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE'; | SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE'; | ||
</source> | </source> | ||
Revision as of 20:22, 25 July 2015
Recover datafiles
Problem:
ORA-00376: file 18 cannot be read at this time
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf'
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
18 OFFLINE OFFLINE
5.8016E+12
22-JUL-15
SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;
ONLINE_
-------
RECOVER
Recover datafile:
SQL> recover datafile 18;
ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1
ORA-00289: suggestion :
/data/oracle/arclog/ORACLESID_1946_1_882824275.ARC
ORA-00280: change 5801623243148 for thread 1 is in sequence #1946
ORA-00278: log file
'/data/oracle/arclog/ORACLESID_1945_1_882824275.ARC' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
Set file online:
SQL> alter database datafile 18 online
Anything else?
SQL> select * from v$recover_file;
no rows selected
Show non default settings
SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE';
