Thinking Out Loud

July 24, 2016

GoldenGate 12.2 Object Exclude

Filed under: 12c,GoldenGate — mdinh @ 6:06 pm

1.8 Details of Support for Objects and Operations in Oracle DDL

The following tables are excluded by default.

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables

If you specify JOB_NAME for datapump, then you may have to manually add the exclusion.

Next, during migration from 10g to 12c with Goldengate, extract was failing with

2016-06-20 01:00:16  ERROR   OGG-00665  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  OCI Error getting OCI_ATTR_NAME for UDT SYS.ANYDATA (status = 24328-ORA-24328: illegal attribute value), SQL.
2016-06-20 01:00:17  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  PROCESS ABENDING.

The following exclusion was added to the extract.

SCHEDULER$_JOB_ARG

It might be better to add “*.SCHEDULER$*”?

July 23, 2016

Troubleshooting ORA-02049: timeout: distributed transaction waiting for lock

Filed under: oracle — mdinh @ 10:45 pm

Controlling Connections Established by Database Links

Tracing can be done using – ALTER SYSTEM SET EVENTS ‘2049 trace name ERRORSTACK level 3’;

Nice note from MOS.

How to Obtain a System State Trace BEFORE the Error ORA-2049 Occurs, While Still Experiencing the Contention (Doc ID 789517.1)

Commit after select from dblink – blocking lock.

Session 1:
++++++++++
04:44:51 ARROW:(DEMO@leo):PRIMARY>
04:45:30 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.01
04:45:36 ARROW:(DEMO@leo):PRIMARY> commit;

Commit complete.

Elapsed: 00:00:00.00
04:45:41 ARROW:(DEMO@leo):PRIMARY>
04:45:49 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;

