DBA Blogs

SQLLDR Process is showing password

Tom Kyte - 11 hours 50 min ago
Hi Tom, we have a risk of exposing our APPS schema password for host based concurrent programs in Oracle Apps. The ps -ef command exposes the password unless encrypted. We have gone back and added the ENCRYPT option for the Options Field for the Concurrent Program definitions. We have also used $FCP_LOGIN instead of $1 while invoking SQL*Plus or SQL*Loader. As part of these changes, we had a test case where we were trying to observe the this sub process, either SQL*Plus or SQL Loader itself in the ps -ef command. We have observed that SQL*Plus does not display the password in the ps -ef command. However, SQL Loader does display the password. Hence, Can you please let us know the way to fix this or any workarounds for this. Thanks in Advance
Categories: DBA Blogs

Managing Blockchain tables in Oracle Database 20c

Oracle’s multi model database 20c is a yearly short term release support, users and developers will benefit from the converged database approach of managing all data in one single database...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Query Statspack’s “SQL ordered by” sections over a time period

Pythian Group - Fri, 2020-07-31 09:26

In my previous blog post <link>, I presented the statspack_load_trends.sql script, which provides a high-level overview of workload dynamics on DB instance(s) over time. In this post, I’ll present the statspack_top_sqls.sql script, which returns SQL performance statistics by mining the Statspack repository. You can download it <here>.

The script provides the same SQLs with the same performance statistics as in Statspack’s various “SQL ordered by” sections. However, it does so by reporting the figures of all categories in a single line, and, more importantly, does it over a time range, considering each available consecutive pair of snapshots. Thus, it provides a way to quickly identify SQLs contributing the most to a particular “SQL ordered by” category over a time period. Alternatively, we can also check for specific SQL(s) behavior over time.

Its core logic is based on Oracle’s $ORACLE_HOME/rdbms/admin/sprepins.sql (StatsPack Report Instance) script, but it doesn’t rely on its existence to run. Similarly as for statspack_load_trends.sql, it queries the Statspack repository directly. It doesn’t create or use any (temporary) objects, not even the global temporary table that sprepins.sql uses when producing the report. We can also use it to analyze a repository imported from another DB and handles periods spanning instance restart(s).

Important note in case you want to compare the results with Statspack reports

Testing the script by comparing its output to regular Statspack reports (created by running $ORACLE_HOME/rdbms/admin/spreport.sql or sprepins.sql), I noticed that sometimes the numbers in the “SQL ordered by” sections didn’t match between the two. Examples include SQLs reported by my script, but not by Statspack reports. Or even Statspack reports reporting the same SQL (same hash value) multiple times in the same “SQL ordered by” section.
The root cause of those anomalies is described in the MOS note “Statspack Reports Show Different Results In “SQL ordered by …” When Taken At Different Times with Same Snapid (Doc ID 2258762.1)”: “When more than one statspack reports are taken repeatedly in the same session by non-perfstat user, some data might get mixed up in the temporary table used by the reporting script, and the result may get corrupted.”.

The problem was not connecting as the owner of the Statspack repository (usually PERFSTAT) when generating multiple consecutive snapshots looping over a snapshot range. The same was true also when creating a single Statpack report.
The takeaway is to always connect as the Statspack repository owner when running spreport.sql, especially if you use any helper scripts which generate Statspack reports for a series of snapshots.

Usage Starting the script

Let’s see the script in action analyzing a sample Swingbench run on a 2 node RAC database. The output is wide, so I suggest to spool it to a file for easier viewing/plotting:

SQL> spool top_sqls.txt
SQL> @statspack_top_sqls.sql

List SQL by [elapsed_time | cpu_time | buffer_gets | disk_reads | executions | parse_calls | max_sharable_mem | max_version_count | cluster_wait_time]:

Enter a value - default "elapsed_time" :

First, we specify by which category we want the SQLs to be ordered by. We can choose one of the above-listed possibilities, which are the same categories the “SQL ordered by” Statspack report’s sections displays. The script reports the same SQLs in the same order as they appear in the selected Statspack report category.

Suppose we want to order SQLs by “cpu_time”, and that the corresponding Statspack report lists 10 SQLs in the “SQL ordered by CPU” section. The script lists the same ones. However, the added benefit of the script is that it reports values, which the Statspack report doesn’t display. For example, the “SQL ordered by CPU” Statspack report section doesn’t display the “Physical Reads” statistic. Instead, the “SQL ordered by Elapsed time” section lists it. If a SQL isn’t qualified to display in the “SQL ordered by Elapsed time” section, we won’t get those values from the Statspack report.

