No other site explain SQL Joins as THIS SITE
Dataguard commands and SQL scripts
Summary of DGMGRL Commands with Example
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
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 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
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 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.
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.
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.
There are quite a few useful additions, such as converting view into a table while exporting and turning off logging while import.
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.
The following substitution variables are available for both export and import operations.
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
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:
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:
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: