System & Database Related Solutions

Saturday 4 November 2017

After upgrading the database from 11.2.0.3 to 11.2.0.4 one of the component (OWB) is not upgraded

Scenario: After upgrading the database from 11.2.0.3 to 11.2.0.4 one of the component (OWB) is not upgraded

Step 1: Check the registry status and found OWB is not upgraded to 11.2.0.4

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name; 2

COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
OWB VALID 11.2.0.3.0
Oracle Application Express VALID 3.2.1.00.1
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
Oracle Enterprise Manager VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0

COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
Oracle OLAP API VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0

18 rows selected.

Step 2: Now we have to do the following two activities

a) execute the script $ORACLE_HOME\owb\UnifiedRepos\clean_owbsys.sql
b) execute the script $ORACLE_HOME\owb\UnifiedRepos\cat_owb.sql

SQL>

bash-3.00$ cd $ORACLE_HOME
bash-3.00$ pwd
/ultimus/oracle/product/11.2.0.4/db_1
bash-3.00$ cd ow
owb/ owm/
bash-3.00$ cd owb/UnifiedRepos/
bash-3.00$ pwd
/ultimus/oracle/product/11.2.0.4/db_1/owb/UnifiedRepos
bash-3.00$

bash-3.00$ ls -lrt
total 290
-rw-r--r-- 1 oracle dba 63 Apr 1 2006 bug5060857.sql
-rw-r--r-- 1 oracle dba 111 Apr 1 2006 bug5087832.sql
-rw-r--r-- 1 oracle dba 540 Apr 1 2006 bug5072235.sql
-rw-r--r-- 1 oracle dba 792 Apr 1 2006 patch_paris_repos_sys.sql
-rwxr-x--- 1 oracle dba 4446 Apr 1 2006 patch_paris_repos.sh
-rw-r--r-- 1 oracle dba 4610 Apr 1 2006 patch_paris_repos.bat
-rw-r--r-- 1 oracle dba 3756 Apr 1 2006 bug5092719.sql
-rw-r--r-- 1 oracle dba 96 Aug 14 2006 wkspDrop.xml
-rw-r--r-- 1 oracle dba 189 Aug 16 2006 wkspInstall.xml
-rw-r--r-- 1 oracle dba 324 Oct 29 2007 racsetup.xml
-rw-r--r-- 1 oracle dba 56 Jan 6 2008 wkspUserInstall.xml
-rw-r--r-- 1 oracle dba 56 Jan 6 2008 wkspUserDrop.xml
-rw-r--r-- 1 oracle dba 163 Sep 26 2008 clean_owbsys.sql
-rw-r--r-- 1 oracle dba 142 Sep 26 2008 clean_owbmeta.sql
-rw-r--r-- 1 oracle dba 74 Dec 4 2008 unlock_owbsys.sql
-rw-r--r-- 1 oracle dba 6070 May 5 2009 reposDrop.xml
-rw-r--r-- 1 oracle dba 688 May 5 2009 licenseInstall.xml
-rw-r--r-- 1 oracle dba 768 May 15 2009 remote_owb_install.sql
-rw-r--r-- 1 oracle dba 39435 Dec 19 2010 part_owb.sql
-rw-r--r-- 1 oracle dba 7180 Oct 4 2012 cat_owbmeta.sql
-rw-r--r-- 1 oracle dba 44656 Oct 14 2012 reposInstall.xml
-rw-r--r-- 1 oracle dba 4372 Feb 12 2013 cat_owb_reposupgrade.sql
-rw-r--r-- 1 oracle dba 11828 Jul 8 2013 cat_owb.sql
-rw-r--r-- 1 oracle dba 2714 Jul 11 2013 reset_owbcc_home.sql

********* execute the script $ORACLE_HOME\owb\UnifiedRepos\clean_owbsys.sql

bash-3.00$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 5 10:24:38 2017

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

Enter user-name: /as sysdba

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

SQL> @clean_owbsys.sql

User dropped.

User dropped.

Role dropped.

Role dropped.

Role dropped.

********* execute the script $ORACLE_HOME\owb\UnifiedRepos\cat_owb.sql

User altered.

User altered.

NOTE: To associate the Control Center with the correct OWB home, now run owb/UnifiedRepos/reset_owbcc_home.sql. You must run the script as a user with java admin privileges. For example, connect as sys as sysdba to run the script.
SQL>

Step 3: Now check the status of OWB

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name; 2

COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
OWB VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.1
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
Oracle Enterprise Manager VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0

COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
Oracle OLAP API VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0

18 rows selected.

SQL>

Thats it !!!!!!

Sunday 29 October 2017

Step by step Install and Deinstall DBconsole in Oracle 11g R2

Step 1: Check the status of dbconsole

[oracle@saidodv ~]$ emctl status dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0.4/db_1/oc4j/j2ee/OC4J_DBConsole_saidodv.localdomain_orcl not found.
[oracle@saidodv ~]$

Step 2: Deinstall/Drop existing dbconsole

[oracle@saidodv ~]$ cd $ORACLE_HOME/bin
[oracle@saidodv bin]$ ./emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Oct 30, 2017 11:44:53 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: orcl
Listener port number: 1521
Password for SYS user:
Oct 30, 2017 11:45:04 AM oracle.sysman.emcp.DatabaseChecks checkRestrictedMode
WARNING: ORA-01034: ORACLE not available

