Errors in Goldengate Scenario 1

 Scenario 1

ERROR   OGG-00446  Missing filename opening checkpoint file.

ERROR   OGG-01668  PROCESS ABENDING.

___

Checking the replicat status

GGSCI (node4.example.com as ogguser@euro) 45> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
REPLICAT    STOPPED     REP1        00:00:00      00:00:06


Below is the error :-

2015-09-26 17:26:03  ERROR   OGG-00446  Missing filename opening checkpoint file.

2015-09-26 17:26:03  ERROR   OGG-01668  PROCESS ABENDING.


## this means that in replicat file keyword REPLICAT rep_group name is not written, lets fix this :-

edit params rep1

GGSCI (node4.example.com as ogguser@euro) 49> view params rep1

REPLICAT REP1
ASSUMETARGETDEFS
USERID ogguser, PASSWORD o
MAP scott.emp4, TARGET scott.emp4;


# Now we have updated REPLICAT REP1


GGSCI (node4.example.com as ogguser@euro) 52> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (node4.example.com as ogguser@euro) 53> info replicat rep1

REPLICAT   REP1      Last Started 2015-09-26 17:28   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Process ID           9086
Log Read Checkpoint  File /u01/app/oracle/product/ogg_trg/dirdat/rt000000
                     2015-09-26 16:49:45.000288  RBA 1964

Important points related to Multitenant Architecture Oracle 12C

Important points related to Multitenant Architecture Oracle 12C

# Undo tablespace is common for all containers.

# A common temporary tablespace is required for all containers however each PDB can have its own temporay tablespace for its application users

# Redo log files is common for all containers.

# Each Datafile is specific to a single container represented by a con_id

# Control files are common for all containers.

# Regarding archivelog mode or non archivemode, all the PDBs in a CDB share the archivelog mode of the CDB.

# Now each container has its own dictionary that means each container has its own system tablespace now and a sysaux tablespace also.

# There is a limit of 253 PDBs in a CDB including the seed

# Limit of 512 services in a CDB

Storing application data in Pluggable databases.

Storing application data in Pluggable databases.

First we will connect to pluggable database pdb1

SQL> alter session set container = pdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

Now we will create tablespace pdb1_tbs1 in pluggable database pdb1

SQL> create tablespace pdb1_tbs1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_tbs1a.dbf' size 100m;

Tablespace created.

Creating the table in above tablespace in pdb1 pluggable database

SQL> create table sales (id number) tablespace pdb1_tbs1;

Table created.

## Connecting to root now

SQL> alter session set container = cdb$root ;

Session altered.

SQL> create table sales (id number) ;                  

Table created.

## Confirming the table_name, tablespace_name, con_id and owner

SQL> select table_name,CON_ID,owner from cdb_Tables where table_name='SALES'

SQL> select table_name,CON_ID,tablespacE_name,owner from cdb_Tables where table_name='SALES';

TABLE_NAME     CON_ID TABLESPACE_NAME                OWNER
---------- ---------- ------------------------------ ------------------------------
SALES               3 PDB1_TBS1                      SYS
SALES               1 SYSTEM                         SYS

Querying about CDB or a NON CDB

To determine whether a database is a CDB or a non CDB, execute the below query :-

SQL> SELECT CDB FROM V$DATABASE;

CDB
---
YES

## Let us check about each Container in a CDB

We will query the V$CONTAINERS view.

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT          1  862823670          1 FD9AC20F64D344D7E043B6A9E80A2F2F
PDB$SEED          2 3526827491 3526827491 1E8418ECE0501751E05372C909C0C35E
PDB1              3 1620003564 1620003564 1E84263113671CB0E05372C909C0893F
PDB2              4 2018651655 2018651655 1E84287383001DBEE05372C909C012F3
PDB2_11           5  938823506  938823506 1E851D182997251BE05372C909C034B6
PDB_22            6 2820822967 2820822967 1E85456E7EFC246EE05372C909C08EA3

6 rows selected.

Lets check container ID, Name, and Status of Each PDB

SQL> COLUMN PDB_NAME FORMAT A15

SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

    PDB_ID PDB_NAME        STATUS
---------- --------------- ---------
         2 PDB$SEED        NORMAL
         3 PDB1            NORMAL
         4 PDB2            NORMAL
         5 PDB2_11         NORMAL
         6 PDB_22          NEW

Seed PDB in Oracle 12C

A Seed PDB is actually the PDB$SEED which is an oracle supplied template that the CDB can use to create new PDBs. We are not allowed to add or modify objects in seed PDB.

You can use the CREATE PLUGGABLE DATABASE statement to create a PDB by copying the files from PDB$SEED, which is a template for creating PDBs.

The following SQL statement creates a PDB named crmpdb from the seed using Oracle Managed Files:

CREATE PLUGGABLE DATABASE crmpdb
 ADMIN USER karan IDENTIFIED BY mypassword

We can see below that PDB seed may not be dropped or altered

SQL> alter pluggable database PDB$SEED open;
alter pluggable database PDB$SEED open
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

However any other pdb definately can be dropped or altered.

SQL> alter pluggable database PDB1 open ;

Pluggable database altered.

Some important Views in Oracle Multitenant Environment.

Some important Views in Oracle Multitenant Environment.

    DBA_CDB_RSRC_PLANS: This view contains information about all the CDB resource plan directives  
 
    DBA_CDB_RSRC_PLANS: This view contains information about all the CDB resource plans.  
         
    DBA_PDBS:  This view contains PDBs belonging to a given CDB. When queried from the root,  it will describe all PDBs belonging to a given CDB. When queried from a PDB, it will always return no rows.
     
     DBA_PDB_HISTORY: This view contains the lineage of the PDB to which it belongs.
     
    V$PDBS: This view contains information about PDBs associated with the current instance.
     
    V$PDB_INCARNATION: This view contains information about all PDB incarnations.  Oracle creates a new PDB incarnation whenever a PDB is opened with the RESETLOGS option.

Setting up Consumer Groups and Categories with PL/SQL in the Database

How to set up Consumer Groups and Categories with PL/SQL in the Database

BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_CATEGORY(
     CATEGORY => 'dss',
     COMMENT => 'DSS consumer groups');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'critical_dss',
     CATEGORY => 'dss',
     COMMENT => 'performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'normal_dss',
     CATEGORY => 'dss',
     COMMENT => 'non performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'etl',
     CATEGORY => 'maintenance',
     COMMENT => 'data import operations');

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

In addition to the consumer groups that you set up, the database contains predefined consumer groups. The DBA_RSRC_CONSUMER_GROUPS view displays information about consumer groups, and the DBA_RSRC_CATEGORIES view displays information about categories in the database.

Consumer Groups and Categories in an Oracle Database

SQL> SELECT consumer_group, category FROM DBA_RSRC_CONSUMER_GROUPS where
     consumer_group not like 'ORA%' ORDER BY category;

CONSUMER_GROUP                                   CATEGORY
---------------------------------------------  ---------------------------------------------
SYS_GROUP                                                   ADMINISTRATIVE
ETL_GROUP                                                   BATCH
BATCH_GROUP                                             BATCH
DSS_GROUP                                                   BATCH
CRITICAL_DSS                                              DSS
NORMAL_DSS                                               DSS
DSS_CRITICAL_GROUP                              INTERACTIVE
INTERACTIVE_GROUP                               INTERACTIVE
ETL                                                                  MAINTENANCE
LOW_GROUP                                                OTHER
OTHER_GROUPS                                          OTHER
AUTO_TASK_CONSUMER_GROUP          OTHER
DEFAULT_CONSUMER_GROUP               OTHER

13 rows selected