Next, we provide the DBID and instance number we want to be analyzed. If we don’t provide an instance number, the script considers all which are present in the repository:

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ --------------------------------
 1558102526        1 ORCL         orcl1        ol7-122-rac1.localdomain
 1558102526        2 ORCL         orcl2        ol7-122-rac2.localdomain


Enter DBID to analyze - default "1558102526" :
Enter instance number or "all" to analyze all instancs for DBID = 1558102526 - default "all" :

Finally, we specify the time range we’d like to analyze:

Enter begin time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 10:54" : 15-FEB-2020 12:30
Enter end time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 22:54" : 15-FEB-2020 13:00
Script output

Let’s check what the script output looks like. Because of the output width, I have broken it up in several sections. The whole file containing the spool, and the respective Statspack reports for comparison, can be obtained here .

Since the original spool file contains 280 lines, I’m showing an abbreviated version of the first 8 columns. The full output lists SQLs between each snapshot. Since we didn’t specify which instance number we’re interested in, both instances are considered. Additionally, we see it’s normal and expected, that consecutive snapshots don’t have consecutive numbers. Snapshots 4 and 21 are two consecutive snapshots on instance 1, as we can confirm by checking their snap time. The other columns are self-explanatory:

INSTANCE_NUMBER  B_SNAP_ID  E_SNAP_ID B_SNAP_TIME        E_SNAP_TIME        INTERVAL_MIN  DBTIMEMIN        AAS
--------------- ---------- ---------- ------------------ ------------------ ------------ ---------- ----------
1          1          2 15-FEB-20 12:30:00 15-FEB-20 12:40:00           10       2.30       0.23
&lt;removed 16 lines listing SQLs&gt;
2          3 15-FEB-20 12:40:00 15-FEB-20 12:50:00           10       1.60       0.16
&lt;removed 25 lines listing SQLs&gt;
3          4 15-FEB-20 12:50:00 15-FEB-20 13:00:00           10       1.55       0.15
&lt;etc&gt;
4         21 15-FEB-20 13:00:00 15-FEB-20 13:10:00           10       1.66       0.17

21 22 15-FEB-20 13:10:00 15-FEB-20 13:20:00 10 1.30 0.13

22 23 15-FEB-20 13:20:00 15-FEB-20 13:30:00 10 1.18 0.12

2 11 12 15-FEB-20 12:30:00 15-FEB-20 12:40:00 10 3.81 0.38

12 13 15-FEB-20 12:40:00 15-FEB-20 12:50:00 10 2.70 0.27

13 14 15-FEB-20 12:50:00 15-FEB-20 13:00:00 10 2.50 0.25

14 15 15-FEB-20 13:00:00 15-FEB-20 13:10:00 10 2.94 0.29

15 16 15-FEB-20 13:10:00 15-FEB-20 13:20:00 10 2.18 0.22

16 17 15-FEB-20 13:20:00 15-FEB-20 13:30:00 10 1.98 0.20

 

Let’s check an excerpt of the output for snapshots 1-2 and 2-3. Apart from the “HV” column (SQL old hash value), the other columns are self-explanatory. For blog post brevity, I’m showing only the first 10 SQLs per snapshot pair.

B_SNAP_ID  E_SNAP_ID         HV ELAPSED_TIME_SEC EXECUTIONS ELAPSED_PER_EXEC_SEC PERCENT_OF_DBTIME_USED CPU_TIME_SEC CPU_TIME_MS_PER_EXEC
--------- ---------- ---------- ---------------- ---------- -------------------- ---------------------- ------------ --------------------
        1          2 3565022785            80,55        483                  ,17                  58,47        14,63                 30,3
                     2319948924             55,8       5931                  ,01                  40,51         7,69                  1,3
                     1852190137            14,22       1024                  ,01                  10,32         7,75                 7,57
                     1113394757             8,17      12332                    0                   5,93         2,97                  ,24
                     4194254847              6,4        483                  ,01                   4,64          ,84                 1,73
                     1283549268             4,55        169                  ,03                    3,3          ,89                 5,28
                     2588369535             4,21         24                  ,18                   3,06         1,12                46,55
                     4212635381             4,18         24                  ,17                   3,04         1,09                45,39
                     4219272024             3,97       1396                    0                   2,88          ,86                  ,62
                     2835506982             3,74        173                  ,02                   2,71          ,57                 3,32
                     (..)                                                                                                                                         
        2          3 3565022785            46,93        956                  ,05                  48,79        18,87                19,73
                     2319948924            22,85      11550                    0                  23,75         7,52                  ,65
                     1852190137            15,35       2158                  ,01                  15,95        11,98                 5,55
                     1283549268             6,36        380                  ,02                   6,61         1,65                 4,33
                     2835506982                6        377                  ,02                   6,24         1,03                 2,72
                     1822227481             5,32       7742                    0                   5,53         1,26                  ,16
                     4194254847             4,69        957                    0                   4,87         1,22                 1,28
                     3463613875             4,61        380                  ,01                   4,79          ,62                 1,62
                     1113394757             4,07      25794                    0                   4,23         3,28                  ,13
                     4219272024             3,89       2945                    0                   4,04         1,43                  ,49
                     (..)
