ORA-01031: insufficient privileges while configure Enterprise Manager

Configuring OEM manually seems to be very straight forward but sometimes you may come across erors which make it difficult.

Below is one of the problem and its solution which I faced last week.

[oracle@PSNA-UAT-DBO001(VOTEUT01):~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:53:32 AM

EM Configuration Assistant, Version 11.2.0.3.0 Production

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

Enter the following information:

Database SID: VOTEUT01

Listener port number: 1571

Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID [ +ASM ]:

ASM port [ 1571 ]:

ASM username [ ASMSNMP ]:

ASM user password:

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

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com

Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Listener port number ................ 1571

Database SID ................ VOTEUT01

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID ................ +ASM

ASM port ................ 1571

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 6, 2015 4:54:32 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log.

Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: ORA-01031: insufficient privileges

Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.EMConfig perform

SEVERE:

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

Some of the possible reasons may be:

1) Listener port 1571 provided is incorrect. Provide the correct port.

2) Listener is not up. Start the Listener.

3) Database service VOTEQA01 is not registered with listener. Register the database service

4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.

5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.

6) /etc/hosts does not have correct entry for hostname.

Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.

Could not complete the configuration. Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.

You may face this issue because OS user not added to onistall or dba group.but in my case it was there.

After researching I found the reason was missing password file.So to resolve it I have created new password file and re-ran the command and it completed successfully.

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$orapwd file=orapw$ORACLE_SID password=oracle entries=1 force=y

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:58:39 AM

EM Configuration Assistant, Version 11.2.0.3.0 Production

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

Enter the following information:

Database SID: VOTEUT01

Listener port number: 1571

Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID [ +ASM ]:

ASM port [ 1571 ]:

ASM username [ ASMSNMP ]:

ASM user password:

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

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com

Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Listener port number ................ 1571

Database SID ................ VOTEUT01

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID ................ +ASM

ASM port ................ 1571

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 6, 2015 4:59:28 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_58_39.log.

Apr 6, 2015 4:59:30 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Apr 6, 2015 5:01:54 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Apr 6, 2015 5:01:55 AM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Apr 6, 2015 5:08:59 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Apr 6, 2015 5:09:01 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO: Uploading configuration data to EM repository (this may take a while) ...

Apr 6, 2015 5:09:54 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Apr 6, 2015 5:09:55 AM oracle.sysman.emcp.ParamsManager getLocalListener

WARNING: Error retrieving listener for PSNA-UAT-DBO001.ad.issgovernance.com

Apr 6, 2015 5:09:57 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://PSNA-UAT-DBO001.ad.issgovernance.com:5500/em <<<<<<<<<<<

Apr 6, 2015 5:10:32 AM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1/PSNA-UAT-DBO001.ad.issgovernance.com_VOTEQA01/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************

Enterprise Manager configuration completed successfully

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, ORA-29283: invalid file operation

When doing export occur above error, let’s check directories:

SQL> select directory_name, directory_path from dba_directories

DIRECTORY_NAME     DIRECTORY_PATH

——————————————————————————–

DUMP_DIR       /u02/dump_dir

Two thing’s that hadn’t done is:

1) given correct permissions for that user to acccess the logical directory for export:

SQL> GRANT read, write on directory dump_dir TO ika; <-- username

2) create the physical directory

[oracle@oel6]$ mkdir -p /u02/dump_dir

After that our export run successfully;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to clone the Oracle database software

This note describes the process of cloning an Oracle Home - in this case we are cloning 11g Release 2 Oracle Home from source machine DEV to target machine QA.

STEP 1: Take tar backup of source oracle home location

On source (DEV)

ORACLE_HOME location is: /u01/app/oracle/product/11.2.0/dbhome_1

Go to directory on source machine one level higher and tar the db_home1 directory and all sub-directories

[oracle@(DEV):~]$ pwd

[oracle@(DEV):~]$ /u01/app/oracle/product/11.2.0 

[oracle@(DEV):~]$ tar -cvf 11gr2.tar dbhome_1

STEP 2: Create required directory on target database

On Target (QA)

Create the directory /u01/app/oracle/product/11.2.0 if it does not exist

