ORACLE - ORA-00205: error in identifying control file

[root@cacti XE]# su - oracle

-bash-4.1$ ls

admin  connect  diag  exit  fast_recovery_area  oradata  oradiag_oracle  product

-bash-4.1$ cd oradata/

-bash-4.1$ cd XE/

-bash-4.1$ ls

control.dbf  sysaux.dbf  system.dbf  temp.dbf  undotbs1.dbf  users.dbf

-bash-4.1$ ll

total 1285700

-rw-r-----. 1 oracle dba   9748480 Dec 10 07:11 control.dbf

-rw-r-----. 1 oracle dba 744497152 Dec 10 07:10 sysaux.dbf

-rw-r-----. 1 oracle dba 377495552 Dec 10 07:10 system.dbf

-rw-r-----. 1 oracle dba  20979712 Dec  9 22:00 temp.dbf

-rw-r-----. 1 oracle dba  78651392 Dec 10 07:05 undotbs1.dbf

-rw-r-----. 1 oracle dba 104865792 Dec  8 12:41 users.dbf

-bash-4.1$ pwd

/u01/app/oracle/oradata/XE

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 11 10:47:30 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-00205: error in identifying control file, check alert log for more info

SQL> alter system set control_files='/u01/app/oracle/oradata/XE/control.dbf' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size     2228080 bytes

Variable Size   322961552 bytes

Database Buffers   192937984 bytes

Redo Buffers     3809280 bytes

Database mounted.

Database opened.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

VERSION   STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS   SHU DATABASE_STATUS INSTANCE_ROLE     ACTIVE_ST BLO EDITION

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

      1 XE

cacti.siltechconsult.com.br

11.2.0.2.0   11-DEC-14 OPEN NO       1 STOPPED

ALLOWED    NO  ACTIVE PRIMARY_INSTANCE   NORMAL    NO  XE

SQL> 

Read More

ORACLE - Swiss penknife QUERY

Swiss penknife QUERY

SHOW AND KILL USER CONNECTED 

SQL> alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='TESTE';

SHOW SIZE BY DATAFILE

SQL> select file_id, block_id, blocks*8192/1024 MB,

owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"

from  sys.dba_Extents

where file_id = &&fileid

union

select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P

osition MB"

from sys.dba_free_space

where file_id = &fileid

order by 1,2,3

/

SHOW INFO BY OBJECTS

SQL> select owner, object_name, object_type, status from all_objects where object_name='TSILGT';

OWNER       OBJECT_NAME       OBJECT_TYPE   STATUS

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

TREINA       STP_GRAVATABLOG       PROCEDURE   VALID

SANKHYA        STP_GRAVATABLOG       PROCEDURE   VALID

WMS       STP_GRAVATABLOG       PROCEDURE   VALID

TESTE       STP_GRAVATABLOG       PROCEDURE   INVALID

SQL> alter procedure teste.STP_GRAVATABLOG compile;

Warning: Procedure altered with compilation errors.

SQL> show error

Errors for PROCEDURE TESTE.STP_GRAVATABLOG:

LINE/COL ERROR

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

33/5 PL/SQL: SQL Statement ignored

46/9 PL/SQL: ORA-00942: table or view does not exist

SQL> SELECT text FROM all_source WHERE name = 'STP_GRAVATABLOG'  and owner='TESTE' ORDER BY line;

SHOW SIZE BY TABLE OR SEGMENTS

SQL>select sum(bytes/1024/1024) as Tamanho_MB from dba_segments

where owner = 'SVP'

and segment_type = 'TABLE'

and segment_name in ('TABLE_1','TABLE_2','TABLE_N')

SQL>select onwer, segment_name, segment_type, tablespace_name, bytes/1024/1024 

from dba_segments where segment_name='TABLE_NAME';

SHOW DATAFILES INFO GENERAL/STATUS 

SQL> select tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY/1024/1024 nextmb from dba_data_files;

TABLESPACE FILE_NAME MBYTES AUT   MAXBYTES   NEXTMB

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

USERS   /u01/app/oracle/oradata/XE/users.dbf    100 YES 11264 .001220703

SYSAUX   /u01/app/oracle/oradata/XE/sysaux.dbf   710 YES 32767.9844 .001220703

UNDOTBS1   /u01/app/oracle/oradata/XE/undotbs1.dbf     75 YES 32767.9844 .000610352

SYSTEM   /u01/app/oracle/oradata/XE/system.dbf   360 YES   600 .001220703

SHOW ALL INX / REBUILD

SQL>select 'alter index ' || owner || '.' || index_name ||' rebuild;'
from dba_indexes
where status ='UNUSABLE'
order by index_name
/

in construction ... 

Read More

ORACLE - ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

-bash-4.1$ oratop siltech/123456@XE

oratop: Release 13.2.4 Production on Mon Dec  8 12:26:17 2014

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

Processing ...

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

-bash-4.1$ 