B_SNAP_ID  E_SNAP_ID         HV PHYSICAL_READS PHYSICAL_READS_PER_EXECUTION BUFFER_GETS GETS_PER_EXECUTION ROWS_PROCESSED ROWS_PROCESSED_PER_EXECUTION PARSE_CALLS
--------- ---------- ---------- -------------- ---------------------------- ----------- ------------------ -------------- ---------------------------- -----------
        1          2 3565022785           5860                        12,13      261329             541,05            483                            1         483
                     2319948924           4614                          ,78      205925              34,72          26467                         4,46           1
                     1852190137            394                          ,38      131100             128,03           1024                            1        1025
                     1113394757            336                          ,03      124291              10,08         172648                           14           1
                     4194254847            396                          ,82        7760              16,07            483                            1           1
                     1283549268            262                         1,55        6188              36,62            169                            1         169
                     2588369535             76                         3,17       13104                546             24                            1          24
                     4212635381             76                         3,17       13104                546            737                        30,71           1
                     4219272024            167                          ,12       19979              14,31           1396                            1           1
                     2835506982            255                         1,47        3579              20,69            173                            1         173
                                                                                                                                                                  
        2          3 3565022785           1138                         1,19      511742             535,29            956                            1         957
                     2319948924            487                          ,04      402425              34,84          51879                         4,49           0
                     1852190137            164                          ,08      274493              127,2           2158                            1        2157
                     1283549268            303                           ,8       13726              36,12            380                            1         380
                     2835506982            448                         1,19        7218              19,15            377                            1         377
                     1822227481            259                          ,03       23226                  3           7742                            1           0
                     4194254847            150                          ,16       14371              15,02            957                            1           0
                     3463613875            298                          ,78        5844              15,38            380                            1           0
                     1113394757              0                            0      260084              10,08         361116                           14           0
                     4219272024             76                          ,03       42277              14,36           2945                            1           0
B_SNAP_ID  E_SNAP_ID         HV MAX_SHARABLE_MEM_KB LAST_SHARABLE_MEM_KB MAX_VERSION_COUNT LAST_VERSION_COUNT DELTA_VERSION_COUNT CLUSTER_WAIT_TIME_SEC
--------- ---------- ---------- ------------------- -------------------- ----------------- ------------------ ------------------- ---------------------
        1          2 3565022785               55,39                55,39                 1                  1                   1                 13,67
                     2319948924               47,44                47,44                 1                  1                   1                  9,38
                     1852190137               55,38                55,38                 1                  1                   1                  2,65
                     1113394757               43,41                43,41                 1                  1                   1                  2,17
                     4194254847               47,64                47,64                 1                  1                   1                  1,26
                     1283549268               59,46                59,46                 1                  1                   1                   ,83
                     2588369535               55,38                55,38                 1                  1                   1                  2,21
                     4212635381               35,51                35,51                 1                  1                   1                  2,21
                     4219272024               27,42                27,42                 1                  1                   1                  1,15
                     2835506982               55,38                55,38                 1                  1                   1                   ,39
                                                                                                                                                       
        2          3 3565022785              113,27               113,27                 2                  2                   1                 15,91
                     2319948924               47,44                47,44                 1                  1                   0                     9
                     1852190137              113,25               113,25                 2                  2                   1                  1,32
                     1283549268              121,42               121,42                 2                  2                   1                  1,42
                     2835506982              113,27               113,27                 2                  2                   1                   ,72
                     1822227481                31,5                 31,5                 1                  1                   0                   1,5
                     4194254847               47,64                47,64                 1                  1                   0                  1,76
                     3463613875               59,63                59,63                 1                  1                   0                   ,81
                     1113394757               43,41                43,41                 1                  1                   0                   ,41
                     4219272024               27,42                27,42                 1                  1                   0                  1,77
