Category Archives: Oracle

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

12c, Administration, Oracle

Oracle 12c – Valid Time Temporal Rules

April 5, 2017 Larry Catt Leave a comment

In Oracle 12c Valid Time Temporal functionality to define a start and stop time where the data is valid. This article covers the basic of Valid Time Temporal rules for implementation

Temporal Validity

Valid time dimension is placed on a table for each row.
Two hidden columns are added to table definition.
When querying the table, rows will be displayed regardless of temporal validity, unless you filter based on these two columns.
Temporal Validity has no effect on storage or performance.
It provides a new filter capability only.
Provides a range of time for each row when the data is valid.
Date range of validity can be set by user or application.
Concepts of Temporal Validity”
1. VALID TIME – This is a user-defined representation of time. Examples of a valid time include project start and finish dates, and employee hire and termination dates.
2. Tables with valid-time semantics — These tables have one or more dimensions of user-defined time, each of which has a start and an end.
3. Valid-time flashback queries — This is the ability to do as-of and versions queries using a valid-time dimension.
Valid-time periods require pairs of date-time: start and end.

Larry Catt

OCP

12c, EM, Oracle

EM Express 12c Configuration Page

March 17, 2017 Larry Catt Leave a comment

Oracle EM Express Configuration Page provides the ability to administrate the initialization parameters of an instance or multiple instances in a RAC configuration; view the memory configuration and usage; usage of various features; and database properties. This article covers the general layout of EM Express Configuration Page and its functional usage.

Logon the EM Express as an administrative user. In this case we will use the URL: https://10.30.15.63:5500/em. This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default). NOTE: You may receive a certificate warning but select continue to site option. Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.
Configuration drop down give the four options: Initialization Parameters, Memory, Database Feature Usage, and Current Database Properties.
Initialization Parameters lists all current instance parameters and SPFILE parameters. From this page you can adjust most parameters.
Memory option gives more details of memory usage that currently exists and allows the administrator a visual prospective of current environment.
Database Feature Usage lists the features of database used by a user or application. This allows the administrator the ability to focus on features which may present a performance problem.
Current Database Properties lists the value of all database property variables. However this view does not give the ability to update this values.

This article summarized the feature and use of the Configuration Page of Oracle 12c EM Express.

Larry Catt

OCP

Cloud Control, EM, EM Express, Oracle

EM Express 12c Configuration Page

March 7, 2017 Larry Catt Leave a comment

Logon the EM Express as an administrative user. In this case we will use the URL: https://10.30.15.63:5500/em. This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default). NOTE: You may receive a certificate warning but select continue to site option. Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

12c, dbca, linux, Multitenant, Oracle, PDB

Create PDB in silent mode

December 12, 2016 Larry Catt Leave a comment

In Oracle 12c RDBMS you can use the Database Configuration Assistant to create a PDB from shell scripts or command line in an existing CDB. You can provide all of the necessary parameters for the PDB creation through a response file or at the command prompt. In this example we will provide all required command line option to create new PDB called pdb1 in the CDB1 container.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Wed Oct 5 15:10:16 EDT 2016 on pts/2

[oracle@linux2 ~]$

Define the following parameter with values:
1. -sourceDB = the name of the CDB database you wish to create your PDB in.
2. -pdbName = the name of the new PDB database.
3. -pdbAdminPassword = password for the PDB admin user.

You will have to also supply the tags: –silent and -createPluggableDatabase

Update the below DBCA command with the values defined in step 2.

dbca -silent -createPluggableDatabase -sourceDB cdb1 -pdbName pdb1 -pdbAdminPassword password

Example execution.

[oracle@linux2 admin]$ dbca -silent -createPluggableDatabase -sourceDB cdb1 -pdbName pdb1 -pdbAdminPassword password

Creating Pluggable Database

4% complete

12% complete

21% complete

38% complete

85% complete

Completing Pluggable Database Creation

100% complete

Look at the log file “/opt/app/oracle/cfgtoollogs/dbca/cdb1/pdb1/cdb1.log” for further details.

[oracle@linux2 admin]$

View the status of the listener process to validate that the new PDB has been picked up by the listener with the command: lsnrctl status

[oracle@linux2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 20-OCT-2016 10:21:44

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 19-OCT-2016 14:06:54

Uptime 0 days 20 hr. 14 min. 50 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 2 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “cdb1XDB” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

Service “pdb1” has 1 instance(s).

Instance “cdb1”, status READY, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 admin]$

Update the tnsnames.ora file with the reference to newly created PDB1 with the command:

echo -e “PDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Example execution:

[oracle@linux2 admin]$ echo -e “PDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = pdb1)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]$

Test connection to new PDB with the command: tnsping pdb1

[oracle@linux2 admin]$ tnsping pdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 20-OCT-2016 10:32:32

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes the creation of new PDB in silent mode.

Larry Catt

OCP

12c, Administration, Oracle, Oracle 10g, oracle 9i

Oracle 12c – Perform daily administration tasks

December 4, 2016 Larry Catt Leave a comment

Oracle 12c does not eliminate the need for daily administrative tasks of a database administrator, however it does have many enhancement that can aid in the identification of problems/sub-par performance. Additionally, there exist two camps on monitoring the health of a database: One wanting to automate all monitoring tasks and let the database tell you when something is wrong; and One that wants tasks to remain manual where you keep a closer contact with database structures and are able to determine trends. Regardless of how you perform your monitoring, the following list of daily DBA tasks is a good general guideline. NOTE: This list is not limited to just error checking, a DBA job covers both error correction and error avoidance.

Check for any database errors in alert and trace.
Check for any networking errors in listener alert. (other network connectivity logs)
Check for errors in Operating System log files for database servers.
Check for errors in Storage Area Network log files.
Check Space allocation for database storage. (database tablespaces)
Check for Space availability at Operating System Layer.
Check any Application Layer error logs.
Installing, Upgrading, and Patching Oracle Database server software (OS) and application tools. (Test installing, upgrading, and Patching before production implementations)
Allocation of system storage and planning future storage.
Creating primary database storage structure (tablespaces)
Create primary database objects (tables, views, indexes)
Planning backup and recovery procedure.
Consult oracle technical support
Ensure compliance with Oracle license agreement.
Modify database structures.
Managing users and maintain security.
Controlling and Monitoring user activity and privileges.
Monitor and optimize performance.
Maintain archive tape backup/recovery devices (other type of backup device).
Backup and restore database. (including test recovery at least twice a year)

Larry Catt

OCP

Oracle

Oracle 12c – Use Online operation enhancements

April 2, 2016 Larry Catt Leave a comment

Online table redefinition allows to restructure table with little affect to table availability. The table is open for queries and DML for most of the redefine procedure, but is locked in exclusive mode for a short time in process. Redefinition is available in EM Cloud and through DBMS_REDEFINITION package.

Three enhancements in 12c for redefine:

Multiple Partitions – you can create multiple partitions in single redefinition execution. This reduces time for re-partitioning but requires more space, because an interim table is built for each partition creation.
Tables with VPD Policies – You can now redefine tables with Virtual Private Database policies. The copy_vpd_opt parameter of START_REDEF_TABLE procedure must set to handle VPD policies during process. Parameter can be set to:
1. CONS_VPD_NONE – This is the default, for use when VPD not in user, error will occur on a VPD table.
2. CONS_VPD_AUTO – VPD policies will be copied to new table during redefinition process.
3. CONS_VPD_MANUAL – VPD policies must be manually moved to new table, done when structural changes are made that affect VPD policies.
Lock Timeout for FINISH_REDEF_TABLE – You can specify a number of seconds that a redefine process will hold an exclusive lock for swapping interim table. If it is exceeded redefine will exit.
REDEF_TABLE – New procedure in the DBMS_REDEFINITION package, which allows for table redefinition in a single step to change the following:
1. Change the tablespace for a table, partition, index or LOB storage.
2. Compression changes including table, partition, index or LOBs
3. LOB column changes for SECUREFILE or BASICFILE storage.

Larry Catt

OCP

12c, CDB, listener, network, Oracle

Configure listener.ora for CDB

March 6, 2016 Larry Catt Leave a comment

With Oracle 12c multitenant databases connection is still normally performed through the Oracle networking files of listener.ora, tnsnames.ora, and sqlnet.ora. This articles shows the configuration of listener.ora file to support connection to a CDB database called cdb1.

Logon to your Oracle Server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon FEB 24 09:57:56 EDT 2016 on pts/0

[oracle@linux2 ~]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

” (SID_NAME = “$ORACLE_SID”)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

> ” (SID_NAME = “$ORACLE_SID”)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Now start the listener with the command: lsnrctl start

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 10:58:06

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-FEB-2016 10:58:06

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

You can test the listener configuration by suppling the tnsping utility with the <machine_IP>:<Port>/<service_name> as below:

[oracle@linux2 admin]$ tnsping 10.30.15.75:1521/cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:04:40

Used parameter files:

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=cdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

OK (10 msec)

[oracle@linux2 admin]$

This completes configuration of listener.ora file for CDB.

Larry Catt

OCP

12c, linux, Oracle

Part 10 – Building Oracle 12c development suite – Preparing Linux OS for 12c installation

June 8, 2015 Larry Catt Leave a comment

Preparing Linux OS for 12c installation
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration of Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your Linux Server as the root user and create the group “dba” and user account “oracle” to be your oracle software owner with the following commands:
groupadd dba
useradd oracle -g dba -G root
passwd oracle

3. Create the following directory structures to support your oracle software installation with the commands:
mkdir /opt/app
mkdir /opt/app/oracle
mkdir /opt/app/oracle_software
mkdir /opt/app/oracle_inventory
mkdir /opt/app/oracle_inventory/inventory

4. Change ownership of directory structures to the oracle software owner with the following commands:
chown oracle:dba /opt/app
chown oracle:dba /opt/app/oracle
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_inventory
chown oracle:dba /opt/app/oracle_inventory/inventory

5. Change the ownership of the Oracle RDBMS media files downloaded in Part 9 of this serious to the oracle software owner with the following commands:
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_software/*
chmod 777 /opt/app/oracle_software/*

6. Switch to the oracle software owners account and change directory back to /opt/app/oracle_software location.
[root@oel65 oracle_software]# su – oracle
[oracle@oel65 ~]$ cd /opt/app/oracle_software
[oracle@oel65 oracle_software]$

7. Unpack all the mediate files with the following command: “find . -name “*.zip” -exec unzip {} \;”
[oracle@oel65 oracle_software]$ find . -name “*.zip” -exec unzip {} \;
…
..
inflating: database/response/netca.rsp
inflating: database/response/dbca.rsp
inflating: database/response/db_install.rsp
[oracle@oel65 oracle_software]$

8. This completes the setup of the VM Enterprise Linux for Oracle 12c RDBMS installation.

Larry Catt, OCP

Catt Solutions

Category Archives: Oracle

Finding SCN for Oracle Database by Date and time

Executing SQL script from command line or executable shell script

Oracle 12c – Valid Time Temporal Rules

EM Express 12c Configuration Page

EM Express 12c Configuration Page

Create PDB in silent mode

Oracle 12c – Perform daily administration tasks

Oracle 12c – Use Online operation enhancements

Configure listener.ora for CDB

Part 10 – Building Oracle 12c development suite – Preparing Linux OS for 12c installation

Oracle tips and tricks.