Category: Configuration

How to Detach or Attach Oracle Home in Oracle Inventory

DETACH an ORACLE_HOME

[oracle@racnode1 dbhome_1]$ $ORACLE_HOME/oui/bin/runInstaller 
               -silent -detachHome 
              ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 8182 MB Passed
The inventory pointer is located at /etc/oraInst.loc
'DetachHome' was successful.

For GI / RAC Local Node Only:

$ $ORACLE_HOME/oui/bin/runInstaller  -silent -local -detachHome    \
                                     ORACLE_HOME="/u01/app/grid/12.2.0.1"

ATTACH an ORACLE_HOME

[oracle@racnode1 dbhome_1]$ $ORACLE_HOME/oui/bin/runInstaller -silent \
-attachHome ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1"    \
ORACLE_HOME_NAME="OraDB12Home1"

FOR GI HOME

$ORACLE_HOME/oui/bin/runInstaller -silent -attachHome ORACLE_HOME="/u01/app/grid/12.2.0" ORACLE_HOME_NAME="OraGI12Home" LOCAL_NODE='racnode1' CLUSTER_NODES=racnode1,racnode2 CRS=true

For RAC HOME

$ $ORACLE_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/oracle/app/oracle/product/11.2.0/db_1" ORACLE_HOME_NAME="OraDb11g_home1" LOCAL_NODE='racnode1' CLUSTER_NODES=racnode1,racnode2

Database Logon Trigger to Block Sessions from Some Users using Toad,SQL Developer….

Here is an example of  database logon trigger for blocking specific users from accessing database through tools like  Toad or SQL Developer tools, etc.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Any database users with “ADMINISTER DATABASE TRIGGER” will bypass the database logon trigger.

SQL> grant ADMINISTER DATABASE TRIGGER to testuser;

Grant succeeded.

SQL> connect testuser
Enter password:
Connected.

Create Materialised View Log on the Remote Database

DB1 is a local database, on which the materialized views are to be created.
DB2 is a remote database, on which materialized view logs are created .

The high level steps are:

  • on local database DB1, create a database link between DB1 and DB2 database.
  • On the remote database DB2, create materialized logs on the tables of DB2.
  • On local database DB1, create materialized views with Fast Refresh option , etc.
  • On Local database DB1, run a complete refresh on your materialized views:          Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’c’);
  • Then on local database DB1 again ,  refresh the materialized  view using the Fast Refresh option:
    Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’f’);

DBCA: DISPLAY not set. Set DISPLAY environment variable, then re-run.

Logon as non-oracle user “scott”, then sudo to oracle database user “oracle”. Try to run “dbca” with below errors:

$ ./dbca
DISPLAY not set.
Set DISPLAY environment variable, then re-run.

$ xclock
Error: Can't open display:

Please subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Check Oracle Hidden Parameters ?

As we know, “show parameter” doesn’t show hidden parameters values in Oracle database version before 18c. Instead, the below sql script will list hidden parameters.

SQL> show parameter report

NAME                                 TYPE        VALUE
---------------------------------- ----------- --------------
optimizer_adaptive_reporting_only    boolean     FALSE

SQL>show parameter "_report_capture_cycle_time"
SQL>
set pagesize 120
set linesize 150
col Parameter format a40
col Description format a40
col "Session Value" format a10
col "Instance Value" format a10

SELECT 
       a.ksppinm  "Parameter", 
       a.ksppdesc "Description", 
       b.ksppstvl "Session Value",  
       c.ksppstvl "Instance Value" 
FROM 
       x$ksppi a, 
      x$ksppcv b, 
      x$ksppsv c 
WHERE  
      a.indx = b.indx 
AND   a.indx = c.indx 
AND   a.ksppinm LIKE  '/_report_capture%'  escape '/'  
ORDER BY 1;

Parameter                                Description                              Session Va Instance V
---------------------------------------- --------------------------------
_report_capture_cycle_time               Time (in sec) between two cycles of repo 60         60
                                         rt capture daemon
_report_capture_dbtime_percent_cutoff    100X Percent of system db time daemon is 50         50
...
..
.

From 18c, it is easier to show a hidden parameter as showing a normal parameter:

SQL> show parameter allow_insert_with

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
_allow_insert_with_update_check      boolean     TRUE
SQL>