DBCA Fails With INS-00001 and DBT-00007

November 28, 2022 Leave a comment

Problem:

While trying to run dbca from Oracle user I received the following error:

Cause - No additional information available.  
Action - Refer to the logs or contact Oracle Support Services  

Summary  
- [DBT-00007] User does not have the appropiate write privileges.  
- [DBT-00007] User does not have the appropiate write privileges.

Screenshot:

Solution:

Make sure the directory has the correct owner and permissions, otherwise set:

# chown oracle:oinstall $ORACLE_BASE/cfgtoollogs/dbca
# chmod 750 $ORACLE_BASE/cfgtoollogs/dbca

Rerun dbca.

Filed under DB, Uncategorized Tagged with , , , ,

Monitor NFS mount point IO performance

April 22, 2022 Leave a comment

Problem:

I am configuring RMAN backup of my Oracle database and redirecting backup sets to Azure Blob Storage with NFSv3 access (quite a new feature for this time and needs to be tested). But I don’t know what will be the writing performance for this type of storage.

Solution:

One of the useful tools is nfsiostat, we will test it in this blog post.

1. After mounting Azure Blob Storage to my database node as /dbbackup mount point, instead of triggering RMAN backup I’d prefer dd command at this time: 

$  dd if=/dev/zero of=/dbbackup/myfile oflag=direct bs=1M count=512000

2. Run nfsiostat with interval 1sec and monitor values:

[oracle@rac1 data]$ nfsiostat 1

marirmanstorage.blob.core.windows.net:/marirmanstorage/dbbackup mounted on /dbbackup:

   op/s	   rpc bklog
   0.11	   0.00
read:      ops/s    kB/s      kB/op.    retrans	 avg RTT (ms)	avg exe (ms)
	   0.000    0.000     0.000     0 (0.0%) 0.000	        0.000
write:     ops/s    kB/s      kB/op     retrans	 avg RTT (ms)	avg exe (ms)
	   18.000   18437.977 1024.332  0 (0.0%) 55.500	        55.611

For information, interval specifies the amount of time in seconds between each report. The first report contains statistics for the time since each file system was mounted. Each subsequent report contains statistics collected during the interval since the previous report

3. Cancel dd operation, you will also get information about the speed :

[oracle@rac1 dbbackup]$  dd if=/dev/zero of=/dbbackup/myfile oflag=direct bs=1M count=512000
^C
124+0 records in
124+0 records out
130023424 bytes (130 MB) copied, 6.85939 s, 19.0 MB/s

More information about nfsiostat can of course be found using man nfsiostat.

Filed under Uncategorized

Print the content of multiple differently named files in Linux

November 25, 2021 Leave a comment

If the number of files you are working on is big, then you need automation as soon as possible.
This post describes find -o option, which helps you work on differently named files when their number is big.

For example, if you want to output the content of files physical_block_size and logical_block_size located under /sys/block/*/queue, run the following: 

# find /sys/block/*/queue -name physical_block_size -o -name logical_block_size | while read f ; do echo "$f $(cat $f)" ; done

..
/sys/block/dm-0/queue/physical_block_size 4096
/sys/block/dm-0/queue/logical_block_size 512
/sys/block/dm-1/queue/physical_block_size 512
...

Where -o means OR.

Useful when working on ASM disks.

Filed under ASM, Linux, Uncategorized Tagged with , , , ,

Check what is my public ip from command line

April 24, 2021 Leave a comment

Problem:

Need to check what is server’s public ip.

Solution:

Simply run:

# dig +short myip.opendns.com @resolver1.opendns.com
40.89.251.13

Filed under Uncategorized

Listener on TCPS, TNS-12557: TNS:protocol adapter not loadable

August 14, 2020 Leave a comment

Error

Listener configured on TCPS protocol cannot be started from 11g SE home:

[oracle@skybase ~]$ lsnrctl start LISTENER_TCPS
...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/skybase/listener_tcps/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=skybase.example.com)(PORT=1522)))
TNS-12557: TNS:protocol adapter not loadable
 TNS-12560: TNS:protocol adapter error
  TNS-00527: Protocol Adapter not loadable

