Feed aggregator

Introduction to Microsoft Power BI Platform | Everything You Must Know

Online Apps DBA - Thu, 2021-04-22 08:23

Microsoft Power BI is a handy and one-stop-solution tool in every Data Analyst or Business Intelligence practitioner’s toolkit. Power BI is the solution to massage various forms of data into useful and valued business information. Want to know more about Microsoft Power BI Platform? Read the blog post at k21academy.com/da10012 to learn more. We are […]

The post Introduction to Microsoft Power BI Platform | Everything You Must Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Primary and Standby in the same RMAN Catalog

Hemant K Chitale - Wed, 2021-04-21 10:23

 A quick demo of an RMAN Catalog shared by both Primary and Standby databases

For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby

At the Primary :



oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB READ WRITE

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit


Recovery Manager complete.
oracle19c>


Now, at the Standby



oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB MOUNTED

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 04/21/2021 22:26:19
RMAN-01005: Mounted control file type must be CURRENT to register the database

RMAN> quit


Recovery Manager complete.
oracle19c>


(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)

It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).

So, how do I run RMAN Backups and have them saved in the Catalog ?

On the Primary, I run a FULL Backup.


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=280 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


Then, I run a separate backup at the Standby :


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=249 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


So, I now have two different database backups of the same NAME and DBID registered in one catalog.

How do I distinguish them ?  From the RMAN command lime query  I can verify in this manner (querying only for datafile 1 for the purpose of this demo)

At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245 Full 231.82M DISK 00:00:38 21-APR-21
BP Key: 1255 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223134
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
List of Datafiles in backup set 1245
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11072202 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


And at the Standby :

oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300 Full 241.08M DISK 00:00:40 21-APR-21
BP Key: 1308 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223703
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
List of Datafiles in backup set 1300
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11061162 21-APR-21 11072277 NO /opt/oracle/oradata/STDBYDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).

But the listing does show that 

the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf  is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp 

while  the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.

You might also notice that the Checkpoint SCNs are different.



Can I run an SQL query in the RMAN Catalog schema itself ?

oracle19c>sqlplus rmanschema/rmanschema@rmancat

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select db_key, dbid, name from rc_database;

DB_KEY DBID NAME
---------- ---------- --------
1 2778483057 ORCLCDB

SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;

SITE_KEY DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
3 1 PRIMARY ORCLCDB
804 1 STANDBY STDBYDB

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 bs.bs_key, bs.backup_type
3 from rc_site site, rc_backup_set bs
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and bs.start_time > sysdate-1
7* and bs.bs_key in (1245,1300)
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY B
---------- ------- ---------------- ---------- -
3 PRIMARY ORCLCDB 1245 D
804 STANDBY STDBYDB 1300 D

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 df.bs_key, df.file#, df.checkpoint_change#
3 from rc_site site, rc_backup_set bs, rc_backup_datafile df
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and site.db_key=df.db_key
7 and bs.bs_key=df.bs_key
8 and bs.start_time > sysdate-1
9 and bs.bs_key in (1245,1300)
10* and df.file#=1
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
3 PRIMARY ORCLCDB 1245 1 11072202
804 STANDBY STDBYDB 1300 1 11061162


Here we can set that RC_DATABASE has only 1 record for the actual database.  But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.

BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN.  Today's backup of Datafile#1 at the Primary was in Backup Set  1245 while that of the same datafile at the Standby was in Backup Set 1300.  (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.

There are many more RC_%  Tables and Views in the RMAN Schema that you can query.


Categories: DBA Blogs

Docker Image Vulnerabilities & Trivy Image Scanning Demo

Online Apps DBA - Tue, 2021-04-20 02:35

Docker containers in today’s world is seeming to be a must for application deployment! A docker image is a file that defines which data and processes should exist inside a particular container when it starts. Any docker image be it from official repositories or the one built on your own might have vulnerabilities and possess […]

The post Docker Image Vulnerabilities & Trivy Image Scanning Demo appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Flexible Network Load Balancing Now Available

Online Apps DBA - Tue, 2021-04-20 02:22

