Identity columns in Oracle 12c

The identity column introduced in Oracle 12c can be used in the different manners, according to your needs. Here are some basic examples:

create table songs (
    song_id number generated always as identity,
    title varchar2(128)
);

The "generated always" clause is default and may be omitted.

In this scenario, no inserts may contain a value for the column song_id, even if the datatype is correct. Neither null values are accepted. Oracle will generate its own value and will not allow any manipulation of the value recorded in the identity column.


Allow for the possibility to override the auto generated numbers by specifying "by default" instead of "always":

create table songs (
    song_id number generated by default as identity,
    title varchar2(128)
);

In other words, the statements below would both be permissible:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;

However, null cannot be specified as a legitimate value for the identity column:

insert into songs values(null, 'White wedding');

would return the error

ORA-01400: cannot insert NULL into ("MUSIC"."SONGS"."SONG_ID")

If you need to allow for NULL values in the identity column, you create the table as follows:

create table songs (
    song_id number generated by default on null as identity,
    title varchar2(128)
);

With the above definition, Oracle

* permits overriding the autogenerated numbers
* allows update of the identity column
* allows null values to be specified during inserts

All of the three statements below are thus valid:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
insert into songs values(null, 'White wedding');

Tim Hall has published a more in-depth article about the same topic, as usual of the highest quality

How to run RMAN with debug information

Tested with Oracle 12.2

For example, to trace a duplicate database session, I have put the following into a file called "run_duplication.cmd":

spool trace to run_duplication.trc
spool log to run_duplication.log
set echo on;
debug on;


connect target sys/password@sourcedb
connect auxiliary sys/password@auxdb

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
DUPLICATE DATABASE "sourcedb.mydomain.com" TO auxdb
FROM ACTIVE DATABASE
TABLESPACE TOOLS
USING COMPRESSED BACKUPSET;
}
exit

When you execute your rman script:

rman cmdfile='run_duplication.cmd'

your log and trace files will be generated in your working directory.

How to use the TIMESTAMP_TO_SCN function

select timestamp_to_scn(to_timestamp('2020-05-11 14:36:22', 'YYYY-MM-DD HH24:MI:SS')) "scn" from dual;

scn
----------
20325895

Reversed:

select scn_to_timestamp(20325895) "timestamp" from dual;

timestamp
----------------------------------
11-MAY-20 02.36.20.000000000 PM

How to avoid the error Can't locate Data/Dumper.pm in @INC during installation of AHF

During installation of ahf, I received the following error:

Extracting AHF to /u01/ahf/oracle.ahf
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_per l /usr/lib64/perl5 /usr/share/perl5 . /u01/ahf/oracle.ahf/tfa/bin /u01/ahf/oracle.ahf/tfa/bin/common /u01/ahf/oracle.ahf/tfa/bin/modules /u01/ahf/oracle.a hf/tfa/bin/common/exceptions) at /u01/ahf/oracle.ahf/tfa/bin/common/tfactlshare.pm line 1350, line 1.
BEGIN failed--compilation aborted at /u01/ahf/oracle.ahf/tfa/bin/common/tfactlshare.pm line 1350, line 1.
Compilation failed in require at /u01/ahf/oracle.ahf/tfa/bin/tfasetup.pl line 107, line 1.
BEGIN failed--compilation aborted at /u01/ahf/oracle.ahf/tfa/bin/tfasetup.pl line 107, line 1.

Solution: Install missing packages:

su -
yum install perl-Data-Dumper

At this point, you need to uninstall AHF:

tfactl uninstall

Then cleanup old files. Go to the directory where you attempted to install AHF;

cd /u01/tfa
rm -rf oracle.ahf

Go to the directory where the zipped file has been extracted, and run the installation again:

/u01/oracle/patch/AHF
./ahf_setup

Potential solution to ORA-1033 during configuration of standby database in data guard broker

In my environment, the following message was displayed in dgmgrl when trying to configure a data guard environment:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

I had prior to this attempt duplicated the target database to the auxiliary using "duplicate target databas for standby from active database" - and the output looked fine.

Turns out that you cannot set the parameter redo_transport_user to any other value than SYS when cloning for standby.
In my environment, we use a dedicated user for this purpuse, as we avoid using the SYS user as much as possible.

I adjusted the parameter to read SYS on both the primary and standby instance, and reran the duplication.

Afterwards, the database could be added:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Database "hdal_stb" added

How to use mkstore to set up passwordless duplication

Oracle provides a method called "Secure External Password Store" to hide your passwords in a wallet, instead of using them directly in your scripts.

The feature Secure External Password Store can be used without any restriction in all product editions, you do not require a license for the Advanced Security Option (ASO).

/

What exactly is a "secure external password store"?
Oracle describes it as "a secure software container that stores authentication and signing credentials"
You need to use the utility mkstore to manage secure external password store.

Personally, I find them very useful when performing duplication. Here is how I set things up when I was cloning a database for standby.


NOTE: If you intend to clone for standby, you need to add the SYS user and nothing else to your wallet!
Any other privileged user will give you trouble during cloning.


On the standby server, create directory where you want the wallet to reside. I normall put it in the $TNS_ADMIN directory:

mkdir -p $TNS_ADMIN/wallet

Create the wallet:

mkstore -wrl $TNS_ADMIN/wallet -create
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

Add a credential to one or more database

mkstore -wrl $TNS_ADMIN/wallet -createCredential hdal_primary dbaadmin
mkstore -wrl $TNS_ADMIN/wallet -createCredential hdal_stby dbaadmin

Note!
If you need to include a domain in your credential, use " " around your database name:

mkstore -wrl $TNS_ADMIN/wallet -createCredential "hdal_primary.mydomain.com" dbaadmin

If you later need to modify the same credential:

mkstore -wrl $TNS_ADMIN/wallet -modifyCredential hdal_stby dbaadmin

If you later need to delete a credential:

mkstore -wrl $TNS_ADMIN/wallet -deleteCredential hdal_stby

Verify that the credentials were added:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: hdal_stby dbaadmin
1: hdal_primary dbaadmin

Add the following in the server's $TNS_ADMIN/sqlnet.ora:

#Point out the server ("client") wallet
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /orasw/product/12201/network/admin/wallet)
  )
 )
 
#This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases:
SQLNET.WALLET_OVERRIDE = TRUE
 
#BUG 20721271 DUPLICATE FOR STANDBY FROM ACTIVE DATABASE HANGS WHILE RESTORING CONTROL FILE
#Turning this parameter on disables the ability to send and receive "break" messages using urgent data provided by the underlying protocol.
#This would apply to all protocols used by the client.
DISABLE_OOB=on

It works fine with an environmental variable directly in the sqlnet.ora file, too, instead of an absolute path:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = $TNS_ADMIN/wallet)
  )
 )

You should now be able to connect to both databases using rman. Test with a simple script called conntest.cmd.

connect target /@hdal_primary
connect auxiliary /@hdal_stby
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
}
exit

Output in my case was:

connected to target database: HDAL (DBID=1893290026)

connected to auxiliary database: HDAL (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1192 device type=DISK

allocated channel: c2
channel c2: SID=52 device type=DISK

allocated channel: aux1
channel aux1: SID=1152 device type=DISK

allocated channel: aux2
channel aux2: SID=1150 device type=DISK

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: c1
released channel: c2
released channel: aux1
released channel: aux2

How to avoid ERROR: role cannot be dropped because some objects depend on it in PostgreSQL

When you try to drop a user in a PostgreSQL database, you may encounter this error:

postgres=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb01

Solution: Drop the user's objects first:

postgres=# drop owned by scott;
DROP OWNED

Dropping can now proceed:

postgres=# drop role scott;
DROP ROLE

If you get errors like:

postgres=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb02
25 objects in database mydb02

You need to open a connection to mydb02 and revoke the privileges, like this:

postgres=# \connect mydb02
You are now connected to database "mydb02" as user "postgres".

Try dropping the role now, and you'll get a list of objects with dependencies (the list is abbriviated):

mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for sequence my_seq1
privileges for sequence my_seq2
privileges for sequence my_seq3
privileges for table mytable1
privileges for table mytable2
privileges for table mytable3
privileges for schema public

Revoke access to all the public tables:

mydb02=# revoke all on all tables in schema public from scott;
REVOKE

When you attempt to drop the role now, you can see that all the tables previously listed are gone:

mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for sequence my_seq1
privileges for sequence my_seq2
privileges for sequence my_seq3
privileges for schema public

Privileges to sequences and a schema remains. Revoke access to the sequences:

mydb02=# revoke all on all sequences in schema public from scott;
REVOKE
mydb02=# drop role scott;
ERROR:  role "scott" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

Revoke the final privilege:

mydb02=# revoke usage on schema public from scott;
REVOKE

Finally, we can drop the role:

mydb02=# drop role scott;
DROP ROLE