Reason

TCPS Support for Oracle SE not enabled.

Solution

Enable TCPS support using the following steps:

1. In $ORACLE_HOME/lib, backup libntcps11.a: 

[oracle@skybase ~]$ cd $ORACLE_HOME/lib

[oracle@skybase lib]$ cp libntcps11.a libntcps11.a.backup

2. Replace libntcps11.a with libntcps11_ee.a.dbl 

[oracle@skybase lib]$ cp libntcps11_ee.a.dbl libntcps11.a

[oracle@skybase lib]$ ls -al libntcps*

-rw-r--r-- 1 oracle oinstall 306314 Aug 14 16:31 libntcps11.a
-rw-r--r-- 1 oracle oinstall 296986 Aug 14 16:30 libntcps11.a.backup
-rw-r--r-- 1 oracle oinstall 306314 Jul 15  2013 libntcps11_ee.a.dbl

3. Stop all running processes from Oracle home:

$ srvctl stop database -d orcl

4. Relink

[oracle@skybase lib]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/lib

[oracle@skybase lib]$ relink all
writing relink log to: /u01/app/oracle/product/11.2.0/dbhome_1/install/relink.log

5. Start all previously stopped services and try to start listener again:

[oracle@skybase lib]$ srvctl start database -d orcl

[oracle@skybase lib]$ lsnrctl start LISTENER_TCPS
...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/skybase/listener_tcps/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=skybase.example.com)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=skybase.example.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TCPS
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                14-AUG-2020 16:35:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/skybase/listener_tcps/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=skybase.example.com)(PORT=1522)))
The listener supports no services
The command completed successfully

Filed under Uncategorized

How to change listener ports after cluster setup (FG enabled clusters)

July 1, 2020 Leave a comment

The document describes the steps required for modifying listener port numbers after setting up the Flashgrid cluster.

How to change SCAN listener port number

1) On the first node only, modify SCAN listener port number in CRS:

# su - grid
$ srvctl modify scan_listener -p <new_scan_listener_port>
$ srvctl stop scan_listener
$ srvctl start scan_listener

2) On each database node, update the following entry in /etc/flashgrid-scan.cfg (as root):

From:
scan_port: 1521

To:
scan_port: <new_scan_listener_port>

3) On each database node, restart Flashgrid SCAN Proxy service, and make sure that it is running successfully:

# systemctl restart flashgrid-scan-proxy.service
# systemctl status flashgrid-scan-proxy.service

How to change local listener port number

1) On each database node, modify port for NodeFQDN entry in <DATABASE_HOME>/network/admin/tnsnames.ora (as oracle):

DONOTDELETE,NODEFQDN = (ADDRESS = (PROTOCOL = TCP)(Host = <database node hostname>)(Port = <new_local_listener_port>))

2) On the first node only, modify local listener port number in CRS:

# su - grid
$ srvctl modify listener -p <new_local_listener_port>
$ srvctl stop listener
$ srvctl start listener

3) On each database node, update /etc/sysconfig/iptables (as root):

  • Remove immutable flag from iptables file:
  # chattr -i /etc/sysconfig/iptables
  • Modify value in /etc/sysconfig/iptables: From: --dport 1522 To: --dport <new_local_listener_port>
  • Add immutable flah to iptables file:
  # chattr +i /etc/sysconfig/iptables

4) On each database node, restart iptables:

# systemctl restart iptables.service

5) If you have already created database(s), connect to each database and run:

# su - oracle
$ sqlplus / as sysdba
SQL> alter system set local_listener='NODEFQDN';

Filed under Uncategorized

MGTCA-1176/MGTCA-1162 : An error occurred while marking the Cluster Manifest File as expired.

June 28, 2018 2 Comments

If you are installing “Oracle Member Cluster for Oracle Database” and during the installation GIRM configuration assistant fails with the following error:

MGTCA-1176 : An error occurred while marking the Cluster Manifest File as expired.
MGTCA-1162 : failed to add a property to the provided Cluster Manifest File

Just give the following permission to the manifest file , to let the installer make changes there:

chmod 777 manifest.xml

There is no useful info about that on the internet and metalink! I guessed it myself.  That’s why posting that simple solution here.

Filed under 12c, Domain Cluster Member, Real World Scenarios, Uncategorized

Oracle Management Server is Down – Failed to push ldap config data to libOvd for service instance “idstore.ldap”

January 3, 2017 Leave a comment

I have setup Oracle Cloud Control 13c on virtual machine.

Yesterday machine was shut downed forcibly and Cloud Control was unable to start up after that.

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down

Let’s check the following log:

[oracle@oragc log]$ tailf /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out

SEVERE: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default", 
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; lineNumber: 210; 
columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF.
Jan 03, 2017 2:02:17 PM oracle.security.opss.internal.runtime.ServiceContextManagerImpl createContextInternal
WARNING: Service instance "idstore.ldap" cannot be instantiated. 
Reason: oracle.security.jps.service.idstore.IdentityStoreException: 
JPS-02592: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default",
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; 
lineNumber: 210; columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF..
Jan 03, 2017 2:02:17 PM oracle.security.jps.internal.config.OpssCommonStartup start
INFO: Jps startup failed.
<Jan 3, 2017 2:02:17 PM GET> <Error> <Security> 
<BEA-090892> <The loading of an OPSS java security policy provider failed due to an exception. 
See the exception stack trace or the server log file for the root cause. 
If there is no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. 
Error message: null>

I have found the solution on Oracle Community site: https://community.oracle.com/thread/2375509

“Please follow below steps to get you domain up and running
1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

2) Delete $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

3) copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd_11.1.1/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/

4) Restart the server.”

After performing the above steps:

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

Filed under 13C, Uncategorized Tagged with , , ,

Upgrade 11.2.0.3 to 11.2.0.4 and apply latest PSU 11.2.0.4.160719

August 26, 2016 4 Comments

Pre-requisites:

  1. Install Oracle Enterprise Linux 6.5
    Recommendations:
    a) After installing OS it is better to install oracle-rdbms-server-11gR2-preinstall-1.0-7.el6.x86_64.rpm from installation cd. It will prepare the environment for future database installation.
    b) Turn off selinux.
    c) Set static ip instead of DHCP.
  2. Install Oracle 11.2.0.3 software and create database named ORCL.

Steps described in this post:

  1. Install Oracle 11.2.0.4 software only.
  2. Upgrade ORCL database using DBUA(located in 11.2.0.4)
  3. Download latest Opatch version for 11.2.
  4. Using Oracle Recommended Patch Advisor download
    Patch 23054359 – Database Patch Set Update 11.2.0.4.160719 (Includes CPUJul2016)  and apply it to newly upgraded database.

