Wednesday, June 29, 2011

ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin)


$ emctl status dbconsole     
OC4J Configuration issue. /oracle/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_****.***.***.***_T11852 not found.

$ emctl start dbconsole
OC4J Configuration issue. /oracle/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_*********_T11852 not found.

Then decided to reconfigure dbconsole.

First step is deconfigure and drop the repository already created.
$ ./emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jun 29, 2011 3:44:46 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: T11852
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 
Password for SYSMAN user: 
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jun 29, 2011 3:45:09 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/cfgtoollogs/emca/T11852/emca_2011_06_29_15_44_45.log.
Jun 29, 2011 3:45:09 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed. Some of the possible reasons may be:
 1) EM is configured with different hostname then physical host. Set environment variable ORACLE_HOSTNAME=<hostname> and re-run EMCA script
 2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script
Jun 29, 2011 3:45:09 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Jun 29, 2011 3:45:10 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /oracle/product/11.2.0/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=T11852.xxx.xxx.usg_ons))) -repos_user SYSMAN -action drop -verbose -output_file /oracle/cfgtoollogs/emca/T11852/emca_repos_drop_2011_06_29_15_45_09.log
Jun 29, 2011 3:45:10 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository
Jun 29, 2011 3:45:10 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /oracle/cfgtoollogs/emca/T11852/emca_repos_drop_<date>.log for more details.
Jun 29, 2011 3:45:10 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error dropping the repository
Refer to the log file at /oracle/cfgtoollogs/emca/T11852/emca_2011_06_29_15_44_45.log for more details.
Could not complete the configuration. Refer to the log file at /oracle/cfgtoollogs/emca/T11852/emca_2011_06_29_15_44_45.log for more details.
oracle@cookie:/oracle/product/11.2.0/bin[T11852]
$

Checked /oracle/cfgtoollogs/emca/T11852/emca_repos_drop_2011_06_29_15_45_09.log:
=================================================================================

[29-06-2011 15:45:10] Enter SYS user's password :
[29-06-2011 15:45:10]
[29-06-2011 15:45:10] Getting temporary tablespace from database...
[29-06-2011 15:45:10] Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=**********)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=T11852.******))): ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin)


I was not able to connect to db using "sqlplus sys@T11852 as sysdba". I was getting ORA-01031 error.

I did below:
1.
SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. There was no password file under $ORACLE_HOME/dbs

3. created password file using
orapwd file=$ORACLE_HOME/dbs/orapwT11852 password=u_pick_it force=y nosysdb>

4. Retried "sqlplus sys@T11852 as sysdba" and it connected.

5. then reran emca and it worked.

Environment variable ORACLE_UNQNAME not defined.

I have a new install of 11.2.0.2 and upgraded the 10.2.0.4 database to 11.2.0.2.

As I said in my last post that the EM configuration failed during the upgrade of database by using dbua.
I was looking to workaround that problem by running emca, before that I wanted to see what it would show for status of dbconsole.

$ emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

I went and looked in the documentation here:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/em_manage.htm#ADMQS032

Note:
If you are prompted to set the ORACLE_UNQNAME environment variable, then set this variable and run the emctl command again. Set the variable to the database unique name (the value of the DB_UNIQUE_NAME database parameter). A typical value for DB_UNIQUE_NAME is orcl.

For RAC:
=======
In case of RAC the dbconsole service runs from a single host.
Let's say you have three node RAC cluster with ORCL database running on all 3 of them. orcl1, orcl2, and orcl3 are the instance names.

Assume that dbconsole is running from node1, in that case if you would have to set, below parameter before running emctl commands :


ORACLE_UNQNAME=orcl (i.e. db_unique_name) 

HTH. 

Tuesday, June 28, 2011

10.2.0.4 to 11.2.0.2 Standalone DB upgrade using dbua

Followed Complete checklist to upgrade the database to 11g R2 using DBUA [ID 870814.1]


1. Check dba_registry for correctness of status and version at the source 10.2.0.4 database. Take necessary steps to validate them.
2. Copy from 11g Env: $ORACLE_HOME/rdbms/admin/utlu112i.sql to some other location e.g. /tmp
3. From 10g Env:
cd /tmp
$ sqlplus '/ as sysdba'
SQL> spool upgrade_prereq.log
SQL> @utlu112i.sql
SQL> spool off

4. Review the upgrade_prereq.log and fix them.

I will put some lines from my log file where it suggested some changes:
..
..
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]                       
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************                    

WARNING: --> "shared_pool_size" needs to be increased to at least 472 MB
WARNING: --> "db_cache_size" needs to be increased to at least 50331648 bytes
==> dbua would take care of this.

**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"   
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"  
==> dbua would take care of this

