Category: Tools

Database, Migration | Upgrades | Patching, Shell Scripts, Tools

Getting Oracle version – new utility on 18c oraversion

While scripting on an environment with different Oracle versions, I often needed to get the Oracle version for the target database as usually the SQL to be executed is version depended.

I used different methods to grab the database  version some you can see below:

SQL> select 
substr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1, 
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
1, 
instr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1,
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
'.'
)-1
) version
from v$version
where rownum = 1
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

VERSION
--------------------------------------------------------------------------------
18

Or like this

SQL> select substr(version,1,instr(version,'.')-1) version from product_component_version where product like 'Oracle%';

VERSION
--------------------------------------------------------------------------------
18

But on Oracle 18c there is a new utility called oraversion which you can get the same result as the above queries.

[oracle@server01 ~]$ oraversion
This program prints release version information.
These are its possible arguments:
-compositeVersion: Print the full version number: a.b.c.d.e.
-baseVersion: Print the base version number: a.0.0.0.0.
-majorVersion: Print the major version number: a.
-buildStamp: Print the date/time associated with the build.
-buildDescription: Print a description of the build.
-help: Print this message.
[oracle@server1 ~]$

[oracle@server01 ~]$ oraversion -majorVersion
18
[oracle@server01 ~]$

This could be somewhat useful but I though it was worth sharing.

Until next time.

Elisson Almeida

Database, Tools

Quick ways to transfer files to Oracle Support – MOS directly from the Database Server

 , ,

Hi all,

This will be a quick one but helped me a lot last week.

I was working on an Ora-0600 issue on a 2 node RAC cluster. Working with Oracle Support I was asked to transfer a TFA data as well ADRCI data so they could move forward with the SR, very common process right?

After I generated all the files, it was over 3gb of data to be sent over. If I was to get the file from the database server to my laptop I would need to transfer these files multiple times due to the jump servers in the middle.

This time I was luck as the database server was able to ping the site https://transport.oracle.com so I tried sending the files directly to MOS.

I will show you how I did it. This is not a mistery but can really save you time

You have 2 options which are straight forward

Using curl

curl -T FILE_YOU_WANT_TO_SEND -u MOS_USER https://transport.oracle.com/upload/issue/SR_NUMBER/

Using tfacfl

tfactl upload -sr SR_NUMBER -user MOS_USER FILE_YOU_WANT_TO_SEND

On both you need to provide your MOS credentials the SR number and the file you want o upload

After the upload is done, you will see in the attachments in the SR that the files is with the status like “Transferring to the SR” after a few minutes later you will see the update on the SR saying that the file was uploaded to the SR.

Hope this helps!

Elisson Almeida

Cloud Services, Cloud Services | Exadata | Linux, Database, Tools

Oracle New Visual Online Tools

 , ,

Hi all,
So, after a long time of same old documentation style, Oracle started sharing some nice and visual pages for some specifics. Here are 2 examples:

1. Oracle Move to Cloud  (http://www.oracle.com/goto/move)
This one was shown to me by Ricardo Gonzalez, and it’s a GREAT tool if you are planning migrations to Oracle Cloud in general.

The part I like most is where you select the source and destination formats/versions you want and all recommended migration methods are suggested. Of course additional validation on database are required for each method, but it can potentially remind you of things you may be forgetting.

NewScreenshot 2019-05-08 às 16.56.46

Something else I like very much as well is the section with executive summaries of each migration tool. It’s basically ready for a print screen to put in the slides you are going to present to your boss… 🙂

NewScreenshot 2019-05-08 às 16.57.16.png

Also, you have handy some links to papers for the most different migration scenarios, as per:

NewScreenshot 2019-05-08 às 16.57.24

2. Oracle Database Features: (https://apex.oracle.com/database-features/

NewScreenshot 2019-05-08 às 16.52.14

And by selecting any of the features you can see more info in a very simple way, as per below. By the way, with one click you can be directed to the documentation (the old fashioned one):

NewScreenshot 2019-05-08 às 16.55.08

 

Hope you enjoy those as much as I did and hope Oracle continue providing those sort of interfaces to us!

Cheers!

Database, SQL | PLSQL, Tools

PLSQL: Which code is taking longer?

 , ,

So you have a slow process calling several PLSQL Codes, including Procedures, Functions and etc, but don’t know what is taking longer?

Your problems has ended… 

In Oracle 11gR1 was introduced the PL/SQL Hierarchical Profiler to help developers by providing hierarchical profiler data analysis for PL/SQL programs.

From Oracle base:
“The hierarchical profiler consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.”

For example, we can set a profiler for procedure proc_example_1 and check in all calls and related statistics:

BEGIN
  DBMS_HPROF.start_profiling (
    location => 'ORACLE_DIR',
    filename => 'prof.txt');

  proc_example_1(p_number => 10);

  DBMS_HPROF.stop_profiling;
END;
/

And to see results, a simple output is:

COLUMN owner FORMAT A20
COLUMN module FORMAT A20
COLUMN type FORMAT A20
COLUMN function FORMAT A25
SELECT symbolid,
       owner,
       module,
       type,
       function
FROM   dbmshp_function_info
WHERE  runid = 1
ORDER BY symbolid;

  SYMBOLID OWNER                MODULE               TYPE                 FUNCTION
---------- -------------------- -------------------- -------------------- -------------------------
         1 TEST                 proc_example_1       PROCEDURE            proc_example_1
         2 TEST                 proc_example_2       PROCEDURE            proc_example_2
         3 TEST                 proc_example_3       PROCEDURE            proc_example_3
         4 SYS                  DBMS_HPROF           PACKAGE BODY         STOP_PROFILING
    5 TEST                 proc_example_3       PROCEDURE            __static_sql_exec_line5

5 rows selected.

Which can also be seen with this nice SQL from Oracle Base (Tim Hall):

SET LINESIZE 500 PAGESIZE 1000
COLUMN name FORMAT A100
SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
       a.subtree_elapsed_time,
       a.function_elapsed_time,
       a.calls
FROM   (SELECT fi.symbolid,
               pci.parentsymid,
               RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
               NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
               NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
               NVL(pci.calls, fi.calls) AS calls
        FROM   dbmshp_function_info fi
               LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
        WHERE  fi.runid = 1
        AND    fi.module != 'DBMS_HPROF') a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL;


NAME                                                SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
--------------------------------------------------- -------------------- --------------------- ----------
TEST.proc_example_1                                                31262                    31          1
  TEST.proc_example_2                                              31231                   133         10
    TEST.proc_example_3                                            31098                  3241        100
      TEST.proc_example_3.__static_sql_exec_line5                  27857                 27857       1000

Cheers!

Database, Tools

Segment Creation on Demand or Deferred Segment Creation

 , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Segment Creation on Demand or Deferred Segment Creation

I also talked about it in post EXP Missing Tables on 11.2.

Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.

More“Segment Creation on Demand or Deferred Segment Creation”

Database, Linux, Shell Scripts, Tools

/bin/rm: cannot execute [Argument list too long]

 , , ,

Hey all!

Just a quickie and useful thing today. How many times you found this?

/bin/rm: cannot execute [Argument list too long]

Ok, so, first thing: Is it related to Oracle logs? If so, you may want to ADCRI. Check this post for more info: ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases.

If not, you may solve this using find with rm. Ok, but want to keep the most recent files?

Some examples for you, removing audit files:

# Remove older then 1 day:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -mtime +1 -exec rm {} \;

# Remove older then 1 hour:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -cmin +60 -exec rm {} \;

More“/bin/rm: cannot execute [Argument list too long]”

Database, GoldenGate, Tools

GGATE ABBENDED: ORA-00308: Cannot Open Archived Log

 , , , , ,

Hi all!
Ok, so this is one of the most common issues for GGate administration. How to solve it? Easy thing.

First let’s understand what it means: It means the redologs don’t have the required information (assuming integrated) and you have already deleted archivelogs the extract needs. Why? Probably because you already backed up those archivelogs and they were not needed for the database anymore.

Unfortunately we don’t have any kind of ARCHIVELOG DELETION POLICY to guarantee extracts had already read it, like we have for Dataguard. So, what can we do?

Restore the missing archivelogs.

But first let’s confirm on the errors. Some examples:

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Opening ASM file +ARCH/2_11055_742755632.dbf in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+ARCH/2_11055_742755632.dbf' ORA-17503.

or

ERROR OGG-01028 Oracle GoldenGate Capture for Oracle,ext1.prm: Getting attributes for ASM file +ARCH/2_86720_716466928.dbf,

SQL : (15056)

ORA-15056: additional error message ORA-15173: entry '2_86720_716466928.dbf' does not exist in directory '/...


SOLUTION:

Restore all archive logs starting from recovery checkpoint until the current checkpoint and restart the extract:
More“GGATE ABBENDED: ORA-00308: Cannot Open Archived Log”

Database, Shell Scripts, SQL | PLSQL, Tools

Scheduler Job for OS Audit Trail Cleanup Routine

Hello all!
As you all know, most of OS files generated by oracle, like traces and logs can be cleared/managed using ADRCI. However, more than once, I saw filesystem get full of Audit Trail files.
In general, I see DBAs and companies implementing shell scripts to house keep those files. Usually some variation of the find with rm I posted some weeks ago. However, we have a very good “official” solition for this, using the DBMS_AUDIT_MGMT.

Basically we can create Scheduler Jobs in our databases to keep track on this. The advantages are seveal. The job clear in both nodes, this keep all Oracle information correctly updated, you may receive OEM notification for failure if monitoring jobs, etc.
You can see more detaiils here in MOS Note 731908.1 New Feature DBMS_AUDIT_MGMT to Manage and Purge Audit Information.

How I did?

1. Initial Cleanup to clear files older than a week.
2. Set last archive to a week ago.
3. Created Purge Job, purging older than a week

As per:

More“Scheduler Job for OS Audit Trail Cleanup Routine”

Cloud Services, Cloud Services | Exadata | Linux, Database, SQL | PLSQL, Tools

Database in Cloud: Quickest Procedure to Clone a Schema

Hey all!
So, we all know that operating with files/dump files can be tricky when using DBaaS in Public Cloud. In some situations, like Amazon RDS service, we simply don’t have access to SO.

In this scenario, how can we quickly clone a schema in the database? Using IMPDP with Database Link.

Also note that when working on AWS environments, avoiding to use dumpfiles when dealing with expdp/impdp is also encouraged to save IOPS from local disks (it is capped based on machine type).

This is, of course, also valid for On-Premise environments with limited area for dump files.

For this to work, we only need to create a database link pointing to the same database.
Also, of course, it is mandatory to use remap_schema, with optional clause remap_tablespace.

In the following example the link name is loop and proceed with impdp from a jumpbox with an Oracle client and tns configuration to RDS database.

1. Creating Database Link:

SQL> create database link loop connect to my_sysdba identified by "***" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service-name)))';

Database link created.

SQL> select * from dual@loop;

D
-
X

2. Running IMPDP:

[oracle@jumpbox ~]$ impdp schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log

Import: Release 11.2.0.4.0 - Production on Wed Fev 7 21:03:54 2018

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

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_IMPORT_SCHEMA_02": /******** AS SYSDBA schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.481 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NEW_SCHEMA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
...
. . imported "NEW_SCHEMA"."TABLE1" 0 rows
. . imported "NEW_SCHEMA"."TABLE2" 0 rows
. . imported "NEW_SCHEMA"."TABLE3" 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Wed Fev 7 21:08:52 2018 elapsed 0 00:04:54

Hope it helps,
Cheers!

Community, SQL | PLSQL, Tools

Guru99: PL/SQL 101 Completely Free!

 ,

Hey all!
Just passing by today to share something I found really inetresting, and the best: it’s all free!
Do you know Guru99?

It’s a preety nice repository with lots of free tutorials and guides for PLSQL, SQL and several other languages, plus some other IT-related knowledge, like Big Data, PMP and etc.

It’s not rare to have people asking me “How to Start learning SQL” or saying “All material (including grepora :D) is too much specific, we don’t have good general documentation”.
Even Oracle having a pretty nice documentation on the products and features, general concepts are usually forgotten. Aaaaand, now I have something to link on those cases! 😀

Having a look on Guru99, First I found this guide PL/SQL First Program: Hello World, including detailed steps on:
– How to access SQL* Plus
– Connecting to Database
– How to write a simple program using PL/SQL
– Declaring and usage of variables in program
– Commenting Codes

Then found an entire category PL/SQL Tutorials. Talking to Alex (Editor), seems over 120 hours were spent on it. And they were well spent, the material is really very easy to read and understand. Chapters on this Course are:
1. Introduction to PL/SQL
2. Blocks in PL/SQL
3. PL SQL First Program: Hello World
4. PL/SQL Data Types
5. Complex Data Types in PL/SQL
6. PL/SQL Decision Making Statements
7. Loops in PL/SQL
8. Subprograms: Procedures and Functions in PL/SQL
9. Exception Handling in PL/SQL
10. SQL in PL/SQL
11. Packages in PL/SQL
12. Triggers in PL/SQL
13. Object Types in PL/SQL
14. Dynamic SQL in PL/SQL
15. Nested Blocks in Pl/Sql
16. PL/SQL Identifiers

More“Guru99: PL/SQL 101 Completely Free!”