Monthly Archives: August 2018

Managing a Physical Standby Database

Data Guard, Tips,

Below steps are related to day-to-day operations of the Oracle Data Guard environment. These operations include starting the standby database, enabling managed recovery, opening the standby in read-only, as well as general maintenance tasks.

Starting a Physical Standby
Before Oracle 10g

SQL> startup nomount;
SQL> alter database mount standby database;

Starting with Oracle 10g, the start-up task can be done in single step

SQL> startup mount;

During startup, Oracle will read the controlfile when mounting the database to make the determination to mount the database as astandby database or as a primary database.

Starting  Managed Recovery:

Once the standby database has been started, it will begin receiving redo data from the primary database. This redo data will stack up in the form of archivelogs until we instruct the standby database to begin applying the redo data to the standby database. For a physical standby, the redo application is performed via the MRP.

SQL> alter database recovery managed standby database;

This above command will appear to hang because MRP is part of the session that it was started in. If we cancel out of this session, the MRP will also exit. To avoid this we need to run the MRP in the background as below

SQL> alter database recover managed standby database disconnect;

If the physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for the standby redo log to be archived. This functionality was introduced in 10g and is called Real-Time Apply. Real-Time Apply can shorten the role transition time by minimizing the redo that needs to be applied

To start Real-Time Apply, you initiate MRP by issuing the following command:

SQL> alter database recover managed standby database using current logfile disconnect;

Stopping Managed Recovery:

We could simply perform a normal shutdown on the database, or we could cancel managed recovery,
leaving the standby database up and running. To cancel managed recovery, issue the following:

SQL> alter database recover managed standby database cancel;

Starting and Stopping Active Data Guard:

The actual process of enabling Active Data Guard is simple: Open the physical standby database in read-only mode and start Redo Apply. A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. The Data Guard physical standby should be in
one of two states prior to enabling Active Data Guard:

1. The standby database is mounted and Redo Apply is running.

2. The standby database has been shut down cleanly and Redo Apply was stopped.

In the first scenario, proceed as follows using SQL*Plus

1. Stop Redo Apply:

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Open the database read-only:

SQL> ALTER DATABASE OPEN READ ONLY;

3. Restart Redo Apply:

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

In the second scenario, where the physical standby and Redo Apply are already shut down,
proceed as follow using SQL*Plus alone,

1. Start the physical standby in read-only mode.

SQL> STARTUP

2. Start Redo Apply.

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
——————–
READ ONLY

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

Open Physical Standby For Read Write Testing and Flashback

Data Guard, Tips,

Hi all, today I will show the process to Open a Dataguard database in Read Write mode, after that we will revert back using FlashBack Technologies.

Step 1 – In Standby database

A ) Set up a flash recovery area.

If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; 
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';

B ) Cancel Redo Apply and create a guaranteed restore point.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  
SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;

To Confirm the details of restore point and its SCN and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point; 

NAME                          SCN            TIME 
---------------------------  -------------   ----------
STANDBY_FLASHBACK_TESTING    22607810        12-APR-18

Continue reading Open Physical Standby For Read Write Testing and Flashback →

SQL*Plus Enhancements

12c, SQL,

PDB Support

SQL*Plus includes support for the multitenant architecture.

The SHOW command displays information about PDBs.

SQL> SHOW CON_ID

CON_ID
------------------------------
1
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

Command History

Before we can use the HISTORY command we have to make sure it is turned on. In the example below we attempt to use the HISTORY command with no options and we are told the HISTORY command is not enabled, so we enable it as instructed. Continue reading SQL*Plus Enhancements →

Histograms

Tips, Tuning,

Histograms

 

Histograms tell the Optimizer about the distribution of data within a column. By default (without a histogram), the Optimizer assumes a uniform distribution of rows across the distinct values in a column. Therefore, the Optimizer calculates the cardinality (number of rows returned) for an equality predicate by dividing the total number of rows in the table by the number of distinct values in the column used in the equality predicate.

If the data distribution in that column is not uniform (i.e., a data skew exists) then the cardinality estimate will be incorrect.
In order to accurately reflect a non-uniform data distribution, a histogram is required on the column. The presence of a histogram changes the formula used by the Optimizer to estimate the cardinality, and allows it to generate a more accurate execution plan.
A histogram sorts values into “buckets,” as you might sort coins into buckets.

Continue reading Histograms →

Mark data deleted or In-Database Archiving

12c, SQL,

Rather than deleting data, some applications have a concept of “mark for delete”, so the data remains present in the table, but is not visible to the application. This is usually achieved by doing the following.

  • Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
  • Add an extra predicate to every statement that checks the deleted status, like “WHERE deleted = 'N'“, to exclude the deleted rows from the SQL and then create a view on that table to display only “active” records.

In-Database Archiving is a feature added to Oracle Database 12c to allow this type of “mark for delete” functionality out-of-the-box, with fewer changes to the existing application code.

Continue reading Mark data deleted or In-Database Archiving →

Data Pump enhancements

12c, DataPump, ,

There are quite a few useful additions, such as converting view into a table while exporting and turning off logging while import.

New Substitution Variables for File Names

Multiple files are generated by parallel exports, so each file needs to have a unique name. This is achieved using substitution variables. In previous releases the only substitution variable available was “%U”, which generated a two digit number from 01-99.
Oracle 12.2 includes additional substitution variables.

The following substitution variables are only available for export operations.

  • %d, %D : The current day of the month in DD format.
  • %m, %M : The current month in MM format.
  • %y, %Y : The current year in YYYY format.
  • %t, %T : The current date in YYYYMMDD format.

The following substitution variables are available for both export and import operations.

  • %U : Unchanged from previous releases. A two digit number incremented between 01-99.
  • %l, %L : This starts off similar to “%U”, producing a two digit number between 01-99, but it can extend up to 2147483646, so the resulting file name is not a fixed length.

The following example shows the usage of two of the new substitution variables in a parallel export. The output is edited to reduce its size.

$ expdp system@DB schemas=scott directory=TEST dumpfile=scott_%T_%L.dmp logfile=expdpscott.log parallel=4
...
....
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/scott_20180814_01.dmp
  /tmp/scott_20180814_02.dmp
  /tmp/scott_20180814_03.dmp
  /tmp/scott_20180814_04.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Wed Mar 22 16:05:41 2017 elapsed 0 00:01:07

Continue reading Data Pump enhancements →

12c: PLUG IN 12c NON-CDB AS PDB

12c,

In this post, I will demonstrate the conversion of a 12.1.0.1 non-CDB  to a PDB. We will plug in  12.1.0.1 non-cdb named ncdb12c into a CDB called CDB2

Current scenario:

Source  = 12.1.0.1 non-cdb  : ncdb12c
Target CDB                               : CDB2
Target PDB                              : ncdb12c

To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB. The conversion process is similar to unplug and plug of a pdb.

Overview:

– Log into ncdb12c as sys
– Get the database in a consistent state by shutting it down cleanly.
– Open the database in read only mode
– Run DBMS_PDB.DESCRIBE to create an XML file  describing the database.
– Shut down ncdb12c
– Connect to target CDB (CDB2)
– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
– Plug-in Non-CDB (NCDB12c) as PDB(NCDB12c) into target CDB(CDB2).
– Access the PDB and run the noncdb_to_pdb.sql script.
– Open the new PDB in read/write mode.

Implementation:

Continue reading 12c: PLUG IN 12c NON-CDB AS PDB →

ACCESS EM EXPRESS FOR CDB / PDB / Non-CDB

12c, OEM,

Let’s see how to access EM express for various types of databases.

Oracle 12c has introduced multitenant architecture in which we can have different types of databases:

– CDB’s
– PDB’s
– Non CDB’s

Let’s see how to access EM express for various types of databases:

Accessing EM express for CDB’s

At the root container, data is displayed for the entire CDB, and all actions taken impact all containers.  Data pertaining to a PDB, such as a user or a SQL statement, is qualified with the container name.  The actions a user can perform at the root container include:

– viewing and setting initialization parameters;
– administering common users and privileges; and
– managing memory configuration, undo information and redo logs.

– Find out the port on which EM Express is configured:

Three methods are available:

Continue reading ACCESS EM EXPRESS FOR CDB / PDB / Non-CDB →