Network Load Balancer provides the benefits of flow high availability, source and destination IP addresses, and port preservation. It is designed to handle volatile traffic patterns and millions of flows, offering high throughput while maintaining ultra-low latency. Want to know more about Flexible Network Load Balancing? Read the blog post at k21academy.com/oci84 to learn more. […]

The post Flexible Network Load Balancing Now Available appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

How to Enable SSH Equivalency Between EC2 Instances

Pakistan's First Oracle Blog - Tue, 2021-04-20 01:55

 If you want to login to a Linux instance from other Linux instance without password or without mentioning the key, then ssh equivalency is the solution. 

Normally, in order to generate ssh equivalency between 2 Linux instances, you create both public and private keys, then copy them over to other instance and add it to authorized_keys file etc. 

But in EC2 instance in AWS, you have to create or specify the keys during the launch time of instance. When you launch an EC2 instance, public keys are already present in home directory of the user. For example, for Amazon Linux , the public key would be already present in /home/ec2-user/.ssh/authorized_keys file. That is why, you only need the private key to ssh into that server.

Let's say you have another EC2 instance which is Linux based and you want to establish ssh equivalency between these two instances. Let's suppose both are using the same key-pair. It means that both would already have public key present in their /home/ec2-user/.ssh/authorized_keys file. In that case all you need to do is following on both servers to establish ssh equivalency:


1- Login to Instance 1

2- Go to /home/ec2-user/.ssh/ directory

3- Touch a new file

touch id_rsa

chmod 700 id_rsa

4- Copy the content of your pem key and paste it into this id_rsa file

Now you should be able to ssh to the other server, which has the same keypair.

Repeat above steps on other server if you want to enable reverse ssh equivalency.

Categories: DBA Blogs

Is row chaining implicit on "wide" tables?

Tom Kyte - Tue, 2021-04-20 00:26
Hi Tom, in our ETL tool we have a process by which a flat file is read and transformed. A table is created with columns to hold the flat file content and columns for the functions that are applied sequentially per row. the file content is loaded via bulk copy. After the bulk copy a series of update statements are applied into the function columns, again applied sequentially because updates are dependent on previous updates in many cases. All these tables have PCTFREE = 10. The total count of columns is over 255 in all these examples and i was expecting all rows to be chained because of this. However in one case i have ~5% chaining, in others i have 99% chaining. Notes These are edge cases..normally files are not so wide. We are looking at separating the tables. We cannot alter the order of the columns We cannot drop any columns Examples Flat File Col Count Function Col Count % Chain 261 116 7.5% 252 73 99% So some questions: 1. Can the diff in chaining be explained by which columns are being referred to in the update statements? The whole splitting of the table (in memory terms) from the right-hand side perhaps 2. Can PCTFREE settings have any positive impact on chaining with wide tables? 3. Are there any other tactics you can suggest?
Categories: DBA Blogs

TEMP_UNDO_ENABLED parameter

Tom Kyte - Mon, 2021-04-19 06:06
what is the use of "TEMP_UNDO_ENABLED" in oracle database. when our user also use global temporary table. When it's necessary to set TEMP_UNDO_ENABLED = TRUE in which circumtance? If I enable this parameter to true so it's decrese the performance of database ?
Categories: DBA Blogs

tkprof sort option to find most expensive query

Tom Kyte - Mon, 2021-04-19 06:06
Hi, I saw various sort options in tkprof. sort=(prsela,exeela,fchela) sort=PRSDSK, EXEDSK, FCHDSK sort=EXECPU,FCHCPU sort=(exeela,fchela) I really want to get poorly performing queries on the top. and then i want to print 10 top most poorly written queries from trace file. PRINT = 10 tkprof a.trc limited_data sys=no explain=apps/ABC@DB sort=(prsela,exeela,fchela) print=10 Hence what is a right sorting option? And is one option is helpful for both Select & DML ( insert/update/delete) statements or need to use two sort options. Please can you provide some light. Thank Amey
Categories: DBA Blogs

Oracle Developer Tools Review

Andrejus Baranovski - Mon, 2021-04-19 03:28
I walk through Oracle Developer Tools and giving my honest opinion about the past, present, and future of Oracle products for developers.

 

