Get "The database status is currently unavailable..." when opening OEM

when opening OEM, got this error message :
" The database status is currently unavailable. It is possible that the database is in mount or nomount state. Click 'Startup' to obtain the current status and open the database. If the database cannot be opened, click 'Perform Recovery' to perform an appropriate recovery operation."

first, check the database's status :

[~]$ srvctl status database -d database_name

if the instance is running, check if it's in open or mount state :

SQL>select status from v$instance;

if the status is OPEN, the database is fine.


One reason why the OEM cannot be opened is :
because the password of SYSMAN and DBSNMP are different from the password that created during installation.
And changing the password cannot just use the ALTER command.

therefore, SYSMAN and DBSNMP password changes can be done by using the ALTER command and change the configuration file.

Change SYSMAN's password :

• turn off all the OMS

[~]$ emctl stop dbconsole                                                            

• make sure that the OMS are turned off

[~]$ emctl status agent                                                                

• connect to the database, change SYSMAN's password using ALTER command

SQL> alter user SYSMAN identified by [new_password];               

• check if SYSMAN is unlocked by login itnto database. if we got ORA-28000 : account is locked, then we have to unlock it

  SQL> alter user SYSMAN account unlock;                                      

• after we are sure that SYSMAN is unlocked, then we'll edit emoms.properties file in $ORACLE_HOME/[HOST]_[SID]/sysman/config directory. Don't forget to copy the original file as backup.

• Parameter that must be changed are :

1. oracle.sysman.eml.mntr.emdRepPwd=[new_password]
2. oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

no need to worry because the password has not been encrypted,     because once DBConsole and Oracle Agent on the restart, the password will be immediately encrypted and oracle.sysman.eml.mntr.emdRepPwdEncrypted will return to TRUE.

• startup DBConsole

[~]$ emctl start dbconsole
                                                        

then, we have to change the DBSNMP's password :

the procedures are similar with SYSMAN's changing password, but file that has to be edited is different.

here, we have to edit targets.xml file which is placed in $ORACLE_HOME/[HOST]_[SID]/sysman/emd directory. Don't forget to make a copy as backup.

Parameters that have to be edited are :

Property NAME="password" VALUE="[encrypted password]" ENCRYPTED="TRUE"

change [encrypted password] with new password and TRUE into FALSE. Password is encrypted and value of ENCRYPTED will return to TRUE after DBConsole and Oracle Agent are restarted.

startup the DBConsole then we can access the OEM...

Deleting ASM Files : Hapus ASM files lewat console

1. Set ORACLE_HOME ke tempat grid diinstal, misalnya :
[oracle@node1 ~]$ export ORACLE_HOME=/u01/grid

2. Set ORACLE_SID dengan ASM instance-nya. bila tidak tahu nama ASM instance, bisa dilihat dari LSNRCTL STATUS
[oracle@node1 ~]$ lsnrctl status

kemudian set ORACLE_SIDnya :
[oracle@node1 ~]$ export ORACLE_SID=namaASMinstance

3. Masuk ke ASM instance :
[oracle@node1 ~]$ asmcmd
ASMCMD>