Let’s start:

  1. Install Oracle 11.2.0.4 software only using silent installation method:

    a) Download 11.2.0.4 software from here: p13390677_112040_Linux-x86-64_1of7.zip and p13390677_112040_Linux-x86-64_2of7.zip or from metalink.
    b) Make necessary directories to save installation files and unzip files there:

    mkdir /0
    cd /0
    mkdir 11.2.0.4
    cd 11.2.0.4

    Place zip files in /0/11.2.0.4/

    unzip p13390677_112040_Linux-x86-64_1of7.zip
    unzip p13390677_112040_Linux-x86-64_2of7.zip

    c) Prepare the response file located in /0/11.2.0.4/database/response/db_install.rsp

    Just change the following entries:

    #—————————————————————–
    # Specify the installation option.
    # It can be one of the following:
    # – INSTALL_DB_SWONLY
    # – INSTALL_DB_AND_CONFIG
    # – UPGRADE_DB
    #—————————————————————–
    oracle.install.option=INSTALL_DB_SWONLY

    #—————————————————————–
    # Specify the hostname of the system as set during the install. It can be used
    # to force the installation to use an alternative hostname rather than using the
    # first hostname found on the system. (e.g., for systems with multiple hostnames
    # and network interfaces)
    #—————————————————————–
    ORACLE_HOSTNAME=oracle01

    #—————————————————————–
    # Specify the Unix group to be set for the inventory directory.
    #—————————————————————–
    UNIX_GROUP_NAME=oinstall

    #—————————————————————–
    # Specify the location which holds the inventory files.
    # This is an optional parameter if installing on
    # Windows based Operating System.
    #—————————————————————–
    INVENTORY_LOCATION=/u01/app/oraInventory

    SELECTED_LANGUAGES=en

    #—————————————————————–
    # Specify the complete path of the Oracle Home.
    #—————————————————————–
    ORACLE_HOME=/u01/app/oracle/product/11.2.0.4

    #—————————————————————–
    # Specify the complete path of the Oracle Base.
    #—————————————————————–
    ORACLE_BASE=/u01/app/oracle

    # Specify the installation edition of the component.
    # The value should contain only one of these choices.
    # – EE : Enterprise Edition
    # – SE : Standard Edition
    # – SEONE : Standard Edition One
    # – PE : Personal Edition (WINDOWS ONLY)
    #—————————————————————–
    oracle.install.db.InstallEdition=EE

    #—————————————————————–
    # The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
    #—————————————————————–
    oracle.install.db.DBA_GROUP=dba

    #—————————————————————–
    # The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
    # The value to be specified for OSOPER group is optional.
    #—————————————————————–
    oracle.install.db.OPER_GROUP=dba

    #—————————————————————–# Specify the auto-updates option. It can be one of the following:
    # – MYORACLESUPPORT_DOWNLOAD
    # – OFFLINE_UPDATES
    # – SKIP_UPDATES
    #—————————————————————–
    oracle.installer.autoupdates.option=SKIP_UPDATES

    #—————————————————————–
    # Specify whether to enable the user to set the password for
    # My Oracle Support credentials. The value can be either true or false.
    # If left blank it will be assumed to be false.
    #
    # Example : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
    #—————————————————————–
    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

    #—————————————————————–
    # Specify whether user doesn’t want to configure Security Updates.
    # The value for this variable should be true if you don’t want to configure
    # Security Updates, false otherwise.
    #
    # The value can be either true or false. If left blank it will be assumed
    # to be false.
    #
    # Example : DECLINE_SECURITY_UPDATES=false
    #—————————————————————–
    DECLINE_SECURITY_UPDATES=true

    d) Switch to oracle user(this user is created when you install oracle-rdbms-server-11gR2-preinstall-1.0-7.el6.x86_64.rpm)

    su – oracle
    cd /0/11.2.0.4/database
    ./runInstaller -silent -responseFile /0/11.2.0.4/database/response/db_install.rsp -showProgress

    Run root scrips as indicated at the end of the installation.

    Note: If you are getting the following error after executing runInstaller:

    [FATAL] [INS-35172] Target database memory (1500MB) exceeds the systems available shared memory ({0}MB).
    CAUSE: The total available shared memory on the system (1496 MB) was less than the chosen target database memory (1500 MB).
    ACTION: Enter a value for target database memory that is less than 1496 MB.

    In parameter file there was described MEMORY_TARGET parameter to 1500MB but /dev/shm size was not big enough. You should increase the size for /dev/shm (the same as tmpfs).

    su –
    vi /etc/fstab

    Add option size=10G accross the line tmpfs.

    tmpfs /dev/shm tmpfs defaults,size=10G 0 0

    Remount tmpfs to make the changes take effect without restarting server.

    mount -o remount tmpfs

    2. Upgrade ORCL database using DBUA(located in 11.2.0.4)

    Note database should be started from old home of course.

    a) Set the environment variables using oraenv

    [oracle@oracle01 ~]$ . oraenv
    ORACLE_SID = [oracle] ? orcl
    The Oracle base has been set to /u01/app/oracle

b) Run dbua from 11.2.0.4 home. Indicate sid of the database to be upgraded and     oracle home of the database to be upgraded  :  /u01/app/oracle/product/11.2.0.3

