ORA-15260 Error Dropping Diskgroup

Today I was dropping an asm diskgroup, with the next command;

drop diskgroup CLOUD_DATA_DG;

When I got the next error;

ERROR at line 1:
ORA-15260: permission denied on ASM disk group

The problem here, really is very simple. It is because I have connected to the ASM instance as sysdba (whenever I connect to a database). And the solution is as simple as connecting as sysdba 😦

HTH – Antonio NAVARRO

Advertisements

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

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

Import From Excel To Oracle By Using SQL Developer

SQL Developer is an increasingly important tool. I’ve known it since it first came out and in each new release they get things more and more powerful. I have also seen that many people who use it do not exploit more than 10 -20% of the capabilities of the tool.

Perhaps the main disadvantage that SQL Developer has is that it is programmed in JAVA, which makes it portable between different Operating Systems, but penalizes performance and memory consumption. This tool developed in c could be incredible, but it would imply porting to each platform it supports.

Today I just want to show a feature that has saved me hours of work when importing excel sheets into an Oracle database. Of course, using Microsoft Office or LibreOffice you can open a connector to the database and do the same.

Let’s see the steps

On table, click rigth button and select “Import Data”;

import_sql_dev_1

The next window appears. Here click on Browse and choose the xlsx file

import_sql_dev_2

In this case we will make the insertion as individual inserts, click on “Next”;

import_sql_dev_3

In my case I selected all columns to import. Click on “Next”;

import_sql_dev_4

In this case it is matched by name, click on “Next”;

import_sql_dev_5

In this phase of the process we are shown a summary of all the steps. Click on “Finish”;

import_sql_dev_6

If everything went well we will see a window like this;

import_sql_dev_7

HTH – Antonio NAVARRO

CheckConflictAgainstOHWithDetail Fail With Invalid Patch zip File

Installing a PSU, in the checking phase of prerequisites and being located in the direcotiro where I have decompressed the patch I get the following error;

 
/home/oracle/PSU/Apr_2019/bbdd/29141015> opatch prereq CheckConflictAgainstOHWithDetail
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2019, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : xxxxxxxxxxxxx
Central Inventory : xxxxxxxxxxxxx
   from           : /oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.1.0.2.0
Log file location : xxxxxxxxxxxxx

Invoking prereq "checkconflictagainstohwithdetail"
The patch base location should be a directory or a zip file.

Prereq "checkConflictAgainstOHWithDetail" is not executed.

The details are:
Unable to create Patch Object.
Exception occured : Invalid patch zip file.

OPatch failed with error code 2

In this case a workaround that does not solve the problem is to run the prerequisites check in the following way, located above the directory where we have decompressed the patch.

 
HTH – Antonio NAVARRO

How To move ASM Spfile To A Different Disk Group (Method 3/3)

In this method we use the spmove command, the main advantage is that it automatically updates the GPnP profile, and we also avoid having to erase the original SPFILE as it did with the spcopy.

### Get the curren spfile in use

ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.722601213

### Make a backup

ASMCMD> spbackup +DATA/asm/asmparameterfile/registry.253.722601213 /tmp/ASM_SPFILE.BCK

### Execute the spmove command

asmcmd spmove ‘+DATA/asm/asmparameterfile/registry.253.722601213’ ‘+DATA1/asm/spfileASM.ora’

### Check

asmcmd spget

It is a good idea, as in the previous cases, to bounce the entire CRS stack.

HTH – Antonio NAVARRO

How To move ASM Spfile To A Different Disk Group (Method 2/3)

In this method we use the spcopy command that will make a copy of the spfile, we need to update the XML file used by the GPnP profile, this step is important, because if we do not do it we will have a copy but it will not be used. Subsequently to be a copy you must manually delete the previous file.

### Where is the ASM spfile

asmcmd spget

###  show the diskgroups

asmcmd lsdg

### Copy spfile updating GPnP profile by using -u option

asmcmd spcopy -u +DATA/asm/asmparameterfile/registry.153.754750145 +DATA2/

### Check

crsctl stop has
crsctl start has
asmcmd spget

### Drop the older

asmcmd ls -l +DATA2
asmcmd rm +DATA/asm/asmparameterfile/registry.153.754750145

HTH – Antonio NAVARRO