What is AWS Command Line Interface (CLI) | How to Install and Configure

Online Apps DBA - Mon, 2021-04-19 01:57

AWS Command Line Interface (CLI) is a unified configuration to administer AWS public cloud services. With only one tool, we can download, configure and monitor multiple AWS services using commands and automate them via scripts. Firstly we will set up CLI on any operating system like Windows, Linux, macOS, or Docker containers then it can […]

The post What is AWS Command Line Interface (CLI) | How to Install and Configure appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Use Different Listener For Standby Duplication?

Michael Dinh - Sun, 2021-04-18 11:59

I know what you are thinking!

Why make this more complicate than it needs to be and is an ounce of prevention worth a pound of cure?

Oracle support started patching Oracle Exadata Cloud@Customer (ExaCC) environment.

After patching, the listener did not start because there were entries in the listener referencing database and oracle home that have been removed.

There are multiple database homes for the same database versions and this is how it was implemented.

Primary database (DB_ASHBURN) was used to create a second standby (DB_PHOENIX).

The primary database (DB_ASHBURN) was switchover to second standby (DB_PHOENIX)

DB_PHOENIX is now the new primary and DB_ASHBURN is the standby.

DB_ASHBURN (standby) was decommissioned and ORACLE_HOME was removed.

Unfortunately, listener.ora was not modified and failed to start after patching was completed.

Here is an example for LISTENER failed to start.

[oracle@ol7-112-dg1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:19:49

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

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/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/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0.4/dbhome_2/bin/oracle for SID DB_PHOENIX
[oracle@ol7-112-dg1 admin]$

---------------------------------------------------------
--- ORACLE_HOME may be in /etc/oratab but does not exist.
---------------------------------------------------------
[oracle@ol7-112-dg1 admin]$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/oracle/product/11.2.0.4/dbhome_1
/u01/app/oracle/product/11.2.0.4/dbhome_2

[oracle@ol7-112-dg1 admin]$ ls -ld /u01/app/oracle/product/11.2.0.4/
drwxr-xr-x. 3 oracle oinstall 22 Apr 14 18:29 /u01/app/oracle/product/11.2.0.4/
[oracle@ol7-112-dg1 admin]$

Having separate listeners, LISTENER started without issues

[oracle@ol7-112-dg1 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:32:24

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

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/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/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg1.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-APR-2021 16:32:25
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/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk_DGMGRL" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-112-dg1 admin]$

What’s your preference, having separate listeners to play it safe or decommissioning the environment properly and completely?

Pretty Print JSON in PL/SQL

Jeff Kemp - Fri, 2021-04-16 01:11

I have been working with some code that uses JSON, sometimes fairly large documents of the stuff, and it’s often necessary to send this to the debug log (e.g. DBMS_OUTPUT) for debugging; however, the builtin functions that convert a JSON object to a string (or clob) return the JSON document in one big long line, like this:

{"surname":"Jones","name":"Bob","age":42,"alive":true,"children":[{"name":"Sally","age":10},{"name":"Terrance","age":8},{"name":"Ulyses","age":6}]}

To show this formatted, I added the following function using JSON_SERIALIZE with the “PRETTY” option, to my utility package:

function format_json (p_clob in clob) return clob is
    l_blob blob;
    l_clob clob;

    function clob_to_blob(p_clob clob) return blob is
        l_blob blob;
        o1 integer := 1;
        o2 integer := 1;
        c integer := 0;
        w integer := 0;
    begin
        sys.dbms_lob.createtemporary(l_blob, true);
        sys.dbms_lob.converttoblob(l_blob, p_clob, length(p_clob), o1, o2, 0, c, w);
        return l_blob;
    end clob_to_blob;

begin
    l_blob := clob_to_blob(p_clob);
    select JSON_SERIALIZE(l_blob returning clob PRETTY) into l_clob from dual;
    return l_clob;
end format_json;

Note that my function takes a CLOB, not a JSON object, because sometimes I receive the data already as a CLOB and I don’t want to require conversion to JSON before passing it to my formatting function.

Now, when I call this function:

declare
    l_json     json_object_t := json_object_t();
    l_children json_array_t := json_array_t();
    l_clob     clob;
begin
    l_json.put('surname','Jones');
    l_json.put('name','Bob');
    l_json.put('age',42);
    l_json.put('alive',true);
    l_children.append(json_object_t('{"name":"Sally","age":10}'));
    l_children.append(json_object_t('{"name":"Terrance","age":8}'));
    l_children.append(json_object_t('{"name":"Ulyses","age":6}'));
    l_json.put('children',l_children);
    l_clob := l_json.to_clob;
    l_clob := utility_pkg.format_json(l_clob);
end;

I get the following result:

{
  "surname" : "Jones",
  "name" : "Bob",
  "age" : 42,
  "alive" : true,
  "children" :
  [
    {
      "name" : "Sally",
      "age" : 10
    },
    {
      "name" : "Terrance",
      "age" : 8
    },
    {
      "name" : "Ulyses",
      "age" : 6
    }
  ]
}

Where to Put PostgreSQL in AWS

Pakistan's First Oracle Blog - Thu, 2021-04-15 22:44

When it comes to putting PostgreSQL database in AWS, you are spoiled for choice. There are 3 ways to do that:



1) Install and configure PostgreSQL on EC2 instance.

2) Amazon RDS for PostgreSQL

