Category Archives: Tips

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

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

Playing with Oracle 12c and Virtual Box

I keep “playing'” with 12c, and this time I’d like to show you how easy is to install the Pre-Built VMs provided by Oracle.

As I mentioned, the process is very simple, these are the requirements to install it:

  • At least 2GB RAM. Default VM is 1G RAM, for better performance increase.
  • At least 15GB of free space (Note: virtualization works best with contiguous space so it is a good idea if on Windows to run a defrag program, and make sure you are using NTFS for your file system to handle large files on Windows. )
  • 2GHz Processor (a lesser processor will be acceptable but slower)
  • Mozilla Firefox 2.0 or higher, Internet Explorer 7 or higher, Safari 3.0 and higher or Google Chrome 1.0 or higher
  • Adobe Acrobat reader
  • Admin privileges on your box

So now that you know the minimum requirements, let’s move to the installation process:

  1. Download and install Oracle VM VirtualBox on your host system from HERE.
  2. Download the Virtual Machine from HERE. (DownloadOTN_Developer_Day_VM.ova (5,215,947,264 bytes, md5sum: 4e8ef75cdbd0fbe3d3beee8f873b2d4a)
  3. Open Oracle VM VirtualBox and Import your VM: File > Import Appliance to launch Appliance Import Wizard.
    Click Choose… to browse to the directory you re-assembled all the files in and select the OTN_Developer_Day_VM.ova.  Then click Next to begin importing the virtual machine. It will prompt you to agree to the appropriate developer licenses while importing. You will see ‘Oracle Developer Days (Powered Off)’ when it is finished importing.
  4. Test your VM: Once the import has completed, double-click the OTN Developer Days VM. Click OK to close the Virtualbox Information dialogs. When you get to the Enterprise Linux 6 screen you can now login. (Username and password is oracle.) Allow the process to complete; it is ready when you see a terminal window, which you can close.
  5. Once you are finished working in the guest VM you can shut it down via System > Shut Down; this will return the guest VM to the Powered Off state.

As you can see, the process is pretty simple right?

Have a nice day!!

Using ADRCI Properly

The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data and is part of the fault diagnosability infrastructure introduced in Oracle 11g. ADRCI enables you to:

  • View diagnostic data within the Automatic Diagnostic Repository (ADR).
  • View Health Monitor reports.
  • Package incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

Invoke ADRCI as the Oracle OS user:

> adrci
ADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle" adrci> show home ADR Homes:
diag/diagtool/user_oracle/host_12454656_11
diag/rdbms/orcl/ORCL
diag/asm/+asm/+ASM1
diag/tnslsnr/orcl/listener_cad Set the home adrci> set home diag/rdbms/orcl/ORCL
To check the alert log use the following option.

adrci> show alert -tail -f
2014-01-21 10:20:23.418000 -05:00
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1176
Successful close of redo thread 1
Completed: alter database close normal
alter database dismount
Completed: alter database dismount
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
2014-01-21 10:20:24.852000 -05:00
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Stopping background process VKTM:
2014-01-21 10:20:27.036000 -05:00
Instance shutdown complete

Continue reading Using ADRCI Properly