11g New Feature: OLTP Table Compression
Posted at Tuesday, February 19, 2008
In 11g table compression has been expanded and is now suitable for conventional
DML operations, including previously supported direct-load operations (e.g. INSERT /*+ APPEND */). Therefore, it is now possible to use compression in OLTP environments as well. It is important to note, however, that this feature requires compatible to be 11.1.0 or higher.
The benefit of using such compression is that disk space is greatly reduced as is memory consumption (since less buffers need to be written into an instance's buffer cache). The downside is that there is additional CPU overhead for loading as well as DML operations.
Here is an example of applying the new table compression to an existing table:
Using SH.CUSTOMERS as the example, query its storage characteristics:
We now know how many blocks are employed by the table segment plus how many records are currently stored per block.
Create a copy of the table us CTAS and then observe whether the table compression feature stores the same number of records in fewer blocks or not.
Query the data dictionary to confirm the newly created segments have the desired attribues:
Query the compressed table's storage characteristics:
The resulting compressed table stores the same data in about half the space:
DML operations, including previously supported direct-load operations (e.g. INSERT /*+ APPEND */). Therefore, it is now possible to use compression in OLTP environments as well. It is important to note, however, that this feature requires compatible to be 11.1.0 or higher.
The benefit of using such compression is that disk space is greatly reduced as is memory consumption (since less buffers need to be written into an instance's buffer cache). The downside is that there is additional CPU overhead for loading as well as DML operations.
Here is an example of applying the new table compression to an existing table:
SQL> connect sh
Using SH.CUSTOMERS as the example, query its storage characteristics:
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
2 from sh.customers;
TABLE BLOCK COUNT
-----------------
1454
SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
2 from sh.customers group by dbms_rowid.rowid_block_number(rowid);
AVERAGE RECORDS PER BLOCK
-------------------------
38.170564
We now know how many blocks are employed by the table segment plus how many records are currently stored per block.
Create a copy of the table us CTAS and then observe whether the table compression feature stores the same number of records in fewer blocks or not.
SQL> create table sh.customers_compressed compress for all operations as select * from sh.customers;
Table created.
Query the data dictionary to confirm the newly created segments have the desired attribues:
SQL> select table_name, compression, compress_for
2 from user_tables where table_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
CUSTOMERS DISABLED
CUSTOMERS_COMPRESSED ENABLED FOR ALL OPERATIONS
Query the compressed table's storage characteristics:
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
2 from sh.customers_compressed;
TABLE BLOCK COUNT
-----------------
763
SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
2 from sh.customers_compressed group by dbms_rowid.rowid_block_number(rowid);
AVERAGE RECORDS PER BLOCK
-------------------------
72.7391874
The resulting compressed table stores the same data in about half the space:
SQL> select segment_name, round(bytes/1024/1024) "MB"
2 from dba_segments
3 where owner='SH'
4 and segment_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');
SEGMENT_NAME MB
------------------------------ ----------
CUSTOMERS_COMPRESSED 7
CUSTOMERS 12
How to Setup the Oracle Wallet for Encryption Functions
Posted at Sunday, February 10, 2008
Oracle has a variety of encryption features, most of which leverage the Oracle Wallet. Here are the steps to setup the wallet on an 11g database (most apply to 10g as well):
Step 1: Configure Networking
Add the following entry to your $TNS_ADMIN/sqlnet.ora changing the directory to a path relevant to your installation.
Step 2: Create the wallet via your preferred method (I use Oracle Wallet Manager)
Launch the Oracle Wallet Manager:
Here are the screenshots for this step:
Create a new wallet
Enter the password
Specify the location
Set as an Auto-login wallet and save
Should yield the following files:
Step 3 Create Master Key for TDE:
Note that the Oracle wallet manager does not create this master key. The above is the appropriate method for doing so.
Step 4 Verify wallet is open
You should see that the size of the auto-login and encryption wallet have changed (since the master key has been added):
If the wallet is not open:
At this point, you should be able to transparently encrypt tablespaces, columns, LOBS, etc.
Frequently asked questions about TDE: 10g
11g Tablespace Encryption: Three easy steps
Step 1: Configure Networking
Add the following entry to your $TNS_ADMIN/sqlnet.ora changing the directory to a path relevant to your installation.
[/u03/app/oracle/product/db/11.1.0.6/network/admin nf@rac2]$ cd $TNS_ADMIN
[/u03/app/oracle/product/db/11.1.0.6/network/admin nf@rac2]$ more sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u03/app/oracle/oradata/nf/wallet)))
Step 2: Create the wallet via your preferred method (I use Oracle Wallet Manager)
Launch the Oracle Wallet Manager:
[/u03/app/oracle/product/db/11.1.0.6/bin nf@rac2]$ export DISPLAY=192.168.1.104:0.0
[/u03/app/oracle/product/db/11.1.0.6/bin nf@rac2]$ ./owm
Here are the screenshots for this step:
Create a new wallet
Enter the password
Specify the location
Set as an Auto-login wallet and save
Should yield the following files:
[/u03/app/oracle/product/db/11.1.0.6/bin nf@rac2]$ ls -lart /u03/app/oracle/oradata/nf/wallet
total 24
drwxr-x--- 6 oracle dba 4096 Feb 11 00:56 ..
-rw------- 1 oracle dba 7312 Feb 11 00:56 ewallet.p12
drwx------ 2 oracle dba 4096 Feb 11 00:56 .
-rw------- 1 oracle dba 7340 Feb 11 00:56 cwallet.sso
Step 3 Create Master Key for TDE:
SQL> alter system set encryption key identified by "password";
System altered.
Note that the Oracle wallet manager does not create this master key. The above is the appropriate method for doing so.
Step 4 Verify wallet is open
SQL> col wrl_parameter format a40
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ---------
file /u03/app/oracle/oradata/nf/wallet OPEN
You should see that the size of the auto-login and encryption wallet have changed (since the master key has been added):
[/u03/app/oracle/oradata/nf/wallet nf@rac2]$ ls -alrt /u03/app/oracle/oradata/nf/wallet
total 32
drwxr-x--- 6 oracle dba 4096 Feb 11 00:56 ..
drwx------ 2 oracle dba 4096 Feb 11 00:56 .
-rw------- 1 oracle dba 8453 Feb 11 01:24 ewallet.p12
-rw------- 1 oracle dba 8481 Feb 11 01:24 cwallet.sso
If the wallet is not open:
SQL> alter system set encryption wallet open identified by "password";
System altered.
At this point, you should be able to transparently encrypt tablespaces, columns, LOBS, etc.
Frequently asked questions about TDE: 10g
11g Tablespace Encryption: Three easy steps
Extracting DDL from Oracle: 2 Approaches
Posted at Wednesday, February 06, 2008
In the past, the most practical approach to extracting DDL from Oracle was either to use a 3rd-party tool and/or write a ton of dynamic SQL. I like using Quest's TOAD product, but it's also useful to know the alternatives.
Approach 1: Use the DBMS_METADATA package
Example:
Here is a script that first creates a temporary table, then creates a PL/SQL procedure that makes various calls to DBMS_METADATA. Reference the Oracle docs to get the most out of this package: DBMS_METADATA package reference. In my example, I show how to use this method to extract the DDL for all the directory objects in the database (i.e. DBA_DIRECTORIES). Of course, I could have extracted just about anything.
Contents of sample_ddl_extract.sql:
Run the example:
Query the results via:
Examine the results of the output file, sample_ddl_extract.out:
Approach 2: Use the DATAPUMP utilities
Example:
In this example I show how to accomplish the exact same result as in Approach 1. The only difference here is that we use the DATAPUMP utilities. Contents of sample_ddl_extract.bsh:
Run the example:
Examine the results of the output file, /u02/app/oradata/cubs/admin/dp/directory.sql:
Consult DATABASE_EXPORT_OBJECTS and SCHEMA_EXPORT_OBJECTS to see which object types are available for similar metadata exports.
Here is a zip archive from this example: ddl_extract_sample.zip
Approach 1: Use the DBMS_METADATA package
Example:
Here is a script that first creates a temporary table, then creates a PL/SQL procedure that makes various calls to DBMS_METADATA. Reference the Oracle docs to get the most out of this package: DBMS_METADATA package reference. In my example, I show how to use this method to extract the DDL for all the directory objects in the database (i.e. DBA_DIRECTORIES). Of course, I could have extracted just about anything.
Contents of sample_ddl_extract.sql:
DROP TABLE my_metadata;
CREATE TABLE my_metadata(md CLOB);
CREATE OR REPLACE PROCEDURE sample_ddl_extract
AS
hndl NUMBER; --dbms_metadata handle
th NUMBER; --transform handle
DDL CLOB; --individual clobs extracted from the database
BEGIN
hndl := DBMS_METADATA.OPEN ('DATABASE_EXPORT'); --Open the metadata
DBMS_METADATA.set_filter (hndl, 'INCLUDE_PATH_EXPR', '=''DIRECTORY'''); --Filter data as appropriate
th := DBMS_METADATA.add_transform (hndl, 'DDL'); --Get the Transform Handle
DBMS_METADATA.set_transform_param (th, 'SQLTERMINATOR', TRUE); --Include the semicolon
LOOP
DDL := DBMS_METADATA.fetch_clob (hndl); --Loop through the result set, inserting into our temp table
EXIT WHEN DDL IS NULL;
INSERT INTO my_metadata
(md)
VALUES (DDL);
COMMIT;
END LOOP;
DBMS_METADATA.CLOSE (hndl);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
show errors
Run the example:
[/tmp cubs2@rac2]$ sqlplus "/ as sysdba" @/tmp/sample_ddl_extract.sql
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 6 15:19:18 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Table dropped.
Table created.
Procedure created.
No errors.
Query the results via:
exec sample_ddl_extract
set echo off
set long 9000000
set longc 900
set lines 400
set trimspool on
set pages 0
set feedback off
set head off
set sqlblanklines off
spool /tmp/sample_ddl_extract.out
select * from my_metadata;
spool off;
Examine the results of the output file, sample_ddl_extract.out:
[/tmp cubs2@rac2]$ more sample_ddl_extract.out
SQL> select * from my_metadata;
CREATE OR REPLACE DIRECTORY "DBCAPTURE" AS '/u02/app/oradata/cubs/admin/dbcapture';
CREATE OR REPLACE DIRECTORY "DATA_PUMP_DIR" AS '/u02/app/oradata/cubs/admin/dp';
CREATE OR REPLACE DIRECTORY "DBREPLAY" AS '/u02/app/oradata/cubs/admin/dbreplay';
CREATE OR REPLACE DIRECTORY "ORACLE_OCM_CONFIG_DIR" AS '/u03/app/oracle/product/db/11g/ccr/state';
CREATE OR REPLACE DIRECTORY "AUDIT_DIR" AS '/tmp/';
CREATE OR REPLACE DIRECTORY "IDR_DIR" AS '/u02/app/oradata/cubs/admin/log/diag/rdbms/cubs/cubs2/ir';
CREATE OR REPLACE DIRECTORY "XMLDIR" AS '/u03/app/oracle/product/db/11g/rdbms/xml';
GRANT READ ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";
GRANT WRITE ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";
GRANT READ ON DIRECTORY "DBCAPTURE" TO "SYSTEM";
GRANT WRITE ON DIRECTORY "DBCAPTURE" TO "SYSTEM";
GRANT READ ON DIRECTORY "DBREPLAY" TO "SYSTEM";
GRANT WRITE ON DIRECTORY "DBREPLAY" TO "SYSTEM";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";
SQL> spool off;
Approach 2: Use the DATAPUMP utilities
Example:
In this example I show how to accomplish the exact same result as in Approach 1. The only difference here is that we use the DATAPUMP utilities. Contents of sample_ddl_extract.bsh:
#!/bin/bash
export ORACLE_SID=cubs2;
. oraenv
sqlplus -S /nolog <<EOF
CONNECT / AS SYSDBA;
COL OBJECT_PATH FORMAT A20;
COL COMMENTS FORMAT A80;
SET LINES 200;
SELECT * FROM DATABASE_EXPORT_OBJECTS WHERE OBJECT_PATH='DIRECTORY';
EOF
$ORACLE_HOME/bin/expdp \"/ as sysdba\" reuse_dumpfiles=y include=directory full=y content='METADATA_ONLY' directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp
$ORACLE_HOME/bin/impdp \"/ as sysdba\" directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp sqlfile=data_pump_dir:directory.sql
Run the example:
[/tmp cubs2@rac2]$ ./sample_ddl_extract.bsh
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
OBJECT_PATH COMMENTS N
-------------------- -------------------------------------------------------------------------------- -
DIRECTORY Directories and their dependent grants and audits Y
Export: Release 11.1.0.6.0 - Production on Wednesday, 06 February, 2008 15:29:27
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" reuse_dumpfiles=y include=directory full=y content=METADATA_ONLY directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u02/app/oradata/cubs/admin/dp/metadata.expdp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:29:41
Import: Release 11.1.0.6.0 - Production on Wednesday, 06 February, 2008 15:29:42
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=data_pump_dir dumpfile=data_pump_dir:metadata.expdp sqlfile=data_pump_dir:directory.sql
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 15:29:46
Examine the results of the output file, /u02/app/oradata/cubs/admin/dp/directory.sql:
[/tmp cubs2@rac2]$ more /u02/app/oradata/cubs/admin/dp/directory.sql
-- CONNECT SYS
ALTER SESSION SET EDITION = "ORA$BASE";
-- new object type path: DATABASE_EXPORT/DIRECTORY/DIRECTORY
CREATE DIRECTORY "DBCAPTURE" AS '/u02/app/oradata/cubs/admin/dbcapture';
CREATE DIRECTORY "DATA_PUMP_DIR" AS '/u02/app/oradata/cubs/admin/dp';
CREATE DIRECTORY "DBREPLAY" AS '/u02/app/oradata/cubs/admin/dbreplay';
CREATE DIRECTORY "ORACLE_OCM_CONFIG_DIR" AS '/u03/app/oracle/product/db/11g/ccr/state';
CREATE DIRECTORY "AUDIT_DIR" AS '/tmp/';
CREATE DIRECTORY "XMLDIR" AS '/u03/app/oracle/product/db/11g/rdbms/xml';
-- new object type path: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT READ ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";
GRANT WRITE ON DIRECTORY "ORACLE_OCM_CONFIG_DIR" TO "ORACLE_OCM";
GRANT READ ON DIRECTORY "DBCAPTURE" TO "SYSTEM";
GRANT WRITE ON DIRECTORY "DBCAPTURE" TO "SYSTEM";
GRANT READ ON DIRECTORY "DBREPLAY" TO "SYSTEM";
GRANT WRITE ON DIRECTORY "DBREPLAY" TO "SYSTEM";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "SCOTT";
Consult DATABASE_EXPORT_OBJECTS and SCHEMA_EXPORT_OBJECTS to see which object types are available for similar metadata exports.
Here is a zip archive from this example: ddl_extract_sample.zip
[/tmp cubs2@rac2]$ unzip -l ddl_extract_sample.zip
Archive: ddl_extract_sample.zip
Length Date Time Name
-------- ---- ---- ----
539 02-06-08 14:49 sample_ddl_extract.bsh
949 02-06-08 15:18 sample_ddl_extract.sql
1145 02-06-08 15:25 sample_ddl_extract.out
1080 02-06-08 15:29 directory.sql
-------- -------
3713 4 files
11g New Feature: (DRCP) Database Resident Connection Pooling
Posted at Monday, February 04, 2008
Oracle 11g introduces a Database Server-side Connection pooling feature which holds much promise.
A great overview of this new feature can be found in this excellent article:
Oracle Database 11g:
The Top New Features for DBAs and Developers
by Arup Nanda: Caching and Pooling
I followed Arup's example, and just extended upon it:
Here is the tnsnames.ora entry I created just for connections that are going to use the database resident connection pool (DRCP):
Of course, I did this on both of my instances.
Next, I cut and pasted Arup's Java class and modified it for my purposes:
Then I compiled it:
Now, the sample Java Class is ready to go. I created a wrapper script which calls this Class from a bash while loop:
Next, we have to start the default connection pool:
I placed one on these scripts on each of my database hosts and adjusted the $ORACLE_SID value as appropriate. I will call many instances of this script (using nohup to the background) from each database server to perform a load test; for example:
The combination of the DRCP as well as the results cache, should allow me to scale well beyond prior limits.
Here are the some of the results:
As you can see, I am simply using the default connection pool:
Note the high 'Find Count' for each instance:
As you can see, I was able to perform over a thousand executions on the cluster with virtual no I/O over an 11-minute period:
Here are the AWR reports from the test (both instances) - notice the efficiency:
CUBS1 Instance AWR Report
CUBS2 Instance AWR Report
A great overview of this new feature can be found in this excellent article:
Oracle Database 11g:
The Top New Features for DBAs and Developers
by Arup Nanda: Caching and Pooling
I followed Arup's example, and just extended upon it:
Here is the tnsnames.ora entry I created just for connections that are going to use the database resident connection pool (DRCP):
[/u03/app/oracle/product/db/11g/network/admin cubs2@rac2]$ tail -19 tnsnames.ora
CUBS_POOL.COLESTOCK.TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = cubs.colestock.test)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Of course, I did this on both of my instances.
Next, I cut and pasted Arup's Java class and modified it for my purposes:
[/tmp cubs2@rac2]$ more CacheTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CacheTest {
private String jdbcURL = "jdbc:oracle:oci8:@CUBS_POOL";
private Connection conn = null;
public CacheTest( ) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
CacheTest check = new CacheTest();
check.dbconnect();
check.doSomething();
}
public void dbconnect() throws SQLException {
System.out.println("Connecting with URL="+jdbcURL+" as scott/tiger");
try {
conn = DriverManager.getConnection( jdbcURL, "scott" , "tiger");
System.out.println("Connected to Database");
} catch (SQLException sqlEx) {
System.out.println(" Error connecting to database : " + sqlEx.toString());
}
}
public void doSomething() throws SQLException {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
System.out.println("Created Statement object");
rset = stmt.executeQuery("select /*+ result_cache */ count(*) from scott.dept");
System.out.println("Retrieved ResultSet object");
if(rset.next())
System.out.println("Result:"+rset.getString(1));
} catch (SQLException sqlEx) {
} finally {
try {
System.out.println("Closing Statment & ResultSet Objects");
if (rset != null) rset.close();
if (stmt != null) stmt.close();
if (conn != null) {
System.out.println("Disconnecting...");
conn.close();
System.out.println("Disconnected from Database");
}
} catch (Exception e) { }
}
}
}
Then I compiled it:
[/tmp cubs2@rac2]$ export ORACLE_SID=cubs2
[/tmp cubs2@rac2]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[/tmp cubs2@rac2]$ $ORACLE_HOME/jdk/bin/javac -verbose -classpath .:$ORACLE_HOME/jdbc/lib/jodbc5.jar CacheTest.java
[parsing started CacheTest.java]
[parsing completed 52ms]
[search path for source files: [.]]
[search path for class files: [/u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jsse.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jce.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/charsets.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/localedata.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunjce_provider.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunpkcs11.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/dnsns.jar, .]]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Connection.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/DriverManager.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/ResultSet.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/SQLException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Statement.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Object.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/String.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/ClassNotFoundException.class)]
[checking CacheTest]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Exception.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Throwable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Class.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/System.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/PrintStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/FilterOutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/OutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/util/Properties.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Error.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/RuntimeException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/AbstractStringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/CharSequence.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/Serializable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Comparable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuffer.class)]
[wrote CacheTest.class]
[total 423ms]
Now, the sample Java Class is ready to go. I created a wrapper script which calls this Class from a bash while loop:
[/tmp cubs2@rac2]$ more run_CacheTest.bsh
#!/bin/bash
export ORACLE_SID=cubs2;
. oraenv ;
while true
do
$ORACLE_HOME/jdk/bin/java -classpath /tmp/:.:$ORACLE_HOME/jdbc/lib/ojdbc5.jar CacheTest
done
Next, we have to start the default connection pool:
SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.
I placed one on these scripts on each of my database hosts and adjusted the $ORACLE_SID value as appropriate. I will call many instances of this script (using nohup to the background) from each database server to perform a load test; for example:
[/tmp cubs1@rac1]$ nohup /tmp/run_CacheTest.bsh &
[5] 24453
nohup: [/tmp cubs1@rac1]$ appending output to `nohup.out'
The combination of the DRCP as well as the results cache, should allow me to scale well beyond prior limits.
Here are the some of the results:
As you can see, I am simply using the default connection pool:
SQL> select pool_name, inst_id, num_open_servers, num_busy_servers, num_requests, num_waits, historic_max, num_purged from gv$cpool_stats;
POOL_NAME INST_ID NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_REQUESTS NUM_WAITS HISTORIC_MAX NUM_PURGED
------------------------------ ---------- ---------------- ---------------- ------------ ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL 1 14 2 2448 0 14 0
SYS_DEFAULT_CONNECTION_POOL 2 10 1 562 0 10 0
Note the high 'Find Count' for each instance:
SQL> select * from gv$result_cache_statistics order by name, inst_id;
INST_ID ID NAME VALUE
---------- ---------- ---------------------------------------- ----------
1 3 Block Count Current 32
2 3 Block Count Current 32
1 2 Block Count Maximum 1728
2 2 Block Count Maximum 1728
1 1 Block Size (Bytes) 1024
2 1 Block Size (Bytes) 1024
1 6 Create Count Failure 0
2 6 Create Count Failure 0
1 5 Create Count Success 1
2 5 Create Count Success 3
1 9 Delete Count Invalid 0
2 9 Delete Count Invalid 0
1 10 Delete Count Valid 0
2 10 Delete Count Valid 0
1 7 Find Count 3426
2 7 Find Count 1292
1 8 Invalidation Count 0
2 8 Invalidation Count 0
1 4 Result Size Maximum (Blocks) 86
2 4 Result Size Maximum (Blocks) 86
As you can see, I was able to perform over a thousand executions on the cluster with virtual no I/O over an 11-minute period:
select * from
2 (select instance_number, executions_total - lag(executions_total,1) over (order by instance_number, snap_id) "EXECUTIONS_TOTAL",snap_id
3 from dba_hist_sqlstat
4 where sql_id='1gy9qq7yjm054')
5 where snap_id=96;
INSTANCE_NUMBER EXECUTIONS_TOTAL SNAP_ID
--------------- ---------------- ----------
1 662 96
2 529 96
Here are the AWR reports from the test (both instances) - notice the efficiency:
CUBS1 Instance AWR Report
CUBS2 Instance AWR Report
Great Blog Post On Manually Running SQL Performance Analyzer (SPA)
Posted at Saturday, January 19, 2008
New in 11g: Active Database Duplication - Example
Posted at Monday, January 14, 2008
Another long-overdue feature, introduced in 11g, is the ability to clone/duplicate a database (for standby or otherwise) without the use of an RMAN backup. In previous versions, duplications were based upon backups of the TARGET database in question. Now, you can duplicate a database (over the network), without an RMAN backup.
Here is a complete example of duplicating an 11g database on the same machine (Linux) using the new, aforementioned active database duplication:
1. Create any needed directories
2. Create hard-coded network entries for your new, auxiliary database
Addition to SID_LIST_LISTENER in listener.ora:
Addition to tnsames.ora:
3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET)
4. Create 'dummy' parameter file for auxiliary instance
5. Add relevant entry to oratab on non-Windows environments
6. 'Nomount' the auxiliary instance in preparation for duplication
7. Duplicate the database using RMAN
If successful, you should see output similar to the following:
Here is a complete example of duplicating an 11g database on the same machine (Linux) using the new, aforementioned active database duplication:
1. Create any needed directories
mkdir /u03/app/oracle/oradata/duptest
mkdir /u03/app/oracle/admin/duptest/adump
2. Create hard-coded network entries for your new, auxiliary database
Addition to SID_LIST_LISTENER in listener.ora:
(SID_DESC =
(GLOBAL_DBNAME = duptest.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = duptest)
)
Addition to tnsames.ora:
DUPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = duptest.colestock.test)
)
)
3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET)
cd $ORACLE_HOME/dbs
orapwd file=orapwduptest password=password
4. Create 'dummy' parameter file for auxiliary instance
[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ more initduptest.ora
db_name=duptest
control_files=/u03/app/oracle/oradata/duptest/control01.ctl, /u03/app/oracle/ora
data/duptest/control02.ctl, /u03/app/oracle/oradata/duptest/control03.ctl
5. Add relevant entry to oratab on non-Windows environments
[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ grep duptest /etc/oratab
duptest:/u03/app/oracle/product/db/11.1.0.6:N
6. 'Nomount' the auxiliary instance in preparation for duplication
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ export ORACLE_SID=duptest
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ . oraenv
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 14 11:03:52 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit
7. Duplicate the database using RMAN
rman target=sys/password@nf auxiliary=sys/password@duptest
duplicate target database to duptest
2> from active database
3> db_file_name_convert '/nf/','/duptest/'
4> spfile
5> parameter_value_convert '/nf/','/duptest/'
6> set log_file_name_convert '/nf/','/duptest/'
7> set log_archive_dest_1='';
If successful, you should see output similar to the following:
Starting Duplicate Db at 14-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
contents of Memory Script:
{
backup as copy reuse
file '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilenf.ora' auxiliary format
'/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora' ;
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''";
}
executing Memory Script
Starting backup at 14-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
Finished backup at 14-JAN-08
sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPTEST'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/duptest/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/duptest/control01.ctl'', ''/u03/app/oracle/oradata/duptest/control02.ctl'', ''/u03/app/oracle/oradata/duptest/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/duptest/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
'''' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPTEST'' comment= ''duplicate'' scope=spfile
sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/duptest/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u03/app/oracle/oradata/duptest/control01.ctl'', ''/u03/app/oracle/oradata/duptest/control02.ctl'', ''/u03/app/oracle/oradata/duptest/control03.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/nf/'', ''/duptest/'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes
contents of Memory Script:
{
set newname for datafile 1 to
"/u03/app/oracle/oradata/duptest/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/duptest/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/duptest/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/duptest/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/duptest/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/duptest/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/duptest/ts401.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/app/oracle/oradata/duptest/system01.dbf" datafile
2 auxiliary format
"/u03/app/oracle/oradata/duptest/sysaux01.dbf" datafile
3 auxiliary format
"/u03/app/oracle/oradata/duptest/undotbs01.dbf" datafile
4 auxiliary format
"/u03/app/oracle/oradata/duptest/users01.dbf" datafile
5 auxiliary format
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf" datafile
6 auxiliary format
"/u03/app/oracle/oradata/duptest/ts201.dbf" datafile
7 auxiliary format
"/u03/app/oracle/oradata/duptest/ts301.dbf" datafile
8 auxiliary format
"/u03/app/oracle/oradata/duptest/ts401.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 14-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
output file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:28
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
output file name=/u03/app/oracle/oradata/duptest/users01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:37
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
output file name=/u03/app/oracle/oradata/duptest/system01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
output file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/ts201.dbf
output file name=/u03/app/oracle/oradata/duptest/ts201.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/flashback_data01.dbf
output file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/ts301.dbf
output file name=/u03/app/oracle/oradata/duptest/ts301.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u03/app/oracle/oradata/nf/ts401.dbf
output file name=/u03/app/oracle/oradata/duptest/ts401.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-JAN-08
sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u03/app/oracle/oradata/nf/arch/nf_1_277_635081437.arc" auxiliary format
"/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc" ;
catalog clone archivelog "/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 14-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=277 RECID=329 STAMP=643982787
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-JAN-08
cataloged archived log
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=1 STAMP=643982804
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982804 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts401.dbf
contents of Memory Script:
{
set until scn 3167561;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 14-JAN-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=151 device type=DISK
starting media recovery
archived log for thread 1 with sequence 277 is already on disk as file /u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc thread=1 sequence=277
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-JAN-08
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/app/oracle/oradata/duptest/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/sysaux01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/undotbs01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/users01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts201.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts301.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts401.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u03/app/oracle/oradata/duptest/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf RECID=1 STAMP=643982828
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf RECID=2 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/users01.dbf RECID=3 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf RECID=4 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts201.dbf RECID=5 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts301.dbf RECID=6 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts401.dbf RECID=7 STAMP=643982829
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982828 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts401.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 14-JAN-08
11g New Feature: Statistic Gathering and Publishing Decoupled/Statistics Maintenance Changes
Posted at Wednesday, October 31, 2007
In addition to the extended statistics functionality introduced by 11g, you can now gather statistics without having to publish them right away.
This allows one to gather the statistics and test the efficacy thereof before effecting a live production environment; in other words, existing explain plans will not be invalidated/regenerated.
Reference this 'Oracle by Example' article for a great demonstration: Gathering and Publishing Statistics Independently.
Additionally, Oracle has made some significant changes which makes using the out-of-the-box statistics collection program much more feasible. Namely, DBMS_STATS, has been changed so that you can set statistics collection preferences - think arguments passed to DBMS_STATS.GATHER_*_STATS procedures - at both the global and table levels. In turn, the automatic statistics gathering job will respect these preferences when it runs (during the applicable maintenance window).
In the past, I have always disabled the aforementioned 'AutoTask' job - for this very lack of granular control - in favor of a custom statistics gathering job. This was particularly necessary in Data Warehouse environments due to heavy use of partitioning as well as concomitant data volumes. In such environments, sampling only the partitions that have changed is critical to the conservation of system resources.
With the abovementioned scenario in mind, if you want to employ the out-of-the-box 'AutoTask' job then simply set table preferences (as appropriate) using DBMS_STATS. For instance, in order to gather large partitioned tables' statistics properly, you may wish to alter the following: INCREMENTAL, DEGREE, METHOD_OPT, STALE_PERCENT, and ESTIMATE_PERCENT.
For each, verify the current value for the table:
The current setting for the table looks like the system default.
Alter the value, just for the table in question:
Verify that the global setting and table-specific setting co-exist and are, in fact, different:
Here is another example, where we change METHOD_OPT for a table, analyze it using the DBMS_STATS package and then verify that the number of buckets we specify are created. It is important to note that the DBMS_STATS.SET_TABLE_PREFS doesn't seem to support the full syntax that METHOD_OPT allows:
Set the preference:
Gather stats - this should pick up the tables preference:
Verify that the number of histograms are created per the specificed METHOD_OPT:
11g, also introduces the ability to easily revert to previous statistics' settings. They have extended the DBMS_STATS package as well as provided new *_STATS_HISTORY views.
Building on our prior example, let's say I analyze the aforementioned table again (with different options), but then I want to revert to the previous statistics above:
Analyze the table:
Verify that the number of buckets, are in fact, different:
Query the timestamp for the prior collected stats:
Restore the old stats for the table:
Validate that the old stats have been put back:
Otherwise, Oracle has not changed a lot in terms of Statistics Maintenance. The same instances in which you have had to gather statistics manually still apply: System Statistics (those found in aux_stats$), Fixed Statistics, Volatile Tables, Tables post Direct-load, etc.
Reference the following Oracle docs:
Managing Optimizer Statistics
DBMS_STATS package reference
This allows one to gather the statistics and test the efficacy thereof before effecting a live production environment; in other words, existing explain plans will not be invalidated/regenerated.
Reference this 'Oracle by Example' article for a great demonstration: Gathering and Publishing Statistics Independently.
Additionally, Oracle has made some significant changes which makes using the out-of-the-box statistics collection program much more feasible. Namely, DBMS_STATS, has been changed so that you can set statistics collection preferences - think arguments passed to DBMS_STATS.GATHER_*_STATS procedures - at both the global and table levels. In turn, the automatic statistics gathering job will respect these preferences when it runs (during the applicable maintenance window).
In the past, I have always disabled the aforementioned 'AutoTask' job - for this very lack of granular control - in favor of a custom statistics gathering job. This was particularly necessary in Data Warehouse environments due to heavy use of partitioning as well as concomitant data volumes. In such environments, sampling only the partitions that have changed is critical to the conservation of system resources.
With the abovementioned scenario in mind, if you want to employ the out-of-the-box 'AutoTask' job then simply set table preferences (as appropriate) using DBMS_STATS. For instance, in order to gather large partitioned tables' statistics properly, you may wish to alter the following: INCREMENTAL, DEGREE, METHOD_OPT, STALE_PERCENT, and ESTIMATE_PERCENT.
For each, verify the current value for the table:
SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
FALSE The current setting for the table looks like the system default.
Alter the value, just for the table in question:
SQL> exec dbms_stats.set_table_prefs('SH','COSTS','INCREMENTAL' ,'TRUE') ;
PL/SQL procedure successfully completed.SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
TRUE Verify that the global setting and table-specific setting co-exist and are, in fact, different:
SQL> select dbms_stats.get_prefs('INCREMENTAL') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL')
--------------------------------------------------------------------------------
FALSEHere is another example, where we change METHOD_OPT for a table, analyze it using the DBMS_STATS package and then verify that the number of buckets we specify are created. It is important to note that the DBMS_STATS.SET_TABLE_PREFS doesn't seem to support the full syntax that METHOD_OPT allows:
Set the preference:
SQL> exec dbms_stats.set_table_prefs(ownname=>'SH',tabname=>'SALES',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 10');
Gather stats - this should pick up the tables preference:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES');
PL/SQL procedure successfully completed.
Verify that the number of histograms are created per the specificed METHOD_OPT:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ ------------------------------ ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11
11g, also introduces the ability to easily revert to previous statistics' settings. They have extended the DBMS_STATS package as well as provided new *_STATS_HISTORY views.
Building on our prior example, let's say I analyze the aforementioned table again (with different options), but then I want to revert to the previous statistics above:
Analyze the table:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
PL/SQL procedure successfully completed.
Verify that the number of buckets, are in fact, different:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 72
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 250
SALES CUST_ID 255
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 2
Query the timestamp for the prior collected stats:
SQL> select count(*), stats_update_time
2 from user_tab_stats_history
3 where table_name='SALES'
4 group by stats_update_time;
COUNT(*) STATS_UPDATE_TIME
---------- ----------------------------------------
29 25-JAN-08 11.18.09.693351 AM -07:00
29 25-JAN-08 11.18.57.432585 AM -07:00
Restore the old stats for the table:
SQL> exec dbms_stats.restore_table_stats(ownname=>'SH',tabname=>'SALES',AS_OF_TIMESTAMP=>'25-JAN-08 11.18.57.432585 AM -07:00');
PL/SQL procedure successfully completed.
Validate that the old stats have been put back:
SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11
Otherwise, Oracle has not changed a lot in terms of Statistics Maintenance. The same instances in which you have had to gather statistics manually still apply: System Statistics (those found in aux_stats$), Fixed Statistics, Volatile Tables, Tables post Direct-load, etc.
Reference the following Oracle docs:
Managing Optimizer Statistics
DBMS_STATS package reference
Labels: 11g, Statistics, Tuning
11g New Feature: Extended Optimizer Statistics
Posted at Tuesday, October 30, 2007
In 11g you can now correlate the relationship between 2 skewed columns. Here is a great 'Oracle by Example' article that exhibits how you can use extended statistics to aid the optimizer's explain plans: Using Extended Statistics to Optimize Multi-Column Relationships and Function-Based Statistics
Labels: 11g, Statistics, Tuning
11g New Feature: SQL Result Cache
Posted at Friday, October 26, 2007
In previous versions it was possible to manipulate the instance's memory structures to improve the performance of user queries. For instance, a table or index could be "pinned" into memory via usage of the db_keep_cache. Alternatively, tables could be "cached," prohibiting tables - accessed via a full scan - from being placed on the tail-end of the LRU.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
In my case should result in
Similarly,
Should result in
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
Results in
The memory report also reflects the result cache is now being used.
Of course it is subsequent submissions of the same query which yield the performance gain.
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.
Oracle also, introduced an OCI-centric client-side SQL Result Caching feature with 11g, however this post will cover just the server-side functionality since this has much more promise in terms of widespread use.
The following example shows how to set-up and test this new feature.
Change instance parameters as appropriate
SELECT name "Parameter"
, round(value/1024/1024) "MB"
FROM v$spparameter
WHERE name IN ('shared_pool_size',
'result_cache_max_size',
'memory_target',
'memory_max_target',
'sga_target',
'pga_aggregate_target')
ORDER BY 2 DESC;
In my example, I am using Oracle's new Automatic Memory functionality. As you can see, I am letting Oracle manage the sizing of my instance, both pga_aggregate_target and sga_target. The only exception is that I define a lower-limit for the shared_pool_size. I do this so that I can explicit set the maximum size the result cache can grow to as specified by result_cache_max_size:
Parameter MB
---------------------------------------- ----------
memory_max_target 256
memory_target 240
shared_pool_size 64
result_cache_max_size 16
pga_aggregate_target 0
sga_target 0
After cycling the instance, confirm that the ceiling set for the result cache has been properly set:
SELECT
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=4)/
(SELECT value
FROM V$RESULT_CACHE_STATISTICS
WHERE ID=1) "Result Set Max Size in MB" FROM DUAL;
In my case should result in
16
Similarly,
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;
Should result in
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 5132 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
I create a couple of sample objects and load some sample data to demonstrate how using this feature can improve the performance of repetitive queries
DROP TABLE SCOTT.MY_EMPLOYEE;
CREATE TABLE SCOTT.MY_EMPLOYEE
(
EMPNO NUMBER(32) NOT NULL,
ENAME VARCHAR2(100),
SAL NUMBER(16,2),
GRADE NUMBER(1),
UPDATE_TS TIMESTAMP(6) DEFAULT systimestamp)
TABLESPACE USERS;
CREATE UNIQUE INDEX SCOTT.PK_MY_EMPLOYEE ON SCOTT.MY_EMPLOYEE
(EMPNO)
TABLESPACE USERS;
ALTER TABLE SCOTT.MY_EMPLOYEE ADD (CONSTRAINT PK_MY_EMPLOYEE PRIMARY KEY (EMPNO) USING INDEX);
set serverout on
declare
begin
execute immediate 'truncate table scott.my_employee';
for i in 1..10000000 loop
insert into scott.my_employee (empno, ename, sal, grade) values (i,'James'||i,i,1);
end loop;
commit;
execute immediate 'analyze table scott.my_employee estimate statistics sample 1 percent';
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
At this point, I have a table with some sample data and a properly configured SQL Result Cache with which to demonstrate this functionality.
Sample Performance without the use of SQL Result Caching
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.56
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
2457 recursive calls
0 db block gets
20424 consistent gets
19930 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
84 sorts (memory)
0 sorts (disk)
1 rows processed
Since our example assumes a query that is run repetitively, the second run of the aforementioned query should resemble roughly what the performance would be, because the plan will have already been established, etc.; this second run will be the baseline
SQL> select count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 2242393760
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20414 (1)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_MY_EMPLOYEE | 10M| 20414 (1)| 00:04:05 |
---------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fd812f285bb05d3f" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19877 consistent gets
19876 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
When the database's result_cache_mode initialization parameter is set to 'MANUAL' the /*+ result_cache */ hint must be supplied in order to cache the query's results and/or for the optimizer to even consider using previously cached results towards satisfying a user request.
Flush the Database Buffer Cache, Shared Pool, and Result Cache. Afterwards, pin the results of the query in question
SQL> execute dbms_result_cache.flush;
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
1000 recursive calls
0 db block gets
20322 consistent gets
20113 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
42 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the explain plan is a little different. It reflects the fact that Oracle automatically caches the results of the query. You can now query the system to validate this very fact.
SELECT type,
cache_id,
object_no,
space_overhead
FROM V$RESULT_CACHE_OBJECTS;
Results in
TYPE CACHE_ID OBJECT_NO SPACE_OVERHEAD
---------- ---------------------------------------- ---------- --------------
Dependency SCOTT.MY_EMPLOYEE 71439 0
Result 9kuwb9z28jrdqdcpuxk1j0ry8v 0 238
The memory report also reflects the result cache is now being used.
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 16M bytes (16K blocks)
Maximum Result Size = 16M bytes (16K blocks)
[Memory]
Total Memory = 103528 bytes [0.095% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.090% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
Of course it is subsequent submissions of the same query which yield the performance gain.
SQL> select /*+ result_cache */ count(*) from scott.my_employee;
COUNT(*)
----------
10000000
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3630384065
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5581 (2)| 00:01:07 |
| 1 | RESULT CACHE | 9kuwb9z28jrdqdcpuxk1j0ry8v | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FAST FULL SCAN| PK_MY_EMPLOYEE | 10M| 5581 (2)| 00:01:07 |
---------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.MY_EMPLOYEE); attributes=(single-row); name="select /*+ result_cache */ count(*) from scott.my_employee"
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_910af5d7eff1e4f3" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, there are no physical reads and no consistent gets when the same query is executed subsequently. Notice that the explain plan shows that the previously establish cache id is used instead of performing the original plan's operations.
Vital statistics concerning the SQL Result Cache can be found here
SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS;
ID NAME VALUE
---------- -------------------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 16384
3 Block Count Current 32
4 Result Size Maximum (Blocks) 16384
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 3
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
It is important to watch the 'Invalidation Count' because this represents how many times the result sets were invalidated due to updates to the underlying table data. Ideally, your 'Find Count' would be high and your 'Invalidation Count' would be low in order to reap maximum performance gains.
The following functionality can be applied to PL/SQL programs as well. See this great Oracle by Example article Improving Application Performance with Result Cache. Also, refer to this article on the topic from a recent Oracle Magazine: On the PL/SQL Function Result Cache.
Testing Real-time Query
Posted at Monday, October 22, 2007
In my last post I documented the steps to create an 11g Physical Standby Database configuration using RMAN and the Data Guard Broker. Now, I want to test the 'Real-time Query' functionality, introduced with 11g.
With the primary already started, I mount the standby and put it into managed recovery until it is caught up with the primary; after which, I cancel the recovery.
After that I open the standby and once again place it into managed recovery.
I test by creating a table at the primary and then querying the standby to see whether the change is propagated.
I see the table and row that I inserted at the primary on the standby so the test was successful!
With the primary already started, I mount the standby and put it into managed recovery until it is caught up with the primary; after which, I cancel the recovery.
$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:22:34 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 196681728 bytes
Fixed Size 1298836 bytes
Variable Size 104861292 bytes
Database Buffers 83886080 bytes
Redo Buffers 6635520 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> alter database recover managed standby database cancel
2 ;
Database altered.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2 FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 59 1 246
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 60 0 0
RFS IDLE 1 60 827 6
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
9 rows selected.
SQL> alter database recover managed standby database cancel;
Database altered.
After that I open the standby and once again place it into managed recovery.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
I test by creating a table at the primary and then querying the standby to see whether the change is propagated.
$ export ORACLE_SID=nf
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:38:02 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table scott.real_time_query_test (rtqt_date date) tablespace users;
Table created.
SQL> insert into scott.real_time_query_test values (sysdate);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:39:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from scott.real_time_query_test;
RTQT_DATE
---------
22-OCT-07
I see the table and row that I inserted at the primary on the standby so the test was successful!
Labels: 11g, Data Guard
Creating a Physical Standby using RMAN/Data Guard Broker on 11g
Posted at Saturday, October 20, 2007
In this example, I am creating a Physical Standby database - on the same host - using RMAN to create the standby and the Data Guard Broker to manage the Standby's configuration.
The main impetus for doing this is to evaluate some enhancements that
are part of 11g. In particular, I want to test out the new Data Guard functionality that allows for simultaneous application of changes to the Physical Standby and processing of clients' read-only queries. As you are probably aware, these activities used to be mutually exclusive. In the past, issuing queries to the Standby - ostensibly to offload production load - required stopping the application of redo and opening the database in a read-only mode.
I start by creating the Standby using RMAN. In this case, I will be creating the Standby from an RMAN backup, instead of from the live database.
Preparing the primary database:
Ensure that the database in question is in force logging mode. If not, enable force logging as in below.
Preparing the auxiliary instance:
Add an entry to /etc/oratab
Create a passwordfile under $ORACLE_HOME/dbs. Remember that the value of the password needs to be the same as that of the primary database's password file. I have found that the ignorecase parameter is essential in 11g when putting one of these configurations together.
Add a static listener entry to $TNS_ADMIN/listener.ora (and reload) as well as a net service entry to $TNS_ADMIN/tnsnames.ora
listener entry
tnsnames entry
Create a barebones init.ora file for the auxiliary instance
initstdby.ora
Issue the commands to create the directories needed for the new database:
Startup the instance in no mount. I perform the optional step of opening up the wallet, just to ensure that the wallet location is set appropriately in sqlnet.ora
Create the necessary RMAN duplicate script
Create a wrapper script that calls this script and creates all necessary instance connections
Run the aforementioned script
Afterwards, you will find that the database has been created and that all of the files are renamed and in the locations intended
An examination of the v$database view should yield 'PHYSICAL STANDBY'
At this point, the basic Duplication routine is complete, now a lot of additional configurations need to be performed in order to have a complete Standby Database configuration
Add explicit entries to the appropriate listener (and reload) for the services that will be part of the Data Guard configuration. These are used because the Data Guard broker needs to be able to stop and start the instances involved regardless of their state.
I appended the static service entries to my listener's sid list:
Now, I alter the appropriate init.ora parameters for both the primary and standby instances
alter_primary.sql
alter_standby.sql
Run both scripts after settting the appropriate environment variables
At this point, changes should be propagating to the standby. Verify and troubleshoot as necessary, using the method you prefer.
For example, here is how I verify that my standby is up to date with the primary
As you can see, the standby database is waiting for log sequence 38 from the primary, which is the primary's current log.
Managing switchover/failover operations is simplified by using the DGMGRL command-line interface. Now that I have everything up and working, I will configure this tool to manage my Data Guard configuration.
The configuration then needs to be enabled
As you can see, the 'Maximum Performance' protection mode now absolutely requires Standby redo logs.
Create the standby redo logs. Remember one more group (in quantity) then the online redo logs. Notice that I only have one log member per group - this is a test instance (I would never do this in a production environment)
I also create these on the Primary via
After adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.
Remedy via
Now we have a fully functioning configuration for our 11g new features testing
The main impetus for doing this is to evaluate some enhancements that
are part of 11g. In particular, I want to test out the new Data Guard functionality that allows for simultaneous application of changes to the Physical Standby and processing of clients' read-only queries. As you are probably aware, these activities used to be mutually exclusive. In the past, issuing queries to the Standby - ostensibly to offload production load - required stopping the application of redo and opening the database in a read-only mode.
I start by creating the Standby using RMAN. In this case, I will be creating the Standby from an RMAN backup, instead of from the live database.
Preparing the primary database:
Ensure that the database in question is in force logging mode. If not, enable force logging as in below.
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
Preparing the auxiliary instance:
Add an entry to /etc/oratab
$ grep stdby /etc/oratab
stdby:/u03/app/oracle/product/db/11.1.0.6:N
Create a passwordfile under $ORACLE_HOME/dbs. Remember that the value of the password needs to be the same as that of the primary database's password file. I have found that the ignorecase parameter is essential in 11g when putting one of these configurations together.
$ orapwd file=orapwstdby password=password ignorecase=y entries=25
Add a static listener entry to $TNS_ADMIN/listener.ora (and reload) as well as a net service entry to $TNS_ADMIN/tnsnames.ora
listener entry
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
)
tnsnames entry
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.colestock.test)
)
)
Create a barebones init.ora file for the auxiliary instance
initstdby.ora
db_name=stdby
control_files=(/u03/app/oracle/oradata/stdby/control01.ctl,/u03/app/oracle/orad
ata/stdby/control02.ctl)
log_file_name_convert=('/nf/','/stdby/')
Issue the commands to create the directories needed for the new database:
$ mkdir /u03/app/oracle/oradata/stdby
$ mkdir -p /u03/app/oracle/oradata/stdby/arch
$ mkdir -p /u03/app/oracle/admin/stdby/adump
$ mkdir -p /u03/app/oracle/admin/stdby/bdump
$ mkdir -p /u03/app/oracle/admin/stdby/cdump
$ mkdir -p /u03/app/oracle/admin/stdby/dpdump
$ mkdir -p /u03/app/oracle/admin/stdby/pfile
$ mkdir -p /u03/app/oracle/admin/stdby/scripts
$ mkdir -p /u03/app/oracle/admin/stdby/udump
Startup the instance in no mount. I perform the optional step of opening up the wallet, just to ensure that the wallet location is set appropriately in sqlnet.ora
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 14:13:10 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> alter system set wallet open identified by "password";
System altered.
Create the necessary RMAN duplicate script
$ more create_standby.rman
DUPLICATE TARGET DATABASE
FOR STANDBY
DORECOVER
DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
SPFILE
PARAMETER_VALUE_CONVERT '/nf','/stdby'
SET SERVICE_NAMES 'stdby.colestock.test'
SET "db_unique_name"="stdby"
SET log_file_name_convert '/nf/','/stdby/';
Create a wrapper script that calls this script and creates all necessary instance connections
$ more create_standby.bsh
#!/bin/bash
export ORACLE_SID=stdby
. oraenv
rman target=sys/password@nf catalog=rman/password@nf auxiliary=/ @create_standby.rman;
Run the aforementioned script
./create_standby.bsh
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Oct 20 14:32:20 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NF (DBID=2018903642)
connected to recovery catalog database
connected to auxiliary database: STDBY (not mounted)
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> DORECOVER
4> DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
5> SPFILE
6> PARAMETER_VALUE_CONVERT '/nf','/stdby'
7> SET SERVICE_NAMES 'stdby.colestock.test'
8> SET "db_unique_name"="stdby"
9> SET log_file_name_convert '/nf/','/stdby/';
10>
Starting Duplicate Db at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
contents of Memory Script:
{
set until scn 1335853;
restore clone spfile to '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora';
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''";
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-07
sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/stdby/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment=
'''' scope=spfile";
sql clone "alter system set SERVICE_NAMES =
''stdby.colestock.test'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stdby'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/stdby/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/stdby/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment= '''' scope=spfile
sql statement: alter system set SERVICE_NAMES = ''stdby.colestock.test'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stdby'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/nf/'', ''/stdby/'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 196681728 bytes
Fixed Size 1298836 bytes
Variable Size 75501164 bytes
Database Buffers 113246208 bytes
Redo Buffers 6635520 bytes
contents of Memory Script:
{
set until scn 1335853;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u03/app/oracle/oradata/stdby/control01.ctl
output file name=/u03/app/oracle/oradata/stdby/control02.ctl
output file name=/u03/app/oracle/oradata/stdby/control03.ctl
Finished restore at 20-OCT-07
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 1335853;
set newname for tempfile 1 to
"/u03/app/oracle/oradata/stdby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u03/app/oracle/oradata/stdby/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/stdby/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/stdby/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/stdby/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/stdby/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/stdby/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/stdby/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/stdby/ts401.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to /u03/app/oracle/oradata/stdby/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1
the file name for datafile 7 is missing in the control file
the file name for datafile 8 is missing in the control file
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/stdby/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/stdby/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/stdby/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/stdby/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/stdby/flashback_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/stdby/ts201.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_06iu4rlp_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_06iu4rlp_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 20-OCT-07
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/ts201.dbf
contents of Memory Script:
{
set until scn 1335853;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-OCT-07
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 20 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf
archived log for thread 1 with sequence 21 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf
archived log for thread 1 with sequence 22 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf
archived log for thread 1 with sequence 23 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf
archived log for thread 1 with sequence 24 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf
archived log for thread 1 with sequence 25 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf
archived log for thread 1 with sequence 26 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf thread=1 sequence=20
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf thread=1 sequence=21
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=7 name=/u03/app/oracle/oradata/stdby/ts301.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=8 name=/u03/app/oracle/oradata/stdby/ts401.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf thread=1 sequence=23
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf thread=1 sequence=24
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf thread=1 sequence=25
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:00:30
Finished recover at 20-OCT-07
Finished Duplicate Db at 20-OCT-07
Recovery Manager complete.
Afterwards, you will find that the database has been created and that all of the files are renamed and in the locations intended
$ ls -lart /u03/app/oracle/oradata/stdby
total 2515888
drwxr-xr-x 5 oracle dba 4096 Oct 20 12:55 ..
drwxr-xr-x 2 oracle dba 4096 Oct 20 13:18 arch
drwxr-xr-x 3 oracle dba 4096 Oct 20 14:35 .
-rw-r----- 1 oracle dba 11542528 Oct 20 14:35 users01.dbf
-rw-r----- 1 oracle dba 110108672 Oct 20 14:35 undotbs01.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts401.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts301.dbf
-rw-r----- 1 oracle dba 524296192 Oct 20 14:35 ts201.dbf
-rw-r----- 1 oracle dba 744497152 Oct 20 14:35 system01.dbf
-rw-r----- 1 oracle dba 682106880 Oct 20 14:35 sysaux01.dbf
-rw-r----- 1 oracle dba 262152192 Oct 20 14:35 flashback_data01.dbf
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control03.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control02.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control01.ctl
An examination of the v$database view should yield 'PHYSICAL STANDBY'
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
At this point, the basic Duplication routine is complete, now a lot of additional configurations need to be performed in order to have a complete Standby Database configuration
Add explicit entries to the appropriate listener (and reload) for the services that will be part of the Data Guard configuration. These are used because the Data Guard broker needs to be able to stop and start the instances involved regardless of their state.
I appended the static service entries to my listener's sid list:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = nf_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = nf)
)
)
Now, I alter the appropriate init.ora parameters for both the primary and standby instances
alter_primary.sql
$ more alter_primary.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg2.conf' scope=spfile;
alter system set log_archive_format='nf_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=stdby VALID_FOR=(ONLINE_LOGF
ILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=stdby scope=spfile;
alter system set fal_client=nf scope=spfile;
alter system set db_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set log_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup;
alter_standby.sql
$ more alter_standby.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg2.conf' scope=spfile;
alter system set log_archive_format='stdby_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=nf VALID_FOR=(ONLINE_LOGFILE
S,PRIMARY_ROLE) DB_UNIQUE_NAME=nf' scope=both;
alter system set log_archive_dest_state_2=defer scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=nf scope=spfile;
alter system set fal_client=stdby scope=spfile;
alter system set db_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set log_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;
Run both scripts after settting the appropriate environment variables
$ export ORACLE_SID=nf
$ . oraenv
$ sqlplus "/ as sysdba" @alter_primary.sql
$ export ORACLE_SID=stdby
$ . oraenv
$ sqlplus "/ as sysdba" @alter_standby.sql
At this point, changes should be propagating to the standby. Verify and troubleshoot as necessary, using the method you prefer.
For example, here is how I verify that my standby is up to date with the primary
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 38 0 0
RFS IDLE 1 38 5406 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
$ export ORACLE_SID=nf
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 19:04:00 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/app/oracle/oradata/nf/arch
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38
As you can see, the standby database is waiting for log sequence 38 from the primary, which is the primary's current log.
Managing switchover/failover operations is simplified by using the DGMGRL command-line interface. Now that I have everything up and working, I will configure this tool to manage my Data Guard configuration.
$ dgmgrl
DGMGRL> connect sys/password@nf
Connected.
DGMGRL> CREATE CONFIGURATION 'MY_DG' AS PRIMARY DATABASE IS 'nf' CONNECT IDENTIFIER IS 'NF';
Configuration "MY_DG" created with primary database "nf"
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show configuration
Configuration
Name: MY_DG
Enabled: NO
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database
Fast-Start Failover: DISABLED
Current status for "MY_DG":
DISABLED
DGMGRL> show database verbose 'nf'
Database
Name: nf
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s):
nf
Properties:
DGConnectIdentifier = 'NF'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'nf'
StandbyArchiveLocation = '/u03/app/oracle/oradata/nf/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'nf_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "nf":
DISABLED
DGMGRL> show database verbose 'stdby';
Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby
Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "stdby":
DISABLED
The configuration then needs to be enabled
DGMGRL> enable configuration;
Enabled.
DGMGRL> show database 'nf'
Database
Name: nf
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
nf
Current status for "nf":
Warning: ORA-16789: standby redo logs not configured
As you can see, the 'Maximum Performance' protection mode now absolutely requires Standby redo logs.
Create the standby redo logs. Remember one more group (in quantity) then the online redo logs. Notice that I only have one log member per group - this is a test instance (I would never do this in a production environment)
SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database add standby logfile group 5 '/u03/app/oracle/oradata/stdby/redo05.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 6 '/u03/app/oracle/oradata/stdby/redo06.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 7 '/u03/app/oracle/oradata/stdby/redo07.log' size 50M;
Database altered.
SQL> alter database add standby logfile group 8 '/u03/app/oracle/oradata/stdby/redo08.log' size 50M;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
I also create these on the Primary via
alter database add standby logfile group 5 '/u03/app/oracle/oradata/nf/redo05.log' size 50M;
alter database add standby logfile group 6 '/u03/app/oracle/oradata/nf/redo06.log' size 50M;
alter database add standby logfile group 7 '/u03/app/oracle/oradata/nf/redo07.log' size 50M;
alter database add standby logfile group 8 '/u03/app/oracle/oradata/nf/redo08.log' size 50M;
After adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Remedy via
DGMGRL> remove database 'stdby'
Removed database "stdby" from the configuration
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show database verbose 'stdby';
Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby
Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "stdby":
DISABLED
DGMGRL> enable database 'stdby'
Enabled.
DGMGRL> show configuration
Configuration
Name: MY_DG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database
Fast-Start Failover: DISABLED
Current status for "MY_DG":
SUCCESS
DGMGRL> show database 'nf' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database 'stdby' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
Now we have a fully functioning configuration for our 11g new features testing
Labels: 11g, Data Guard, RMAN
_507.png)
Subscribe
