how to run OS commands in the sqlplus command prompt

For example, you want to list all the directories from /home/oracle directory. To do that run the below command in sql prompt:

SQL> host ls /home/oracle

a Documents links.txt Pictures Templates afiedt.buf Downloads Music psit test Desktop labs oradiag_oracle Public Videos Continue reading

Advertisements

Leave a comment

Filed under Oracle

oracle 11g- emctl start dbconsole fails on standalone grid

When trying to start dbconsole, I got below error on my linux server

bash-3.00$emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

Starting Oracle Enterprise Manager 11g Database Control…………….
………………………………………………………………………………… failed.

Please verify the targets.xml file and notice all the information are correct. If no values in it, then copy the information”agentseed”, EMD_URL and host from your emd.properties file(located under $ORACLE_HOME/<instance name>/sysman/config directory) and add in the targets.xml.

E.g:

<Targets AGENT_SEED=”259930272″>
<Target TYPE=”oracle_emd” NAME=”oracle.test.com:1158″/>
<Target TYPE=”host” NAME=”oracle.test.com”/>
</Targets>

Then, please ensure that database and listener is up and running and run the below commands from oracle Grid home bin directory (e.g in my case /u01/app/oracle/product/11.2.0/grid/bin

emctl stop dbconsole
emca -repos drop
emca -repos create
emca -config dbcontrol db

Leave a comment

Filed under Oracle

check the content of zip file in linux

On linux server, you can run either “unzip -l” or “zipinfo”

e.g $ zipinfo data.zip
Archive:  data.zip   363181 bytes   10 files
-rw-r–r–  2.3 unx      404 tx defN 26-Aug-14 02:51 auser.xml
-rw-r–r–  2.3 unx   206354 tx defN 18-Jul-13 08:12 full_test.xml
-rw-r–r–  2.3 unx     2212 bx defN 13-Oct-14 07:28 identity.jks
-rw-r–r–  2.3 unx      846 tx defN  4-Feb-14 06:00 a2-.xml
-rw-r–r–  2.3 unx    22624 tx defN 19-Feb-15 07:35 list
-rwxrwxrwx  2.3 unx    15327 tx defN 19-Aug-14 05:58 l.sh
-rwxr-x—  2.3 unx   364982 bx defN  6-Mar-15 04:08 d.jar
-rw-r–r–  2.3 unx     1197 tx defN 19-Aug-14 10:00 l.ldif
-rw-r–r–  2.3 unx     2173 tx defN 10-Mar-15 03:26 1.properties
-rw-r–r–  2.3 unx      372 tx defN 19-Aug-14 09:55 5.ldif
10 files, 616491 bytes uncompressed, 361679 bytes compressed:  41.3%

$ unzip -l data.zip
Archive:  data.zip
Length     Date   Time    Name
——–    —-   —-    —-
404  08-26-14 02:51   auser.xml
206354  07-18-13 08:12   full_test.xml
2212  10-13-14 07:28   identity.jks
846  02-04-14 06:00   a2.xml
22624  02-19-15 07:35   list
15327  08-19-14 05:58   l.sh
364982  03-06-15 04:08   d.jar
1197  08-19-14 10:00   l.ldif
2173  03-10-15 03:26   1.properties
372  08-19-14 09:55   5.ldif
——–                   ——-
616491                   10 files

Leave a comment

Filed under Linux

oracle 11g – Location of SPFILE or PFILE

Default location of spfile or pfile is $ORACLE_HOME/dbs, however if you are not sure, then run the below command using sqlplus:

SQL> show parameter spfile;

Name                       Type                                           Value
————–               ——————                    —————————–
spfile                         string                                /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

With ASM, the spfile is often located into an ASM disk group.

Leave a comment

Filed under Oracle

how to figure out the issues with flash recovery area in oracle database.

Space usage in the flash recovery area is automatically monitored by Oracle database. Warning messages are written to the alert logs when the space usage has reached 85% and a critical warning is issued when the space usage nears 97%. Oracle also removes files that are on the obsolete file list when there is less than 10 percent free space available. Alternatively dba_outstanding_alerts view can be queried to get the details about the warnings and suggested actions:

SQL> SELECT OBJECT_TYPE, MESSAGE_TYPE, MESSAGE_LEVEL,
REASON, SUGGESTED_ACTION
FROM DBA_OUTSTANDING_ALERTS;

Leave a comment

Filed under Oracle

Oracle database – What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. The data file is at a prior point of time than the current database.

Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time, or roll-forward until the last transaction recorded in the log files.

Leave a comment

Filed under Oracle

Oracle database – location of alert log

You can run the below sql command to find the alert log location of your database instance:

SQL> select value from v$parameter where name=’background_dump_dest’;

VALUE
——————————————————————————–
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle trace]$ ls
alert_orcl.log       orcl_arc2_6924.trc  orcl_cjq0_4073.trc  orcl_dbw0_4004.trc   orcl_j001_3624.trc

or run the below sql command:

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
———————————— ———– ——————————
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Leave a comment

Filed under Oracle