3) Amazon Aurora for PostgreSQL

You can watch the whole video here.

Categories: DBA Blogs

Radio radio button prompt shifted after upgrade to 20.2

Tom Kyte - Thu, 2021-04-15 10:26
We are upgrading Apex from 5.2 to 20.2. Our applications are using theme 26. (Of course we need to update the applications to theme 42, but for now this step is too big.) After the upgrade, the prompt of the region buttons is shifted to left (behind the buttons). See https://apex.oracle.com/pls/apex/f?p=18605:1. Can we easily solve this with an css-adjustment?
Categories: DBA Blogs

convert database server sysdate to GMT date and daylight saving aware

Tom Kyte - Thu, 2021-04-15 10:26
We need to code a function to convert database server sysdate to GMT date. So we use: alter database set time_zone='EST' to set dbtimezone, and then code something like: CREATE OR REPLACE FUNCTION FN_GET_GMT_DATE RETURN DATE IS v_dbtimezone varchar2(10); BEGIN select dbtimezone into v_dbtimezone from dual; RETURN NEW_TIME(SYSDATE, ltrim(rtrim(v_dbtimezone)), 'GMT'); END FN_GET_GMT_DATE; / But this is not daylight saving awarance. What's the best way to convert database sysdate to GMT date and also daylight saving aware?
Categories: DBA Blogs

Deploying Oracle RAC on Docker

Tom Kyte - Thu, 2021-04-15 10:26
Hello Ask-Tom-Team, I came across this Oracle documentation ?Deploying Oracle RAC on Docker?: https://www.oracle.com/technetwork/database/options/clustering/rac-ondocker-bp-wp-5458685.pdf Specifically the sentence: OracleRAC on Docker supports the <b>non-privilege</b> mode feature. This allows Oracle RAC to safely and securely run on a single hostor <b>multiple hosts without interference</b> from the other DockerContainers. Does it mean, I could install two Oracle RAC clusters (e.g. 2 node each, 4 total) on multiple containers on the same physical host without interference between them? Oracle RAC clusterware uses Linux-Root account taking over the entire physical host. With Oracle RAC Docker non-privilege mode feature, I guess that wouldn't be the case. Where do I find more information on this Oracle RAC Docker non-privilege mode feature? Does Oracle supports / certifies "RAC on Docker" for production systems? Thank you. Elio
Categories: DBA Blogs

Password Security During Logins in 2021

