mzh

Solution to Problem: "ORA-01665: control file is not a standby control file"

2015-04-06T11:02:00.000+06:00

Solution to Problem: "ORA-01665: control file is not a standby control file"

The Solution is to put the command to tell dataguard that this is physical standby.

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Steps:

SQL> STARTUP MOUNT

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PRIMARY

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> STARTUP MOUNT

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

So, now the control file is for Physical Standby database.

How to grant select on v$session

2014-11-23T17:23:00.000+06:00

How to grant select on v$session

SQL> grant select on v$session to test;
grant select on v$session to test
ORA-02030: can only select from fixed tables/views

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$SESSION';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------------------------------------------------------------------- -------------------
PUBLIC V$SESSION SYNONYM

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------------------------------------------------------------------- -------------------
SYS V_$SESSION VIEW

SQL> grant select on v_$session to test;
Grant succeeded

EXPDP error ORA-39001: invalid argument value and ORA-01775: looping chain of synonyms

2014-09-17T12:50:00.004+06:00

EXPDP error ORA-39001: invalid argument value and ORA-01775: looping chain of synonyms

After issuing expdp command the following errors were occured:
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

The solution is so simple in this case.

You have to find the synonym which was creating problem.

Issue the following command:

Select owner, object_name, object_type, status

from dba_objects

where object_name like '%SYS_EXPORT_%';

Drop the synonym or all the synonym found in this query.

drop public synonym SYS_EXPORT_SCHEMA_01;

Now run the expdp command again.

=======================================================

Happy to help !!!

MRP: Background Media Recovery process shutdown or NO MRP0 EXISTS !

2014-04-20T12:27:00.000+06:00

MRP: Background Media Recovery process shutdown

The main problem was standby db not applying the archive redo received from production DB. The MRP0 process was missing. When we query the following SQL it returned no rows.

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

no rows returned.

Suspicious list of errors showing on Standby Server alert:

ORA-16401: archive log rejected by Remote File Server (RFS)
MRP: Background Media Recovery process shutdown

To Drill down check the error message in dataguard status:

select severity,
error_code,
message,
to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') "TIMESTAMP"
from v$dataguard_status
order by timestamp desc;

You will find an error like following:
MRP0: Detected orphaned datafiles!

This could be for two reason.

1. There may me UNNAMED datafiles

To Check:

select file#,name from v$datafile where name like '%UNNAMED%';

if no. 1 returns no row then try no. 2.

2. The problem may be for mismatch in Database Incarnation between production and standby.

To check:

Issue this RMAN command in production to check production DB incarnation:

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ORCL 1771320492 PARENT 1 24-AUG-13

2 2 ORCL 1771320492 CURRENT 925702 23-MAR-14

and the production incarnation is:

RMAN> list incarnation of database;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ORCL 1771320492 PARENT 1 24-AUG-13

2 2 ORCL 1771320492 PARENT 925702 23-MAR-14

3 3 ORCL 1771320492 CURRENT 1932062 02-APR-14

Now reset the standby incarnation to match production incurnation:

RMAN> reset database to incarnation 2;

Now check again:

RMAN> list incarnation of database;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ORCL 1771320492 PARENT 1 24-AUG-13

2 2 ORCL 1771320492 CURRENT 925702 23-MAR-14

3 3 ORCL 1771320492 ORPHAN 1932062 02-APR-14

So, its now same.

The next task is to recover the standby database:

RMAN> recover database;

After recovering the standby start the MRP0 process again:

SQL> alter database recover managed standby database disconnect;

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS

---------

MRP0

Else, If no. 1 returns row then try below steps.

I have checked below query outputs:

a.) select file#, error from v$recover_file;

70 FILE MISSING
b.) select file#, name, status from v$datafile;

70 /ora....oracle/product/11.2.0/db_1/dbs/UNNAMED00070 RECOVER

Steps:

1. Backup related file at primary

On primary:
——————–
$ rman target /

RMAN> backup datafile 70 format '/orasoft/cbs_rman/oracle-backup/st1/backdata.bk' tag 'PRIMARY_70';

Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc

3. At the standby site, catalog the backuppiece and confirm it’s available for use:

On standby:
——————–
$ rman target /

RMAN> catalog backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backup of datafile 70;