B_SNAP_ID  E_SNAP_ID         HV CWT_PERCENT_OF_ELAPSED_TIME AVG_HARD_PARSE_TIME_MS MODULE                      SQL_TEXT
--------- ---------- ---------- --------------------------- ---------------------- --------------------------- ----------------------------------------------------------------
        1          2 3565022785                       16,97                        JDBC Thin Client            BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
                     2319948924                        16,8                        New Order                   SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     1852190137                       18,61                                                    BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
                     1113394757                       26,55                        Browse Products             SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     4194254847                       19,72                        New Order                   INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_
                     1283549268                       18,16                                                    BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:
                     2588369535                       52,52                                                    BEGIN :1 := orderentry.SalesRepsQuery(:2 ,:3 ,:4 ); END;
                     4212635381                        52,9                        Sales Rep Query             SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE, CUSTOMERS
                     4219272024                       28,85                        New Order                   INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN
                     2835506982                       10,53                                                    BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
                                                                                                              
        2          3 3565022785                       33,89                        JDBC Thin Client            BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
                     2319948924                       39,39                        New Order                   SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     1852190137                        8,63                                                    BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
                     1283549268                       22,26                                                    BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:
                     2835506982                       12,06                                                    BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
                     1822227481                       28,14                        New Order                   SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG
                     4194254847                       37,54                        New Order                   INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_
                     3463613875                       17,49                        Update Customer Details     INSERT INTO CUSTOMERS ( CUSTOMER_ID , CUST_FIRST_NAME , CUST_LAS
                     1113394757                        9,98                        Browse Products             SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     4219272024                       45,53                        New Order                   INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN

 

Final note

Oracle’s sprepins.sql script has a /*+ first_rows */ hint in the inline view containing analytical functions used to compute the current and previous row values from the stats$sql_summary table. The hint is present, but not enabled in statspack_top_sqls.sql. If due to some reason you need to re-enable it, just search for and enable it in the script (or use it’s alternative first_rows(0) ).

Categories: DBA Blogs

Generating and Storing a Report in the Database

Tom Kyte - Fri, 2020-07-31 08:06
>Hi Tom 's team, I am happy for your quick response my old question <b>https://asktom.oracle.com/pls/apex/asktom.search?tag=storing-and-accessing-reports-in-the-database-on-oracle-apex-of-version-20</b> I make a new question for my present problem. I listed those steps I created for that problem: a. I created a table named from file report_archive_tbl.sql on my database or directly on APEX. b. I created a process named store_blobquery_report when press Print button. Print button redirect to url I created for a report query. <code>declare l_report blob; begin l_report := apex_util.get_print_document ( p_application_id => :APP_ID, -- assign id of app or no need p_report_query_name => 'multiquery', -- <b>create name for a report query</b> p_report_layout_name => 'multiquery', -- <b>create name for a report layout</b> p_report_layout_type => 'rtf', -- <b>create a format for a report</b> p_document_format => 'pdf'-- <b>define format for a report<b> ); insert into report_archive ( filename, mimetype, report, created_date, created_by ) values ( 'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')', 'application/pdf', l_report, sysdate, :USER ); end;</code> <b>c. When I want to click print report, one row were inserted into the database for each report you ran. </b>. That step while I pressed Print button, it only show that report downloaded but on a table of report_archive had no data. Thank you in advance ^_^.
Categories: DBA Blogs

Save compressed BLOB to OS file

Tom Kyte - Fri, 2020-07-31 08:06
Hello - We're doing following in oracle db - Extract comma delimited file from Siebel database using SQL query - we're using BLOB to store comma delimited file - Compressing BLOB data using UTL_compress LN_COMPRESS - Sending data to REST API using HTTP post REST service is erroring out due to Oracle compression and can't read oracle compressed data. Btw, its fine when I zip file manually using winzip and post using POSTMAN. REST API owner would like to know Oracle UTL_compress COMPRESS specification. Also there are asking to send OS file (oracle compressed file) Need to know following... 1)what specification Oracle is using for UTL_COMPRESS 2) How can I create file using Oracle COMRESSED blob? Thanks in advance
Categories: DBA Blogs

Archive Log Deletion Policy with a Standby Database

Hemant K Chitale - Thu, 2020-07-30 23:15
Previously, I have blogged about Archive Log Deletion Policy even when a Standby database is not present.