4. Untuk menghapus file2 ASMnya, masuk ke direktori tempat file2 ASM itu berada, kemudian hapus file yang dimaksud dengan menggunakan perintah linux :
ASMCMD> rm 2010_11_01/* --> menghapus isi dari direktori 2010_11_01

5. Untuk melihat isi direktori, bisa menggunakan perintah ls seperti biasa :
ASMCMD>ls

6. Menghapus file ASM dalam 1 direktori tidak bisa dilakukan melalui OEM.

Menyembunyikan Tomcat Version di Default Error Page

untuk menyembunyikan versi Tomcat dari error page,bisa dilakukan dengan cara :

• buat direktori baru org/apache/catalina/util di $CATALINA_HOME/lib:

mkdir -p $CATALINA_HOME/lib/org/apache/catalina/util

• buat file baru ServerInfo.properties di dalam direktori yang baru dibuat:

#vi $CATALINA_HOME/lib/org/apache/catalina/util/ServerInfo.properties

kemudian isi dengan :

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

server.info=Apache Tomcat/@VERSION@
server.number=@VERSION_NUMBER@
server.built=@VERSION_BUILT@

untuk server.info bisa diganti sesuai keinginan.

• ubah owner file tersebut sesuai dengan user yang digunakan (selain root):

chown -R user.user $CATALINA_HOME/lib/org/apache/catalina/util/ServerInfo.properties

• restart Tomcat
• hasilnya :

PRCR-1079 pada saat starting database RAC

Pada saat menyalakan database RAC, muncul pesan error :
PRCR-1079 : Failed to start resource ora.simpli.db
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.simpli.db' on 'cluster2' failed
CRS-2632: There are no more servers to try to place resource 'ora.simpli.db' on that would satisfy its placement policy
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.simpli.db' on 'cluster1' failed

ORA-00119 muncul biasanya karena masalah listener, apakah host atau portnya salah. Cek di tnsnames.ora:

(ADDRESS = (PROTOCOL = TCP)(HOST = clustersmi-scan.xyz.com)(PORT = 1521)

karena ini merupakan database RAC, kita mengakses database dengan menggunakan SCAN ip address. Dilihat apakah host pada tnsnames.ora sudah sama dengan SCAN namenya:

[oracle@cluster1 ~]$ srvctl config scan
SCAN name: clustersmi-scan.abcde.com, Network: 1/192.87.58.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /clustersmi-scan.abcde.com/192.87.58.150

ternyata hostname di tnsnames.ora berbeda dengan SCAN name, maka kita edit tnsnames.ora menjadi sama dengan SCAN name.
Begitu juga di pfilenya. Di bagian remote_listener diganti dengan SCAN name:

remote_listener=clustersmi-scan.abcde.com:1521

kemudian kita nyalakan kembali databasenya.
[oracle@cluster1 ~]$ srvctl start database -d simpli
PRCR-1079 : Failed to start resource ora.simpli.db
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.simpli.db' on 'cluster2' failed
CRS-2632: There are no more servers to try to place resource 'ora.simpli.db' on that would satisfy its placement policy
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.simpli.db' on 'cluster1' failed
ternyata masih mendapat error yang sama.

Ubah kembali hostname, remote_listener,host file ke SCAN name pada saat instalasi clusterware, yaitu clustersmi-scan.xyz.com.

di tnsnames.ora :
(ADDRESS = (PROTOCOL = TCP)(HOST = clustersmi-scan.xyz.com)(PORT = 1521))

di remote_listener dalam pfile :
remote_listener=clustersmi-scan.xyz.com:1521

di hosts file :
192.87.58.150 clustersmi-scan.xyz.smi clustersmi-scan
192.87.58.152 clustersmi-scan.xyz.smi clustersmi-scan

kemudian, nyalakan kembali database RACnya :
[oracle@cluster1 ~]$ srvctl start database -d simpli
[oracle@cluster1 ~]$ srvctl status database -d simpli
Instance simpli_1 is running on node cluster1
Instance simpli_2 is running on node cluster2

Database RAC bisa dinyalakan tanpa pesan error. Ini berarti SCAN name pada saat penginstallan yang digunakan. Perubahan SCAN name dengan cara menghapus SCAN kemudian menambahkannya lagi, tidak bisa mengganti SCAN name awal penginstalan meskipun di tnsnames.ora, hosts file, dan pfile sudah diganti dengan SCAN name yang baru.

Mengetahui Lokasi Voting Disk

Untuk mengetahui letak dari Voting Disk, bisa dilakukan dengan perintah :
$ GRID_HOME/bin/crsctl query css votedisk

outputnya :
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 442ef04571544f84bf6b731fbd300026 (ORCL:VOL1) [DATA]
Located 1 voting disk(s).

Tablespace Information

Untuk mengetahui penggunaan dari tablespace di database Oracle melalui console, dapat diperoleh melalui script berikut:

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from  dba_data_files
group  by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from  dba_free_space
group  by TABLESPACE_NAME
)
b
where  a.TABLESPACE_NAME=b.TABLESPACE_NAME
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc

hasilnya :


keterangan :
1. Tablespace : nama dari tablespace
2. Bytes_used : ukuran file yang terpakai dalam bytes
3. Bytes_free : ukuran free space dalam bytes
4. Largest : ukuran terbesar dari free space dalam bytes
5. Percent_used : prosentase dari tablespace yang telah digunakan

ORA-01102 : cannot mount database in EXCLUSIVE mode

ORA-01102 muncul pada saat menghidupkan database, atau pada saat hendak mounting database.
Penyebabnya adalah ada instance lain yang dibuka dalam parallel mode.
Solusinya adalah :
1. Lihat di /u01/app/oracle/admin//bdump/alert_.log. Pada kasus saya, di lognya muncul pesan :

starting up 5 shared server(s) ...
Tue Feb 23 16:56:41 2010
ALTER DATABASE MOUNT
Tue Feb 23 16:56:41 2010
sculkget: failed to lock //u01/app/oracle/product/10.2.0/db_1/dbs/lkSIMPLIBU exclusive
sculkget: lock held by PID: 27415
Tue Feb 23 16:56:41 2010
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 27415

dari log tersebut, terlihat bahwa proses dengan PID 27415 mengunci sculkgetnya.

2. Lihat dengan menggunakan ps ax proses dengan PID 27415 :

27411 ? Ss 0:00 ora_psp0_simplibu
27413 ? Ss 0:00 ora_mman_simplibu
27415 ? Ss 0:00 ora_dbw0_simplibu
27417 ? Ss 0:00 ora_lgwr_simplibu
27419 ? Ss 0:00 ora_ckpt_simplibu
27421 ? Ss 0:00 ora_smon_simplibu

3. Kill proses dengan PID tersebut :

[root@localhost ~]# kill -9 27415

4. Startup kembali instancenya :

SQL > startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Sized 1218292 bytes
Variable Size 75499788 bytes
Database Buffers 83886080 bytes
Redo Buffers 7168000 bytes

SQL > alter database mount;
Database altered.

SQL > alter database open;
Database altered.

Instance bisa dimount dan diopen seperti biasa.