Hidden Parameters in Oracle

The oracle database have a houndres of hidden parameters, that a lot of those parameters are not documented.

Know about those parameters are important for dbas involved in database migrations and upgrades.

Many parameters have a high performance improve on different systems.

 set line 150
 col PARAMETER format a30
 col DESCRIPTION format a60
 col SESSION_VALUE format a10
 col INSTANCE_VALUE format a10
 spool HIDDEN_parameter_database.log


 SELECT a.ksppinm AS parameter,
        a.ksppdesc AS description,
        b.ksppstvl AS session_value,
        c.ksppstvl AS instance_value
 FROM   x$ksppi a,
        x$ksppcv b,
        x$ksppsv c
 WHERE  a.indx = b.indx
 AND    a.indx = c.indx
 AND    a.ksppinm LIKE '/_%' ESCAPE '/'
 ORDER BY a.ksppinm;

spool off

Now, after we save this list, is possible that we need started with an planificated change.

Hidden ParameHow can I set the value of a hidden parameter?

alter system set "_report_capture_cycle_time"=0 scope=spfile sid='*';

Remember, the importance that these parameters must be recommendations of the oracle support, because it can change the behavior of the engine and can leave us in the situation of not having support.

How to identified issues with ADRCI

In oracle 19 as the previusly versions ADRCI (Automatic Diagnostic Repository Command Interpreter) its a great tool to solved issues and deal with Oracle Errors and Tracefiles

We could verified in the alert the oracle error :

Here, we will review quickly and easy and introduction to know the most basically commands to start.

I will start, as common with alert log file:

tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
Alert log evindecy

For years, Oracle has provided us with the possibility of reviewing in the alert log, the most critical movements such as the behavior of database engine processes.

Hands on

Now, we starting how to ADRCI we help us to undestand our issue.


oracle@DSORA02:~/JuanAndresMercado $ adrci

Sigue leyendo

How to Start , Stop and Restart database easy in Oracle 19.

Hello friends, here again.

It’s a minimal post to share this option to start, stop and restart the oracle databases as linux services.

If you remember on my last article oracle installation, we used the file oracledb_ORCLCDB-19c to configure and install the databases and containers, we will use the same script as operating system service.

We must run this script as root user ever, because will appear error as the follow:

You must be root user to run the configurations script. Login as root user and try again.
Sigue leyendo

Delete archivelog until time

Many, but many times the people have problems with the archivelogs. And call our team to save you…. The FRA its full.

So… we discovered that they not take policys and controls about the databases , when they have 100 o more database to admin.

Here… the scritp to clean… but remember… this is not the solution.

The solution its planning and automatization. Happy delete !

All:

delete archivelog all;

The Last Day:

delete archivelog until time 'SYSDATE-1';

Specific Sequence or Between Sequence:

delete archivelog until sequence 456;

delete archivelog from sequence 456 until sequence 1000;

Specific Time:

delete archivelog until time "to_date('SEP 21 2018 20:00:00','Mon DD YYYY HH24:MI:SS')";

Install Oracle 19c – RPM Package Mode

Simple and easy, as it should always be.

For years I wanted to witness this moment, install oracle database in a single, simple and fast method.

With the current CI-CD paradigm, continuous deployments, continuous delivery and continuous integration, Oracle really had to provide such a solution.

Through this methodology, which involves performing the installation and configuration of the database, in a simple and agile way, it can be part of a Jenkins process or TeamCity job.


We are going to review the steps that we are going to execute:

  • Configuration of the Operating System and Download Packages.
  • Installation of the database Software.
  • Install a Database, Oracle 19, Container and a PDB.
  • Install Listener and OS Services.

Hands On

Obtain the RPM file and configure the Operating System

On my case , I use Oracle Linux.

Oracle Linux Server release 7.2

I’m login as root , execute the yum commands.

Sigue leyendo

Oracle Database 19c, Now Available !

Oracle 19c

Hi Friends !

I’m come back to share the last recently news about the Oracle Databases.

Oracle Database 19c is the long-term support version of the Oracle Database 12c and 18c family of products, which is available to Premier and Extended Support customers through March 2023 and March 2026, respectively.

It is available on Linux, Windows, Solaris, HP / UX and AIX platforms, as well as Oracle Cloud.

Oracle Database 19c offers customers the best performance, scalability, reliability and security for all their analytical and operational workloads.

Oracle 19 c : https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html

Creating an Oracle Profile

Administering users with different policies have the possibility of major security in oracle database that compliam with all security departments in each organization.

In Oracle 19, have the same way to create profiles as Oracle 12.1, 12.2 , 18 and 19.

CREATE PROFILE GANDALF LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

Here, you have an small explanation about each parameter :

  1. SESSIONS_PER_USER: The user can have any number of concurrent sessions.
  2. CPU_PER_SESSION: In a single session, the user can consume an unlimited amount of CPU time.
  3. CPU_PER_CALL: A single call made by the user cannot consume more than 30 seconds of CPU time.
  4. CONNECT_TIME: A single session cannot last for more than 45 minutes.
  5. LOGICAL_READS_PER_SESSION: In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
  6. LOGICAL_READS_PER_CALL: A single call made by the user cannot read more than 1000 data blocks from memory and disk.
  7. PRIVATE_SGA: A single session cannot allocate more than 15 kilobytes of memory in the SGA.
  8. COMPOSITE_LIMIT: in a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.

Those are not the only paramters ,but this its a great example to start diferent tests.