Here's a 19c example with a Standby Database

I first set it to "APPLIED ON ALL STANDBY" meaning that an ArchiveLog can be deleted only if the Primary has confirmed that the ArchiveLog (i.e. all the Redo in that ArchiveLog) has been applied on every Standby database configured for this Primary.

I then attempt to use the DELETE command to delete all recent ArchiveLogs.


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 31 11:45:40 2020
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)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCLCDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f'; # default

RMAN> configure archivelog deletion policy to applied on all standby;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all completed after "sysdate-1/24";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf

128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf

130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


RMAN>
RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf thread=1 sequence=43
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf thread=1 sequence=44
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
118 1 37 A 12-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf

119 1 38 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf

120 1 39 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf

125 1 40 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf

126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf

128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf RECID=118 STAMP=1045695910
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf RECID=119 STAMP=1045696247
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf RECID=120 STAMP=1045696315
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf RECID=125 STAMP=1045696378
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf RECID=126 STAMP=1047209331
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf RECID=128 STAMP=1047209610
Deleted 6 objects


RMAN>
RMAN> list archivelog all completed after "sysdate-1/24";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


RMAN>


RMAN refused to delete ArchiveLogs 43 and 44 that were recently generated because they haven't been applied to the Standby database(s).

Once the Standby confirms that it has received and applied all the Redo in 43 and 44 :


2020-07-31T11:51:53.314269+08:00
PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_43_1036108814.dbf
2020-07-31T11:51:53.676981+08:00
PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_44_1036108814.dbf
PR00 (PID:3718): Media Recovery Waiting for T-1.S-45 (in transit)
2020-07-31T11:51:53.868134+08:00


