All posts by dpafumi

Start DG Database Automatically

In order to start a DG database automatically you can use DGMGRL. You must have parameter dg_broker_start=true,
You can set it to true using:
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

When database is started from DGMGRL, it starts the database in mode according to database_role.

For Primary, it will start database in open read write stage.
For Physical Standby, it will start database in mount stage and also start the managed recovery.
For Logical Standby, it will start database in open stage and also start the SQL apply.

Steps for Windows:
1- Set ORA_SID_AUTOSTART to false in windows registry.

2- Create a batch job on the both primary and standby database:
start1.bat
=========
set ORACLE_SID=
dgmgrl / “startup”

start2.bat
======
set ORACLE_SID=
dgmgrl sys/ “startup”

++ Use either of 2 (start1.bat or start2.bat), only difference in these is OS and database authentication.

3- Now schedule this bat file to execute automatically at the system reboot to start the database automatically

Continue reading Start DG Database Automatically

All Datapump Options

Oracle 12c datapump have following clauses, which we would discuss one by one:-

EXCLUDE – This is used to filter the metadata from the export operation. This is more like a where clause which you can use to exclude any object or schema from the export job.

Example if you want a full database export except 1 schema (say TEST), then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp FULL=YES EXCLUDE=SCHEMA:"='TEST'" LOGFILE=test.log

Similarly suppose you want to exclude all indexes from a schema export and the condition is to export only indexes that starts with TEST_%, then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" LOGFILE=test.log

So using this clause you can exclude any specific content from the dump, try and explore this option more. To get a list of available objects that can be excluded query the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views.

INCLUDE – Exactly opposite to EXCLUDE clause, this is used when you explicitly want to export, say only some tables, indexes along with views, packages in a schema dump, so only the the object types explicitly mentioned in the include clause would be imported and nothing else.

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST INCLUDE=TABLE:"IN ('EMP', 'DEPT')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'TEST%'" LOGFILE=test.log

So the above expdp command would only export tables EMP & DEPT, PROCEDURE and all indexes starting with TEST% no other objects of TEST schema would be exported.

QUERY – This clause allows you to filter the data that gets exported, think of it as EXCLUDE & INCLUDE clause but with more control over filtering, thus this is more like a WHERE clause of the expdp command.

When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table.

It accepts values in the form:- QUERY = [schema.][table_name:] query_clause

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST QUERY=TEST.EMP:"WHERE DEPT_ID > 90 AND SAL > 90000" LOGFILE=test.log

Above command would export all tables of TEST schema but only specific records of EMP table.

CLUSTER – This is used in RAC env. and used to spread the worker process on other oracle instances to utilize RAC resources for datapump jobs. Continue reading All Datapump Options

Managing a Physical Standby Database

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

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

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

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

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