++++++++++
Session 2:
04:45:18 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.00
04:45:21 ARROW:(DEMO@leo):PRIMARY> @/media/sf_working/sql/b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   DEMO            32,17        74nhxnyztg454    402 SQL*Net message to c ACTIVE      0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           41,271       1qfpvr7brd2pq    113 enq: TX - row lock c ACTIVE      8 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   DEMO            7741d4und71ph with s as ( SELECT decode(level,1,'BLOCKING','WAITING') state, LPAD('*',(level-1
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
04:54:19 ARROW:(DEMO@leo):PRIMARY> select count(*) from t;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01
05:00:49 ARROW:(DEMO@leo):PRIMARY>

No commit after select from dblink – ORA-02049: timeout: distributed transaction waiting for lock.

Session is automatically killed based on database paraneter tributed_lock_timeout (default is 60s)

Session 1:
++++++++++
21:58:06 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.01
21:58:14 ARROW:(DEMO@leo):PRIMARY>

Session 2:
+++++++++
21:58:45 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
21:58:51 ARROW:(DEMO@leo):PRIMARY> update t set id=1;
update t set id=1
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:01:00.01
21:59:59 ARROW:(DEMO@leo):PRIMARY>

Don’t forget to commit or rollback after using dblinks.

UPDATED: Tried to determine if there was a way to monitor distributed transaction and in this test case was not able to.

Session 1: 
++++++++++
15:59:32 ARROW:(MDINH@leo):PRIMARY> update demo.t set id=100;

1 row updated.

Elapsed: 00:00:00.01
15:59:43 ARROW:(MDINH@leo):PRIMARY>

Session 2: Distributed Transaction
++++++++++
16:00:43 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
16:00:47 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.00
16:10:57 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.01
16:23:03 ARROW:(DEMO@leo):PRIMARY>

Session 3: Monitoring
++++++++++
16:21:23 ARROW:(SYS@leo):PRIMARY> show parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     600
16:21:35 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:21:38 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:10 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   23 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE     10 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:56 ARROW:(SYS@leo):PRIMARY> @b.sql

no rows selected

Elapsed: 00:00:00.01

no rows selected

Elapsed: 00:00:00.00
16:23:12 ARROW:(SYS@leo):PRIMARY>

July 22, 2016

GoldenGate 12.2 TROUBLESHOOTING USING GETENV

Filed under: 12c,GoldenGate — mdinh @ 2:14 am

I was faced with a very strange situation.

When using SETENV (ORACLE_SID = db01), replicat ABEND with  OGG-00664  OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available

When using SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1), replicat starts without any issues.

I know what you are thinking, why not set both ORACLE_SID and ORACLE_HOME and you are correct since this is best practices.

The client already knows this and wanted to troubleshoot why it was failing?

Any ideas? I scoured the internet and MOS and finally found that using get_env was the key to troubleshooting.

Tried tracing the replicat and did not work since replicat abended.

NOTE:  Environment variables are all set correctly from the OS.

BAD:

REPLICAT R_2D2
SETENV (ORACLE_SID = db01)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = b01
USERIDALIAS ggadmin

2016-07-21 18:33:46 ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory).

GOOD:

REPLICAT R_2D2

SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = db01
USERIDALIAS ggadmin

Notice, there are 2 different ORACLE_HOMEs being used.

Bad one is ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1 since the HOME does not exists.

Wait a minute, how is it possible the HOME does not exists and where did Goldengate get this information ???

Let’s find the installation log.

/u01/app/oraInventory/logs> grep "MANAGER_PORT" installActions2016*.log|uniq
installActions2016-05-09_04-57-53PM.log:INFO: Setting value for the property:MANAGER_PORT in the bean:OGGInstallSettings
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                       
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                                                    
installActions2016-05-09_04-57-53PM.log:INFO: adding the variable MANAGER_PORT to command line args table
installActions2016-05-09_04-57-53PM.log:INFO: Setting variable 'MANAGER_PORT' to '7809'. Received the value from the command line.
installActions2016-05-09_04-57-53PM.log:INFO: This variable MANAGER_PORT is not added to the global context map

/u01/app/oraInventory/logs> grep DATABASE_HOME installActions2016-05-09_04-57-53PM.log
INFO: Setting value for the property:DATABASE_HOME in the bean:OGGInstallSettings
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1  
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1                               
 
INFO: adding the variable DATABASE_HOME to command line args table
INFO: Setting variable 'DATABASE_HOME' to '/u01/app/oracle/product/11.2.0.3/dbhome_1'. Received the value from the command line.
INFO: This variable DATABASE_HOME is not added to the global context map

There it is, Goldengate is using ORACLE_HOME defined from the installation.

DATABASE_LOCATION is not required, so why set it?

Admittedly, I have have been setting DATABASE_LOCATION but will not be doing so any more.

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=

Lastly, I have not attempted to reproduce the error. Too lazy. May be you can help?

July 21, 2016

Different names for 12c CDB

Filed under: 12c,CDB,oracle — mdinh @ 2:15 pm

Truthfully, I am feeling a little lazy to write a nice blog.

Configuring listener.ora and tnsnames.ora, varying names for database, connecting to database.

Summary:

db_name                              string      tmnt
db_unique_name                       string      tmnt_a
instance_name                        string      tmnt01

ADR Home /u01/app/oracle/diag/rdbms/tmnt_a/tmnt

Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...

Details:

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat listener.ora
LISTENER_TMNT =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
      (ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))
    )
  )

SID_LIST_LISTENER_TMNT =
  (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = tmnt)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = tmnt)
    )
  )

USE_SID_AS_SERVICE_LISTENER_TMNT = ON
INBOUND_CONNECT_TIMEOUT_LISTENER_TMNT = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_TMNT = OFF
DIAG_ADR_ENABLED_LISTENER_TMNT = OFF
LOGGING_LISTENER_TMNT = OFF
TRACE_LEVEL_LISTENER_TMNT = OFF
SAVE_CONFIG_ON_STOP_LISTENER_TMNT = TRUE

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat tnsnames.ora
APRIL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APRIL)
    )
  )

TMNT_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TMNT_A)
    )
  )

TMNT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SID = tmnt)
    )
  )

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl status listener_tmnt

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JUL-2016 06:58:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_tmnt
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JUL-2016 06:23:03
Uptime                    0 days 0 hr. 35 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)))
Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
The command completed successfully

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ ps -ef|grep pmon
oracle    2572     1  0 Jul20 ?        00:00:02 ora_pmon_tmnt
oracle    8998  3716  0 06:58 pts/0    00:00:00 grep pmon

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:58:58 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:57:32 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt_a

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:58:58 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@april

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:59:06 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

APRIL:(SYSTEM@april):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus sys/oracle@tmnt_a as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:22 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@tmnt_a):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
global_names                         boolean     FALSE
instance_name                        string      tmnt01
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      tmnt_a
CDB$ROOT:(SYS@tmnt_a):PRIMARY> @/home/oracle/diag_info.sql

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 Diag Enabled                   TRUE                                                                                      0
         1 ADR Base                       /u01/app/oracle                                                                           0
         1 ADR Home                       /u01/app/oracle/diag/rdbms/tmnt_a/tmnt                                                    0
         1 Diag Trace                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace                                              0
         1 Diag Alert                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/alert                                              0
         1 Diag Incident                  /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/incident                                           0
         1 Diag Cdump                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/cdump                                              0
         1 Health Monitor                 /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/hm                                                 0
         1 Default Trace File             /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace/tmnt_ora_9016.trc                            0
         1 Active Problem Count           0                                                                                         0
         1 Active Incident Count          0                                                                                         0

11 rows selected.

tmnt

CDB$ROOT:(SYS@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[07:00]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

July 20, 2016

12.1.0.2 New Features

Filed under: 12c,CDB,oracle — mdinh @ 2:51 am

Just a quick note as I play with CDB/PDB.

No more triggers!

PDB State Management Across CDB

select con_name, instance_name, state from DBA_PDB_SAVED_STATES;
alter pluggable database PDB1 SAVE STATE;
alter pluggable database ALL SAVE STATE;
alter pluggable database ALL DISCARD STATE;
alter pluggable database ALL OPEN;

startup force mount exclusive restrict;
drop database;

Drop database still does not clean up ADR.

[19:32]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 19:32:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@cdb12c):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all open;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all SAVE state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

CON_NAME INSTANCE_NAME STATE
-------- ------------- --------------
PDB2     cdb12c        OPEN
PDB1     cdb12c        OPEN

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all DISCARD state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
CDB$ROOT:(SYS@cdb12c):PRIMARY> !ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfilecdb12c.ora  spfiletmnt.ora

CDB$ROOT:(SYS@cdb12c):PRIMARY> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
CDB$ROOT:(SYS@cdb12c):PRIMARY>
CDB$ROOT:(SYS@cdb12c):PRIMARY> exit
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ps -ef|grep pmon
oracle    7151     1  0 13:04 ?        00:00:01 ora_pmon_tmnt
oracle   16898  5756  0 19:39 pts/1    00:00:00 grep pmon
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfiletmnt.ora
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/diag/rdbms/
cdb12c  tmnt_a
[19:40]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$

July 19, 2016

Create 12c CDB using dbca or sqlplus ???

Filed under: 12c,CDB,oracle — mdinh @ 10:27 pm

How do you typically create database, using dbca or sqlplus?

I am still surprised with all the bloatware added for creating database using dbca.

Also, is there a reason to prefix PDB with pdb?

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 28
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ dbca -silent \
> -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true \
> -gdbName cdb12c -sid cdb12c \
> -SysPassword oracle -SystemPassword oracle \
> -numberOfPdbs 2 -pdbName pdb \
> -pdbadminUsername pdba -pdbadminPassword oracle \
> -emConfiguration NONE \
> -redoLogFileSize 100 \
> -storageType FS -datafileDestination /oradata \
> -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 \
> -totalMemory 1024 -databaseType MULTIPURPOSE \
> -initparams audit_trail=NONE

Registering database with Oracle Restart
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
71% complete
74% complete
Creating Pluggable Databases
79% complete
84% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb12c/cdb12c.log" for further details.

