Create DB Link In Other Schema

Today I want to remember an old method to create database links, it is also valid for other types of objects, when we do not have the password of the user owner or who is going to host the object created.

The trick is basically to create a series of procedures, putting as owner the schema where we want to create the database link. For this we must also have permission (create any procedure). Let’s see how it would be

# Step 1

 
REM
REM Create the procedure
REM 
CREATE OR REPLACE PROCEDURE CLOUD.WRAP_ANR IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE DATABASE LINK TEST1 CONNECT TO REMOTEUSER IDENTIFIED BY "SECRET" USING ''REMOTE_CS'' ';
END;
/

# Step 2

REM
REM  Execute the procedure for create the DB Link.
REM 
BEGIN
  CLOUD.WRAP_ANR;
END;
/

# Step 3

REM
REM  Drop the auxiliary procedure.
REM 
DROP PROCEDURE CLOUD.WRAP_ANR;

HTH – Antonio NAVARRO

Advertisements

Install TFA For Nonroot User

Usually the TFA is installed as the root user, but there is also the option to install it as a different user. It must be indicated that if this option is chosen, the following characteristics will be lost;

To be able to execute it as Daemon
Automatic collections
Collections from remote hosts
To install as an ORACLE_HOME owner use the –extractto option;

Unzip the zip file

 
> ls -lrt 
-rw-r--r--   1 grid18     oinstall 233963383 Jul 12 12:16 TFA-SOLARIS-SPARC64_v19.2.1.zip
> unzip TFA-SOLARIS-SPARC64_v19.2.1.zip
Archive:  TFA-SOLARIS-SPARC64_v19.2.1.zip
  inflating: README.txt
  inflating: installTFA-SOLARIS-SPARC64

> ls -lrt
total 916144
-rw-r--r--   1 grid18     oinstall    1460 Apr 25 20:09 README.txt
-rwxr-xr-x   1 grid18     oinstall 234732682 Apr 25 20:44 installTFA-SOLARIS-SPARC64
-rw-r--r--   1 grid18     oinstall 233963383 Jul 12 12:16 TFA-SOLARIS-SPARC64_v19.2.1.zip

Installing using the -extractto option;

 
> ./installTFA-SOLARIS-SPARC64 -extractto xxxxxxxxxxxxxxxxxxxx

TFA Installation Log will be written to File : /tmp/tfa_install_1644_2019_07_12-12_16_51.log

Starting TFA installation

TFA Version: 192100 Build Date: 201904251105

Running Extractto Setup for TFA as user grid ...

Enabling Access for user grid on mofetac...
TFA is successfully installed...

TFA_BASE for user grid : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

HTH – Antonio NAVARRO

My Scripts For Baselines (Oracle)

Hi, in this post I would like to put a link to my github, where I have left a series of scripts related to the use of baselines in Oracle. I use them as if they were an API to perform optimizations, giving the change to “slow” execution plans (and I do not want to say that they are bad, about this we can talk a lot) for faster or more efficient ones.

Baselines Scripts

HTH – Antonio NAVARRO

Necessary Permissions To Execute SQL Advisor

Today a person from Dev Team ask me about the necessary permissions to execute the Oracle SQL Advisor. This Advisor has get better with the years and it is a powerfull tool for test and optimize SQL in an easy way. It don’t requeried a special expertise and can save many hours from other people (seniors dev, DBAs, etc.). Below show the permissions you need;

grant advisor to <USER>
grant administer sql tuning set to <USER>

In many clients where I worked it is usual create a rol (ess rol_developer_team) with this permissions (and others like debug) and assign to developers users.

NOTE;

Privilege escalate and security checking is guarantee by its (DBMS_SQLTUNE) being the main privilege ADMINISTER SQL MANAGEMENT OBJECT.

HTH – Antonio NAVARRO

AWR tool is not supported on Active Data Guard

I have recently been asked for performance reports from a database in ADG (Active Data Guard), the problem has been that AWRs are not available in this architecture until version 12.2 of Oracle. In my case this is a 12.1 so it can not be used.

As an alternative Oracle proposes to use adapted version of the statpack, known as standby statspack. For more information about this process you can see the note ML / MOS  “Installing and Using Standby Statspack (Doc ID 454848.1)”.

HTH – Antonio NAVARRO

Installing and Using OSWatcher (Release 8.1.2)

For those who do not know this tool, tell you that it is basically a shell script that collects a series of unix commands about the time dimension.

OSWatcher only use the next operating systems commands to recopile information;
VMSTAT
IOSTAT
MPSTAT
IFCONFIG
NETSTAT
TOP
TRACEROUTE

 

To install the latest version you must download it from ML / MOS, you need a license, even the most basic one.

To start it (in an easy way, many options are avaliables);

./startOSWbb.sh

To stop it;

./stopOSWbb.sh

To see the data;

Normally these traces are used to send information to Oracle, but you can see the data graphically with the following command

java -jar $OSWATCHER_HOME/oswbba.jar -i $OSWATCHER_HOME/archive

 

HTH – Antonio NAVARRO