Oct 30, 2017 11:45:04 AM oracle.sysman.emcp.DatabaseChecks checkDataGuardEnabled
WARNING: ORA-01034: ORACLE not available

Step 3 : The error showing above due to Database instance is down. Bring up Database Instance and Listener

[oracle@saidodv bin]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 30 11:45:27 2017

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2257152 bytes
Variable Size 499126016 bytes
Database Buffers 255852544 bytes
Redo Buffers 6885376 bytes
Database mounted.
Database opened.
SQL>

Step 4 : Create / Install dbconsole

[oracle@saidodv bin]$ ./emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Oct 30, 2017 11:48:02 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: orcl
Listener port number: 1521
Password for SYS 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
Oct 30, 2017 11:48:18 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2017_10_30_11_48_02.log.
Oct 30, 2017 11:48:18 AM 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= and re-run EMCA script
2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script
Oct 30, 2017 11:48:18 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Oct 30, 2017 11:49:23 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 30, 2017 11:49:23 AM
[oracle@saidodv bin]$

Step : Install dbconsole

[oracle@saidodv bin]$ ./emca -config dbcontrol db -repos create

STARTED EMCA at Oct 30, 2017 11:50:05 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: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0.4/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):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0.4/db_1

Local hostname ................ saidodv.localdomain
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0.4/db_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Oct 30, 2017 11:50:32 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2017_10_30_11_50_05.log.
Oct 30, 2017 11:50:33 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 30, 2017 11:53:21 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Oct 30, 2017 11:53:28 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Oct 30, 2017 11:53:59 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Oct 30, 2017 11:54:01 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Oct 30, 2017 11:54:05 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl secure dbconsole -host saidodv.localdomain -sid orcl
Oct 30, 2017 11:54:05 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
WARNING: Error securing Database control.
Oct 30, 2017 11:54:05 AM oracle.sysman.emcp.EMDBPostConfig setWarnMsg
INFO: Error securing Database Control. Database Control has been brought-up in non-secure mode. To secure the Database Control execute the following command(s):

1) Set the environment variable ORACLE_UNQNAME to Database unique name
2) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl stop dbconsole
3) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl config emkey -repos -sysman_pwd < Password for SYSMAN user >
4) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl secure dbconsole -sysman_pwd < Password for SYSMAN user >
5) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl start dbconsole

To secure Em Key, run /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < Password for SYSMAN user >
Oct 30, 2017 11:54:05 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 30, 2017 11:54:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 30, 2017 11:54:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://saidodv.localdomain:1158/em <<<<<<<<<<<

Error securing Database Control. Database Control has been brought-up in non-secure mode. To secure the Database Control execute the following command(s):

1) Set the environment variable ORACLE_UNQNAME to Database unique name
2) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl stop dbconsole
3) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl config emkey -repos -sysman_pwd < Password for SYSMAN user >
4) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl secure dbconsole -sysman_pwd < Password for SYSMAN user >
5) /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl start dbconsole

To secure Em Key, run /u01/app/oracle/product/11.2.0.4/db_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < Password for SYSMAN user >
[oracle@saidodv bin]$

Step 5 : Check the status of dbconsole

[oracle@saidodv bin]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
http://saidodv.localdomain:1158/em/console/aboutApplication
EM Daemon is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0.4/db_1/saidodv.localdomain_orcl/sysman/log
[oracle@saidodv bin]$

Step 6: Now try to login dbconsole using following link

http://saidodv.localdomain:1158/em
http://:1158/em

Thats it !!!!!!!!!!!!!!!!!

Tuesday 24 October 2017

How to Enable Audit for Grantor User in Oracle Database

Scenario: Here we want to enable audit for those Database users who are giving Database Privileges to other Database users.

Step 1: Create two users (said and mihir) and grant DBA privileges to one user

Enter user-name: /as sysdba

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

SQL>create user said identified by said ;

SQL> grant dba to said;

SQL> create user mihir identified by mihir;

Step 2: Now connect to Database with DBA privilege user and grant some privileges to other user

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:39:28 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: said/said

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

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED

SQL> grant create table to mihir;

Grant succeeded.

SQL> grant create session to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

COUNT(*)
----------
0

Step 3: Now grant privilege to user SAID for auditing

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

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

SQL> AUDIT system grant by "SAID";

Audit succeeded.

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED

Step 4: Now provide privilege to user mihir, connect database as said

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn said/said
Connected.
SQL>
SQL> show user
USER is "SAID"

SQL> grant create session to mihir;

Grant succeeded.

SQL> grant create table to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

COUNT(*)
----------
2

SQL> column username format a10
SQL> column action_name format a20
SQL> column grantee format a10
SQL> column sql_text format a40
SQL> set lin 100
SQL> select username,action_name,grantee, sql_text from dba_audit_trail;

USERNAME ACTION_NAME GRANTEE SQL_TEXT
---------- -------------------- ---------- ----------------------------------------
SAID SYSTEM GRANT MIHIR grant create table to mihir
SAID SYSTEM GRANT MIHIR grant create session to mihir

SQL>

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED

Thats it....!!!!!!!!!

Pages