STEP 3: Scp tar backup of target server and uncompress it.

On Source

scp or ftp the tar file to this location on the target machine

[oracle@(DEV):~]$scp -rp 11gr2.tar oracle@qa:/u01/app/oracle/product/11.2.0 

On target (QA)

Uncompress the tar file

[oracle@(QA):~]$ cd /u01/app/oracle/product/11.2.0

[oracle@(QA):~]$ tar -xvf 11gr2.tar

STEP 4: Clone the Oracle Home using the perl script clone.pl which is located under the $ORACLE_HOME/clone/bin

[oracle@(QA):~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin

[oracle@(QA):~]$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_HOME_NAME=11GR2_HOME

./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1" "ORACLE_HOME_NAME=11GR2_HOME" -silent -noConfig -nowait

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 13044 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-06-28_12-19-47PM. Please wait ...Oracle Universal Installer, Version 11.2.0.1.0 Production

Copyright (C) 1999, 2009, Oracle. All rights reserved.

You can find the log of this install session at:

 /u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log

.................................................................................................... 100% Done.

Installation in progress (Monday, June 28, 2010 12:20:29 PM WST)

..............................................................................                                                  78% Done.

Install successful

Linking in progress (Monday, June 28, 2010 12:21:06 PM WST)

Link successful

Setup in progress (Monday, June 28, 2010 12:23:34 PM WST)

Setup successful

End of install phases.(Monday, June 28, 2010 12:34:05 PM WST)

Starting to execute configuration assistants

The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.

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

The "/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

The "/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

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

WARNING:

The following configuration scripts need to be executed as the "root" user.

/u01/app/oracle/product/11.2.0/dbhome_1/root.sh

To execute the configuration scripts:

    1. Open a terminal window

    2. Log in as "root"

    3. Run the scripts

The cloning of 11GR2_HOME was successful.

Please check '/u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log' for more details.

Run the root.sh script as root

[oracle@(QA):~]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@(QA):~]$./root.sh

Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_dev_2010-06-29_07-42-31.log for the output of root script

[oracle@(QA):~]$cat /u01/app/oracle/product/11.2.0/dbhome_1/install/root_qa_2010-06-29_07-42-31.log

Running Oracle 11g root.sh script...

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Entries will be added to the /var/opt/oracle/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

STEP 4: Check oraInventory has updated with new oracle home.

We can now check that the OraInventory has been updated with the 11g Release 2 Oracle Home details.

On this machine, earlier there was only a 10g Oracle Home installed. When we check the contents of the inventory.xml file, we will see that it has information about the 11g Oracle Home – 11GR2_HOME

[oracle@(QA):~]$ cat /var/opt/oracle/oraInst.loc

inventory_loc=/u01/app/oracle/oraInventory

inst_group=dba

[oracle@(QA):~]$cd /u01/app/oracle/oraInventory

[oracle@(QA):~]$ ls

backup                   ContentsXML              logs                     oraInstaller.properties  oui

Contents                 install.platform         oraInst.loc              orainstRoot.sh           sessionContext.se

[oracle@(QA):~]$ cd ContentsXML

[oracle@(QA):~]$  ls

comps.xml      inventory.xml  libs.xml

[oracle@(QA):~]$ vi inventory.xml 

<?xml version="1.0" standalone="yes" ?>

<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->

<!-- Do not modify the contents of this file by hand. -->

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>11.2.0.1.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME="ora1020" LOC="/u01/app/oracle/product/10.2.0" TYPE="O" IDX="1"/>

<HOME NAME="11GR2_HOME" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>

</HOME_LIST>

</INVENTORY>

It’s done. J

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Recovery from complete loss of all online redo log files using RMAN

STEP 1: Check database details.

SQL> select instance_name,version from v$instance;

INSTANCE_NAME    VERSION

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

TEST           11.2.0.3.0

SQL> select member from v$Logfile;

MEMBER

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

/u02/ORACLE/TEST/redo03.log

/u02/ORACLE/TEST/redo02.log

/u02/ORACLE/TEST/redo01.log

STEP 2: Delete log files from os level to create scenario.

TEST:/u02/ORACLE/TEST>rm redo*.log

STEP 3: find last archive log sequence.

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file we can see the following error message:

Tue Jan 30 00:47:19 2015

ARC1: Failed to archive thread 1 sequence 93 (0)

Tue Jan 30 00:47:24 2007

Errors in file /opt/oracle/admin/TEST/bdump/TEST_arc0_32722.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u02/ORACLE/TEST/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

The file is missing at the operating system level.

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.

From the error message in the log file we can get the last archived file in our case it is sequence 92 as the error shows that it fails to archive the log file sequence 93.

SQL> select * from v$Log;

  GROUP#   THREAD#  SEQUENCE#   BYTES    MEMBERS ARC   STATUS    FIRST_CHANGE#   FIRST_TIM

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

    1          1         95   52428800          1 NO  CURRENT     3203078        30-JAN-15

    2          1         93   52428800          1 NO  INACTIVE    3202983        30-JAN-15

    3          1         94   52428800          1 NO  INACTIVE    3203074        30-JAN-15

At the operating system also we can find the last archived logfile:

TEST:/u02/ORACLE/TEST/arch> ls –lrt

total 54824

-rw-r-----    1 oracle   dba        714240 Jan 29 16:02 arch_1_90_613129285.dbf

-rw-r-----    1 oracle   dba      46281216 Jan 30 00:37 arch_1_91_613129285.dbf

-rw-r-----    1 oracle   dba         11264 Jan 30 00:41 arch_1_92_613129285.dbf

STEP 4: Shut down the database and startup in Mount stage.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  2069680 bytes

Variable Size              92277584 bytes

Database Buffers           67108864 bytes

Redo Buffers                6316032 bytes

Database mounted.

STEP 5: Connect to rman to Restore and Recover database.

TEST:/u02/ORACLE/TEST>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2015

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

connected to target database: TEST (DBID=1493612009, not open)

RMAN> run {

2> set until sequence 93;

3> restore database;

4> recover database;

5>  alter database open resetlogs;

6> }

executing command: SET until clause

Starting restore at 30-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

channel ORA_SBT_TAPE_1: starting datafile backupset restore

channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u02/ORACLE/TEST/system01.dbf

restoring datafile 00002 to /u02/ORACLE/TEST/undotbs01.dbf

restoring datafile 00003 to /u02/ORACLE/TEST/sysaux01.dbf

restoring datafile 00004 to /u02/ORACLE/TEST/users01.dbf

restoring datafile 00005 to /u02/ORACLE/TEST/users02.dbf

restoring datafile 00006 to /u02/ORACLE/TEST/users03.dbf

restoring datafile 00007 to /u02/ORACLE/TEST/users05.dbf

restoring datafile 00008 to /u02/ORACLE/TEST/users06.dbf

restoring datafile 00009 to /u02/ORACLE/TEST/users07.dbf

restoring datafile 00010 to /u02/ORACLE/TEST/users04.dbf

restoring datafile 00011 to /u02/ORACLE/TEST/drtbs1.dbf

restoring datafile 00012 to /u02/ORACLE/TEST/drtbs2.dbf

restoring datafile 00013 to /tmp/undonew.dbf

channel ORA_SBT_TAPE_1: reading from backup piece 5mi8ornj_1_1

channel ORA_SBT_TAPE_1: restored backup piece 1

piece handle=5mi8ornj_1_1 tag=TAG20070130T004019

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:06

Finished restore at 30-JAN-15

Starting recover at 30-JAN-15

using channel ORA_DISK_1

using channel ORA_SBT_TAPE_1

starting media recovery

archive log thread 1 sequence 92 is already on disk as file /u02/ORACLE/TEST/arch/arch_1_92_613129285.dbf

archive log filename=/u02/ORACLE/TEST/arch/arch_1_92_613129285.dbf thread=1 sequence=92

media recovery complete, elapsed time: 00:00:01

Finished recover at 30-JAN-15

database opened

RMAN>exit

STEP 6: Check all the files are created physically.

The recovery process creates the online redo logfiles at the operating system level also.

TEST:/u02/ORACLE/TEST>ls -lrt redo*

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo03.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo02.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo01.log

Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!