sqlplus scott/tiger@TEST
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Then I tried to login to the database as SYS and I found that the database is in mount state. So why is it not open ? I knew when the windows machine was restarted the database might have come up and went into mount state. Then why it did not open. Let's see.
Let me shutdown and startup it manually and see what went wrong.
SQL> startup;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
ORA-00214: control file 'D:\APPLNS\ORACLE\ORADATA\TEST\CONTROL02.CTL' version
521 inconsistent with file 'D:\APPLNS\ORACLE\ORADATA\TEST\CONTROL01.CTL'
version 519
So this is the problem. One or more copy of control files are inconsistent. As you know,control files are multiplexed and here somehow the copy of control file inconsistent. So let's where are these control files. Remember my db is in mount state.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string D:\APPLNS\ORACLE\ORADATA\TEST\
CONTROL01.CTL, D:\APPLNS\ORACL
E\ORADATA\TEST\CONTROL02.CTL,
D:\APPLNS\ORACLE\ORADATA\TEST\
CONTROL03.CTL
Now what we have to do is startup the database with a single control file which is consistent.Remove the control file copies and start with only one control file.
If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file.
If you use spfile then after issuing startup nomount use show control_files to see existing controlfiles inside spfile and then you can use ALTER SYSTEM SET CONTROL_FILES=file_name; in order to point just one copy of control file.
Here is my control_files parameter in init.ora
*.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl','D:\Applns\oracle\oradata\TEST\control02.ctl','D:\Applns\oracle\oradata\TEST\control03.ctl'
I have modified it to include just only one.
*.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl'
Now let's startup the instance with new pfile.
SQL> startup pfile='D:\Applns\oracle\product\10.2.0\db_1\database\initTEST.ora'
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
Bravo !!!!
You can see my database came up and is open.
Now, let's copy the good control file and make two copies.
D:\Applns\oracle\oradata\TEST>dir CO*
Volume in drive D is Applns
Volume Serial Number is 876D-EFDD
Directory of D:\Applns\oracle\oradata\TEST
05/10/2011 10:54 AM 7,061,504 CONTROL01.CTL
05/05/2011 08:03 AM 7,061,504 CONTROL02.CTL
05/05/2011 08:03 AM 7,061,504 CONTROL03.CTL
3 File(s) 21,184,512 bytes
0 Dir(s) 45,936,365,568 bytes free
D:\Applns\oracle\oradata\TEST>del CONTROL02.CTL
D:\Applns\oracle\oradata\TEST>del CONTROL03.CTL
D:\Applns\oracle\oradata\TEST>copy CONTROL01.CTL CONTROL02.CTL
1 file(s) copied.
D:\Applns\oracle\oradata\TEST>copy CONTROL01.CTL CONTROL03.CTL
1 file(s) copied.
Don't forget to put back these entries in your pfile.
*.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl','D:\Applns\oracle\oradata\TEST\control02.ctl','D:\Applns\oracle\oradata\TEST\control03.ctl'
Let's shutdown and start database with new pfile and create spfile.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='D:\Applns\oracle\product\10.2.0\db_1\database\initTEST.ora';
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\APPLNS\ORACLE\ORADATA\TEST\
CONTROL01.CTL, D:\APPLNS\ORACL
E\ORADATA\TEST\CONTROL02.CTL,
D:\APPLNS\ORACLE\ORADATA\TEST\
CONTROL03.CTL
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
So that was it !!! . Let's try a new connection.
D:\Applns\oracle\oradata\TEST>sqlplus scott/tiger@test
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 10 11:01:59 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> alter user scott identified by tiger;
SQL> alter user scott account unlock;
D:\Applns\oracle\oradata\TEST>sqlplus scott/tiger@test
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 10 11:03:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
It worked !!! . Happy Reading...
Published with Blogger-droid v1.6.9
0 comments:
Post a Comment