8 Replies Latest reply: Feb 18, 2013 6:37 AM by Paul M. RSS

    problem in connecting with database after installation

    991467
      Hi team..
      this is the first time im stating database after installation.
      as per the documentation i tried to connect, but its showing me these errors specified below.
      i have installed oracle 11g release 2 express edition.
      kindly help me to solve this. its urgent, i need to prepare for my exam.


      SQL> connect
      Enter user-name: SYSTEM
      Enter password:
      ERROR:
      ORA-01033: ORACLE initialization or shutdown in progress
      Process ID: 0
      Session ID: 0 Serial number: 0


      SQL> select status, database_status from v$instance;
      SP2-0640: Not connected

      SQL> sqlplus / nolog
      SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.
      SQL> connect / as sysdba
      Connected.
      SQL>
        • 1. Re: problem in connecting with database after installation
          asahide
          Hi,

          You have to startup instance by sysdba user.
          Before startup, you can set ORACLE_SID=XE in your env.

          Regards,
          • 2. Re: problem in connecting with database after installation
            991467
            do i need to set the environment variable? im a starter, kindly tell me exactly what should i specify for the path name and path value.
            • 3. Re: problem in connecting with database after installation
              asahide
              Hi,

              What is your OS? linux or windows?

              $ export ORACLE_SID=XE
              $ sqlplus '/as sysdba'
              SQL> startup
              Regards,
              • 4. Re: problem in connecting with database after installation
                991467
                further i searched in google and tried performing these commands also,

                SQL> select status, database_status from v$instance;

                STATUS DATABASE_STATUS
                ------------ -----------------
                STARTED ACTIVE

                SQL> select open_mode from v$database;
                select open_mode from v$database
                *
                ERROR at line 1:
                ORA-01507: database not mounted


                SQL> alter database mount;
                alter database mount
                *
                ERROR at line 1:
                ORA-00205: error in identifying control file, check alert log for more info


                but i got error..
                so i checked alert_xe.log file..
                the content of alert log is

                Mon Feb 18 11:50:00 2013
                Starting ORACLE instance (normal)
                LICENSE_MAX_SESSION = 0
                LICENSE_SESSIONS_WARNING = 0
                Shared memory segment for instance monitoring created
                Picked latch-free SCN scheme 2
                Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                Autotune of undo retention is turned on.
                IMODE=BR
                ILAT =18
                LICENSE_MAX_USERS = 0
                SYS auditing is disabled
                Starting up:
                Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                Using parameter settings in client-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\CONFIG\SCRIPTS\INIT.ORA on machine HOME
                System parameters with non-default values:
                sessions = 172
                memory_target = 320M
                control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                compatible = "11.2.0.0.0"
                db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                db_recovery_file_dest_size= 10G
                undo_management = "AUTO"
                undo_tablespace = "UNDOTBS1"
                remote_login_passwordfile= "EXCLUSIVE"
                dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                shared_servers = 4
                job_queue_processes = 4
                audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                db_name = "XE"
                open_cursors = 300
                diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                Mon Feb 18 11:50:11 2013
                PMON started with pid=2, OS id=3220
                Mon Feb 18 11:50:11 2013
                PSP0 started with pid=3, OS id=3520
                Mon Feb 18 11:50:12 2013
                VKTM started with pid=4, OS id=976 at elevated priority
                VKTM running at (10)millisec precision with DBRM quantum (100)ms
                Mon Feb 18 11:50:12 2013
                GEN0 started with pid=5, OS id=184
                Mon Feb 18 11:50:13 2013
                DIAG started with pid=6, OS id=3636
                Mon Feb 18 11:50:13 2013
                DBRM started with pid=7, OS id=3344
                Mon Feb 18 11:50:13 2013
                DIA0 started with pid=8, OS id=3516
                Mon Feb 18 11:50:13 2013
                MMAN started with pid=9, OS id=516
                Mon Feb 18 11:50:13 2013
                DBW0 started with pid=10, OS id=2864
                Mon Feb 18 11:50:13 2013
                LGWR started with pid=11, OS id=2904
                Mon Feb 18 11:50:13 2013
                CKPT started with pid=12, OS id=2664
                Mon Feb 18 11:50:13 2013
                RECO started with pid=14, OS id=1244
                Mon Feb 18 11:50:13 2013
                MMON started with pid=15, OS id=3720
                Mon Feb 18 11:50:13 2013
                starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                starting up 4 shared server(s) ...
                ORACLE_BASE from environment = C:\oraclexe\app\oracle
                Mon Feb 18 11:50:13 2013
                SMON started with pid=13, OS id=712
                Mon Feb 18 11:50:13 2013
                MMNL started with pid=16, OS id=2456
                Mon Feb 18 11:50:16 2013
                kcbztek_populate_tbskey: db key in controlfile and datafile 1 is inconsistent..
                Mon Feb 18 11:50:17 2013
                Create controlfile reuse set database "XE"
                MAXINSTANCES 8
                MAXLOGHISTORY 1
                MAXLOGFILES 16
                MAXLOGMEMBERS 3
                MAXDATAFILES 100
                Datafile
                'C:\oraclexe\app\oracle\oradata\XE\system.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\undotbs1.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\sysaux.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\users.dbf'
                LOGFILE
                GROUP 1 SIZE 51200K,
                GROUP 2 SIZE 51200K,
                RESETLOGS
                WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
                Default Temporary Tablespace will be necessary for a locally managed database in future release
                Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_832.trc:
                ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\oradata\XE\system.dbf'
                ORA-27041: unable to open file
                OSD-04002: unable to open file
                O/S-Error: (OS 2) The system cannot find the file specified.
                ORA-1503 signalled during: Create controlfile reuse set database "XE"
                MAXINSTANCES 8
                MAXLOGHISTORY 1
                MAXLOGFILES 16
                MAXLOGMEMBERS 3
                MAXDATAFILES 100
                Datafile
                'C:\oraclexe\app\oracle\oradata\XE\system.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\undotbs1.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\sysaux.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\users.dbf'
                LOGFILE
                GROUP 1 SIZE 51200K,
                GROUP 2 SIZE 51200K,
                RESETLOGS...
                Shutting down instance (immediate)
                Shutting down instance: further logons disabled
                Stopping background process MMNL
                Stopping background process MMON
                License high water mark = 1
                All dispatchers and shared servers shutdown
                ALTER DATABASE CLOSE NORMAL
                ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
                ARCH: Archival disabled due to shutdown: 1089
                Shutting down archive processes
                Archiving is disabled
                ARCH: Archival disabled due to shutdown: 1089
                Shutting down archive processes
                Stopping background process VKTM
                Archiving is disabled
                Mon Feb 18 11:50:23 2013
                Instance shutdown complete
                Mon Feb 18 11:50:26 2013
                Starting ORACLE instance (normal)
                LICENSE_MAX_SESSION = 0
                LICENSE_SESSIONS_WARNING = 0
                Picked latch-free SCN scheme 2
                Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                Autotune of undo retention is turned on.
                IMODE=BR
                ILAT =18
                LICENSE_MAX_USERS = 0
                SYS auditing is disabled
                Starting up:
                Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                Using parameter settings in client-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\CONFIG\SCRIPTS\INITXETEMP.ORA on machine HOME
                System parameters with non-default values:
                sessions = 172
                memory_target = 320M
                control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                compatible = "11.2.0.0.0"
                db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                db_recovery_file_dest_size= 10G
                undo_management = "AUTO"
                undo_tablespace = "UNDOTBS1"
                remote_login_passwordfile= "EXCLUSIVE"
                dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                shared_servers = 4
                audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                db_name = "XE"
                open_cursors = 300
                diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                Mon Feb 18 11:50:34 2013
                PMON started with pid=2, OS id=3244
                Mon Feb 18 11:50:34 2013
                PSP0 started with pid=3, OS id=3136
                Mon Feb 18 11:50:35 2013
                VKTM started with pid=4, OS id=2924 at elevated priority
                VKTM running at (10)millisec precision with DBRM quantum (100)ms
                Mon Feb 18 11:50:35 2013
                GEN0 started with pid=5, OS id=3784
                Mon Feb 18 11:50:35 2013
                DBRM started with pid=7, OS id=3700
                Mon Feb 18 11:50:35 2013
                DIA0 started with pid=8, OS id=4080
                Mon Feb 18 11:50:35 2013
                DIAG started with pid=6, OS id=3828
                Mon Feb 18 11:50:35 2013
                MMAN started with pid=9, OS id=3944
                Mon Feb 18 11:50:36 2013
                DBW0 started with pid=10, OS id=2948
                Mon Feb 18 11:50:36 2013
                LGWR started with pid=11, OS id=2612
                Mon Feb 18 11:50:36 2013
                CKPT started with pid=12, OS id=2616
                Mon Feb 18 11:50:36 2013
                SMON started with pid=13, OS id=3808
                Mon Feb 18 11:50:36 2013
                RECO started with pid=14, OS id=836
                Mon Feb 18 11:50:36 2013
                starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                starting up 4 shared server(s) ...
                ORACLE_BASE from environment = C:\oraclexe\app\oracle
                Mon Feb 18 11:50:36 2013
                Create controlfile reuse set database "XE"
                MAXINSTANCES 8
                MAXLOGHISTORY 1
                MAXLOGFILES 16
                MAXLOGMEMBERS 3
                MAXDATAFILES 100
                Datafile
                'C:\oraclexe\app\oracle\oradata\XE\system.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\undotbs1.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\sysaux.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\users.dbf'
                LOGFILE
                GROUP 1 SIZE 51200K,
                GROUP 2 SIZE 51200K,
                RESETLOGS
                WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
                Default Temporary Tablespace will be necessary for a locally managed database in future release
                Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_3680.trc:
                ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\oradata\XE\system.dbf'
                ORA-27041: unable to open file
                OSD-04002: unable to open file
                O/S-Error: (OS 2) The system cannot find the file specified.
                ORA-1503 signalled during: Create controlfile reuse set database "XE"
                MAXINSTANCES 8
                MAXLOGHISTORY 1
                MAXLOGFILES 16
                MAXLOGMEMBERS 3
                MAXDATAFILES 100
                Datafile
                'C:\oraclexe\app\oracle\oradata\XE\system.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\undotbs1.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\sysaux.dbf',
                'C:\oraclexe\app\oracle\oradata\XE\users.dbf'
                LOGFILE
                GROUP 1 SIZE 51200K,
                GROUP 2 SIZE 51200K,
                RESETLOGS...
                Stopping background process MMNL
                Mon Feb 18 11:50:36 2013
                MMON started with pid=15, OS id=3460
                Mon Feb 18 11:50:36 2013
                MMNL started with pid=16, OS id=660
                Stopping background process MMON
                Starting background process MMON
                Starting background process MMNL
                ALTER SYSTEM enable restricted session;
                alter database "XE" open resetlogs
                ORA-1507 signalled during: alter database "XE" open resetlogs...
                alter database drop logfile group 3
                ORA-1507 signalled during: alter database drop logfile group 3...
                ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oraclexe\app\oracle\oradata\XE\temp.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
                ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oraclexe\app\oracle\oradata\XE\temp.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED...
                ALTER SYSTEM disable restricted session;
                kwqiconfy: Warning AQ Kchunk open notifier failed with 1219
                Mon Feb 18 11:50:37 2013
                MMON started with pid=15, OS id=780
                Mon Feb 18 11:50:38 2013
                Shutting down instance (immediate)
                Mon Feb 18 11:50:37 2013
                MMNL started with pid=16, OS id=1932
                Shutting down instance: further logons disabled
                Stopping background process MMNL
                Stopping background process MMON
                License high water mark = 1
                All dispatchers and shared servers shutdown
                ALTER DATABASE CLOSE NORMAL
                ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
                ARCH: Archival disabled due to shutdown: 1089
                Shutting down archive processes
                Archiving is disabled
                ARCH: Archival disabled due to shutdown: 1089
                Shutting down archive processes
                Stopping background process VKTM
                Archiving is disabled
                Mon Feb 18 11:50:45 2013
                Instance shutdown complete
                Mon Feb 18 11:50:47 2013
                Starting ORACLE instance (normal)
                LICENSE_MAX_SESSION = 0
                LICENSE_SESSIONS_WARNING = 0
                Picked latch-free SCN scheme 2
                Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                Autotune of undo retention is turned on.
                IMODE=BR
                ILAT =18
                LICENSE_MAX_USERS = 0
                SYS auditing is disabled
                Starting up:
                Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                Using parameter settings in server-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA
                System parameters with non-default values:
                sessions = 172
                spfile = "C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"
                memory_target = 320M
                control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                compatible = "11.2.0.0.0"
                db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                db_recovery_file_dest_size= 10G
                undo_management = "AUTO"
                undo_tablespace = "UNDOTBS1"
                remote_login_passwordfile= "EXCLUSIVE"
                dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                shared_servers = 4
                job_queue_processes = 4
                audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                db_name = "XE"
                open_cursors = 300
                diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                Mon Feb 18 11:50:56 2013
                PMON started with pid=2, OS id=2692
                Mon Feb 18 11:50:56 2013
                PSP0 started with pid=3, OS id=1240
                Mon Feb 18 11:50:57 2013
                VKTM started with pid=4, OS id=2736 at elevated priority
                VKTM running at (10)millisec precision with DBRM quantum (100)ms
                Mon Feb 18 11:50:57 2013
                GEN0 started with pid=5, OS id=3400
                Mon Feb 18 11:50:57 2013
                DIAG started with pid=6, OS id=2620
                Mon Feb 18 11:50:57 2013
                DBRM started with pid=7, OS id=1108
                Mon Feb 18 11:50:57 2013
                DIA0 started with pid=8, OS id=1736
                Mon Feb 18 11:50:57 2013
                MMAN started with pid=9, OS id=2824
                Mon Feb 18 11:50:57 2013
                DBW0 started with pid=10, OS id=2036
                Mon Feb 18 11:50:57 2013
                LGWR started with pid=11, OS id=3536
                Mon Feb 18 11:50:58 2013
                SMON started with pid=13, OS id=632
                Mon Feb 18 11:50:58 2013
                RECO started with pid=14, OS id=352
                Mon Feb 18 11:50:58 2013
                starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                starting up 4 shared server(s) ...
                Mon Feb 18 11:50:57 2013
                CKPT started with pid=12, OS id=848
                ORACLE_BASE from environment = C:\oraclexe\app\oracle
                Mon Feb 18 11:50:58 2013
                MMON started with pid=15, OS id=2328
                Mon Feb 18 11:50:58 2013
                MMNL started with pid=16, OS id=2468
                Mon Feb 18 11:50:59 2013
                ALTER DATABASE MOUNT
                ORA-00210: cannot open the specified control file
                ORA-00202: control file: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF'
                ORA-27041: unable to open file
                OSD-04002: unable to open file
                O/S-Error: (OS 2) The system cannot find the file specified.
                ORA-205 signalled during: ALTER DATABASE MOUNT...
                Mon Feb 18 11:50:59 2013
                Checker run found 1 new persistent data failures
                Mon Feb 18 12:50:55 2013
                alter database mount
                Mon Feb 18 12:50:55 2013
                ORA-00210: cannot open the specified control file
                ORA-00202: control file: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF'
                ORA-27041: unable to open file
                OSD-04002: unable to open file
                O/S-Error: (OS 2) The system cannot find the file specified.
                ORA-205 signalled during: alter database mount...



                -------anybody pls give me a solution
                • 5. Re: problem in connecting with database after installation
                  991467
                  windows xp 32-bit
                  • 6. Re: problem in connecting with database after installation
                    asahide
                    hi,
                    Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_832.trc:
                    ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\oradata\XE\system.dbf'
                    ORA-27041: unable to open file
                    OSD-04002: unable to open file
                    O/S-Error: (OS 2) The system cannot find the file specified.
                    ORA-1503 signalled during: Create controlfile reuse set database "XE"
                    It seems that your installation is not succeeded.
                    Check here "C:\oraclexe\app\oracle\oradata\XE\" and re-install.

                    Regards,
                    • 7. Re: problem in connecting with database after installation
                      991467
                      hi,

                      i tried doing as u said, it results like this

                      SQL> connect / as sysdba
                      Connected.
                      SQL> set ORACLE_SID=XE
                      SP2-0735: unknown SET option beginning "ORACLE_SID..."
                      SQL>

                      ......wat to do next kind... kindly help me...
                      • 8. Re: problem in connecting with database after installation
                        Paul M.
                        SQL> set ORACLE_SID=XE
                        That's not a Sql command, but an OS one, you have to execute it at OS prompt before starting Sql*Plus.

                        As asahide said, it looks like something did go wrong with installation. Do you have a database ? What's there in C:\ORACLEXE\APP\ORACLE\ORADATA\XE ?