Oracle Database 11g -- How to Quickly Reset RMAN Configuraiton

Problem

You want to reset RMAN configuration to its default state, quickly with one step

Solution

Use the below procedure:

SQL> exec dbms_backup_restore.resetConfig;

This will reset all RMAN configuration to their default values

 

Oracle GolgenGate 21c -- Configure a CDB Database for GoldenGate Extract

Problem

You need to configure a CDB database for enabling a GoldenGate extract for replication

Solution

Perform the following actions on the source CDB database to prepare for a GoldenGate extract:

At CDB$ROOT:

CREATE USER c##ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;

GRANT DBA to c##ggadmin;

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'C##GGADMIN', CONTAINER => 'ALL' );

alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

alter system set STREAMS_POOL_SIZE=<#_of_extracts * 1>G scope=both; 

ALTER DATABASE FORCE LOGGING; 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

At PDB level:

ALTER SESSION SET CONTAINER=<pdb>;

CREATE USER ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;

GRANT DBA TO ggadmin;

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

Oracle Database 19c -- Configure Database for GoldenGate Extract

Problem

You need to configure an Oracle Database 19c for a GoldenGate Extract 

Solution

1) At CDB$ROOT, create a common admin user for GoldenGate:

CREATE USER c##ggadmin IDENTIFIED BY "<password>"
profile <profile>
DEFAULT TABLESPACE sysaux
TEMPORARY TABLESPACE temp;

GRANT dba to c##ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'c##ggadmin', container=>'all');

2) At a PDB, create a local admin user for GoldenGate:

ALTER SESSION SET CONTAINER=<pdb_name>;

CREATE USER ggadmin IDENTIFIED BY "<password>"
profile bpc_schema_owner_profile
default tablespace users
temporary tablespace temp;

GRANT DBA TO ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

3) At CDB$ROOT, enable GoldenGate for the database:

alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

4) At CDB$ROOT, set the STREAMS_POOL_SIZE parameter based on the formula 1 EXT x 1 GB:

alter system set STREAMS_POOL_SIZE=1g scope=both sid='*';

5) Configure the logging options:

At CDB$ROOT:

ALTER DATABASE FORCE LOGGING;

At CDB$ROOT, and source PDBs:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Verify with:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

At CDB$ROOT:

ALTER SYSTEM SWITCH LOGFILE;

 

 

Oracle GoldenGate 21c -- How Triggers Fired during Replication

Problem

You need a better understanding on how Oracle database triggers work with Oracle GoldenGate replication

Solution

By default in an Oracle database, triggers do fire only when a statement executed from a user process, and do not fire in case of Oracle-maintained processes, like Logical standby SQL Apply or Oracle Streams replication processes 

The property can be changed by using the procedure:

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY

When set to FALSE, triggers will fire during replication, that is, the statements issued by Replicat processes

OCI Autonomous Database -- How to Create a DB Link

Problem

You want to create a database link on an Autonomous Database in OCI (Oracle Cloud Infrastructure)

Solution

Use commands below to create a db link:

If db link being created in a schema other than ADMIN, grant required privileges:

SQL> grant execute on DBMS_CLOUD_ADMIN to <schema>;
SQL> grant CREATE DATABASE LINK to <schema>;

Create credential;

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => '<credential_name>',
    username        => '<username>',
    password        => '<password>'
  );
END;

Create database link:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name       => '<db_link_name>',
          hostname           => 'target_db_host_name',
          port               => '<port>',
          service_name       => '<service_name>',
          ssl_server_cert_dn => NULL,
          credential_name    => '<credential_name>',
          directory_name     => NULL,
          private_target     => true);
END;

Cygwin -- File permissions for ssh

Problem

While configuring openssh for cygwin, ssh key authentication does not work, the key does not get accepted 

Solution

Make sure the .ssh directory and the authorized_keys files have required permissions and ownership:

chown <your_username>:none ~/
chmod g-w ~/
chmod 700 ~/.ssh/
chmod 600 ~/.ssh/authorized_keys

AIX 7.2 -- setreuid fails with PermissionError

Problem

You want to change euid or ruid for a process, for example, the snippet below tries to start bash as user oracle executed by user grid:

[grid] $ python3 -c 'import os; import pwd; import sys; import subprocess; uid=pwd.getpwnam("oracle").pw_uid; os.setreuid(uid,uid); retval = subprocess.run(["bash", "--norc"]).returncode; sys.exit(retval);'
Traceback (most recent call last):
  File "<string>", line 1, in <module>
PermissionError: [Errno 1] Not owner 

Though executing the same snippet as user root succeeds

Solution

AIX 7.2 provides Role Based Access Control (RBAC) mechanisms which allows to control certain security aspects of a system. 

In particular, RBAC allows to grant privileges to users, roles and process to perform certain actions

To allow a process to call setreuid() run as root:

# setsecattr -c accessauths=ALLOW_ALL innateprivs=PV_DAC_O,PV_DAC_UID secflags=FSF_EPS euid=0 egid=0 /opt/bin/python3.7
# setkst

 Note. Aliases not accepted by setsecatt, only direct executables

[grid] $ python3 -c 'import os; import pwd; import sys; import subprocess; uid=pwd.getpwnam("oracle").pw_uid; os.setreuid(uid,uid); retval = subprocess.run(["bash", "--norc"]).returncode; sys.exit(retval);'
[oracle] $ 

Reference:

• Role-based access control

Oracle Database 11.2 -- Manually Install Java Component in an Oracle Database

Problem

You want to manually install the Java Component in a Oracle Database version 11.2 and up

Solution

Note. The solution is application only for the first-time installation. If the Java component was previously installed, then this procedure is not applicable for repair/re-install. 

1) Optionally, cleanly stop the database, create a guaranteed restore point, enable restricted session:

shutdown immediate;
startup mount;
alter database archivelog;
alter system enable restricted session;
alter database open;
alter system switch logfile;
create restore point before_java guarantee flashback database;

2) Install Java component:

alter system set "_system_trig_enabled" = false scope=memory;
alter system set "java_jit_enabled" = false scope=memory;
spool /tmp/full_jvminst.log;
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
11g only @?/rdbms/admin/catexf.sql
@?/rdbms/admin/utlrp.sql
alter system set java_jit_enabled = true scope=memory;
alter system set "_system_trig_enabled"=true scope=memory;
spool off

3) In case of any of the actions of Step 1) was performed, perform the reverse actions:

drop restore point before_java;
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

Reference:

• Safe repair/reinstall of the JVM Component in 11.2 and up (Doc ID 2314363.1)
• Script to Check the Status or State of the JVM within the Database (Doc ID 456949.1) 

 

Oracle Database 12.2 -- How to Create a Dump of a Controlfile

Problem

You need to create a dump of a controlfile

Solution

Use the below command to dump a controlfile to a trace file:

alter session set events 'immediate trace name controlf level 9';

Identify the location of a trace file by either:

v$diag_info

or

oradebug setmypid
oradebug tracefile_name

 

Oracle Database 19c -- Disable VIEW PUSHED PREDICATE

Problem

You want to disable VIEW PUSHED PREDICATE transformation in a session or for the whole system

Solution

Apply the hidden parameter at session or system level:

alter session set "_optimizer_push_pred_cost_based"=false;