Understanding Lag in an Active Data Guard Configuration

Understanding Lag in an Active Data Guard Configuration

• A standby database configured with real-time apply can lag behind the primary database as a result of:
– Insufficient CPU capacity
– High network latency
– Limited bandwidth

• Queries on the standby database need to return current results and/or be within an established service level.
• Ways to “manage” the standby database lag and take necessary action:
– Configure Data Guard configuration with a maximum data lag that will trigger an error when it is exceeded.
– Monitor the redo apply lag and take action when the lag is unacceptable

Monitoring Apply Lag: V$DATAGUARD_STATS

• Apply lag: This is the difference, in elapsed time, between when the last applied change became visible on the standby and when that same change was first visible on the primary.
• The apply lag row of the V$DATAGUARD_STATS view reflects statistics that are computed periodically and to the nearest second.

Standby database:
SQL> SELECT name, value, datum_time, time_computed FROM v$dataguard_stats WHERE name like 'apply lag';

Monitoring Apply Lag: V$STANDBY_EVENT_HISTOGRAM

• View histogram of apply lag on a physical standby database.
• Use to assess value for STANDBY_MAX_DATA_DELAY.
• Use to focus on periods of time when the apply lag exceeds desired levels so that issue can be resolved.
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

Configuring Zero Lag Between the Primary and Standby Databases

• Certain applications have zero tolerance for any lag.
• Query on the standby database must return the same result as though it were executed on the primary database.
• Enforce by setting STANDBY_MAX_DATA_DELAY to 0.
• The standby database must have advanced to a value equal to that of the current SCN on the primary database at the time the query was issued.
• Results are guaranteed to be the same as the primary database, else ORA-3172 error is returned to the query.
• The primary database must operate in maximum availability or maximum protection mode.
• SYNC must be specified for redo transport.
• Real-time query must be enabled.

Setting STANDBY_MAX_DATA_DELAY by Using an AFTER LOGON Trigger

Create an AFTER LOGON trigger that:
• Is database role aware
– It uses DATABASE_ROLE, a new attribute in the USERENV context.
– SQL and PL/SQL clients can retrieve the database role programmatically using the SYS_CONTEXT function.
– It enables you to write role-specific triggers.
• Sets STANDBY_MAX_DATA_DELAY when the application logs on to a real-time query–enabled standby database
• Allows for configuration of a maximum data delay without changing the application source code

CREATE OR REPLACE TRIGGER sla_logon_trigger
AFTER LOGON
ON APP.SCHEMA
BEGIN
IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN execute immediate
'alter session set standby_max_data_delay=5';
ENDIF;
END;

Physical Standby Dataguard Health Checks

Physical Standby Dataguard Health Checks

Check dataguard status of errors:

select message,to_char(timestamp,'DD-MM-YY HH24:MI'),error_code from v$dataguard_status where error_code != 0;

SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

Check transport status:

primary> select * from v$archive_processes where status <> 'STOPPED';

primary> select status,error from v$archive_dest where status <>'INACTIVE';

Background process status in DG:

standby or primary> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

To know LOG status:

standby> select 'Last Applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

To know last sequence released and applied:

Standby> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" from (select thread# thrd, max(sequence#) almax from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) al, (select thread# thrd, max(sequence#) lhmax from v$log_history where first_time=(select max(first_time) from v$log_history)
group by thread#) lh where al.thrd = lh.thrd;

To know transport lag time, apply lag time and apply finish time:

standby>
set lines 180
col name for a40
col value for a40
col unit for a40
select NAME, VALUE, UNIT from v$dataguard_stats union select null,null,' ' from dual union select null,null,'Time Computed: '||MIN(TIME_COMPUTED) from v$dataguard_stats;

Ensure everything is ok from PRIMARY:

select status,error from v$archive_dest where status <>'INACTIVE';
select * from v$archive_processes where status <> 'STOPPED';
show parameter dest_2
show parameter state_2
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system switch logfile;
alter system switch logfile;

12c Multitenant Activities

12c Multitenant Activities

1.Backup and Recovery of CDB & PDB databases in 12c.


Backup:

How to take backup of container and pluggable database in command?
rman target /
backup database plus archivelog;
first it will root container, pluggable database and then seed database.

How to take the backup of root container database?
rman>backup database root;

How to take the backup of a individual pluggable database?
rman>backup pluggable database pdb1;
or
rman target=sys@pdb1
rman>backup database;

Recovery:

To do a full recovery of PDB database ?
rman target=/
run{
alter pluggable database pdb1 close;
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}

PITR in pluggable database ?

rman target=/
run{
alter pluggable database pdb1 close;
set until time "SYSDATA - 10/24/2019"; (we can give either TIME, SCN or SEQUENCE)
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}


2.How to clone a local pluggable database (PDB) ?


-connect to root container database
conn / as sysdba

-check the pdb's
select name, open_mode from v$pdbs order by name;

-switch the source PDB to read-only mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;

-clone the PDB
-if you are not using OMF, convert the paths
sql>create pluggable database pdb2 from pdb1
file_name_convert=('/u01/app/oradata/cdb1/pdb1/','/u01/app/oradata/cdb1/pdb2');
alter pluggable database pdb2 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name; (here only pdb2 will be read write mode)

-switch the source PDB to read-write mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name;

How to remove the clone PDB's ?

alter pluggable database pdb2 close;
drop pluggable database pdb2 including datafiles;



How to clone a remote pluggable database (PDB) ?


-connect to remote PDB
conn sys/password@pdb5 as sysdba

-check database name
select name from v$database;

-create & grant a user we can point a database link to
create user remote_clone_user identified by remote_clone_user;
grant create session, create pluggable database to remote_clone_user;

-switch the remote PDB to read-only mode
alter pluggable database pdb5 close;
alter pluggable database pdb5 open read only;

-connect to root container in the local CDB
conn sys/password@CDB1 as sysdba

-check the database name
select name from v$database;

-create a database link to the remote PDB
create database link clone_link connect to remote_clone_user identified by abc123 using 'pdb5';

-clone the remote database over the database link
create pluggable database pdb5new from pdb5@clone_link file_name_convert=('/u01/aap/oradata/cdb3/pdb5/','/u01/app/oradata/cdb1/pdb5new/');

alter pluggable database pdb5new open;

-check the pdb's
select name, open_mode from v$pdbs order by name;

-open the remote PDB in read-write mode again
alter pluggable database pdb5 close;
alter pluggable database pdb5 open;


How to connect to PDB & CDB database's ?


-connect to CDB using OS authentication
conn / as sysdba

-connect to CDB using a local connection
conn system/manger

-check available services
select name, pdb from v$services order by name;
all services are also registered with listener

-connect to CDB using EZCONNECT & tnsnames.ora
conn system/manager@//localhost:1521/cdb1
conn system/manager@cdb1

-display the container name and ID usign sqlplus
show con_name
show con_id

-switch to a user created pluggable database
alter session set container=pdb1;
show con_name

-switch back to root container
alter session set container=cdb$root;

-connect to pdb using tnsnames.ora
conn system/password@pdb1


How to create a empty pluggable database ?


-connect to root container
conn / as sysdba

-create a pdb based on seed
create pluggable database pdb2 admin user pdb_adm identifed by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed)',/u01/pp/oradata/cdb2/pdb3/';
(or)
-set file_name_convert at session level
alter session set pdb_file_name_convert='/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb4/';
create pluggable database pdb4 admin user pdb_adm identified by password1;

Alertlog show below message:

create pluggable database pdb4 admin user pdb_adm identified by *
Tue Dec 31 02:02:26 2019
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.APEX$_WS_NOTES (CONTENT) - CLOB populated
Tue Dec 31 02:03:17 2019
****************************************************************
Pluggable Database PDB4 with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB4 is WE8MSWIN1252
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#13 to file$(old file#5)
Adding new file#14 to file$(old file#7)
Successfully created internal service pdb4 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=5 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB4 with pdb id - 5 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb4 admin user pdb_adm identified by *


-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NEW
select name, open_mode from v$pdbs;

-finish pdb creation by opening the PDB
alter pluggable database pdb2 open read write;

-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NORMAL

If you want to drop the newly created PDB's
alter pluggable database pdb3 close;
drop pluggable database pdb3 including datafiles;


How to manage tablespaces in a CDB & PDB ?


connect to root container
conn / as sysdba

show con_name
CDB$ROOT

create a tablespace for the CDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

create a tablespace for PDB:
alter session set container=pdb1;
(or)
conn sys/password@pdb1 as sysdba