-bash-4.1$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:25:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=XE))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                08-DEC-2014 11:32:30

Uptime                    0 days 0 hr. 53 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/cacti/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=XE))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.200)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

The listener supports no services

The command completed successfully

-bash-4.1$ 

-bash-4.1$ lsnrctl servite XE

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:33:01

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

NL-00853: undefined command "servite".  Try "help"

-bash-4.1$ lsnrctl service XE

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:33:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.200)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

The listener supports no services

The command completed successfully

-bash-4.1$ 

-bash-4.1$ sqlplus siltech/123456

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 12:46:02 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show parameter service_names;

NAME     TYPE VALUE

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

service_names     string XE

SQL> 

x-bash-4.1$ pwd

/u01/app/oracle/product/11.2.0/xe/network/admin

-bash-4.1$ cat listener.ora 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.20.30.200 )(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (XE)

-bash-4.1$ 

-bash-4.1$ vim listener.ora 

-bash-4.1$ cat listener.ora 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = XE)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)

)

         )

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.20.30.200 )(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (XE)

-bash-4.1$ 

-bash-4.1$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 16:33:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

The command completed successfully

-bash-4.1$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 16:33:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                08-DEC-2014 16:07:07

Uptime                    0 days 0 hr. 26 min. 2 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/cacti/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

Services Summary...

Service "XE" has 1 instance(s).

  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

-bash-4.1$ 

-bash-4.1$ sqlplus siltech/123456@XE

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 16:34:26 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> 

        

          

Read More

ORACLE - ORA-00845: MEMORY_TARGET not supported on this system

-bash-4.1$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 11:43:29 2014

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

SQL> connect sys as sysdba

Enter password: 

Connected to an idle instance.

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

SQL> exit

Disconnected

-bash-4.1$ cd $ORACLE_HOME

-bash-4.1$ cd dbs/

-bash-4.1$ ll

total 20

-rw-rw----. 1 oracle dba 1544 Dec  8 10:52 hc_XE.dat

-rwxr-xr-x. 1 oracle dba 2851 Aug 29  2011 init.ora

-rw-r-----. 1 oracle dba   24 Nov 11 11:42 lkXE

-rw-r-----  1 oracle dba 1536 Dec  8 10:50 orapwXE

-rw-r-----  1 oracle dba 2560 Dec  8 10:52 spfileXE.ora

-bash-4.1$ 

-bash-4.1$ cp spfileXE.ora spfileXE.ora.bkp

-bash-4.1$ ll

total 28

-rw-rw----. 1 oracle dba 1544 Dec  8 12:01 hc_XE.dat

-rwxr-xr-x  1 oracle dba 2851 Dec  8 11:54 init.ora

-rw-r-----. 1 oracle dba   24 Nov 11 11:42 lkXE

-rw-r-----  1 oracle dba 1536 Dec  8 10:50 orapwXE

-rw-r-----  1 oracle dba  881 Dec  8 11:57 spfileXE.ora

-rw-r-----  1 oracle dba 2560 Dec  8 11:56 spfileXE.ora.bkp

BEFORE EDIT

-bash-4.1$ cat spfileXE.ora.bkp 

C"???3CC2XE.__db_cache_size=197132288

XE.__java_pool_size=4194304

XE.__large_pool_size=4194304

XE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

XE.__pga_aggregate_target=272629760

XE.__sga_target=515899392

XE.__shared_io_pool_size=0

XE.__shared_pool_size=301989888

XE.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/XE/adump'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/XE/control.dbf'

*.db_name='XE'

*.DB_RECOVERY_FILE_DEST='/u01/app/oraclCC"e/fast_recovery_area'

*.DB_RECOVERY_FILE_DEST_SIZE=10G

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'

*.job_queue_processes=4

*.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=cacti.siltechconsult.com.br) (PORT=1522))'

*.memory_target=786432000

*.open_cursors=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=20

*.shared_servers=4

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

CC"GeCC"FeC-bash-4.1$ 

-bash-4.1$ vim spfileXE.ora

LATER EDIT 

-bash-4.1$ cat spfileXE.ora

*.audit_file_dest='/u01/app/oracle/admin/XE/adump'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/XE/control.dbf'

*.db_name='XE'

*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'

*.DB_RECOVERY_FILE_DEST_SIZE=10G

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'

*.job_queue_processes=4

*.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=cacti.siltechconsult.com.br) (PORT=1522))'

*.memory_target=500M

*.open_cursors=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=20

*.shared_servers=4

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

-bash-4.1$ 

-bash-4.1$ pwd

/u01/app/oracle/product/11.2.0/xe/dbs

-bash-4.1$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 12:03:40 2014

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

SQL> connect sys as sysdba

Enter password: 

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora';

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size     2228080 bytes

Variable Size   314572944 bytes

Database Buffers   201326592 bytes

Redo Buffers     3809280 bytes

Database mounted.

Database opened.

SQL> 

Read More