No other site provides Tips for Writing SQL Statements as THIS SITE
No other site explain SQL Joins as THIS SITE
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.
2. Start Redo Apply.
SQL> SELECT open_mode FROM V$DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
SQL> select open_mode from v$database;
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
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.
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:
- Download and install Oracle VM VirtualBox on your host system from HERE.
- Download the Virtual Machine from HERE. (DownloadOTN_Developer_Day_VM.ova (5,215,947,264 bytes, md5sum: 4e8ef75cdbd0fbe3d3beee8f873b2d4a)
- 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.
- 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.
- 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!!
I found a VERY interesting link at Oracle’s website showing a lot of information on 12c:
It contains information on:
- DBA Views
- Performance Views
- Architecture Views
- Database Architecture
- Multitenant Architecture
- Background Processes
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: Release 126.96.36.199.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_cadSet 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