Oracle is connected to an idle routine or ora-01034:oracle not available

Because it is the local database does not have important information, so you can toss.

Problem reason: Import a table from the production database to the local library test, because the amount of data is too large, after 4-5 hours import, manually interrupt the import. Whether the exception shutdown is not determined.

Then open the database a little bit of a problem, that is, c disk space, the unauthorized deletion of several large database files and all log files.

--The following is a specific operation, first restore the database file, and then restore the log file:

-----------------------------------------------------1, the database cannot be opened after the data file is physically deleted--------------------------

C:\users\administrator>lsnrctl start

Lsnrctl for 64-bit windows:version 11.2.0.1.0-production on December-February-2015 09:4

1:05

Copyright (c) 1991, Oracle. All rights reserved.

Start Tnslsnr: Please wait ...

Tnslsnr for 64-bit windows:version 11.2.0.1.0-production

System parameter file is C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listene

R.ora

Log letter written to C:\app\administrator\diag\tnslsnr\Sandice-PC\listener\alert\log.xml

Interest

Monitoring: (Description= (Address= (protocol=tcp) (HOST=SANDICE-PC) (port=1521)))

Connecting to (Description= (address= (protocol=tcp) (HOST=SANDICE-PC) (port=1521))

The STATUS of LISTENER

------------------------

Alias LISTENER

Version Tnslsnr for 64-bit windows:version 11.2.0.1.0-produ

Ction

Start Date December-February-2015 09:41:09

Uptime 0 days 0 hours 0 minutes 3 seconds

Trace level off

Security On:local OS Authentication

SNMP OFF

Listener parameter File C:\app\Administrator\product\11.2.0\dbhome_1\network\a

Dmin\listener.ora

Listener log File C:\app\administrator\diag\tnslsnr\Sandice-PC\listener\

Alert\log.xml

Listener Endpoint Summary ...

(Description= (address= (protocol=tcp) (HOST=SANDICE-PC) (port=1521)))

Service Summary:

The service "ORCL" consists of 1 instances.

The instance "ORCL", State UNKNOWN, contains 1 handlers for this service ...

Command execution succeeded

C:\users\administrator>net Start ORACLESERVICEORCL

ORACLESERVICEORCL Service is starting ...

The ORACLESERVICEORCL service has started successfully.

C:\users\administrator>sqlplus

Sql*plus:release 11.2.0.1.0 Production on Wednesday February 11 10:25:37 2015

Copyright (c) 1982, Oracle. All rights reserved.

Please enter user name: SYS as SYSDBA

Enter Password:

Connect to:

Oracle Database 11g Release 11.2.0.1.0-64bit Production

--Note: A is a table originally built in the library

Sql> SELECT * from A

2;

SELECT * FROM A

*

An error occurred on line 1th:

ORA-01219: Database not open: Only allow queries in fixed tables/views

---Description: View the start status of the DB instance, the current boot status is mounted

Sql> select status from V$instance;

STATUS

------------

Mounted

--Description: View Control file location

Sql> select name from V$controlfile;

NAME

--------------------------------------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01. Ctl

C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02. Ctl

--Description: Use the Oracle command to display the contents of the control file in the trace (trace) to view the contents of the control file

sql> ALTER DATABASE backup Controlfile to trace as ' D:\ctl.trc ';

ALTER DATABASE backup Controlfile to trace as ' D:\ctl.trc '

*

An error occurred on line 1th:

ORA-01277: File ' D:\CTL. TRC ' already exists

sql> ALTER DATABASE backup Controlfile to trace as ' D:\ctl.trc ';

The database has changed.

--Description: Open the ' D:\ctl.trc ' file directly, view the contents of the control file, find the location of the database file, and find that the database file has been deleted.

Sql> SELECT count (1) from User_tables

2;

SELECT count (1) from User_tables

*

An error occurred on line 1th:

ORA-01219: Database not open: Only allow queries in fixed tables/views

--Description: Change the DB instance state to open with the following error

sql> ALTER DATABASE open;

ALTER DATABASE Open

*

An error occurred on line 1th:

ORA-01157: Unable to identify/lock data file 2-see DBWR trace file

ORA-01110: Data file 2: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01. DBF '

--The database did not find the data file because the data file was physically deleted without being offline, resulting in inconsistent data for Oracle and therefore failed to start.

Can be resolved by the following methods, operation of several data files

C:\users\administrator>sqlplus Sys/[email protected] as SYSDBA;

Sql*plus:release 11.2.0.1.0 Production on Wednesday February 11 11:10:25 2015

Copyright (c) 1982, Oracle. All rights reserved.

Connect to:

Oracle Database 11g Release 11.2.0.1.0-64bit Production

sql> ALTER DATABASE DataFile ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01. DBF ' of

Fline drop;

The database has changed.

sql> ALTER DATABASE open;

ALTER DATABASE Open

*

An error occurred on line 1th:

ORA-01157: Unable to identify/lock data file 5-see DBWR trace file

ORA-01110: Data file 5: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01. DBF '

sql> ALTER DATABASE DataFile ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01. DBF ' O

Ffline drop;

The database has changed.

sql> ALTER DATABASE open;

ALTER DATABASE Open

*

An error occurred on line 1th:

ORA-01157: Unable to identify/lock data file 6-see DBWR trace file

ORA-01110: Data file 6: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM02. DBF '

sql> ALTER DATABASE DataFile ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM02. DBF ' of

Fline drop;

The database has changed.

-----------------------------------------------------------2, after the log file is physically deleted, Redo log File Recovery--------------------------------------------------

---Description: After the physical removal of all data files, continue opening the database open state, the following error occurred:

sql> ALTER DATABASE open;

ALTER DATABASE Open

*

An error occurred on line 1th:

ORA-03113: End of file for communication channel

Process id:4308

Session id:191 Serial Number: 5

--View process files or Alert_orcl.log file locations

Sql> Show parameter background_dump_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Background_dump_dest string c:\app\administrator\diag\rdbm
S\orcl\orcl\trace

--Description: Find Alert_orcl.log, or trace file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4308.trc, note that 4308 is the process ID

TRC file content:

Dde:problem Key ' ORA 312 ' was flood controlled (0x1) (no incident)
ORA-00312: Online log 1 thread 1: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01. LOG '
Dde:problem Key ' ORA 313 ' was flood controlled (0x1) (no incident)
ORA-00313: Unable to open member of Log Group 1 (for thread)
ORA-00312: Online log 1 thread 1: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01. LOG '
ORA-00313: Unable to open member of Log Group 1 (for thread)
ORA-00312: Online log 1 thread 1: ' C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01. LOG '

--Query select * FROM V$log;

Can see the group 1, 2 file status, is the status of Inactive,group 3 is current, guess, perhaps the log log has been recorded to REDO03.LOG, so the status display

Redo Log Group Recovery for--inactive status

ALTER DATABASE Clear unarchived logfile Group 1, or ALTER DATABASE clear logfile Group 1;

Mine is redo log 1, 2 has been physically deleted, using the ALTER DATABASE clear logfile Group 1;

Restore REDO01, redo02,redo03 status is current, so temporarily do not know how to recover.

The following error occurred while continuing to connect to the database:

sql> ALTER DATABASE open;

ERROR:

ORA-03114: Not connected to ORACLE

Description: Reconnect

C:\users\administrator>sqlplus Sys/[email protected] as SYSDBA;

Sql*plus:release 11.2.0.1.0 Production on Wednesday February 11 11:19:23 2015

Copyright (c) 1982, Oracle. All rights reserved.

Connected to the idle routine.

Sql> SELECT count (1) from User_tables

2;

SELECT count (1) from User_tables

*

An error occurred on line 1th:

Ora-01034:oracle Not available

Process id:0

Session id:0 Serial Number: 0

Sql> Select instance_name,status from V$instance;

Select Instance_name,status from V$instance

*

An error occurred on line 1th:

Ora-01034:oracle Not available

Process id:0

Session id:0 Serial Number: 0

--Description: Whether it is a checklist or an instance, displays Ora-01034:oracle not available

--here, close the instance and use the command to boot to Mount state

The ORACLE routine has been closed.
sql> startup Mount;
The ORACLE routine has been started.

Total System Global area 1670221824 bytes
Fixed size                   2176328 bytes
Variable size             1056967352 bytes
Database buffers           603979776 bytes
Redo buffers                 7098368 bytes
Database is loaded.
Sql> SELECT * from V$log;

    group#    thread#  sequence#      BYTES  blocksize    members ARC
---------------------------------------------------------------
status           first_change# FIRST_TIME      next_change# next_time
---------------------------------------------------------------------
          1          1           0   52428800        512           1 YES
unused               3.4393E+11 2 September-December -14       3.4393E+11 2 September-December-14

3 1 135 52428800 1 NO
Current 3.4393E+11 2 September-December -14 2.8147E+14

2 1 0 52428800 1 YES
UNUSED 3.4393E+11 2 September-December -14 3.4393E+11 2 September-December-14

sql> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
An error occurred on line 1th:
The ora-01139:resetlogs option is only valid after incomplete database recovery

--Note: The restoration is ' 2014-11-01 12:12:12 '

sql> recover database until time ' 2014-11-01 12:12:12 ';
Complete media recovery.
sql> ALTER DATABASE open resetlogs;

The database has changed.

sql> shutdown
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> Startup
The ORACLE routine has been started.

Total System Global area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1056967352 bytes
Database buffers 603979776 bytes
Redo buffers 7098368 bytes
The database is loaded.
The database is already open.
Sql>
Sql> SELECT count (1) from User_tables;

COUNT (1)
----------
960

Sql> select * from V$log;

    group#    thread#  sequence#      BYTES  blocksize    members ARC
---------------------------------------------------------------
status           first_change# FIRST_TIME      next_change# next_time
---------------------------------------------------------------------
          1          1           1   52428800        512           1 NO
current              3.4393E+11 December-February -15       2.8147E+14

         2          1           0   52428800         512          1 YES
unused                        0                             0

3 1 0 52428800 1 YES
UNUSED 0 0

Note Here, Group#1 became current,group#2, 3 became unused, although do not know the specific reasons, but also to achieve their own purposes, the database can be used normally, but the data file has been deleted corresponding table space data should have been lost.

Finally found a problem, the operation is completed, the C:\APP\ADMINISTRATOR\ORADATA\ORCL directory, redo log files and data file time has changed, but the temporary data file did not change, do not understand, and continue to study.

Oracle is connected to an idle routine or ora-01034:oracle not available