show con_name
PDB1

create a tablespace for the PDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

Note:

A PDB doesn't have an undo tablespace, and we can't see it
We can see the undo datafile for the CDB from v$datafile

create temporary tablespace in the PDB:
create temporary tablespace temp2 tempfile '/u01/app/oradata/temp02.dbf' size 10m autoextend on next 10m;

remove a temporary tablespace:
drop tablespace temp2 including contents and datafiles;

There are 2 options for setting default tablespaces in PDB:

alter database default tablespace users;
alter database default temporary tablespace temp;
(or)
use PDB syntax for clarity
alter pluggable database default tablespace user;
alter pluggable database default temporary tablespace temp;


How to managing users and roles for CDB's and PDB's ?


connect to root container database

creating a common user:
create user c##test_user1 identified by password1 container=all;
grant create session to c##test_user1 container=all;

container=all default when issued from root container

create user c##test_user2 identified by password1;
grant create session to c##test_user2;

Switch container while connected to a common user or switch to PDB, alter session set container=PDB1;
conn sys/password1@pdb1 as sysdba

create the local user using the container clause:
create user test_user3 identified by password1 container=current;
grant create session to test_user3 container=current;

container=current default when issued from non-root container.
create user test_user4 identified by password1;
grant create session to test_user4;

Make a local user a DBA role:
grant PDB_DBA to test_user4;

create a common role
conn / as sysdba
create role c##test_role1;
grant it to a common user
grant c##test_role1to c##test_user1 container=all;

grant it to a lcoal user
conn sys/password1@pdb1
grant c##test_role1 to test_user3;

create local role:
create role test_role1;
grant create session to test_role1;


How to pluggable database PDB's archive files ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display the current PDB's:

show pdbs

pdb1 read write

unplug:

alter pluggable database <pdb1> close immediate;
alter pluggable database <pdb1> unplug into '/u01/mqm/pdb1.pdb';

we can safely drop the PDB after archiving
drop pluggable database pdb1 including datafiles;

We can check the content of the pdb archive files using unzip -l command
It will show the .dbf files plus .xml file


plug-in:

check its safe to plugin.

set serveroutput on
declare
1_result BOOLEAN;
BEGIN
1_result := DBMS_PDB.check_plug_compatibility(pdb_descr_file => '/tmp/pdb1.pdb',
      pdb_name =>'pdb1');
IF 1_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/

plugin from an archive file.
create pluggable database pdb1 using '/u01/mqm/pdb1.pdb';
alter pluggable database pdb1 open read write;

show pdbs


How to refresh pluggable database PDB's ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display current pdb's

show pdbs

pdb5 read write

create link user for DBlink
create user c##remote_clone_user identified by remote_clone_user container=all;
grant create session, create pluggable database to c##remote_clone_user container=all;

connect to CDB1 and create database link.
conn sys/password1@cdb1 as sysdba
create database link clone_link connect to c##remote_clone_user identified by remote_clone_user using 'cdb3';

create refreshable read-only PDB
create pluggable database pdb5_ro from pdb5@clone_link refresh mode manual;
alter pluggable database pdb5_ro open read only;

Make changes to the source PDB.
conn sys/password1@cdb3 as sysdba
alter session set container=pdb5;
create tablespace test_tbs datafile size 10m autoextend on next 10m;

check for the TEST_TBS tablespace in the refreshable PDB.
conn sys/password1@cdb1 as sysdba
alter session set container=pdb5_ro;
select tablespace_name from dba_tablespaces;
It will not show the test_tbs tablespace.

Refresh the read-only PDB.
alter session set container=pdb5_ro;
alter pluggable database close immediate;
alter pluggable database refresh;
alter pluggable database open read only;
select tablespace_name from dba_tablespaces;
Now it will show the test_tbs tablespace.

alter the refreshable PDB refresh mode.
alter pluggable database pdb5_ro refresh mode every 120 minutes;
select pdb_id, pdb_name, refresh_mode, refresh_interval from dba_pdbs order by 1;


How to startup & shutdown of PDB's & CDB's database's ?


CDB shutdown:
conn  / as sysdba
shutdown immediate;

CDB startup:
conn / as sysdba
startup

using alter command.
alter pluggable datababase <pdb1_name,pdb2_name,or all> open or close;