[oracle@oracle01 bin]$ /u01/app/oracle/product/11.2.0.4/bin/dbua -silent -sid orcl -oracleHome /u01/app/oracle/product/11.2.0.3 -diagnosticDest /u01/app/oracle
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/orcl/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
7% complete
8% complete
9% complete
10% complete
10% complete
11% complete
12% complete
12% complete
13% complete
14% complete
15% complete
15% complete
16% complete
17% complete
17% complete
18% complete
19% complete
20% complete
20% complete
21% complete
22% complete
Upgrading JServer JAVA Virtual Machine
23% complete
24% complete
Upgrading Oracle XDK for Java
25% complete
26% complete
Upgrading OLAP Analytic Workspace
27% complete
Upgrading OLAP Catalog
29% complete
Upgrading EM Repository
30% complete
31% complete
32% complete
32% complete
33% complete
34% complete
35% complete
35% complete
36% complete
37% complete
37% complete
38% complete
39% complete
40% complete
40% complete
41% complete
42% complete
42% complete
Upgrading Oracle Text
44% complete
Upgrading Oracle XML Database
45% complete
46% complete
47% complete
47% complete
48% complete
49% complete
50% complete
50% complete
51% complete
52% complete
52% complete
53% complete
54% complete
55% complete
55% complete
56% complete
57% complete
Upgrading Oracle Java Packages
58% complete
Upgrading Oracle interMedia
60% complete
60% complete
Upgrading Spatial
62% complete
62% complete
Upgrading Oracle Workspace Manager
64% complete
Upgrading Expression Filter
65% complete
66% complete
67% complete
67% complete
68% complete
69% complete
70% complete
70% complete
71% complete
72% complete
72% complete
73% complete
74% complete
75% complete
Upgrading Rule Manager
76% complete
Upgrading Oracle Application Express
77% complete
Upgrading Oracle OLAP API
78% complete
Performing Post Upgrade
79% complete
80% complete
85% complete
Configuring Database with Enterprise Manager
86% complete
87% complete
92% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file “/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log” for upgrade details.

c) Check the status of the listener, it should be stopped from the old home and started from new home.

[oracle@oracle01 bin]$ lsnrctl status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date 25-AUG-2016 17:55:13
Uptime 0 days 1 hr. 30 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Service “orclXDB” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@oracle01 bin]$ lsnrctl stop

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

[oracle@oracle01 bin]$ cp /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora

[oracle@oracle01 bin]$ cp /u01/app/oracle/product/11.2.0.3/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora

##########################Reset oracle environment variables to take new home.

[oracle@oracle01 ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle

[oracle@oracle01 bin]$ lsnrctl start

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.4.0 – Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date 25-AUG-2016 19:27:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))
The listener supports no services
The command completed successfully

[oracle@oracle01 bin]$ lsnrctl status

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date 25-AUG-2016 19:27:45
Uptime 0 days 0 hr. 0 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Service “orclXDB” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully

Note: If you have been using static registration of the database in listener.ora file. Then you must change ORACLE_HOME parameter there to indicate new home.

d) Let’s check that the version was changed:

[oracle@oracle01 bin]$  cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/u01/app/oracle/product/11.2.0.4:N

 

[oracle@oracle01 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 25 19:29:01 2016

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

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> select instance_name,version,status from v$instance;

INSTANCE_NAME VERSION STATUS
—————- —————– ————
orcl 11.2.0.4.0 OPEN

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
0

exit

3. Download latest Opatch version for 11.2.

On metalink you can find the latest opatch from the following documet.
Patch – Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)

Choose Release OPatch 11.2.0.0.0
Platform Linux x86-64

Downloaded file name is p6880880_112000_Linux-x86-64.zip

Or you can download from my drive p6880880_112000_Linux-x86-64.zip

Unzip downloaded file and place OPatch folder in 11.2.0.4 home directory. For this you will need to delete or rename existing OPatch folder.

mv /u01/app/oracle/product/11.2.0.4/OPatch /u01/app/oracle/product/11.2.0.4/OPatch_backup

cp -R /0/patch/OPatch  /u01/app/oracle/product/11.2.0.4/OPatch

########## Describe this folder in PATH variable to make your work easy

export PATH=$PATH:/u01/app/oracle/product/11.2.0.4/OPatch

4. Using Oracle Recommended Patch Adviser download
Patch 23054359 – Database Patch Set Update 11.2.0.4.160719 (Includes CPUJul2016)  and apply it to newly upgraded database.

Or download from my drive p23054359_112040_Linux-x86-64.zip

Open readme.html and follow the instuctions.

a) Stop database

[oracle@oracle01 ~]$ sqlplus / as sysdba

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

b) Stop listener

[oracle@oracle01 ~]$ lsnrctl stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

c) Stop Enterprise manager

[oracle@oracle01 ~]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracle01:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control …
… Stopped.

d) Unzip downloaded file and go to the directory named 23054359

cd /0
mkdir patch
#############Place p23054359_112040_Linux-x86-64.zip here
unzip p23054359_112040_Linux-x86-64.zip
cd 23054359

e) Apply the patch(Note we have already described path /u01/app/oracle/product/11.2.0.4/OPatch in PATH variable, that is why writing just opatch command is enough)

 [oracle@oracle01 23054359]$ opatch apply

Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.14
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2016-08-25_20-13-27PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0.4’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…

Applying sub-patch ‘17478514’ to OH ‘/u01/app/oracle/product/11.2.0.4’

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.sdo, 11.2.0.4.0…

Patching component oracle.sysman.agent, 10.2.0.4.5…

Patching component oracle.xdk, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…

Patching component oracle.sdo.locator, 11.2.0.4.0…

Patching component oracle.nlsrtl.rsf, 11.2.0.4.0…

Patching component oracle.xdk.rsf, 11.2.0.4.0…

Patching component oracle.rdbms.rman, 11.2.0.4.0…
Applying sub-patch ‘18031668’ to OH ‘/u01/app/oracle/product/11.2.0.4’

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.ldap.rsf, 11.2.0.4.0…

Patching component oracle.rdbms.crs, 11.2.0.4.0…

Patching component oracle.precomp.common, 11.2.0.4.0…

Patching component oracle.ldap.rsf.ic, 11.2.0.4.0…

Patching component oracle.rdbms.deconfig, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…

Patching component oracle.rdbms.rman, 11.2.0.4.0…
Applying sub-patch ‘18522509’ to OH ‘/u01/app/oracle/product/11.2.0.4’

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.precomp.common, 11.2.0.4.0…

Patching component oracle.rdbms.rman, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…

Patching component oracle.rdbms.deconfig, 11.2.0.4.0…
Applying sub-patch ‘19121551’ to OH ‘/u01/app/oracle/product/11.2.0.4’

Patching component oracle.precomp.common, 11.2.0.4.0…

Patching component oracle.sysman.console.db, 11.2.0.4.0…

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.rdbms.rman, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…

…….

Note: In “Email address/User Name:” I have just pressed the enter.

If you have questions please ask.

Thanks to Giorgi Peikrishvili for this case also 🙂

 

 

Filed under Uncategorized

Oracle: Audit DMLs by specific user

February 18, 2016 Leave a comment

Ordinary auditing do not have option to indicate audit some activities done by specific user.

I mean, you cannot write the following:

audit insert on my_schema.my_table by my_user;  <<—-not possible. The right statement is:
audit insert on my_schema.my_table by access;
or
audit insert on my_schema.my_table by session;

If I want to audit only activities done by my_user, one of the way is to create audit policy like the following;

begin
dbms_fga.add_policy(
object_schema=>’my_schema‘,
object_name=> ‘my_table‘,
policy_name=> ‘my_policy’,
audit_condition => ‘sys_context(”USERENV”,”CURRENT_USER”)=”MY_USER”’,
enable => TRUE,
statement_types => ‘INSERT, UPDATE, DELETE’,
audit_column_opts => dbms_fga.all_columns);
END;

So audit_condition gives the opportunity to check something and in this case we are checking user that is running statements indicated in statement_types option.

–Logs will be located here

SELECT * FROM dba_fga_audit_trail

–To see what policies we have

SELECT * FROM dba_audit_policies

Filed under 11g, 12c, DB, Real World Scenarios, Uncategorized Tagged with , , , , ,

← Older posts