**********************************************************************                    
Miscellaneous Warnings                                                                    
**********************************************************************                    
WARNING: --> Database is using a timezone file older than version 14.                     
.... After the release migration, it is recommended that DBMS_DST package                 
.... be used to upgrade the 10.2.0.4.0 database timezone version                          
.... to the latest version which comes with the new release.
==> Checked "Actions FOR DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset (Doc ID 1201253.1)" . Check step B.3b). It says we can skip the DST related upgrade instructions. dbua gives an option of upgrading the timezone version and TIMESTAMP WITH TIME ZONE data.
                              
WARNING: --> Database contains INVALID objects prior to upgrade.                          
.... The list of invalid SYS/SYSTEM objects was written to                                
.... registry$sys_inv_objs.                                                               
.... The list of non-SYS/SYSTEM objects was written to                                    
.... registry$nonsys_inv_objs.                                                            
.... Use utluiobj.sql after the upgrade to identify any new invalid                       
.... objects due to the upgrade.

WARNING: --> EM Database Control Repository exists in the database.                       
.... Direct downgrade of EM Database Control is not supported. Refer to the               
.... Upgrade Guide for instructions to save the EM data prior to upgrade.                 

WARNING: --> Your recycle bin contains 93 object(s).                                      
.... It is REQUIRED that the recycle bin is empty prior to upgrading                      
.... your database.  The command:                                                    
             PURGE DBA_RECYCLEBIN                                                              
.... must be executed immediately prior to executing your upgrade.                        

WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.       
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.           
==> This actually needs be done post upgrade. http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#BABJHIHH

5. Gather dictionary stats prior to upgrade:
On 10g ENV:
sqlplus "/as sysdba"
EXECUTE dbms_stats.gather_dictionary_stats;

6. purge dba_recyclebin
7. Run dbua
==> I saw few errors during  oracle server upgrade but I could ignore those. May be because my 10g db had many invalid objects. As most of them were ORA-04063.
==>Also EM configuration failed. Will write another post for this one.

8. Change compatible.
I havent done this yet, as I would leave it to 10.2.0.4 for few days so that in case I want to downgrade it.

Clone 10g DB manually without RMAN & NID utility


This post assumes that you are cloning "prod" to "clone".

1. Take cold backup of database:
    a. select name from v$datafile;
    b. Shutdown the database
    c. copy the files from step 1a) to the destination server and location.
Note: We dont have to copy controlfiles and redolog files, these will be created fresh.

2. Copy and edit the init.ora. If using spfile, create pfile first.
copy the init.ora to destination $OH/dbs/initclone.ora and do :
modify db_name
modify path for adump, udump, cdump and bdump
modify path for control_files


3. After backup you can start the "prod" db and execute:
alter database backup controlfile to trace; ==> This will generate a trace file in udump of prod db. Find the trace.

4. Use the trace generated in step#3 to create a script to create controfile. e.g. recreatecontrol.sql
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u02/oralog/clone/redo01a.log',
    '/u04/oralog/clone/redo01b.log'
  ) SIZE 100M,
  GROUP 2 (
    '/u03/oralog/clone/redo02a.log',
    '/u04/oralog/clone/redo02b.log'
  ) SIZE 100M,
  GROUP 3 (
    '/u03/oralog/clone/redo03a.log',
    '/u04/oralog/clone/redo03b.log'
  ) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/u04/oradata/clone/system01.dbf',
  '/u04/oradata/clone/undo01.dbf',
  '/u04/oradata/clone/sysaux01.dbf',
  '/u04/oradata/clone/users01.dbf'
CHARACTER SET AL32UTF8
;

5. Set Oracle env for clone db
export ORACLE_HOME
export ORACLE_SID=clone
sqlplus /nolog
sql> conn / as sysdba
sql> startup nomount
SQL>@recreatecontrol

SQL>alter database open resetlogs;

6. Add tempfile to TEMP tablespace.

7. Restart clone db and monitor alert log to make sure everything is ok.

Monday, June 27, 2011

Missing AS Control from new installation of IAS 10.1.3.4

I had installed IAS 10.1.3.4 .

Today I decided to visit the AS control for that installation and I couldn't.
I got "HTTP 404 Not Found".

I was wondering what went wrong and restarted all components using opmnctl command. That did not help.

Thankfully I had taken screenshot of my install which showed below message at the end of installation in summary screen:
"Application Server Control Console is not running in this instance of Oracle Application Server. You can manage this instance remotely through another instance of Oracle Application Server that is configured to run Application Server Control Console."

Then searched more in the installation screens to see if I missed anything and found below.



I had NOT CHECKED "Configure this as an Administration OC4J instance"

Now that I found out my mistake, the next step was how to configure EM AS control. I searched MOS and found a wonderful doc 418395.1

Followed the same and issue was resolved.

To resolve it follow below link and step 1 (a to g) and then just do opmnctl stopall and opmnctl startall.
http://download.oracle.com/docs/cd/B31017_01/core.1013/b28940/em_app.htm#CEGEJCGA