How to unplug and plugin pluggable database (PDB's) ?

check the availabe pdb's by connecting root container database.
conn / as sysdba
select name, open_mode from v$pdbs order by name;

create a new PDB to test the unplug/plugin.
create pluggable database pdb2 admin user pdb_adm identified by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb2/');
alter pluggable database pdb2 open read write;

unplug the PDB:
alter pluggable database pdb2 close;
alter pluggable database pdb2 uplug to '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml';

check the pdbs are present.
select name, open_mode from v$pdbs order by name;
Here pdb2 should be mount stage.

Drop the PDB, but keep the files.
drop pluggable database pdb2 keep datafiles;

check again the pdb status.
select name, open_mode from v$pdbs order by name;
It will drop the PDB2 database.

check the compatibility.

plugin and rename the PDB:
create pluggable database pdb5 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml' file_name_convert=('/u01/app/oradata/cdb1/pdb2/','/u01/app/oradata/cdb1/pdb5/');
alter pluggable database pdb5 open;

check the status
select name, open_mode from v$pdbs order by name;

drop the new PBD
alter pluggable database pdb5 close;
alter pluggable database pdb5 including datafiles;

plugin the database using the same name and file position.
create pluggable database pdb2 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml'
nocopy
tempfile reuse;
alter pluggable database pdb2 open read write;

check the status
select name, open_mode from v$pdbs order by name;

The Multitenant Architecture

The Multitenant Architecture

The Multitenant architecture allows a single super container (“Container” alias CDB) to shelter multiple other containers (“Pluggable” alias PDB). From the server subsystem, there is only one database i.e. the container database, and therefore only one instance (and one SID) is available i.e. the CDB, while each application sees only the specific PDB to which it connects to. There is no application code change required while connecting to a PDB. Each PDB runs as a service within the CDB but ensures complete security and isolation amongst them.

Traditionally, an Oracle database consists of its instance and the file structure. An Oracle database instance is made up of the memory and the background process. The file structure comprises of the datafiles (.dbf), control file, spfile, password file, redo logs, archived logs, undo segment and temp tablespace. Oracle 12c Multitenant architecture simply reorganizes these components and categorizes them as “sharable” component and “application component”. The “sharable” components are instance level pieces and the system pieces of a database like SPFILE (parameter file), control file, SYSTEM and SYSAUX tablespace, redo logs, archived log files, UNDO and TEMP. These sharable components exist at the root container (CDB) level. The user or application data for each database is the application component and therefore, each PDB just holds its own user data. Each PDB plugs into the CDB with its application data only while shares the memory, redo logs, archived logs, UNDO segment, and control file information with other pluggable databases in the same container.

A multitenant container database has three types of containers:

*The Oracle supplied container is called the root container (CDB$ROOT) and consists of just Oracle metadata (and maybe a little bit of user data) and common users. Each CDB has one root.

*The seed container is named PDB$SEED, and there is one of these per CDB. The purpose of the seed container isn’t to store user data—it’s there for you as a template to create PDBs.

*The user container , which is actually called a pluggable database (or PDB), consists of user metadata and user data.

Each of these—the root, the seed, and the PDB(s)—is called a container, and each container has a unique container ID (CON_ID) and container name (CON_NAME). Each PDB also has a globally unique identifier (GUID). The idea behind the concept of a container is to separate Oracle metadata and user data by placing the two types of data into separate containers. That is, the system and user data are separated. There’s a SYSTEM tablespace in both the central container and the PDB containers, however, they contain different types of data. The root container consists of Oracle metadata whereas the PDB container’s SYSTEM tablespace contains just user metadata. The Oracle metadata isn’t duplicated by storing it in each PDB—it’s stored in a central location for use by all the PDBs that are part of that CDB. The CDBs contain pointers to the Oracle metadata in the root container, thus allowing the PDBs to access these system objects without duplicating them in the PDBs. A CDB has similar background processes and files as a normal non-CDB database. However, some of the processes and files are common for both a CDB and its member PDB databases, and some aren’t.

Common Entities between CDB and PDBs

*Background processes There’s a single set of background processes for the CDB. The PDBs don’t have any background processes attached to them.

*Redo log files These are common for the entire CDB, with Oracle annotating the redo data with the identity of the specific PDB associated with the change. There’s one active online redo log for a single-instance CDB or one active online redo log for each instance of an Oracle RAC CDB. A CDB also has a single set of archived redo log files.

*Memory You allocate memory only to the CDB, because that’s the only instance you need in a multitenant database.

*Control files These are common for the entire CDB and will contain information that reflects the changes in each PDB.

*Oracle metadata All Oracle-supplied packages and related objects are shared.

*Temporary tablespace There’s a common temporary tablespace for an entire CDB. Both the root and all the PDBs can use this temporary tablespace. This common tablespace acts as the default TEMP tablespace. In addition, each PDB can also have a separate temporary tablespace for its local users.

*Undo tablespace All PDBs use the same undo tablespace. There’s one active undo tablespace for a single-instance CDB or one active undo tablespace for each instance of an Oracle RAC CDB.

A CDB contains a set of system data files for each container and a set of user-created data files for each PDB. Also CDB contains a CDB resource manager plan that allows resources management among the PDBs in that CDB.

Entities Exclusive for PDBs

*Tablespaces for the applications tables and indexes These application tablespaces that you’ll create are specific to a PDB and aren’t shared with other PDBs, or the central CDB. The data files that are part of these tablespaces constitute the primary physical difference between a CDB and a non-CDB. Each data file is associated to a specific container.

*Local temporary tablespaces Although the temporary tablespace for the CDB is common to all containers, each PDB can also create and use its own temporary tablespaces for its local users.

*Local users and local roles Local users can connect only to the PDB where the users were created. A common user can connect to all the PDBs that are part of a CDB.

*Local metadata The local metadata is specific to each application running in a PDB and therefore isn’t shared with other PDBs.

*PDB Resource Manager Plan These plans allow resource management within a specific PDB. There is separate resource management at the CDB level.

The PDB containers have their own SYSTEM and SYSAUX tablespaces. However, they store only user metadata in the SYSTEM tablespace and not Oracle metadata.

Data files are associated with a specific container. A permanent tablespace can be associated with only one container. When you create a tablespace in a container, that tablespace will always be associated with that container.

Oracle GoldenGate Basics

Oracle GoldenGate Basics

What is Oracle GoldenGate ?

Oracle Golden Gate is a software product that allows you to replicate, filter, and transform data from one database to another database.

What You Can do By using Oracle GoldenGate ?

-> Using Oracle Golden Gate, you can move committed transactions across multiple heterogeneous systems in your enterprise.
-> It enables you to replicate data between Oracle databases to other supported heterogeneous database, and between heterogeneous databases.
-> You can replicate to Java Messaging Queues, Flat Files, and to Big Data targets in combination with Oracle Golden Gate for Big Data.

Oracle GoldenGate Purposes ?

-> A static extraction of data records from one database and the loading of those records to another database.
-> Continuous extraction and replication of transactional Data Manipulation Language (DML) operations and data definition language (DDL) changes (for supported databases) to keep source and target data consistent.
-> Extraction from a database and replication to a file outside the database.

Oracle GoldenGate Key Features

-> Data movement is in real-time, reducing latency.
-> Only committed transactions are moved, enabling consistency and improving performance.
-> Different versions and releases of Oracle Database are supported along with a wide range of heterogeneous databases running on a variety of operating systems.
-> You can replicate data from an Oracle Database to a different heterogeneous database.
-> Simple architecture and easy configuration.
-> High performance with minimal overhead on the underlying databases and infrastructure.

GoldenGate supported Heterogeneous Environments ?

-> Different platforms (Linux, Windows, etc.)
-> Different databases (Oracle, MySQL, SQLServer, etc.)
-> Different database versions

Oracle GoldenGate Components

-> Extract
-> Data pump
-> Replicat
-> Trails or extract files
-> Checkpoints
-> Manager
-> Collector

Oracle GoldenGate Logical Architecture ?

When Do You Use Oracle GoldenGate ?

The most common use cases are
-> Business continuity and high availability
-> Initial load and database migration and upgrades with zero downtime
-> Data integration
-> Decision support and data warehousing.

How Do You Use Oracle GoldenGate ?

There are many different architectures that can be configured; which range from a simple uni-directional architecture to the more complex peer-to-peer. No matter the architecture, Oracle GoldenGate provides similarities between them, making administration easier.

Oracle Golden Gate Supported Topologies ?