Category Archives: 12c

OpenJDK JVM not supported while installing Oracle Fusion Middleware on Linux 7

July 20, 2017 Larry Catt Leave a comment

Various software releases will sometimes have incompatibility because the two products were released or tested at about the same time. This is the cause of Oracle Fusion Middleware 12.2.1.0.0 (FMW 12c) and
Oracle Enterprise Linux 7.2 (OEL 7). FMW 12c was thoroughly tested against OEL 6, but due to its release date collision with OEL 7, there are some validation which fail checks during the install. Resulting in the
error below:

[oracle@linux1 STAGE]$ java -Djava.io.tmpdir=/u04/tmp -jar fmw_12.2.1.1.0_infrastructure.jar -silent -ignoreSysPrereqs -responseFile /u01/STAGE/fmw.rsp
Launcher log file is /u04/tmp/OraInstall2017-07-13_08-41-06AM/launcher2017-07-13_08-41-06AM.log.
Extracting the installer . . . . . . . . . . . . Done
The OpenJDK JVM is not supported on this platform.
The log is located here: /u04/tmp/OraInstall2017-07-13_08-41-06AM/launcher2017-07-13_08-41-06AM.log.
[oracle@linux1 STAGE]$

This error is a result of FMW 12c being testing against OEL 6 with JDK 1.7, but the default JDK for OEL 7 is JDK 1.8. This article outlines a procedure to resolve this error and proceed with installation
of Oracle Fusion Middleware 12.2.1.0.0 on OEL 7 or any Linux 7 distribution.

1. Logon to OEL 7 server as the root user.

[larry@linux1 ~]$ su –
Password:
Last login: Tue Jul 13 09:44:38 EDT 2017 on pts/1
[root@linux1 ~]#

2. Determine current JAVA installed with command: java -version

[root@linux2 ~]# java -version
openjdk version “1.8.0_65”
OpenJDK Runtime Environment (build 1.8.0_65-b17)
OpenJDK 64-Bit Server VM (build 25.65-b01, mixed mode)
[root@linux2 ~]#

We will install an alternative JDK for use of FMW with one we know works, in this case that JDK is 1.7.0_79.

3. Move to a directory to install the alternative JDK. In this case we will use /opt with command: cd /opt

[root@linux2 ~]# cd /opt
[root@linux2 opt]#

4. Download the JDK 1.7.0_79 with wget command: wget –no-check-certificate –no-cookies –header “Cookie: oraclelicense=accept-securebackup-cookie” “http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz” “http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-i586.tar.gz”

[root@linux2 opt]# wget –no-check-certificate –no-cookies –header “Cookie: oraclelicense=accept-securebackup-cookie” “http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz”
–2017-07-14 14:14:46– http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz
Resolving download.oracle.com (download.oracle.com)… 70.186.31.18, 70.186.31.9
Connecting to download.oracle.com (download.oracle.com)|70.186.31.18|:80… connected.
HTTP request sent, awaiting response… 302 Moved Temporarily
Location: https://edelivery.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz [following]
–2017-07-14 14:14:47– https://edelivery.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz
Resolving edelivery.oracle.com (edelivery.oracle.com)… 104.72.8.208
Connecting to edelivery.oracle.com (edelivery.oracle.com)|104.72.8.208|:443… connected.
HTTP request sent, awaiting response… 302 Moved Temporarily
Location: https://edelivery.oracle.com/osdc-otn/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz [following]
–2017-07-14 14:14:48– https://edelivery.oracle.com/osdc-otn/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz
Reusing existing connection to edelivery.oracle.com:443.
HTTP request sent, awaiting response… 302 Moved Temporarily
Location: http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz?AuthParam=1473877008_9518549fbff39b6f0a026bcbd58215c9 [following]
–2017-07-14 14:14:48– http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz?AuthParam=1473877008_9518549fbff39b6f0a026bcbd58215c9
Connecting to download.oracle.com (download.oracle.com)|70.186.31.18|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 153512879 (146M) [application/x-gzip]
Saving to: ‘jdk-7u79-linux-x64.tar.gz’

100%[===============================================================================================>] 153,512,879 4.64MB/s in 32s

2017-07-14 14:15:20 (4.58 MB/s) – ‘jdk-7u79-linux-x64.tar.gz’ saved [153512879/153512879]

[root@linux2 opt]#

5. View the downloaded JDK with ls -l command:

[root@linux2 opt]# ls -l
total 149916
drwxr-xr-x. 4 oracle dba 43 Aug 25 14:22 app
-rw-r–r–. 1 root root 153512879 Apr 13 2015 jdk-7u79-linux-x64.tar.gz
drwxr-xr-x. 2 root root 6 Mar 26 2015 rh
[root@linux2 opt]#

6. Unpack the JDK with tar command: tar xzf ./jdk-7u79-linux-x64.tar.gz

[root@linux2 opt]# tar xzf ./jdk-7u79-linux-x64.tar.gz
[root@linux2 opt]# ls
app jdk1.7.0_79 jdk-7u79-linux-x64.tar.gz rh
[root@linux2 opt]#

7. change directory to the newly created JDK directory under /opt with cd command: cd /opt/jdk1.7.0_79/

[root@linux2 opt]# cd /opt/jdk1.7.0_79/
[root@linux2 jdk1.7.0_79]#

8. Use the alternatives command to install new JDK with command:

alternatives –install /usr/bin/java java /opt/jdk1.7.0_79/bin/java 2

9. Set the newly installed JDK as local default with command: alternatives –config java

[root@linux2 jdk1.7.0_79]# alternatives –config java

There are 2 programs which provide ‘java’.

Selection Command
———————————————–
*+ 1 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.65-3.b17.el7.x86_64/jre/bin/java
2 /opt/jdk1.7.0_79/bin/java

Enter to keep the current selection[+], or type selection number: 2
[root@linux2 jdk1.7.0_79]#

10. Install JAR from new JDK with command: alternatives –install /usr/bin/jar jar /opt/jdk1.7.0_79/bin/jar 2

[root@linux2 jdk1.7.0_79]# alternatives –install /usr/bin/jar jar /opt/jdk1.7.0_79/bin/jar 2
[root@linux2 jdk1.7.0_79]#

11. Install javac executable from new JDK with command: alternatives –install /usr/bin/javac javac /opt/jdk1.7.0_79/bin/javac 2

[root@linux2 jdk1.7.0_79]# alternatives –install /usr/bin/javac javac /opt/jdk1.7.0_79/bin/javac 2
[root@linux2 jdk1.7.0_79]#

11. Set the new JAR as the default with command: alternatives –set jar /opt/jdk1.7.0_79/bin/jar

[root@linux2 jdk1.7.0_79]# alternatives –set jar /opt/jdk1.7.0_79/bin/jar
[root@linux2 jdk1.7.0_79]#

12. Set the new JAVAC executable as the default with command: alternatives –set javac /opt/jdk1.7.0_79/bin/javac

[root@linux2 jdk1.7.0_79]# alternatives –set javac /opt/jdk1.7.0_79/bin/javac
[root@linux2 jdk1.7.0_79]#

13. Check the default java version with command: java -version

[root@linux2 jdk1.7.0_79]# java -version
java version “1.7.0_79”
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)
[root@linux2 jdk1.7.0_79]#

14. NOTE: The Java version is now 1.7 79. Logon as the oracle software owner.

[root@linux2 STAGE]# su – oracle
Last login: Wed Jul 14 10:56:10 EDT 2017 on pts/1
[oracle@linux2 ~]$

15. Move to your FMW 12c installation directory and attempt running the installer again. The java error should be removed.

cd /u01/STAGE
java -Djava.io.tmpdir=/u04/tmp -jar fmw_12.2.1.1.0_infrastructure.jar -silent -ignoreSysPrereqs -responseFile /u01/STAGE/fmw.rsp

[root@linux2 jdk1.7.0_79]# cd /u01/STAGE
[root@linux2 STAGE]# su – oracle
Last login: Wed Jul 14 10:56:10 EDT 2017 on pts/1
[oracle@linux2 ~]$ cd /u01/STAGE
[oracle@linux2 STAGE]$ java -Djava.io.tmpdir=/u04/tmp -jar fmw_12.2.1.1.0_infrastructure.jar -silent -ignoreSysPrereqs -responseFile /u01/STAGE/fmw.rsp
Launcher log file is /u04/tmp/OraInstall2017-07-14_02-26-54PM/launcher2017-07-14_02-26-54PM.log.
Extracting the installer . . . . . . . . . . . . . . . . . . . . . . . . .java -Djava.io.tmpdir=/u04/tmp -jar fmw_12.2.1.1.0_infrastructure.jar -silent -ignoreSysPrereqs -responseFile /u01/STAGE/fmw.rsp . . . . . . . . . . . . . . . . . . Done
Checking if CPU speed is above 300 MHz. Actual 3478.990 MHz Passed
Checking swap space: must be greater than 512 MB. Actual 2047 MB Passed
Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)
Checking temp space: must be greater than 300 MB. Actual 32284 MB Passed

Preparing to launch the Oracle Universal Installer from /u04/tmp/OraInstall2017-07-14_02-26-54PM
oraInstFile: /etc/oraInst.loc

16. NOTE: JDK error is now removed.

Larry Catt, OCP

12c, Administration, CDB, Multitenant

Determining SID(s) from Database Server

July 15, 2017 Larry Catt Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current SID(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’
cdb1
[oracle@linux2 ~]$

3. For this system there is only one database with a SID of ‘cdb1’.

Larry Catt
OCP

12c, Administration

Determining ORACLE_HOME(s) from Database Server

July 14, 2017 Larry Catt Leave a comment

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’
/opt/app/oracle/product/12.1.0.2/db_1
[oracle@linux2 ~]$

3. For this system there is only one database with a ORACLE_HOME of ‘/opt/app/oracle/product/12.1.0.2/db_1’.

Larry Catt
OCP

11g, 12c, Administration

Current sid and serial# of a session from SQL*PLUS

June 16, 2017 Larry Catt Leave a comment

1. From within Oracle SQL*Plus session.

2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.

select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SID SERIAL#
———- ———-
408 16328

SQL>

3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.

Larry Catt
OCP

12c, Administration, Oracle, Performance, script, SQL

Finding SCN for Oracle Database by Date and time

June 9, 2017 Larry Catt Leave a comment

Oracle provides the function TIMESTAMP_TO_SCN to determine the database SCN number for a given time. This is very useful in performing multiple Oracle Administrative tasks. This procedure explains the use of this function to extract SCN.

1. Logon to Oracle Database server as the Oracle software owner and start SQLPLUS.

[root@linux2 etc]# su – oracle
Last login: Thu Jun 1 09:25:23 EST 2017 on pts/1
[oracle@linux2 ~]$
[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 1 13:47:01 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

2. Determine Oracle SCN one hour ago by specifying sysdate-1/24

SQL> select timestamp_to_scn(sysdate-1/24) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/24)
——————————
5062307

SQL>

3. Determine Oracle SCN 1 day ago by specifying sysdate-1

SQL> select timestamp_to_scn(sysdate-1) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1)
—————————
4975332

SQL>

4. Determine Oracle SCN 1 week ago by specifying sysdate-7

SQL> select timestamp_to_scn(sysdate-7) from dual;

TIMESTAMP_TO_SCN(SYSDATE-7)
—————————
4696226

SQL>

5. Determine Oracle SCN by specific date time by using the to_date() function with appropriate date mask.

SQL> select timestamp_to_scn(to_date(’25-MAY-2017 06:30:00′,’DD-MON-YYYY HH24:MI_SS’)) from dual;

TIMESTAMP_TO_SCN(TO_DATE(’25-MAY-201706:30:00′,’DD-MON-YYYYHH24:MI_SS’))
————————————————————————
4696226

SQL>

6. This completes the use of the function TIMESTAMP_TO_SCN.

Larry Catt
OCP

11g, 12c, Administration, linux, Oracle, Oracle 10g, oracle 9i, script, unix

Executing SQL script from command line or executable shell script

June 4, 2017 Larry Catt Leave a comment

You can call into SQLPLUS to execute SQL script from any shell script. This procedure shows how to structure your statements to LINUX environment for call into SQLPLUS from shell script.

1. Logon to your LINUX server as a user with access to SQLPLUS.
2. Use VI to create a executable file. Replace the connection string with appropriate username/password@SID values. Additionally, change the script you which to execute in this example we are executing “utlrp.sql”.

sqlplus sys/password@orcl as sysdba < < EOF @?/rdbms/admin/utlrp.sql exit; EOF

Larry Catt
OCP

11g, 12c, Administration, hp-ux, linux, unix

Executing Oracle Script as a LINUX or UNIX background process

May 30, 2017 Larry Catt Leave a comment

There are multiple situations where you have to execute processes in your Oracle environment as a daemon or background process of your OS. This procedure describes the use of “NOHUP” to begin the execution of a background process. In this example we are executing the Oracle 12c upgrade script ‘preupgrd.sql’ on an OEL (Oracle Enterprise Linux) platform.

1. Logon to your OEL server as the oracle software owner.

root@mylinux#su – oracle
oracle@mylinux#

2. Change directory to the location of your script file.

oracle@mylinux#cd /opt/app/oracle/product/12.1.0.2/rdbms/admin/
oracle@mylinux#

3. At the OS prompt execute the NOHUP command passing in a sqlplus connection string and the script name to be executed following by a terminating ampersand sign ‘&’.

nohup sqlplus sys/password as sysdba @./preupgrd.sql &

oracle@mylinux#nohup sqlplus sys/password as sysdba @./preupgrd.sql &
[1] 7260
oracle@mylinux#nohup: ignoring input and appending output to `nohup.out’

oracle@mylinux#

4. The (nohup) command will produce a log file in the local directory named nohup.out which records all terminal output from the daemon.

oracle@mylinux#cat nohup.out
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 26 17:28:19 2017
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

Loading Pre-Upgrade Package…

SQL>

5. You can also see the processing with the system process command (ps) as shown below.

oracle@mylinux#ps -ef|grep nohup
oracle 9707 9268 0 18:11 pts/0 00:00:00 grep nohup
[1]+ Exit 1 nohup sqlplus sys/password as sysdba @./preupgrd.sql
oracle@mylinux#

6. This completes executing an Oracle script as a LINUX or UNIX background process.

Larry J. Catt, OCP

11g, 12c, Administration, hp-ux, linux, Users

Change time zone of Oracle RDBMS on Linux OS

May 11, 2017 Larry Catt Leave a comment

This procedure demonstrates the method to change the time zone setting of an Oracle RDBMS installed on a Linux server. NOTE: Oracle RDBMS by default take the time of the OS which they are installed on. Due to the number and variations LINUX distributions, this procedure may not work on all LINUX installs. This procedure was performed on Oracle Enterprise Linux 7.2 (OEL 7.2).

Logon to your Linux server as root.

[larry@linux2 ~]$ sudo su

[sudo] password for larry:

[root@linux2 larry]#

Change user to Oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 16:36:03 UTC 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 16:38:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Show the current date and time zone for the Oracle database with command: select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

30-NOV-16 04.40.33.575810 PM +00:00

SQL>

Exit out of SQLPLUS and change user to root.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@linux2 ~]$ su –

Password:

Last login: Wed Nov 30 16:38:00 UTC 2016 on pts/1

[root@linux2 ~]#

Change to directory /etc

[root@linux2 ~]# cd /etc

[root@linux2 etc]#

Display the currently set system time with command date.

[root@linux2 etc]# date

Wed Nov 30 12:39:08 UTC 2016

[root@linux2 etc]#

List the current setting to link /etc/localtime and remove link /etc/localtime, using the “rm –f” command.

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 14:22 localtime -> /usr/share/zoneinfo/UTC

[root@linux2 etc]# rm -rf localtime

[root@linux2 etc]#

Create new link to directory /usr/share/zoneinfo/<your time zone> with you correct timezone. In this example we are changing to EST.

ln -s /usr/share/zoneinfo/EST localtime

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 09:23 localtime -> /usr/share/zoneinfo/EST

[root@linux2 etc]#

Execute linux date command to see new timezone.

[root@linux2 etc]# date

Wed Nov 30 11:43:59 EST 2016

[root@linux2 etc]#

Logon as the oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:41:44 EST 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 11:45:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

To verify the new timezone update has been completed, execute the SQL statement: select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

30-NOV-16 11.46.25.722487 AM -05:00

SQL>

This completes update of timezone for Oracle RDBMS on a Linux platform.

Larry Catt

OCP

12c, data pump

Oracle 12c – User Oracle Data Pump enhancements

May 7, 2017 Larry Catt Leave a comment

Transportable Feature:

Data Pump has full transportable export option while performing full database backup with the transportable export flag set to TRANSPORTABLE=ALWAYS along with the FULL parameter. This causes two types of movement methods to be used: Non-Transportable tablespace – Used for SYSTEM and SYSAUX which cannot be transported and have metadata and data moved using direct-path and external tables; and Transportable tablespaces – Only metadata is placed in dump file, data is moved when the data files are copied to target database. This new data pump features can be used for moving non-CDB databases into PDB or PDB into PDB. This can reduce the time by avoiding data being unloaded and reloaded and indexes do not have to be recreated. The full transport is idea for moving database to new computer.

Restrictions of full transportable exports:

DATAFUMP_EXP_FULL_DATABASE privilege is required.
Default tablespace of user performing export cannot be one of the transportable tablespaces.
If tables or columns being transported are encrypted, the encrypted password must be provided using the parameter ENCRYPTION_PASSWORD.
If encryption is used on source and target database, OS must have same endianness.
If source and target have different endianness, you must convert before convert data before transporting.
Full transportable export is not restart able.
All objects must have all their storage in segments in administrative non-transportable tablespaces or in user-defined transportable tables. Data cannot be in both kinds of tablespaces.

Compression feature:

Compression on import – new option for impdp and DBMS_DATAPUMP package which allows for compression changes during import operations. TRANSFORM parameter of impdp has new TABLE_COMPRESSION_CLAUSE when set to NONE, the table gets default tablespace compression or set to valid compression type will compress on import.
Compression on export – new option for expdp and DBMS_DATAPUMP package to control the degree of compression into file. COMPRESSION clause can be used for entire operation, data, metadata, or no compression. By default only metadata is compressed.

Export View as a table:

EXPDP now exports views as a table rather than the view definition. This allows the impdp utility to import the view data as a table.

LOGTIME:

New clause LOGTIME allows messages during export and import to be timestamped. The LOGTIME lause values are:

NONE – no timestamps
STATUS – timestamps on status messages.
ALL – timestamps on all messages.

Audit Commands:

Data Pump operations can now be audited by creating audit policy on component action DATAPUMP. IE

SQL> create audit policy audit_data_pump actions component=datapump all;

Audit policy created.

SQL> audit policy audit_data_pump by scott;

Audit succeeded.

SQL>

No Logging Options:

New option DISABLE_ARCHIVE_LOGGING for TRANSFORM parameter in impdp and DBMS_DATAPUMP package, allows for disabling of redo logging when loading data into tables and indexes. DBA should take full RMAN backup after completion. Redo is still taken for create and alter statements except create index.

Security:

ENCRYPT_PWD_PROMPT parameter has been added to expdp and impdp which tells data pump to prompt for password during operations.

SecureFiles LOB as Default – new option for impdp and DBMS_DATAPUMP package requires all LOB to be created as SecureFiles LOBS. By default LOBs are recreated as exported.

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis

April 11, 2017 Larry Catt Leave a comment

Oracle 12c gives the ability to perform a user privilege analysis to bounce against granted privileges. This allows you to reduce granted privilege down to what the individual user actually needs and no more. This article gives a procedure to perform User privilege analysis.

Logon oracle server as the oracle software owner than logon to sqlplus with sysdba privilegs. If this is done by none privileged user account, the user must have the CAPTURE_ADMIN role granted.

[root@linux2 ~]# su – oracle

Last login: Thu Dec 15 07:50:00 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:05:59 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Define the privilege analysis policy with the CREATE_CAPTURE procedure of DBMS_PRIVILEGE_CAPTURE package. This example below will capture all privileges used.

BEGIN

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(

name => ‘user_priv_capture’,

description => ‘Capture the privileges by user test’,

type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);

END;

Enable the privilege analysis policy.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Logon as the user test and perform DML processing.

SQL> connect test/test

Connected.

SQL> insert into test.test_times(pers_id, first_name) values(99,’test’);

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

Logon as sysdba and disable the privilege analysis policy’s recording of privilege use.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:25:29 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Generate privilege analysis results.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Query the table dba_used_privs to see privileges used during the capture period.

SQL> column username format a12

column sys_priv format a15

column object_owner format a10

column object_name format a25

set pagesize 500

SELECT username, sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture = ‘user_priv_capture’;

SQL> SQL> SQL> SQL> SQL>

USERNAME SYS_PRIV OBJECT_OWN OBJECT_NAME

———— ————— ———- ————————-

TEST CREATE SESSION

TEST SYS USER_TABLES

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DEFAULT_JOB_CLASS

TEST CREATE SESSION

TEST SYSTEM PRODUCT_PRIVS

TEST SYS DBMS_APPLICATION_INFO

17 rows selected.

SQL>

Optionally, drop the privilege analysis policy.

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘user_priv_capture’);

Larry Catt

OCP

Catt Solutions

Category Archives: 12c

OpenJDK JVM not supported while installing Oracle Fusion Middleware on Linux 7

Determining SID(s) from Database Server

Determining ORACLE_HOME(s) from Database Server

Current sid and serial# of a session from SQL*PLUS

Finding SCN for Oracle Database by Date and time

Executing SQL script from command line or executable shell script

Executing Oracle Script as a LINUX or UNIX background process

Change time zone of Oracle RDBMS on Linux OS

Oracle 12c – User Oracle Data Pump enhancements

Oracle 12c – User privilege analysis

Oracle tips and tricks.