| |
Listen to more than one person talking, learning technology will continue to toss, sabotage, such as backup and recovery, you can try to delete a file, regardless of the log files, temporary files, data files or system files. After deleted, restart the database is certainly being given, and some even at the time the database will hang up, so you can learn to recover a break between a stand, many conventional backup and recovery tools will be to witness. I will try today to a redo log file group on the virtual machine deleted.
1. Prepare the environment
We tested in Oracle11g, the database is non-filing status.
SQL>
SQL> select * from v $ version;
BANNER
-------------------------------------------------- ------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL / SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 9
Current log sequence 11
SQL>
2. Delete a redo log file group, restart the database error
First, by querying v $ log to view the status of access to the database redo log file group.
SQL> select GROUP #, MEMBERS, ARCHIVED, STATUS from v $ log;
GROUP # MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 1 NO INACTIVE
2 1 NO CURRENT
3 1 NO INACTIVE
SQL>
Then, view the data file by ls command, remove the first redo log file group (the group has only one log file members).
[Oracle @ hoegh HOEGH] $ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
example01.dbf redo03.log temp01.dbf
[Oracle @ hoegh HOEGH] $
[Oracle @ hoegh HOEGH] $
[Oracle @ hoegh HOEGH] $ rm redo01.log
[Oracle @ hoegh HOEGH] $ ls
control01.ctl control02.ctl example01.dbf redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
Restart the database, the database error.
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5196
Session ID: 125 Serial number: 5
SQL>
SQL> select status from v $ instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL>
3. Check the alarm log file, locate the problem
ORA-03113 error is a very classic error, given a variety of reasons, from being given information and can not see is what causes the error, we can go to the alarm log file to view valuable clues.
[Oracle @ enmoedu1 trace] $ tail -40 alert_HOEGH.log
Wed Jul 08 21:59:30 2015
MMON started with pid = 15, OS id = 5443
Wed Jul 08 21:59:30 2015
MMNL started with pid = 16, OS id = 5445
starting up 1 dispatcher (s) for network address \ '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP)) \' ...
starting up 1 shared server (s) ...
ORACLE_BASE from environment = / u01 / app / oracle
Wed Jul 08 21:59:39 2015
alter database mount
Wed Jul 08 21:59:43 2015
Successful mount of redo thread 1, with mount id 2105928075
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Wed Jul 08 22:11:45 2015
Time drift detected. Please check VKTM trace file for more details.
Wed Jul 08 22:11:59 2015
alter database open
Wed Jul 08 22:11:59 2015
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: \ '/ u01 / app / oracle / oradata / HOEGH / redo01.log \'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: \ '/ u01 / app / oracle / oradata / HOEGH / redo01.log \'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_5451.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: \ '/ u01 / app / oracle / oradata / HOEGH / redo01.log \'
USER (ospid: 5451): terminating the instance due to error 313
Wed Jul 08 22:12:00 2015
System state dump requested by (instance = 1, osid = 5451), summary = [abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_diag_5425.trc
Dumping diagnostic data in directory = [cdmp_20150708221200], requested by (instance = 1, osid = 5451), summary = [abnormal instance termination].
Instance terminated by USER, pid = 5451
Among them, the yellow marked part of the critical information, we know that "/u01/app/oracle/oradata/HOEGH/redo01.log" can not find the file.
4. Start the database to mount state, rebuild redo log file group
As can be seen from the alarm log, the first set of redo log files is lost, we can sql statement "alter database clear logfile group 1;" rebuild log file group; confirm the log file is successfully created, the database is switched to the open state .
SQL> startup nomount
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
SQL> alter database mount;
Database altered.
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 7 NO INACTIVE
3 6 NO INACTIVE
2 8 NO CURRENT
SQL>
SQL>
SQL>
SQL>
SQL> alter database clear logfile group 1;
Database altered.
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 0 NO UNUSED
3 6 NO INACTIVE
2 8 NO CURRENT
Start the database to the open state
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 0 NO UNUSED
2 8 NO CURRENT
3 6 NO INACTIVE
At this point we look again at the list of files with the following results.
[Oracle @ hoegh HOEGH] $ ls
control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[Oracle @ hoegh HOEGH] $
5. Manually switching redo log file group
To ensure that the new log file group is available, we can manually switch the log file group, change the state of a new log file group (UNUSED to the other).
SQL>
SQL> alter system switch logfile;
System altered.
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 9 NO CURRENT
2 8 NO ACTIVE
3 6 NO INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 9 NO ACTIVE
2 8 NO ACTIVE
3 10 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group #, sequence #, archived, status from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------- ---------- --- ----------------
1 9 NO INACTIVE
2 11 NO CURRENT
3 10 NO INACTIVE
SQL>
among them,
current: indicates that the log group for the current log group, oracle is using the log group;
active: When the current redo group log switch, the status will change to active, in this state, if the database is archive mode, archive process will be archived active log group; if a database crash occurs, the log group also instance recovery required log group;
inactive:. When the active log group is completed archiving and oracle judgment does not require instance recovery, its status changed to inactive, waiting for the next round of use; so when the log group is inactive time, if the database is archive mode then log certainly archiving is complete. |
|
|
|