[15:11]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 48
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 15:10 hc_cdb12c.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 14:56 lkCDB12C
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 7680 Jul 19 15:07 orapwcdb12c
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 3584 Jul 19 15:11 spfilecdb12c.ora
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
#Backup file is  /u01/app/12.1.0.2/grid/srvm/admin/oratab.bak.arrow line added by Agent
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1
cdb12c:/u01/app/oracle/product/12.1.0.2/db_1:N          # line added by Agent

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:12:33 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
CDB12C    DV           Oracle Database Vault                    12.1.0.2.0                     VALID
CDB12C    APEX         Oracle Application Express               4.2.5.00.08                    VALID
CDB12C    OLS          Oracle Label Security                    12.1.0.2.0                     VALID
CDB12C    SDO          Spatial                                  12.1.0.2.0                     VALID
CDB12C    ORDIM        Oracle Multimedia                        12.1.0.2.0                     VALID
CDB12C    CONTEXT      Oracle Text                              12.1.0.2.0                     VALID
CDB12C    OWM          Oracle Workspace Manager                 12.1.0.2.0                     VALID
CDB12C    XDB          Oracle XML Database                      12.1.0.2.0                     VALID
CDB12C    CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
CDB12C    CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID
CDB12C    JAVAVM       JServer JAVA Virtual Machine             12.1.0.2.0                     VALID
CDB12C    XML          Oracle XDK                               12.1.0.2.0                     VALID
CDB12C    CATJAVA      Oracle Database Java Packages            12.1.0.2.0                     VALID
CDB12C    APS          OLAP Analytic Workspace                  12.1.0.2.0                     VALID
CDB12C    XOQ          Oracle OLAP API                          12.1.0.2.0                     VALID
CDB12C    RAC          Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

16 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Manually created CDB.

[15:14]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ . oraenv <<< tmnt ORACLE_SID = [cdb12c] ? The Oracle base remains unchanged with value /u01/app/oracle 

[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs 
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:14:10 2016 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          MOUNTED
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
TMNT      XDB          Oracle XML Database                      12.1.0.2.0                     VALID
TMNT      CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
TMNT      CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$

July 16, 2016

GoldenGate and Networking

Filed under: GoldenGate,linux — mdinh @ 4:16 am

I have never delved into networking for GoldenGate since things just work. Due to security tightening, telnet is no longer available to verify opened port.

What’s to follow is a demo for ports being listened by GoldenGate when manager started and ports listened and when PUMP Extract is started as part using DYNAMICPORTLIST.

Also, how to test opened port using (nc – arbitrary TCP and UDP connections and listens)

Both source and target are running on the same host; however, the same principles still apply.

HAWK (source): PORT 7901 and DYNAMICPORTLIST 15100-15120

THOR (target):   PORT 7801 and DYNAMICPORTLIST 15200-15220

 

 

HAWK (source):

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01

$ cat dirprm/mgr.prm
PORT 7901
DYNAMICPORTLIST 15100-15120
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
USERIDALIAS ggs_admin
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:00:47
EXTRACT     STOPPED     P_HAWK      00:00:00      00:00:35

GGSCI (arrow.localdomain) 2>

THOR (target):

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ cat dirprm/mgr.prm
PORT 7801
DYNAMICPORTLIST 15200-15220
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     R_HAWK      00:00:00      70:18:54

GGSCI (arrow.localdomain) 2>

Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST are LISTEN.

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02

HAWK (source):  Start PUMP

GGSCI (arrow.localdomain) 1> start p*

Sending START request to MANAGER ...
EXTRACT P_HAWK starting

GGSCI (arrow.localdomain) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:02:03
EXTRACT     RUNNING     P_HAWK      00:00:00      00:01:50

GGSCI (arrow.localdomain) 3>

Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST 15200

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
tcp        0      0 :::15200                    :::*                        LISTEN      3297/./server
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$

How to use nc to test opened port.

What RPM is required to install nc. Use yum install nc.

[root@arrow ~]# yum whatprovides nc
Loaded plugins: refresh-packagekit, security
epel/metalink                                                                                   |  12 kB     00:00
epel                                                                                            | 4.3 kB     00:00
epel/primary_db                                                                                 | 5.8 MB     00:03
public_ol6_UEKR3_latest                                                                         | 1.2 kB     00:00
public_ol6_latest                                                                               | 1.4 kB     00:00
public_ol6_latest/primary                                                                       |  59 MB     00:26
public_ol6_latest                                                                                          36199/36199
nc-1.84-22.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : installed
Matched from:
Other       : Provides-match: nc

[root@arrow ~]#

DEMO:

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 7901
Connection to arrow 7901 port [tcp/tnos-sp] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15200
Connection to arrow 15200 port [tcp/*] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15201
nc: connect to arrow port 15201 (tcp) failed: Connection refused
nc: connect to arrow port 15201 (tcp) failed: Connection refused
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$

July 13, 2016

GoldenGate 12.2 DDLOPTIONS MAPSESSIONSCHEMA

Filed under: 12c,GoldenGate — mdinh @ 4:36 am

This is probably not a 12.2 specific features and since I only tested for 12.2, I am not going to vouch for other versions.

Be careful when qualifying schema with DDL replication especially when the source and target schemas are not the same.

Example: Source schema is DEMO and Target schema is DEMO_REP

What happens to DDL replication when the following is used?

alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ sqlplus demo/demo @t.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 12 20:36:55 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARROW:(DEMO@hawk):PRIMARY> drop table demo.t purge;

Table dropped.

ARROW:(DEMO@hawk):PRIMARY> create table demo.t(id int);

Table created.

ARROW:(DEMO@hawk):PRIMARY> create unique index demo.t_pk_ix on demo.t(id);

Index created.

ARROW:(DEMO@hawk):PRIMARY> alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

Table altered.

ARROW:(DEMO@hawk):PRIMARY>

Target: ggserr.log

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table demo.t(id int) (size 27)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."T"(id int) (size 35)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index demo.t_pk_ix on demo.t(id) (size 46)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."T_PK_IX" on "DEMO_REP"."T"(id) (size 62)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].

2016-07-12 20:37:00  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Fatal error executing DDL replication: error 
[Error code [1418], ORA-01418: specified index does not exist SQL alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix 
/* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].
2016-07-12 20:37:04  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  PROCESS ABENDING.

Notice using index demo.t_pk_ix did not get mapped accordingly.

How do we fixed this?

The simple solution is don’t prefix schema name.

2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table y(id int) (size 22)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."Y"(id int) (size 35)].

2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index y_pk_ix on y(id) (size 36)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."Y_PK_IX" on "DEMO_REP"."Y"(id) (size 62)].

2016-07-12 20:53:40  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table y add constraint y_pk primary key(id) using index y_pk_ix (size 69)].
2016-07-12 20:53:40  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."Y" add constraint y_pk primary key(id) using index y_pk_ix (size 82)].

I know what you are probably thinking. What if login to the schema is not allowed?

DDLOPTIONS MAPSESSIONSCHEMA DEMO, TARGET DEMO_REP, MAPSCHEMAS

2016-07-12 21:27:10  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 21:27:10  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].
2016-07-12 21:27:10  INFO    OGG-01369  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation mapped to target database [T], new DDL operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index DEMO_REP.t_pk_ix (size 91)].
2016-07-12 21:27:10  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [DEMO_REP], objname [T].
2016-07-12 21:27:10  INFO    OGG-01562  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Source schema DEMO is mapped to target schema DEMO_REP to set the current schema for DDL execution.
2016-07-12 21:27:10  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Setting current schema for DDL operation to [DEMO_REP].
2016-07-12 21:27:10  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Executing DDL operation.
2016-07-12 21:27:10  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation successful.
2016-07-12 21:27:10  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Restoring current schema for DDL operation to [GGS_ADMIN].

Happy Replication!

Reference: https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters037.htm#GWURF451

July 9, 2016

RMAN 12c NF – SQL interface in RMAN


Filed under: 12c,RMAN,shell scripting — mdinh @ 11:51 pm

Admittedly, I did not realize the benefit at first until there came a need.

Example:


# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 

Oracle Education Dissatisfaction

Filed under: Uncategorized — mdinh @ 11:28 pm

Just like anything, there is good and bad.

Unfortunately for me, my experience with Oracle education has been predominately bad and former manager experienced the same where he was able to retake a course almost a year later since the original was unrealistic for real world scenarios.

With Oracle education, the most simplistic setup is used, e.g. no ASM, no Grid Infrastructure as it may add a little more complexity to set up?

A little advice, try to find out who will be the instructor before registering for the course. Call and ask.

Next, there is a large and great Oracle community where you may learn more about technology and new features.

Here are 2 features I came to know from twitter feed and blogs which were never covered in the 12c Data Guard Administration.

ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C (Doc ID 1987763.1) (Courtesy Nassyam Basha)
RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1) (Courtesy of Deiby Gomez)

 

 

Next Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 511 other followers