The Primary can now Delete these ArchiveLogs (even though they haven't been backed up).


RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf RECID=130 STAMP=1047210443
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf RECID=131 STAMP=1047210452
Deleted 2 objects


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN>


If you want to prevent RMAN from Deleting ArchiveLogs that have not been backed up, you can either add the "backed up 'n' times to disk' clause to the DELETE command :


RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf

136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf


RMAN> delete archivelog all completed after "sysdate-1/24" backed up 1 times to disk;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46

RMAN>


OR use a combination Archive Log Deletion Policy :


RMAN> configure archivelog deletion policy to applied on all standby backed up 1 times to disk;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48

RMAN>


After I have confirmed that the Standby has applied the recent ArchiveLogs


PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_47_1036108814.dbf
2020-07-31T12:04:40.251269+08:00
PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_48_1036108814.dbf
PR00 (PID:4569): Media Recovery Waiting for T-1.S-49 (in transit)
2020-07-31T12:04:40.609327+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


I try the DELETE again on the Primary


RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48

RMAN>
RMAN> backup as compressed backupset archivelog all;

Starting backup at 31-JUL-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=45 RECID=134 STAMP=1047210945
input archived log thread=1 sequence=46 RECID=136 STAMP=1047210952
input archived log thread=1 sequence=47 RECID=138 STAMP=1047211344
input archived log thread=1 sequence=48 RECID=139 STAMP=1047211353
input archived log thread=1 sequence=49 RECID=142 STAMP=1047211650
channel ORA_DISK_1: starting piece 1 at 31-JUL-20
channel ORA_DISK_1: finished piece 1 at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_07_31/o1_mf_annnn_TAG20200731T120730_hl7682or_.bkp tag=TAG20200731T120730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-20

Starting Control File and SPFILE Autobackup at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_07_31/o1_mf_s_1047211651_hl768490_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-20

RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf thread=1 sequence=49
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf

136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf

138 1 47 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf RECID=134 STAMP=1047210945
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf RECID=136 STAMP=1047210952
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf RECID=138 STAMP=1047211344
Deleted 3 objects


RMAN>


At first, I cannot delete any of the ArchiveLogs.  Then, after I backup 45 to 49, I  am able to delete 45 to 47 but not 48 and 49 as they have not yet been applied to the Standby (although they have been backed up locally on the Primary server) 

Thus the Archive Log Deletion Policy protects from accidental deletion with the DELETE ARCHIVELOG command.

HOWEVER, the DELETE OBSOLETE command ignores this Policy.


Notes :
1.  Archive Log Deletion Policy has been available since 10g.  However, if you are on 10g or 11g, see Oracle Support Document ID 728053.1
2.  Instead of the "APPLIED ON [ALL] STANDBY" you can also use "SHIPPED TO [ALL] STANDBY" subclause.  This is useful if you have Standby databases that are [deliberately] lagging the Primary  -- .e.g a Standby that does the Apply 4 hours after the Redo is generated on the Primary.
Categories: DBA Blogs

DataPump crashed, now cant create new jobs

Tom Kyte - Thu, 2020-07-30 13:46
Hy Tom, a sheduled task that worked for month crashed sudenly. Its an EXPDP job transfering Data to an external SSD (USB3.0). The Disk still runs. The errors listet after a new test to export are like: (EXPDP SYSTEM/XXXX@....) ORA-31626: now job exists ORA-31638: Job SYS_EXPORT_SCHEMA_01 for User SYSTEM cannot be attached ORA-06512: in "SYS.DBMS_SYS_ERROR", Line 95 ...... I read some hints resulting in deleting all DataPump jobs (was too much I think).
Categories: DBA Blogs

encrypts file using PGP public

Tom Kyte - Thu, 2020-07-30 13:46
Hi, I don't know this is the right place or not but need to complete this task using PL/SQL. Actually I need to write PLSQL Code need to create a file and encrypt the file using PGP public key. I don't know how to achieve the second part of this task (file encryption) can I use dbms_crypto package for file encrypt and decrypt or need to some other rout. Our client already provides the PGP public key we don't need to create a PGP key need to use that key and encrypt/decrypt the file. Hope I clear enough my requirement. Thanks, Zahid
Categories: DBA Blogs

Determine The Column Font

Tom Kyte - Thu, 2020-07-30 13:46
Greetings , Please inform me how to change The Column font Type and size In Apex classic report? In legacy report Builder 6 it is easy Just Select The column and choose the font you want and the size for it how this can be accomplished in APEX ? Looking forward to hearing from you
Categories: DBA Blogs

Slow Delete with Cascade Delete

Tom Kyte - Thu, 2020-07-30 13:46
I'm trouble-shooting a very slow running delete statement in a stored procedure. The delete statement attempts to delete one record from 1 table. The record to be deleted contains a key field that is pervasive throughout the database. Call it Order_Header. Sixteen tables in the database have foreign keys containing cascade delete constraints referencing Order_Header (children tables). The database also contains 11 tables with foreign keys containing cascade delete constraints referencing the children tables (grand children). I had a similar situation with another large cascade delete that was resolved by adding indexes to the foreign keys. The only potential issue that I see in this case is that two of the grandchildren are also children of Order_Header. Stated a little differently, two of the children of Order_Header are also grandchildren of other children of Order_Header. Could this be the cause of the slow performing delete?
Categories: DBA Blogs

Need to to create index on a column for partial Data

Tom Kyte - Thu, 2020-07-30 13:46
Hi Tom, below is my table <code>create table invoice_fact( invoice_id number(7), INV_CHK_ID_DATE varchar2(1000), ACCOUNTING_DATE date, INV_AMOUNT number(12,4) );</code> and below is data INVOICE_ID ACCOUNTING_DATE INV_AMOUNT INV_CHK_ID_DATE 0000001 12-Mar-16 10000.77 0000001,19-Mar-2016,10000.77 0000002 13-Mar-16 10070.74 0000002,21-Mar-2016,10070.74 0000003 14-Mar-16 10124.7 0000003,16-Mar-2016,10124.7 0000004 15-Mar-16 10136.56 0000004,17-Mar-2016,10136.56 0000005 16-Mar-16 10190.35 0000005,23-Mar-2016,10190.35 0000006 17-Mar-16 10200.94 0000006,20-Mar-2016,10200.94 0000007 18-Mar-16 10255.44 0000007,20-Mar-2016,10255.44 0000008 19-Mar-16 10341.86 0000008,20-Mar-2016,10341.86 and my query is <code>select * from invoice_fact where to_date(substr(INV_CHK_ID_DATE,9,instr(INV_CHK_ID_DATE,',',1,2)-9),'DD-MON-RRRR') between '01-MAR-2016' and '01-MAR-2017'; </code> i have a date in inv_chk_id_date column i have to extract that date and need to apply filter in where clause. no of records in my table is more than 20 millions so if i apply filter like above query is taking very long time to execute, is there any way to speed up above query
Categories: DBA Blogs

Query performance difference in RAC instances

Tom Kyte - Thu, 2020-07-30 13:46
Hi The Oracle DB version I am working on is : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Its a RAC with 2 instances. This is about a query which runs quickly (under 15s) under instance #1 but takes between 10-11 mins when it runs under Instance #2. The query is utilizing indexes as expected. User provided me the query and I got the sql_ids of its historical runs via DBA_HIST_SQLTEXT. That gave me 5 sql_ids of the same query run between 20-Jul and 28-Jul. <code>select sql_id from dba_hist_sqltext where sql_text like '%CLAIMTYP%claimtype%');</code> <b>Observation 1:</b> Via following query, I discovered that slower runs are on Instance #2, & faster ones on #1. <code>select distinct h.instance_number,trunc(h.sample_time) run_date,h.sql_exec_start, h.sql_id , h.session_id, h.sql_child_number, h.sql_full_plan_hash_value, to_char(trunc(elapsed_time_total/1000000/60/60/24),'9') || ':' ||to_char(trunc(MOD(elapsed_time_total/1000000/60/60, 24)),'09') ||':' || to_char(trunc(MOD(elapsed_time_total/1000000,3600)/60),'09') || ':' ||to_char(MOD(MOD(elapsed_time_total/1000000, 3600),60),'09') as "Time Taken [dd:hh:mi:ss]" from dba_hist_active_sess_history h,DBA_HIST_SQLSTAT s where s.sql_id=h.sql_id and h.instance_number=s.instance_number and h.sql_id in ('73b4smcjjk38s','bt8sw7vj42sp3',........);</code> <b>Observation 2:</b> In dba_hist_active_sess_history, under column DELTA_INTERCONNECT_IO_BYTES, I found several non-null values under Instance #2 run, which as per Oracle documentation means "Number of I/O bytes sent over the I/O interconnect over the last DELTA_TIME microseconds". Value of that column is NULL in all other cases where same query ran on Instance #1. And there are many rows in ASH for slower run sql_ids but faster runs have only couple of rows (each row indicating 10s interval). Does above observations indicate some issue with server of Instance #2? Thanks.
Categories: DBA Blogs

Extract xml node value with existnode filter condition

Tom Kyte - Thu, 2020-07-30 13:46
Hi Chris, Appreciate for your support. I have a requirement where want to return specific node values if a particular node exist. Example - for the below XML, my output will be 2 records because (Jobsdata/jobList/jobData/job/document) node exist 2 times and returning nodes are (job/document/fileID and job/directoryid ). O/p --> 100,D100 200,D200 Please find the XML table in the Live SQL link . My O/P should be as given below- <code>id fileID directoryid 1 100 D100 1 200 D200</code> Filter Criteria - I don't want to display the directoryid : D300 because there is no <document> tag. Hence, file ID and directoryid should be displayed as <document> tag available for those. Thanks,Asit
Categories: DBA Blogs

Auditing of all action in a session not created by specific MACHINE

Tom Kyte - Thu, 2020-07-30 13:46
Hi Tom thanks for previous solutions . Please guide me or share some helpful links to achieve below requirement. 1--> we want to audit all actions of a session not created by application. 2--> Will a cursor having 1000 records will generate 1000 rows in auditing. Note* We have 12c R2 SE Oracle Database.So no fine grained auditing . I need to store only information like (action,object_name,schema_name,host,OS_user,IP address,time,date). "ALL actions" includes everything (alter,create,drop,update,delete etc) like "audit all by schema whenever successful ". Actual scenario:: An application user is used by many developers ,we want to only audit actions of developer session ,not application. How can we achieve it with minimum impact on performance.
Categories: DBA Blogs

Oracle Cloud for Existing Oracle Workloads

Pakistan's First Oracle Blog - Mon, 2020-07-27 19:57
As the technology requirements of your business or practice grow and change over time, deploying business-critical applications can increase complexity and overhead substantially. This is where Oracle Cloud can assist the organization in an optimum and cost effective way.


To help manage this ever-growing complexity, organizations need to select a cloud solution which is similar to their existing on-prem environments. Almost all the serious enterprise outfits are running some sort of Oracle workload and it only makes sense for them to select Oracle cloud in order to leverage what they already know in a better and modern way. And they can utilize this architecture best practices to help you build and deliver great solutions.

Cost management, operational excellence, performance efficiency, reliability, and security are hallmarks of Oracle cloud plus some more. Oracle databases are already getting complex and autonomous. They are now harder to manage and that is why it only make sense to migrate them over to the Oracle cloud and let Oracle handle all the nitty gritty.

Designing and deploying a successful workload in any environment can be challenging. This is especially true as agile development and DevOps/SRE practices begin to shift responsibility for security, operations, and cost management from centralized teams to the workload owner. This transition empowers workload owners to innovate at a much higher velocity than they could achieve in a traditional data center, but it creates a broader surface area of topics that they need to understand to produce a secure, reliable, performant, and cost-effective solution.

Every company is on a unique cloud journey, but the core of Oracle is same.



Categories: DBA Blogs

DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS

Tom Kyte - Mon, 2020-07-27 12:26
Hi Tom What's the difference between connections, sessions and processes? I read a note from Metalink about the difference but I simply dont get it! May you give a brief explanation? Thank you
Categories: DBA Blogs

Advanced Queueing tables in InMemory

Tom Kyte - Mon, 2020-07-27 12:26
Could I load AQ tables into InMemory and what are possible downsides when doing that? We are using ExaCC rack.
Categories: DBA Blogs

Best approach for information logging in PL/SQL

Tom Kyte - Mon, 2020-07-27 12:26
Hi Tom, I'm wondering what is the best approach (in term of performance) for logging information about PL/SQL code execution and procedures/functions usage. I know that the most common approach is to use PRAGMA AUTONOMOUS_TRANSACTION and insert any data into table or use audit log but I have many systems written in PL/SQL as backend whith API exposed as package procedures and functions. In many situations I need to gather some information about values in parameters passed to procedure. P.S. <i>Generally in my opinion logging this kind of information on production environment isn't the best approach but unfortunately I'm not able to convince decision makers.</i>
Categories: DBA Blogs

DBMS_SYSTEM.ksdwrt affecting the database operations.

Tom Kyte - Mon, 2020-07-27 12:26
Hi Team, Recently we faced performance degradation situation which impacted overall operations of application. From awr report, a proc is taking more than 3 or 4 seconds to complete. Lets call that proc 'A'. In addmrpt, we are getting a statement similar to one given below: Tune the entry point PL/SQL 'A' of type "PROCEDURE". Refer to the PL/SQL documentation for addition information. Rationale 7821 seconds spent in executing PL/SQL "SYS.DBMS_SYSTEM.KSDWRT" of type "PACKAGE". We are focusing on proc 'A' because performance degradation happens when this proc is called on a regular basis (every one or two seconds). But this proc is spending almost all of its time in executing SYS.DBMS_SYSTEM.KSDWRT. We have inferred this from awr and addm reports. Since SYS.DBMS_SYSTEM.KSDWRT is undocumented, we are not getting any information on how this process is working internally. My doubts are as follows: 1) How does SYS.DBMS_SYSTEM.KSDWRT manage to access the alert.log? 2) Is it using any oracle background process to access this log? 3) Can multipe sessions write to log concurrently or a wait occur there? 4) Does writing to log impact other DML operations?
Categories: DBA Blogs

Performance literal/bind variables vs JOIN CLAUSE

Tom Kyte - Mon, 2020-07-27 12:26
Hello! my client's DBA states that Oracle works better when instead of setting, in the where clause, literals or bind variables is explicit in the condition the reference to a JOIN column. please forgive my english I hope the simple example attached is clearer it's true that QUERY 2 is better for Oracle? is there any literature about it? if it is true to what extent does the performance improvement occur? What evidence can I find, for example, in the trace file? <code>CREATE TABLE TABLE1 ( PKCOL1 NUMBER NOT NULL, COL2 VARCHAR2 (10), COL3 VARCHAR2 (10) ); ALTER TABLE TABLE1 ADD (CONSTRAINT TABLE1_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE); CREATE TABLE TABLE2 ( PKCOL1 NUMBER NOT NULL, COL2 VARCHAR2 (10), COL3 VARCHAR2 (10) ); ALTER TABLE TABLE2 ADD (CONSTRAINT TABLE2_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE); BEGIN FOR I IN 1 .. 100000 LOOP INSERT INTO TABLE1 VALUES (i, 'A' || I, 'B' || I); INSERT INTO TABLE2 VALUES (i, 'A' || I, 'B' || I); END LOOP; COMMIT; END; / --QUERY 1 SELECT A.PKCOL1, A.COL2, A.COL3, B.PKCOL1, B.COL2, B.COL3 FROM TABLE1 A, TABLE2 B WHERE A.PKCOL1 = 200 AND B.PKCOL1 = 200; --QUERY 2 SELECT A.PKCOL1, A.COL2, A.COL3, B.PKCOL1, B.COL2, B.COL3 FROM TABLE1 A, TABLE2 B WHERE A.PKCOL1 = 200 AND B.PKCOL1 = A.PKCOL1;</code>
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs