How to set up "RedoRoutes" in a Data Guard Broker configuration

In this example, the following members participate in my Data Guard Configuration: 

Database Name	Role	Open Mode	Function
pksprod	Primary	OPEN	Primary database
pks_stb	Cascading Physical Standby	MOUNTED	Used for failover
pks_ro	Active Data Guard	READ ONLY WITH APPLY	Used for reporting
pks_tstb	Terminal Physical Standby	MOUNTED	Used for migration to a new geographical location

I am using the concept of a "Terminal Standby Database" to move the database from one geographical location to another.
In order for the cascading database to send its redo log stream to the terminal standby database, I had to configure the Data Guard Broker attribute "redoroutes", like this:

edit database "pksprod" set property redoroutes='(LOCAL : pks_stb, pks_ro ASYNC) (pks_stb : pks_tstb ASYNC)';
edit database "pks_stb" set property redoroutes =' (LOCAL :pksprod ASYNC, pks_ro ASYNC)(pksprod : pks_tstb ASYNC)';

which means

* When pksprod is primary, it shall send redo to pks_stb and pks_ro, while pks_stb shall send its redo to pks_tstb
* When pks_stb is primary, it shall send redo to pksprod and pks_ro, while pksprod shall send its redo to pks_tstb

When done, check the outcome like this:

DGMGRL> show database "pksprod" redoroutes
  RedoRoutes = '(LOCAL : pks_stb,pks_ro ASYNC)(pks_stb : pks_tstb ASYNC)'
DGMGRL> show database "pks_stb" redoroutes
  RedoRoutes = '(LOCAL : pksprod ASYNC, pks_ro ASYNC)(pksprod : pks_tstb ASYNC)'

The 12.2 documentation for the RedoRoutes attribute can be found here. You should familarize yourself with the how you can set up the redoroutes to suit your needs. In my case it was the only way I was able to get the DG configuration to work the way I was intending. It was setup using the 12.2 version of the Oracle database software.

How to change ownership of a symlink in unix

As pointed out in a post on StackExchange.com:

On a Linux system, when changing the ownership of a symbolic link using chown, by default it changes the target of the symbolic link (ie, whatever the symbolic link is pointing to).

Make a mount point directory in the root of your server, and give it the ownership you require:

su - 
cd /
mkdir -p /u09/fra/PRODDB01
chown -R oracle:dba u09
cd /u09/fra/PRODDB01/

Create a symlink that points to your desired destination:

ln -s /data1/onlinelog/TESTDB01 onlinelog
 ls -altr
total 8
drwxr-xr-x 3 oracle dba  4096 Apr 14 10:05 ..
lrwxrwxrwx 1 root   root   19 Apr 14 10:13 onlinelog -> /data1/onlinelog/TESTDB01
drwxr-xr-x 2 oracle dba  4096 Apr 14 10:13 .

Note that the symbolic link is owned by root, not user oracle, as I intended. The normal way of chaning ownership did not work:

chown oracle:dba onlinelog

However, add the -h option:

 chown -h oracle:dba onlinelog

And you will have your ownership of the symlink changed:

 ls -la
total 8
drwxr-xr-x 2 oracle dba 4096 Apr 14 10:14 .
drwxr-xr-x 3 oracle dba 4096 Apr 14 10:05 ..
lrwxrwxrwx 1 oracle dba   19 Apr 14 10:14 onlinelog -> /data1/onlinelog/TESTDB01

How to solve "RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated" during cloning

During an attempt to clone for active database I had created pfile with my desired value for the parameter db_recovery_file_dest, which was /fradata. Excerpt from init.ora used to start the instance (nomount):

cat initproddb01.ora |grep db_recovery_file_dest

*.db_recovery_file_dest='/fradata'

This directory exists on my auxilliary server and is indeed writable by the oracle software installation owner (for most installation, this user is called "oracle") After having started the instance, and executed the clone script:

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='STBY01' COMMENT 'Stanby db 1'
USING COMPRESSED BACKUPSET
NOFILENAMECHECK;

This message appears:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/13/2021 08:07:31
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory

Cause: The spfile that is created contains the target's db_recovery_file_dest setting, not the auxiliary's:

strings spfileproddb01.ora | grep db_recovery_file_dest
*.db_recovery_file_dest='/FRA' <--- this is inherited from the target database, and doesn't exist on the auxiliary server

Solution: Add a new spfile directive to your clone script:

SET DB_RECOVERY_FILE_DEST='/fradata'

and rerun.

What is the difference between dynamic and static partition pruning

From the documentation:

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning.

* Static pruning occurs at compile-time, with the information about the partitions accessed being known beforehand.
* Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand.

A sample scenario for static pruning is a SQL statement containing a WHERE condition with a constant literal on the partition key column:

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = 1001;

There are some exceptions to this rule, listed here, for example if the partition pruning occurs using the results of a sub-query.

An exampel of Oracle using dynamic pruning is when operators or functions are used in the predicat (WHERE-condition). Also, statements that use bind variables against partition columns result in dynamic pruning:

var b1 number;
exec :B1 := 1001;

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = :B1;

How to view a specific property for a databasen using dgmgrl

DGMGRL> show database 'PROD_STB' 'DbFileNameConvert';

How to set timing in a posgreSQL session

Open session in psql:

psql mydb
psql (11.11)
Type "help" for help.

Toggle timing on/off like this:

mydb=# \timing
Timing is on.
mydb=#  \timing
Timing is off.

How to add a different looking index to your interim table during online redefinition

The procedure REGISTER_DEPENDENT_OBJECT in the DBMS_REDEFINITION package lets you add a dependent object to your interim table during online redefinition, and then "plug it into" your redefintion process.

In my case, I was redefining an incorrectly partitioned table. The interim table was also partitioned, but this time correctly. During the phase where I call the COPY_TABLE_DEPENDENTS procedure to transfer the existing dependant objects (triggers, indexes, constraints etc) from the original table to the interim table, it failed with the message

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2761

This was because I was using the directive dbms_redefinition.cons_orig_params in my code:

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SALES',
orig_table=>'DAILYSALES',
int_table=>'DAILYSALES_INTERIM',
copy_indexes=>dbms_redefinition.cons_orig_params,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
END;
/

As such, the redefinition process attempted to transfer the partitioned index from the original table which had a different partitioning scheme, to the new table who was partitioned differently and had fewer partitions.

As a workaround, I recreated the index directly on the interim table:

CREATE INDEX SALES.SEARCHIX1_INTERIM ON SALES.DAILYSALES_INTERIM
(COL1, COL2 DESC)
TABLESPACE DATA2
LOCAL
PARALLEL ( DEGREE 5 INSTANCES 1 );

Then, "plug it into" the redefinition procedure:

exec DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SALES','DAILYSALES','DAILYSALES_INTERIM',2,'SALES','SEARCHIX1','SEARCHIX1_INTERIM');

Finally, change the directive in your COPY_TABLE_DEPENDENTS procedure from

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

And run the COPY_TABLE_DEPENDENTS procedure again.
When done, finish the redefinition by calling the FINISH_REDEF_TABLE procedure, and you'll see that the interim index on the interim tables has been nicely transfered during the switch.

The Oracle 18c documentation can be found here
Another good source is HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (Doc ID 1304838.1 from Oracle Support)