Stop redo apply on the physical standby database

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

In My case redo apply has been already stopped.

On the standby site restore the datafile:

run below command for restoring datafile 70:

RUN {
SET NEWNAME FOR DATAFILE 70 to '/orasoft/oracle-backup/UNTOTBS00001.dbf';
RESTORE DATAFILE 70;
SWITCH DATAFILE 70;
}

Check status of files:Restart redo apply on the physical standby database

On standby:
——————–
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

So the log apply process is now working again.

ORACLE FLASH RECOVERY AREA USAGE QUERY

2013-12-24T14:00:00.000+06:00

FINDING ORACLE FLASH RECOVERY AREA USAGE

SELECT NAME,
(SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,
((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,
ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

Unblock the agent in OEM 12C by performing an agent resync from the console

2013-09-10T10:56:00.000+06:00

Unblock the agent in OEM 12C by performing an agent resync from the console

I am using OEM 12c. Today my EM page was showing that one of the agent in unreachable. So, first of all I tried to see the status of the agent and it showed me the agent is alright.

[oracle@NODE4 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.2.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /home/dir/oemcc/agent_inst
Agent Binaries : /home/oracle/dir/core/12.1.0.2.0
Agent Process ID : 5159
Parent Process ID : 5044
-
-
-
-
---------------------------------------------------------------
Agent is Running and Ready

Then I tried to upload the agent and found the following error:

[oracle@NODE4bin]$ ./emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: The agent is blocked by the OMS. Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. (AGENT_BLOCKED)

Solution:

Go to agent home page from TARGET -> ALL TARGET.

Then from AGENT menu click Resynchronization.

SETUP - > Manage Cloud Control -> AGENTS

Then from AGENT menu click Resynchronization.

After some time agent is reachable again.

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

How to delete/remove Management Agent from Oracle Enterprise Manager 12C

2013-07-07T16:56:00.001+06:00

1. Before you deinstall a Management Agent, do the following:

a. Stop the Agent using command from Management Agent home:

cd /u01/oemcc_latest/core/12.1.0.2.0/bin/

$ emctl stop agent

b. Wait for the Management Agent to go to the unreachable state in the Cloud Control console.

c. It is mandatory to delete the Management Agent and their monitored targets using any of the following methods:

Remove the Agent target manually from the console:

1. Login to 12C Cloud Control

2. Navigate to Setup => Manage Cloud Control => Agents

3. Go to the Home page of the Agent that you want to remove

4. Expand the drop-down menu near the "Agent"

5. Expand the "Target Setup" option

6. Select "Remove Target"

In Cloud Control Release 12.1.0.2, the following dialog box will be displayed if the Agent is still monitoring targets. Click Continue. You can then remove all targets (using the "Select All" link) in the next page.

You will then be able to remove the host/agent itself.

Note: Ensure that all targets monitored by the Agent are removed including the host target; before proceeding

2. Deinstalling using Graphical Method from OEM:

a.) Invoke the installer from Management Agent home by running the following command:

cd /u01/oemcc_latest/core/12.1.0.2.0/oui/bin

$<AGENT_HOME>/oui/bin/runInstaller -deinstall ORACLE_HOME=<absolute_path_to_agent_home> [-removeallfiles]

Example:

./runInstaller -deinstall ORACLE_HOME=/u01/oemcc_latest/core/12.1.0.2.0 -removeallfiles

b.) In the Installation wizard click on "Installed Products" button.

c.) On the Inventory screen, select the plug-in homes, and click "Remove" button.

d.) On the Inventory screen, select the sbin home, and click "Remove" button.

e.) On the Inventory screen, select the Management Agent, and click "Remove" button.

f.) Remove agent folder

For UNIX platforms:

$ rm -rf <absolute_path_to_agent_base_dir>

Example:

$ rm -rf /u01/app/oracle/product/agent12c

references:

Note-ID 1368088.1

Note-ID 1380846.1

How to Create INCIDENT Package

2013-01-07T10:06:00.002+06:00

How to Create INCIDENT Package

You can create a logical package based on an incident number, a problem number, a problem key, or a time interval.
Create a logical package such that it will be most useful to diagnose the error of your concern.
Following are the method using which an incident package can be created :

Creating package based on incident.
Select correct incident if there are many incidents.

adrci > SHOW INCIDENT
adrci > IPS CREATE PACKAGE INCIDENT incident_number

If there are multiple incident then add each of them using following command:

adrci > IPS ADD INCIDENT incident_number PACKAGE package_number

Once you have created a logical package using any methods, next step is to generate a physical package.

adrci > IPS GENERATE PACKAGE package_number IN path

Here path is the Hard disk location where the ZIP file will be generated.

Note: Please see the metalink note 411.1 for further details.

ADRCI error - DIA-48448: This command does not support multiple ADR homes

2013-01-06T17:58:00.004+06:00

ADRCI error - DIA-48448: This command does not support multiple ADR homes

adrci> show control

DIA-48448: This command does not support multiple ADR homes

This error occurs due to multiple ADR home location. Solution is to set the ADRCI home for the instance you want to work.

adrci> show homes

ADR Homes:

diag/rdbms/DB1/DB1

diag/rdbms/DBISL/DBISL

diag/rdbms/ultisl/ULTISL

diag/tnslsnr/DCDBLIVE-3/listenerc

diag/tnslsnr/DCDBLIVE-3/listeneri

adrci> SET HOME diag/rdbms/DB1/DB1

_________________________________________________________________________________

UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle

2012-12-31T03:49:00.000+06:00

UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle

While importing a dumpfile to a test server, the following error were raised. Through a tough time searching the solution, I discovered the solution from a post.

My datapump command was like this:

-bash-4.1$ impdp system/sys123 schemas=scott directory=db_pump dumpfile=BEF_EOD.dmp

Import: Release 11.2.0.3.0 - Production on Mon Dec 31 03:30:01 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

The problem was the streams_pool_size was 0. So, according to the post I changed the value to 40M.

SQL> show parameter stream

NAME TYPE VALUE

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

streams_pool_size big integer 0

SQL> alter system set streams_pool_size=40M scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup

ORACLE instance started.

...and it was OK.

_________________________________________________________________________________

Installing ORACLE 11g R2 on Linux 6.2

2012-10-15T18:17:00.004+06:00

Installing ORACLE 11g R2 on Linux 6.2

Checking memory

# grep MemTotal /proc/meminfo

# grep SwapTotal /proc/meminfo

# df -h /dev/shm/

# mount -t tmpfs tmpfs -o size=10000m /dev/shm

# df -h /dev/shm/

Install All Packages Needed.

Add entry in host file.

#vi /etc/hosts

Make entry like this:

[IP] [HOST_NAME]

Open /etc/sysctl.conf and add the following lines:

# Oracle settings

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

net.ipv4.tcp_wmem = 262144 262144 262144

net.ipv4.tcp_rmem = 4194304 4194304 4194304

Refresh settings

# /sbin/sysctl -p

# /sbin/sysctl -q

Open /etc/security/limits.conf and add these lines.

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:

SELINUX=disabled

Now setup User that we will use as Oracle owner and the groups that it will need for installing and managing Oracle.

/usr/sbin/groupadd oinstall

/usr/sbin/groupadd dba

/usr/sbin/groupadd oper

/usr/sbin/useradd -g oinstall -G dba,oper oracle

/usr/bin/passwd oracle

Change bash profile for oracle.

Create directories where the Oracle Software and database will be installed.

mkdir -p /app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /app

chmod -R 0775 /app

mkdir /dbdata/oracle/oradata/

_________________________________________________________________________________

Run the Installer now.

RMAN Restoration Failed due to Error: RMAN-03002, RMAN-06026 and RMAN-06023

2012-08-02T11:36:00.000+06:00

While restoring a database using RMAN CLONING, I got the following errors.

________________________________________________________________________________

RMAN> restore database;

Starting restore at 02-AUG-12
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/02/2012 11:16:52
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

_________________________________________________________________________________

After a thorough search, I found the command to check INCURNATION. After changing the incurnation the restore command worked.

RMAN> list incarnation of database;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ULTIMUS 29248347 PARENT 1 17-MAR-06

2 2 ULTIMUS 29248347 PARENT 526290 28-JUL-11

3 3 ULTIMUS 29248347 PARENT 4412812324 12-AUG-11

4 4 ULTIMUS 29248347 PARENT 4413871835 13-AUG-11

5 5 ULTIMUS 29248347 PARENT 5973604228 16-JAN-12

6 6 ULTIMUS 29248347 PARENT 6347087922 17-FEB-12

7 7 ULTIMUS 29248347 PARENT 7790199804 21-JUN-12

8 8 ULTIMUS 29248347 PARENT 7960820014 05-JUL-12

9 9 ULTIMUS 29248347 CURRENT 8047353605 12-JUL-12

As the current incarnation key is nine and its not working, so i tried the previous one, i.e. Inc Key-8.

RMAN> reset database to incarnation 8;

After issuing this command restoration was successful.

Query to check the OPTIMAL UNDO RETENTION

2012-07-15T12:31:00.001+06:00

Query to check the OPTIMAL UNDO RETENTION

_____________________________________________________________________________

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",

SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",

ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"

FROM (SELECT SUM(a.bytes) undo_size

FROM v$datafile a, v$tablespace b, dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.STATUS = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#) d,

v$parameter e,

v$parameter f,

(SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec

FROM v$undostat) g

WHERE e.name = 'undo_retention'

AND f.name = 'db_block_size';

______________________________________________________________________________

tHANKS fOR rEADING tHIS aRTICLE

10g Release 2 (10.2.0.5) Patch Set 4 for Solaris Operating System (x86-64)

2012-07-14T15:41:00.000+06:00

10g Release 2 (10.2.0.5) Patch Set 4 for Solaris Operating System (x86-64)

PART ONE:

Applying Patch___________________________________________________

Step 1: Shut Down Oracle Databases

SQL>shutdown immediate;

 Shut down any existing Oracle Database instances with normal or immediate priority. On Oracle RAC systems, shut down all instances on each node.

Step 2: Stopping All Processes for a Single Instance Installation

Shut down the following Oracle Database 10g processes in the order specified before installing the patch set:

 Shut down all processes in the Oracle home that might be accessing a database; for example, Oracle Enterprise Manager Database Control:

$ emctl stop dbconsole

$ lsnrctl stop

Step 3: To install the Oracle Database 10g patch set interactively:

a. Log in as the oracle user.

b. Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software:

% cd patchset_directory/Disk1

% ./runInstaller

c. On the Welcome screen, click Next.

d. On the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.

e. Enter your e-mail address and password for your My Oracle Support account to be informed of security issues, then click Next.

 If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

f. On the Product-specific Prerequisite Checks screen, correct any reported errors, and click Next.

g. On the Summary screen, click Install.

 This screen lists all of the patches available for installation.

h. When prompted, run the $ORACLE_HOME/root.sh script as the root user. If you are applying the patch set to an Oracle RAC installation, then run the root.sh script on each node of the cluster.

i. On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

PART TWO:

Upgrading a Release 10.2 Database using Oracle Database Upgrade Assistant

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Step 1: Log in as the Oracle software owner user.

Step 2: Set the values for the environment variables $ORACLE_HOME, $ORACLE_SID and $PATH.

 For single-instance installations, if you are using Automatic Storage Management, start the Automatic Storage Management instance.

Step 3: For Oracle single-instance installations, start the listener as follows:

$ lsnrctl start listenername

Step 4: Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode:

Interactive mode:

Step 5: Enter the following command from the command prompt:

$ dbua

 Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

a. On the Welcome screen, click Next.

b. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

c. On the Recompile Invalid Objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.

d. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, stipulate the Path, then click Next.

e. On the Summary screen, check the summary, then click Finish.

f. On the End of Database Upgrade Assistant's Upgrade Results screen, click Close to exit from Oracle Database Upgrade Assistant.

 If you are using the Oracle Recovery Manager catalog, enter the following command:

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

 For Oracle RAC installations, start any database services that you want to use by entering the following command:

$ srvctl start service -d db_name -s service_name

Create Tablespace in ORACLE 10g

2012-06-27T14:23:00.000+06:00

Create Tablespace in ORACLE 10g

Permanent tablespace

Adding Tablespace in database:

CREATE SMALLFILE TABLESPACE BU_SYSTEM_TBS DATAFILE '$DATAFILE_LOCATION/BU_SYSTEM_TBS' SIZE 100M AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Adding Datafile in a Tablespace:

Use ALTER TABLESPACE to add datafile in tablespace:

ALTER TABLESPACE BU_HIS_LOG_TBS ADD DATAFILE '$DATAFILE_LOCATION/BU_HISTLOG_TBS_01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

Modify Datafile:

You can modify datafile using ALTER DATABASE command:

ALTER DATABASE DATAFILE ’$DATAFILE_LOCATION/data01.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.

Renaming Tablespaces:

This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

alter tablespace ts_user rename to ts_user_01;

ALTER TABLESPACE BU_IMAGE TO BU_PICTURE;

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

Starting and Stopping the Oracle Enterprise Manager Console

2012-06-24T10:58:00.007+06:00

Starting and Stopping the Oracle Enterprise Manager Console

To access the Oracle Enterprise Manager Console from a client browser, the dbconsole process needs to be running on the server. The dbconsole process is automatically started after installation.

However, in the event of a system restart,change in IP, or other changes, you can start it manually at the command line.

To start the dbconsole process from the command line:

Navigate into your ORACLE_HOME/bin directory.
Run the following statement:

./emctl start dbconsole

Additionally, you can stop the process and view its status.

To stop the dbconsole process:

./emctl stop dbconsole

To view the status of the dbconsole process:

./emctl status dbconsole

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

How to enable log shipping on Standby after failing archival.

2012-06-05T11:52:00.002+06:00

The Primary Database was giving error in alert log that it was not able to send archivelogs to Standby. The error looked like the following:

Tue Jun 5 10:31:36 2012

Errors in file /u01/oracle/admin/DB1/bdump/DB1_arcp_6846.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

Diagnosis:

To check the status of archiving the following sql was issued:

Check what is the destination status

select ds.dest_id id,

ad.status,

ds.database_mode db_mode,

ad.archiver type,

ds.recovery_mode,

ds.protection_mode,

ds.standby_logfile_count "SRLs",

ds.standby_logfile_active active,

ds.archived_seq#

from v$archive_dest_status ds, v$archive_dest ad

where ds.dest_id = ad.dest_id

and ad.status != 'INACTIVE'

order by ds.dest_id;

ID	STATUS	DB_MODE	TYPE	RECOVERY_MODE	PROTECTION_MODE	SRLs	ACTIVE	ARCHIVED_SEQ#
2	ERROR	MOUNTED-STANDBY	LGWR	MANAGED	MAXIMUM PERFORMANCE	8	7	13317
10	VALID	OPEN	ARCH	IDLE	MAXIMUM PERFORMANCE	0	0	13319

We can see one destination which is STANDBY has status "ERROR".

select dest_id, dest_name, status

from v$archive_dest_status

where status != 'INACTIVE';

DEST_ID	DEST_NAME	STATUS
2	LOG_ARCHIVE_DEST_2	ERROR
10	LOG_ARCHIVE_DEST_10	VALID

Where the log stuck in standby

select thread#, sequence#, process, status from v$managed_standby;

Check the errors in dataguard status from primary

select error_code, message, timestamp

from v$dataguard_status

where dest_id = 2;

To check if the process MRPO exists in secondary, issue the command.

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

This SQL returned no row which means the dataguard was not working properly.

To find out the root cause of this problem I tried the "df -h" to check space on my STANDBY.

I found that there was no space left on device.

The Solution:

STEP 1: First of all space was made available by deleting BACKUPSET of STANDBY.

STEP 2: Issue the command to delete the archived and old archivelog.

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

STEP 3: Issue the command to CROSSCHECK the archivelogs.

CROSSCHECK ARCHIVELOG ALL;

STEP 4: Shutdown STANDBY.

alter database recover managed standby database cancel;
(N.B : This command is used before shutting down the STANDBY database for regular maintenance of STANDBY)

shutdown immediate;

STEP 5: Startup the database in mount mode.

startup mount;

STEP 6: Start log applying to secondary:

I. Log on to STAN SQL prompt and issue the command.

alter database recover managed standby database disconnect; ---apply cuurent logfile

II. To check archives are applied to secondary, issue the following command.

Select sequence#,first_time,applied from v$archived_log order by sequence#;

III. To check if the process MRPO exists in secondary, issue the command.

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Now the MRP process should be there and dataguard should work properly.

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

Getting dump or list all parameters set at session level:

2012-06-04T15:40:00.001+06:00

Getting dump or list all parameters set at session level:

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.SQL> alter session set sql_trace=true;Session altered.SQL> alter session set optimizer_mode=ALL_ROWS;Session altered.-- connect to sessionSQL> oradebug setmypidStatement processed.SQL> oradebug dump modified_parameters 1;Statement processed.SQL> oradebug tracefile_name;

The Trace file contents:

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

PARSING IN CURSOR #1 len=43 dep=0 uid=0 oct=42 lid=0 tim=3561292942895 hv=3364811346 ad='90925928'
alter session set optimizer_mode=first_rows
END OF STMT
PARSE #1:c=30000,e=30737,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3561292942888
EXEC #1:c=0,e=341,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3561292943385
*** 2012-06-04 15:28:05.809
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS
---------------------------------------------------------------------------------------------------------

Optimizer_mode

How to Find Database is 32 bit or 64 Bit

2012-06-03T11:15:00.000+06:00

How to Find Database is 32 bit or 64 Bit

Issue the following command.

$ file $ORACLE_HOME/bin/sqlplus

you should see output like
/u01/db/bin/sqlplus: ELF 32-bit LSB executable 80386 Version 1, dynamically linked, not stripped

If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable

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

Script for monitoring RMAN progress

2012-06-01T06:07:00.000+06:00

Script for monitoring RMAN progress:

Using the following SQL you can monitor the progress of RMAN process, as channels completes creating the backup piece, new sessions are created so overall progress can be monitored by looking at where context = 3 as in the case below it shows the overall progress is 38.05% complete.

SELECT SID,
SERIAL#,
USERNAME,
START_TIME,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE",
sysdate + TIME_REMAINING / 3600 / 24 Entimated_End_Time
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Solution to : "WARNING: Detected too many memory locking problems."

2012-05-29T10:25:00.000+06:00

Solution to : "WARNING: Detected too many memory locking problems."

My alert log file was generating too many warnings that i was not aware of. After a thorough search in Google (special thanks), i found that one file permission was responsible for generating the warning.

My alert log file entries:

Current log# 3 seq# 4645 mem# 0: <DATAFILE_LOCATION>/redo03.log
Mon May 28 11:53:39 2012
WARNING: Detected too many memory locking problems.
WARNING: Performance degradation may occur.

The real cause of those warnings is the file oradism located on $ORACLE_HOME/bin folder was lacking permission.

As per meta link note 374367.1, issuing the following command should stop the warnings to get generated:

1- cd $ORACLE_HOME/bin

2- chmod 4550 oradism
3- chmod g+s oradism
4- chown root:dba oradism
5- Bounce the database

These commands ware generating errors on my case so i just made my oracle os user, the owener of this file. and bounced the database. And guess what? the problem was resolved. The oradism warning does not appear in the alert log after the permissions were changed.

Recreating UNDO Tablespace

2012-05-16T10:29:00.000+06:00

If your undo tablespace has grown to a large size and you need to recreate it , you can do so with the following easy steps:

STEP 1: Query V$PARAMETER to see the default UNDO tablespace currently active.

SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
_______________________
undo_management AUTO
undo_tablespace UNDOTBS2

STEP 2: Create a new tablespace that will be the new default tablespace.

CREATE UNDO TABLESPACE undotbs02
DATAFILE '/oradata/oracle/oradata/DB1/undotbs02.dbf' SIZE 500M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

STEP 3: Set this newly created UNDO tablespace to default UNDO tablespace.

ALTER SYSTEM SET undo_tablespace = 'UNDOTBS02';

STEP 4: Set the old tablespace to OFFLINE to be eligible to drop.

ALTER TABLESPACE undotbs2 OFFLINE;

STEP 5: Drop the old UNDO tablespace.

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

-----------------------------------------------------------------------------------------
tHANKS fOR rEADING tHIS aRTICLE.

Shared Pool Tuning: Cursor Tuning (Tuning Open_Cursors, Session_Cached_Cursors, Cursor_Space_For_Time)

2012-05-10T14:59:00.000+06:00

Shared Pool Tuning: Cursor Tuning

The three most important parameter for shared pool tuning are OPEN_CURSORS, SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME. But most of the time we see that these two parameters SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME are ignored or unused.

OPEN CURSORS

Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

V$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open. To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:

n Total cursors open, by session

select a.value, s.username, s.sid, s.serial#

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid = a.sid

and b.name = 'opened cursors current';

n Total cursors open, by username & machine

You may find it useful to monitor open cursors by username and machine while working on N-tier Application:

select sum(a.value) total_cur,

avg(a.value) avg_cur,

max(a.value) max_cur,

s.username,

s.machine

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid = a.sid

and b.name = 'opened cursors current'

group by s.username, s.machine

order by 1 desc;

n Tuning OPEN_CURSORS

To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS

select max(a.value) as highest_open_cur, p.value as max_open_cur

from v$sesstat a, v$statname b, v$parameter p

where a.statistic# = b.statistic#

and b.name = 'opened cursors current'

and p.name = 'open_cursors'

group by p.value;

SESSION_CACHED_CURSORS

Every time a statement is issued, the session searches the shared pool for the parsed state, if it doesn’t get the parsed version, it will “hard parse” it, and if it exists in shared pool, it will still do a “soft parse”.

The term “hard parse” is a costly operation, even a “soft parse” requires library cache latch and CPU overhead, which is will be significant number if we sum up.

This parameters default value is 50. This will improve the “soft parse” performance by doing a softer soft parse. Confused? Let’s have a closer look.

If this parameter is set, oracle maintains a local session cache which stores recently closed cursors of a session and maintains the cursors for which there have been 3 parsed calls in the past, so all the SQL’s issued by a session are not here. Each cursor if pinned here, is not freeable and you may lead to allocate more shared pool area.

A normal cursor in shared pool is sum of 2 components:

a) Heap 0 – size 1KB
b) SQL Area – size multiple of 4k

When we use session_cached_cursors only first component of cursor which is HEAP 0 is pinned in local session cache and if there is a call for re-parse for a statement, Oracle first checks the existence of the cursor in local cache and if found, it gets the address of the rest of the cursor which is in SQL Area provided that it is not aged out, so hereby saving CPU overhead and library cache latch contention.

The following query shows maximum value for session_cached_cursors in the past. If this value equals “session_cached_cursors” (see init.ora parameter), you should consider increasing it.

n Maximum value for session_cached_cursors

select max(value)

from v$sesstat

where STATISTIC# in

(select STATISTIC#

from v$statname

where name = 'session cursor cache count');

n SESSION_CACHED_CURSORS by session

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

select a.value, s.username, s.sid, s.serial#

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid = a.sid

and b.name = 'session cursor cache count';

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_text

from v$open_cursor c, v$sql sql

where c.sql_id = sql.sql_id -- for 9i and earlier use: c.address=sql.address

and c.sid = &sid;

n To see the hard and soft parse percentage, issue the following query:

select to_char (100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,

to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,

to_char(100 * hard / calls, '999990.00') || '%' hard_parses

from (select value calls

from sys.v_$sysstat

where name = 'parse count (total)'),

(select value hard

from sys.v_$sysstat

where name = 'parse count (hard)'),

(select value sess

from sys.v_$sysstat

where name = 'session cursor cache hits');

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- -----------
45.56% 49.98% 4.46%

n To see the usage percentage on session_cached_cursors and open_cursors issue the following query:

select 'session_cached_cursors' parameter,

lpad(value, 5) value,

decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage

from (select max(s.value) used

from sys.v_$statname n, sys.v_$sesstat s

where n.name = 'session cursor cache count'

and s.statistic# = n.statistic#),

(select value

from sys.v_$parameter

where name = 'session_cached_cursors')

union all

select 'open_cursors',

lpad(value, 5),

to_char(100 * used / value, '990') || '%'

from (select max(sum(s.value)) used

from sys.v_$statname n, sys.v_$sesstat s

where n.name in

('opened cursors current', 'session cursor cache count')

and s.statistic# = n.statistic#

group by s.sid),

(select value from sys.v_$parameter where name = 'open_cursors')

PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 40 100%
open_cursors 4000 3%

n Tuning SESSION_CACHED_CURSORS

If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.

select cach.value cache_hits,

prs.value all_parses,

prs.value - cach.value sess_cur_cache_not_used

from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2

where cach.statistic# = nm1.statistic#

and nm1.name = 'session cursor cache hits'

and prs.statistic# = nm2.statistic#

and nm2.name = 'parse count (total)'

and cach.sid = &sid

and prs.sid = cach.sid;

More importantly if you want to see how your session cache is doing. Issue the following query.

select cache / tot * 100 "Session cursor cache%"

from (select value tot from v$sysstat where name = 'parse count (total)'),

(select value cache

from sys.v_$sysstat

where name = 'session cursor cache hits');

Session cursor cache%
---------------------
90.745855740977

A value near 100 is considered very well. But you may still consider increasing this parameter if MAX(VALUE) in query one shows you equal number of cached cursor which you have set.

Also increasing this parameter will mean extra memory required for shared pool, so you must increase your shared pool when you use this parameter.

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

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&1') || '%';

NAME VALUE

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

opened cursors cumulative 186

opened cursors current 1

session cursor cache hits 136

session cursor cache count 35

cursor authentications 11

that shows I've 35 cursors in my "cache" ready to be opened faster then normal -- but I never

exceeded my 1000 open cursors at a time threshold.

CURSOR_SPACE_FOR_TIME

When the cursors are closed by application, they can still be in shared pool, until there comes a request for a new cursor and oracle needs to use more memory using LRU algorithm. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0) when the cursors are closed.

Oracle requires parsed state of SQL at PARSE and EXECUTE phase. If oracle parses (soft or hard) a statement, there is a likely hood that Oracle may age out your SQL out of shared pool after PARSE state if it requires to accommodate a new SQL coming its way. So in the EXECUTE state, there is a possibility that parsed information is lost and oracle parse it again.

CURSOR_SPACE_FOR_TIME if set to TRUE, ensures that SQL is not aged out before the cursor is closed, so in EXECUTE phase, you will have the PARSE information.

But this is generally a rare case and happens in a very highly active environment because to accommodate a new SQL, Oracle first check the free space and if it doesn’t get, it checks the closed cursors and see if any cursor can be aged out and when there is no space which can be reclaimed, Oracle comes to open cursors which are not EXECUTED.
This generally happens when the space of shared pool is too less.

It is not suggested to set up this parameter to TRUE in most of the cases. The reason behind this is that some serious compromise. When a cursor is pinned, it can’t be aged out and related memory cannot be freed for any new SQL, Oracle will keep all the open cursors pinned and not freeable. If you use this parameter, you are pinning the whole cursor not just the HEAP 0 which is 1k, you are pinning HEAP 0 (1k) + SQL Area (multiple of 4k) which makes shared pool life tough because of space issues.

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

hAVE A nICE dAY !!!

Generating public/private rsa key in Solaris

2012-05-09T10:40:00.001+06:00

To set the auto authentication in ssh please follow the steps described below:

bash-3.00$ ssh-keygen -t rsa
Generating public/private rsa key pair.

Enter file in which to save the key (/home/oracle/.ssh/id_rsa): <Press Enter>

Enter passphrase (empty for no passphrase): <Press Enter>

Enter same passphrase again: <Press Enter>

Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
22:55:d2:30:cb:5f:7b:eb:1e:ec:8e:72:48:cb:05:b6 oracle@DCDB01

bash-3.00$ pwd
/home/oracle

bash-3.00$ cd .ssh/
bash-3.00$ cat id_rsa.pub | ssh root@172.17.2.100 "cat - >> /.ssh/authorized_keys"

The authenticity of host '172.17.2.100 (172.17.2.100)' can't be established.
RSA key fingerprint is b5:3a:ce:a0:9f:44:18:ee:a0:33:a3:c6:ff:1a:b0:c3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.2.100' (RSA) to the list of known hosts.

Password:
< Provide password of remote host. i.e. 172.17.2.100 >

< Check That the authentication is working >

bash-3.00$ ssh root@172.17.2.100

Last login: Wed May 9 10:12:55 2012 from 172.17.2.101
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
Sourcing //.profile-EIS.....

root@DCDB3 #

< So, the connection was established>

Case Sensitive Passwords in Oracle Database 11g

2012-05-08T10:17:00.000+06:00

Case Sensitive Passwords in Oracle Database 11g

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

SQL>

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.