Tom Kyte - Thu, 2021-04-15 10:26
Greetings, I have a few questions regarding how Oracle by default encrypts the password when sent to the Oracle databases. (https://asktom.oracle.com/pls/apex/asktom.search?tag=password-security-during-logins&p_session=117601880297172) First, please confirm that Oracle by default also encrypts the password when the client uses a JDBC thin client to connect to the databases. Second, what kind of encryption algorithm (AES128, DES, etc.) does it use? Third, why doesn't the default encryption of passwords require the complexity of setting up SSL encryption for data in motion? The complexity I am referring to is the need to create various server and client certificates ( CA certificate, self signed, pass it to through SHA-2 algorithm), create wallets and setup several sqlnet.ora parameters on both client and server? Forth, do you think it is a good idea for Oracle Oracle to provide a simple on/off option to turn on the same default password encryption but for all data in motion? Lastly, if Oracle encrypts the password automatically, why does Oracle provide this document: "Step by Step Guide To Configure SSL Authentication (Doc ID 736510.1)?" "This article describes how to configure and use the Secure Sockets Layer (SSL) for Oracle Database User Authentication." If Oracle encrypts the password by default, then Oracle providing the document seems unnecessary. Thanks for your valuable insights. John
Categories: DBA Blogs

ORA-65040 when loading jar file into pluggable database with sys.dbms_java.loadjava or commandline tool loadjava

Tom Kyte - Thu, 2021-04-15 10:26
I want to load a jar file into the database. First I loaded it into the root container. loadjava -o -f -noverify -synonym -stdout -verbose -recursivejars -resolve -grant PUBLIC -user sys/password@CDB javax.mail.jar >\mail.log This also works, but I don't have access to it in the PDB. Trying to load the jarfile into the PDB fails with the error ORA-65040: operation not allowed from within a pluggable database. The same message comes when I use dbms_java.loadjava('-v -r -g PUBLIC /path/java/javax.mail.jar'); after switching into the PDB. How can I load jarfiles into a PDB or make them available there? Thanks for help Sigrid
Categories: DBA Blogs

How to exclude rows from outer join calculation but still include them in results

Tom Kyte - Thu, 2021-04-15 10:26
Hello Chris, Hello Connor. I have a view with a schedule of some kind of launches: <code>create or replace view v_schedule as select trunc(sysdate) + interval'1'minute * level as plan_date from dual connect by level <= 100000</code> And a log of actual launch dates: <code>create table t_launches as select trunc(sysdate) + interval'1'minute * level + mod(abs(dbms_random.random), 60) * interval'1'second as launch_date from dual connect by level <= 1000;</code> I want to join them like that: <code>create table t1 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule ) left join t_launches on (launch_date >= plan_date and launch_date < next_plan_date);</code> This works for about 10 seconds. But I have an extra piece of information: launch_date cannot be larger than sysdate(<i>trunc(sysdate) + interval'1'minute * 1000 in this example</i>). So for 99% of rows in v_schedule there is no point in even trying to join them with anything in t_launches. So a query like that works for about half a second: <code>create table t2 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule where plan_date <= trunc(sysdate) + interval'1'minute * 1000 ) left join t_launches on (launch_date >= plan_date and launch_date < next_plan_date) union all select plan_date, null from v_schedule where plan_date > trunc(sysdate) + interval'1'minute * 1000;</code> My question is: Is there any way to tell Oracle to filter rows from an outer join calculation but still include them in the results? I thought this should work, but it doesn't: <code>create table t3 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule ) left join t_launches on (plan_date <= trunc(sysdate) + interval'1'minute * 1000 and launch_date >= plan_date and launch_date < next_plan_date);</code> I would very much like to avoid union all because my actual query is much larger and complicated than this and union all would almost double its size. Besides, it does an extra pass over v_schedule.
Categories: DBA Blogs

Synchronize PK values

Tom Kyte - Wed, 2021-04-14 16:06
Hi! We would appreciate your advice about problem we have. We have two tables of the same structure in different DB (DB1 and DB2) . Rows are inserted into table in DB1, and then some of them (based on simple condition) are being prorogated to the table in DB2 via trigger using dblink. When rows are inserted into those tables different sequences are used to generate primary key values. So, all the data in rows being pushed between DB is identical except for PK values. Now, we need to synchronize PK values as well and we would like to drop sequence in DB2 and insert rows with incoming PK values. Is it possible to actualize PK values for already inserted rows in the table in DB2 or the only way in to drop and recreate the table ? Thank you!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator