Most software only have a single patch process to update the software, however relational databases are a bit different. You have both a binary set of files for the software and the internal configuration and objects within the database itself. Thus, it is necessary to validate data both patch levels at the OS level and database level. Mismatch patches at this level can cause significant errors during operations.
To see the patch level of the internal database libraries you have to query the data dictionary view “dba_registry_sqlpatch”. Connect to one of the databases you have in your current server using sqlplus and execute the below query.
column action_time format a28
column action format a8
column version format a8
column comments format a30
column status format a10
set line 200 pages 500
select patch_id, version, status, Action,Action_time from dba_registry_sqlpatch order by action_time desc;
orcl (SYS)> column action_time format a28
column action format a8
column version format a8
column comments format a30
column status format a10
set line 200 pages 500
select patch_id, version, status, Action,Action_time from dba_registry_sqlpatch order by action_time desc;orcl (SYS)> orcl (SYS)> orcl (SYS)> orcl (SYS)> orcl (SYS)> orcl (SYS)>
PATCH_ID VERSION STATUS ACTION ACTION_TIME
---------- -------- ---------- -------- ----------------------------
32228578 12.2.0.1 SUCCESS APPLY 19-MAY-21 02.53.44.053902 PM
32119931 12.2.0.1 SUCCESS APPLY 19-MAY-21 02.53.43.930720 PM
31668898 12.2.0.1 SUCCESS ROLLBACK 19-MAY-21 02.53.43.919599 PM
31741641 12.2.0.1 SUCCESS APPLY 01-DEC-20 08.50.09.424579 PM
Category Archives: Oracle
Removing all files older then X days
Log files and output files are automatically generated by applications continuesly. So you will likely have thousands after a few days or weeks of operation. There are multiple ways to remove these older files, but we will demonstrate a simple bash command of
find ./* -mtime +<days> -exec rm {} \;
# Remove files older then 30 days
find ./* -mtime +30 -exec rm {} \;# Remove files older then 7 days
find ./* -mtime +7 -exec rm {} \;# Remove files older then 1 hour
find ./* -mtime +1/24 -exec rm {} \;Oracle rebuilding Database Links
Database links create a connection from one database to another within Oracle and to third party database objects. There are time such as a clone process or movement of data sets to new servers, which may require the rebuilding of database links. The Below sql script will generate a schell script db_links.sh from the originating database which can be executed in newly created database. NOTE: If you are replacing a test database with production data set, you would first execute below script on you test database, perform your clone/replacement procedure, and finally execute the db_links.sh file generated.
-- rebuild_db_links.sql
--
-- Date: 12-1-2018
-- By: Larry Catt
-- Modified: 12-1-2018
-- Rebuilds database links from within an oracle, generating file db_links.sh
-- which must be launched as the oracle software owner.
--
spool db_links.sh
set serveroutput on
declare
v_txt varchar2(4000);
begin
for rec in(select * from dba_db_links
where owner not in('SYS','PUBLIC'))
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('@conn '||rec.owner);
dbms_output.put_line('drop database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
--PUBLIC
for rec in(select * from dba_db_links
where owner='PUBLIC')
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('drop public database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
--SYS
for rec in(select * from dba_db_links
where owner='SYS')
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('drop database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
end;
/
spool offFinding SCN for Oracle Database by Date and time
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
Executing SQL script from command line or executable shell script
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
Oracle 12c – Valid Time Temporal Rules
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”
- 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.
- 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.
- 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
EM Express 12c Configuration Page
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
EM Express 12c Configuration Page
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.
Create PDB in silent mode
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:
- -sourceDB = the name of the CDB database you wish to create your PDB in.
- -pdbName = the name of the new PDB database.
- -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
Copyright (c) 1991, 2014, Oracle. All rights reserved.
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
Copyright (c) 1997, 2014, Oracle. All rights reserved.
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
Oracle 12c – Perform daily administration tasks
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