Archive for the ‘Oracle 11g’ Category
Posted by Gavin Soorma on November 6, 2009
Businesses are increasingly realizing the immense value that historical data can provide to help them understand market trends and customer behaviour as well. Further, many regulatory and compliance policies
like SOX and BASEL-2 mandate retention of historical data.
Until now, this unfortunately involved application rewrites,administration overheads or costly third-party software solutions. Total Recall or the new Oracle 11g Flashback Data Archive feature has greatly enhanced the flashback technology which was introduced in Oracle 9i to make it far more than just a tool to recover from logical corruptions and human error. It is now an out-of-the box Information Life Cycle Management tool and provides ease of management of historical information and long term secure data tracking with minimal performance overheads.
11g Flashback Data Archive provides the automated ability to track and store all transactional changes to a table over its lifetime without having to build this intelligence into your application. Prior to 11g, Flashback technology to a large part was based on the availability of undo data or flashback logs and both the undo data as well as flashback logs are subject to recycling when out of space pressure exists. The UNDO tablespace in Oracle was primarily meant for transaction consistency and not data archival.
Flashback Data Archive or Total Recall Features
Easy to configure – apply to all tables, one or a group of tables with simple “enable archive” command
Secure – complete protection from accidental or malicious updates and deletes
Efficiency of performance and storage – capture process is asynchronous background process and data in history tables is partitioned as well as compressed automatically
Easy to access historical data using standard SQL “AS OF” constructs
Automated data management – historical data is automatically purged without any human intervention
Retention policies customised to suit business needs
Flashback Data Archive uses a background process fbda to capture data asynchronously. It runs every 5 minutes which is the default as well as at more frequent intervals depending on system activity. The Primary source for historical data is the Undo data, but this undo data is not reused until the historical data is written first.
Getting Started
System Privilege – FLASHBACK ARCHIVE ADMINISTER to create and administer a flashback data archive
Connect as SYSDBA
Object Privilege – FLASHBACK ARCHIVE privilege on the specific flashback data archive to enable historical data tracking
Quota on the tablespace where the flashback data archive has been created
Creating a Flashback Data Archive (lets call it FBDA)
Create a new tablespace or use existing tablespace – tablespace needs to be ASSM
Specify the FBDA as the default (optional)
Assign a quota for the FBDA (optional)
Assign a retention period for the FBDA
Retention period integer denoting days,months or years
Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.
SQL> CREATE TABLESPACE his_data_1
2 DATAFILE '+data' SIZE 500M;
Tablespace created.
SQL> CREATE FLASHBACK ARCHIVE DEFAULT fba1
2 TABLESPACE his_data_1
3 RETENTION 2 DAY;
Flashback archive created.
SQL> SELECT FLASHBACK_ARCHIVE_NAME, to_char(CREATE_TIME,'dd-mon-yyyy') Created,
2 RETENTION_IN_DAYS,STATUS FROM DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NA CREATED RETENTION_IN_DAYS STATUS
-------------------- ----------- ----------------- -------
FBA1 02-nov-2009 2 DEFAULT
SQL> GRANT FLASHBACK ARCHIVE ON fba1 TO scott;
Grant succeeded.
SQL> ALTER TABLE mysales FLASHBACK ARCHIVE fba1;
Table altered.
SQL> CREATE TABLE
2 EMPSAL_HIS
3 (EMPNO number,
4 ENAME VARCHAR2(10),
5 SAL NUMBER,
6 FLASHBACK ARCHIVE;
Table created.
SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
2 from USER_FLASHBACK_ARCHIVE_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
----------- -------------------- -------------------- ------------
MYSALES FBA1 SYS_FBA_HIST_77429 ENABLED
EMPSAL_HIS FBA1 SYS_FBA_HIST_77419 ENABLED
Note: for every table where Flashback Archive is enabled, corresponding internal history tables are created in the flashback archive tablespace as shown below. These tables are automatically partitioned and compressed as well
SQL> select object_id from dba_objects where object_name=‘DEPT';
OBJECT_ID
----------
73201
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
SYS_FBA_DDL_COLMAP_73201 HIS_DATA_1
SYS_FBA_TCRV_73201 HIS_DATA_1
SYS_FBA_HIST_73201
SQL> desc SYS_FBA_HIST_73201
Name Null? Type
----------------------------------------- -------- ----------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(10)
DEPTNO NUMBER(2)
JOB VARCHAR2(20)
SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_77429 HIGH_PART ENABLED
SYS_FBA_HIST_77419 HIGH_PART ENABLED
Posted in Administration, Oracle 11g | Tagged: 11G, fbda, flashback archive, total recall | 1 Comment »
Posted by Gavin Soorma on October 23, 2009
Let us have a look at the installation screenshots of 11g Release 2 on a Red Hat Linux platform. Currently the 11g R2 software has only been released for the Linux platform with release dates for other platforms like Solaris and AIX set for sometime end October to mid November.
There are quite a few new installation options available as can be seen from the screenshots below. What we are installing below is “Grid Infrastructure for a stand alone server”.
In 11g R2, ASM is now part of what is called the Grid Infrastructure (nothing to do with Grid Control). It is no longer an option available in dbca and there is a command line option asmca which is launched from the Grid Infrastructure home. The ASM instance will be running from the Grid Home and not the database Oracle Home.
Further, the Grid Infrastructure can be installed for a stand alone server or for a cluster deployment. The ASM and Clusterware are instaled in the same home directory and it should be noted that now in 11g R2, the clusterware files like the OCR and the Voting Disk can be located on ASM storage and raw devices is no longer supported.
Also, now there is separation between SYSASM and SYSDBA and if you want to connect to the ASM instance you need to do it as SYSASM. It is recommended to also create a separate group in addition to the DBA group specific for ASM administration and make SYSASM grantees members of this group. In one of the screenshots below we will see an alert being displayed when we have selected the dba group for the ASM installation.
Posted in Administration, Oracle 11g, Oracle 11g release 2 | Tagged: 11gR2, grid insfrastructure, Oracle 11g release 2 | Leave a Comment »
Posted by Gavin Soorma on October 16, 2009
The following are some of the new ASM related features introduced in 11g R2:
In Oracle 11g Release 2, ASM and the Oracle Clusterware are now installed in a common home called the Grid Infrastructure Home.
The clusterware files like the Voting Disk and the Cluster Registry can no longer be installed on raw devices. They can now be installed on ASM Disk Groups
Need to connect as SYSASM to perform any administrative operations on the ASM instance or we will get an ORA-01031 error as shown below
[oracle@redhat346 stage]$ sqlplus sys/xxx as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 16 09:10:55 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option
SQL> shutdown immediate;
ORA-01031: insufficient privileges
ACFS or Automatic Storage Management Cluster File System is an extension of the ASM technology to support storage of files like Oracle binaries and other application files related to text,video, audio etc
The example below will show how we can configure and mount a ACFS on Red HatLinux with 11g R2.
Note: ASM operations like creating and adding disk groups etc which were earlier performed via DBCA will now be performed via the GUI ASM Configuration Assistant which needs to be launched via the ‘asmca‘ command from the Grid Infrastructure Home.
We first create a disk group called ASMCFS and then create a volume called ASMCFS_VOL1 of 25 GB using the disk group that we just created.
We will then use a mount point /u03 to mount this file system. A configuration script is created which includes the commands to be run as root to mount this file system. After the script is run we will see the ACFS file system mounted on /u03.
/bin/mount -t acfs /dev/asm/asmcfs_vol1-44 /u03
chown oracle:dba /u03
chmod 775 /u03
[oracle@redhat346 stage]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
.....
......
/dev/asm/asmcfs_vol1-44
26214400 89112 26125288 1% /u03
Posted in Administration, Oracle 11g, Oracle 11g release 2 | Tagged: 11gR2, ACFS, ASM, ASm Cluster File System | Leave a Comment »
Posted by Gavin Soorma on September 30, 2009
asmcmd is a command line tool that we can use to manage Oracle ASM instances, ASM disk groups, files and dierctories, templates very much as in the same way we would do while working with say a UNIX file system.
ASMCMD can be launched in interactive or non-interactive modes and we need to first ensure that our environment points to the “Grid Infrastructure Home” as in 11g R2, ASM is not part of the standard database software installation. Also chec that the ORACLE_SID points to the ASM instance which should be running.
Let us have a quick look at some of the useful command options which are now available with 11g Release 2.
lsct: Lists information about current Oracle ASM clients from the V$ASM_CLIENT view.
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM DATA
testdb CONNECTED 11.2.0.1.0 11.2.0.0.0 testdb DATA
cp: We can copy files from ASM to OS as wel as from OS to ASM disk groups
ASMCMD> cp EXAMPLE.265.697907183 /tmp/example01.bak
copying +DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183 -> /tmp/example01.bak
ASMCMD> cp /tmp/example01.bak +DATA/TESTDB/DATAFILE/example01.bak
copying /tmp/example01.bak -> +DATA/TESTDB/DATAFILE/example01.bak
ASMCMD> ls
EXAMPLE.265.697907183
SYSAUX.257.697907095
SYSTEM.256.697907095
UNDOTBS1.258.697907095
USERS.259.697907095
example01.bak
du: Total space in MB used by files – particular directory can also be specified
ASMCMD> pwd
+DATA/TESTDB/DATAFILE
ASMCMD> du
Used_MB Mirror_used_MB
1574 1574
ASMCMD> du +DATA/TESTDB/ONLINELOG
Used_MB Mirror_used_MB
153 153
find: we can use the wildcard or can specify a particular file type by using the ‘–type’ clause
ASMCMD> find --type ONLINELOG +DATA *
+DATA/TESTDB/ONLINELOG/group_1.261.697907177
+DATA/TESTDB/ONLINELOG/group_2.262.697907179
+DATA/TESTDB/ONLINELOG/group_3.263.697907179
ASMCMD> find +DATA example*
+DATA/ASM/DATAFILE/example01.bak.267.698929915
+DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183
+DATA/TESTDB/DATAFILE/example01.bak
ls: list the contents of an ASM Disk Group directory as well as attributes of files located in the directory
ASMCMD> ls -s
Block_Size Blocks Bytes Space Name
8192 12801 104865792 106954752 EXAMPLE.265.697907183
8192 89601 734011392 736100352 SYSAUX.257.697907095
8192 89601 734011392 736100352 SYSTEM.256.697907095
8192 7681 62922752 65011712 UNDOTBS1.258.697907095
8192 641 5251072 6291456 USERS.259.697907095
example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE SEP 30 11:00:00 Y EXAMPLE.265.697907183
DATAFILE UNPROT COARSE SEP 28 23:00:00 Y SYSAUX.257.697907095
DATAFILE UNPROT COARSE SEP 29 22:00:00 Y SYSTEM.256.697907095
DATAFILE UNPROT COARSE SEP 18 22:00:00 Y UNDOTBS1.258.697907095
DATAFILE UNPROT COARSE SEP 18 22:00:00 Y USERS.259.697907095
N example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915
iostat: Uses the V$ASM_DISK_IOSTAT view to display I/O statistics of disks in mounted ASM disk groups
ASMCMD> iostat -G DATA
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 25448671744 19818926592
ASMCMD> iostat -t
Group_Name Dsk_Name Reads Writes Read_Time Write_Time
DATA DATA_0000 25450195456 19819686912 6491.434444 8042.604156
lsdg: Uses V$ASM_DISKGROUP_STAT view to list information about a particular disk group
ASMCMD> lsdg DATA
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Name
MOUNTED EXTERN N 512 4096 1048576 51200 49375 0 49375 DATA/
ASMCMD> lsdsk -t -G DATA
Create_Date Mount_Date Repair_Timer Path
18-SEP-09 18-SEP-09 0 /dev/raw/raw1
lsattr: List attributes of a disk group
ASMCMD> lsattr -l -G DATA
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 10.1.0.0.0
disk_repair_time 3.6h
sector_size 512
Use the setattr command to change an attribute
ASMCMD> setattr -G data compatible.rdbms 11.2.0.0.0
ASMCMD> lsattr -l -G DATA
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 11.2.0.0.0
disk_repair_time 3.6h
sector_size 512
Posted in Administration, Oracle 11g | Tagged: asmcmd | Leave a Comment »
Posted by Gavin Soorma on September 22, 2009
We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command.
Also note that in 11g we can run RMAN scripts using the ‘@’ like a SQL script
testbkp.rcv
connect target sys/oracle
run{
backup incremental level &1
database
tag &2;
}
exit;
testbkp.sh
#! /bin/ksh
export level=$1
export tag=$2_`date +%d%b%y`
rman @testbkp.rcv using $level $tag
Note the actual RMAN command that is run …….. it adds a date along with the tag name (parameter 2) and runs a level 2 backup as the first parameter was ‘2′
apex:/u01/oracle> ./testbkp.sh 2 dly_inc_bkp
Recovery Manager: Release 11.1.0.6.0 – Production on Tue Sep 22 11:28:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target *
2> run{
3> backup incremental level 2
4> database
5> tag dly_inc_bkp_22Sep09;
6> }
7> exit;
connected to target database: APEX (DBID=1312143933)
Starting backup at 22-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=349 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
channel ORA_SBT_TAPE_1: starting incremental level 2 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/apex/undotbs01.dbf
input datafile file number=00002 name=/u02/oradata/apex/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/apex/system01.dbf
input datafile file number=00005 name=/u02/oradata/apex/monitor_data01.dbf
input datafile file number=00007 name=/u02/oradata/apex/encrypt_data01.dbf
input datafile file number=00008 name=/u02/oradata/apex/disc10_data01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 22-SEP-09
channel ORA_SBT_TAPE_1: finished piece 1 at 22-SEP-09
piece handle=a5kpsim9_1_1 tag=DLY_INC_BKP_22SEP09 comment=API Version 2.0,MMS Version 5.5.1.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-SEP-09
Starting Control File and SPFILE Autobackup at 22-SEP-09
piece handle=c-1312143933-20090922-05 comment=API Version 2.0,MMS Version 5.5.1.0
Finished Control File and SPFILE Autobackup at 22-SEP-09
Recovery Manager complete.
Posted in Backup and Recovery, Oracle 11g | Tagged: 11g RMAN, Substitution Variables, USING | Leave a Comment »
Posted by Gavin Soorma on September 21, 2009
11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any outage as the application was accessing the procedure or package which needed to be modified.
An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.
Let us see an example of this where we create a procedure, make some changes to the procedure in a new edition and then make those changes visible to other database users.
SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> CREATE OR REPLACE PROCEDURE hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');
END hello;
/
2 3 4 5
Procedure created.
SQL> set serverout on
SQL> BEGIN hello(); END;
/
2 Hello, edition 1.
PL/SQL procedure successfully completed.
SQL> CREATE EDITION e2;
Edition created.
SQL> ALTER SESSION SET EDITION = e2;
Session altered.
SQL> CREATE OR REPLACE PROCEDURE hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');
END hello;
/
2 3 4 5
Procedure created.
SQL> BEGIN hello(); END;
2 /
Hello, edition 2.
PL/SQL procedure successfully completed.
Note, now if we change the edition to the default edition ‘ORA$BASE’, we will now see the original procedure and not the updated one
SQL> ALTER SESSION SET EDITION = ora$base;
Session altered.
SQL> BEGIN hello(); END;
2 /
Hello, edition 1.
Let us now see how this can be rolled out to database user HR
SQL> grant execute on hello to hr;
Grant succeeded.
SQL> GRANT USE ON EDITION e2 to public;
Grant succeeded.
We now make the default edition of the database, E2 so that all changes in the new edition E2 will be visible to all database users
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> ALTER DATABASE DEFAULT EDITION=e2;
Database altered.
SQL> conn hr/hr
Connected.
SQL> set serverout on
SQL> exec sh.hello;
Hello, edition 2.
Posted in Administration, Oracle 11g, Oracle 11g release 2 | Tagged: 11g R2, edition, Oracle 11g release 2, redefinition | Leave a Comment »
Posted by Gavin Soorma on September 18, 2009
Prior to Oracle 11g, the default behaviour was to make statistics available for the optimizer to use as soon as they were gathered.
While statistics are required to enable the optimizer to generate optimal execution plans, sometimes just by gathering fresh statistics tried and trusted execution plans can abruptly change and thereby adversely affect application performance.
In Oracle 11g we can now ‘defer’ the publication of statistics until they have been tried and tested and once we have confirmed that the execution plans are correct and optimal. Statistics remain in the pending state until they are published and the parameter optimizer_use_pending_statistics (default value FALSE) which when set at the session level will enables us to test the pending statistics independently of other database sessions.
Let us look at a test case using the SALES table in the SH schema.
If we see the query below, it appears that the data for the column CHANNEL_ID is skewed where majority of the rows have the value 3 and a very small majority have the value 9.
SQL> select channel_id,count(*) from sales
2 group by channel_id order by 2;
CHANNEL_ID COUNT(*)
———- ———-
9 2074
4 118416
2 258025
3 540328
But both the queries below are performing a full table scan of the SALES table when ideally it should be performing an index scan when the value 9 is used in the WHERE clause considering it accounts for a very small proportion of the rows in the SALES table.
SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=9
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/
SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=3
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/
As earlier mentioned, statistics for a table are published immediately by default, so we use the DBMS_STATS package to change this default behaviour.
SQL> exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;
DBMS_STATS.GET_PREFS('PUBLISH','SH','SALES')
----------------------------------------------------------------------------------------------
FALSE
Since we observed that the data in the table is skewed, we will gather histograms which may help the optimizer make more informed decisions when generating an execution plan.
Note that since we have disabled the publishing of statistics for the table SALES, even though we have analyzed the table, the LAST_ANALYZED column shows that the table has not been recently analyzed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';
LAST_ANAL
---------
18-AUG-2009
However, we we query the DBA_TAB_PENDING_STATS view, it shows that the SALES table which is partitioned has been analyzed today.
SQL> SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED FROM DBA_TAB_PENDING_STATS;
TABLE_NAME PARTITION_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
SALES 17-SEP-09
SALES SALES_Q3_1998 17-SEP-09
SALES SALES_Q3_1999 17-SEP-09
SALES SALES_Q3_2000 17-SEP-09
SALES SALES_Q3_2001 17-SEP-09
SALES SALES_Q3_2002 17-SEP-09
SALES SALES_Q3_2003 17-SEP-09
SALES SALES_Q4_1998 17-SEP-09
SALES SALES_Q4_1999 17-SEP-09
SALES SALES_Q4_2000 17-SEP-09
SALES SALES_Q4_2001 17-SEP-09
SALES SALES_Q4_2002 17-SEP-09
SALES SALES_Q4_2003 17-SEP-09
SALES SALES_1995 17-SEP-09
SALES SALES_1996 17-SEP-09
SALES SALES_H1_1997 17-SEP-09
SALES SALES_H2_1997 17-SEP-09
SALES SALES_Q1_1998 17-SEP-09
SALES SALES_Q1_1999 17-SEP-09
SALES SALES_Q1_2000 17-SEP-09
SALES SALES_Q1_2001 17-SEP-09
SALES SALES_Q1_2002 17-SEP-09
SALES SALES_Q1_2003 17-SEP-09
SALES SALES_Q2_1998 17-SEP-09
SALES SALES_Q2_1999 17-SEP-09
SALES SALES_Q2_2000 17-SEP-09
SALES SALES_Q2_2001 17-SEP-09
SALES SALES_Q2_2002 17-SEP-09
SALES SALES_Q2_2003 17-SEP-09
We would like to see if the query execution plan has changed or improved after the recent gathering of statistics. We can use the parameter optimizer_use_pending_statistics for this by setting the value to TRUE (default is FALSE)
SQL> alter session set optimizer_use_pending_statistics=TRUE;
Session altered.
We now find that the optimizer is performing an index scan when the value 9 is used as a predicate value for CHANNEL_ID while it performs a full table scan when the value 3 is used which is a much more optimal plan than the original one before gathering fresh statistics.
explain plan
for
SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=3
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/ 2 3 4 5 6 7 8 9 10 11 12
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3701591983
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4992 | 79872 | 2649 (4)| 00:00:32 | | |
| 1 | SORT GROUP BY | | 4992 | 79872 | 2649 (4)| 00:00:32 | | |
| 2 | PARTITION RANGE ALL| | 541K| 8461K| 2601 (2)| 00:00:32 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 541K| 8461K| 2601 (2)| 00:00:32 | 1 | 28 |
----------------------------------------------------------------------------------------------
SQL> explain plan
for
SELECT
S.cust_id
,S.prod_id
,SUM(S.amount_sold)
FROM sh.sales S
WHERE
channel_id=9
GROUP BY S.cust_id, S.prod_id
ORDER BY S.cust_id, S.prod_id
/
2 3 4 5 6 7 8 9 10 11 12
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3360497850
--------------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 913 | 14608 | 315 (1)| 00:00:04
|
| 1 | SORT GROUP BY | | 913 | 14608 | 315 (1)| 00:00:04
|
| 2 | PARTITION RANGE ALL | | 1423 | 22768 | 314 (0)| 00:00:04
|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1423 | 22768 | 314 (0)| 00:00:04
|
| 4 | BITMAP CONVERSION TO ROWIDS | | | | |
|
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | |
|
--------------------------------------------------------------------------------------------------------
Since we have now confirmed that the plan is acceptable and the new statistics can be used by the optimizer, we PUBLISH the statistics now for the SALES table. Note that the LAST_ANALYZED column is also updated and the DBA_TAB_PENDING_STATS shows that there are no more pending statistics which need to be published.
SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SH','SALES');
PL/SQL procedure successfully completed.
SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';
LAST_ANAL
---------
17-SEP-09
SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;
COUNT(*)
----------
0
SQL> alter session set optimizer_use_pending_statistics=FALSE;
Session altered.
Posted in Administration, Oracle 11g | Tagged: 11g pending statistics, optimizer_use_pending_statistics, publish statistics | Leave a Comment »
Posted by Gavin Soorma on September 15, 2009
In versions prior to Oracle 11g, we were very likely to have faced the error shown below especially if we used range partitioning and the partition column was a date field.
We needed to ensure that we precreated all the partitions before hand based on the expected values of data that would be inserted (or updated) in a table.
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
While we could use the MAXVALUE clause to create a ‘catch-all’ partition, this would not help us when we would like to perform any partition maintenance operations based on a date range or if wanted to use the partitioning feature to perform some kind of data archiving at the partition level.
11g Interval Partitioning
In Oracle 11g, the creation of partitions (for range) is automated and partitions are created as and when needed and takes the task of managing the creation of new partitions from the DBA. All that is required is to define the interval criteria and create the first partition. Subsequent partitions are created automatically based on the interval criteria.
create table mypart
(ename varchar2(20), doj date)
partition by range (doj)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
STORE IN (tbs1,tbs2)
(partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
)
;
Note the NUMTOYMINTERVAL is an SQL Funtion used to convert a number to an INTERVAL YEAR TO MONTH literal. The accepted values are ‘YEAR’ and ‘MONTH’.
The STORE IN clause will create in the partitions in a round robin manner in tablespaces tbs1 and tbs2 as we will see below.
Let us now insert some values into the table.
SQL> insert into mypart
2 values
3 ('Tom','21-SEP-2009');
1 row created.
SQL> insert into mypart
2 values
3 ('Joe','02-JAN-2010');
1 row created.
What has happened after the second insert? – a new partition ‘SYS_P42′ has been created for the year 2010 with a high value of ‘01-JAN-2011′.
The first partition that we had precreated has been created in the default tablespace USERS since no tablespace name has been prescribed and the new partition has been created in the tablespace TBS2. The next partition that comes along will be created in tablespace TBS1 and so on.
SQL> select partition_name,high_value from user_tab_partitions
2 where table_name='MYPART';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_2009 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='MYPART';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2009 USERS
SYS_P43 TBS2
We can use the ALTER TABLE SET INTERVAL command to convert a range partitioned table to an interval partitioned table as shown below.
SQL> create table mypart2
(ename varchar2(20), doj date)
partition by range (doj)
(partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
)
; 2 3 4 5 6
Table created.
SQL> alter table mypart2
2 SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR');
Table altered.
We need to keep the following points in mind when using Interval Partitioning:
The partitioning column can be only one and it must be of type NUMBER or DATE
We cannot use the MAXVALUE clause
We cannot use this with Index Organised Tables
Posted in Administration, Oracle 11g | Tagged: 11G, interval partitioning, partitioning | Leave a Comment »
Posted by Gavin Soorma on September 14, 2009
Analyze the impact of change on SQL statements using the SQL Performance Analyzer
In the earlier post, we had discussed the Database Capture and Replay component of 11g Real Application Testing – (http://gavinsoorma.wordpress.com/2009/09/11/11g-real-application-testing-making-changes-with-confidence/)
We can also analyse the impact of upgrading from 10.2.0.4 to 11.1.0.6 at the SQL statement level by using the other component of 11g Real Application Testing which is called SQL Performance Analyzer or SPA.
We will use the following two SQL statements to create a SQL Tuning Set (STS) in the 10.2.0.4 environment. This STS is then transported to the 11g target environment and then we will use the GUI Database Control to replay the statements in the STS and generate a Performance Analysis report which will help us identify if any statements have improved or digressed in the changed environment.
SELECT /*+TEST_SPA*/ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;
SELECT /*+TEST_SPA*/ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = ‘Peripherals and Accessories’
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;
We are using a ‘tag’ TEST_SPA to help us easily identify these SQL statements in the shared pool.
Source 10.2.0.4 environment – Create and Export SQL Tuning Set
SQL> exec dbms_sqltune.drop_sqlset(‘SH_10204′);
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.create_sqlset(‘SH_10204′,’Demo Workload to test SPA’);
PL/SQL procedure successfully completed.
SQL> DECLARE
2 stscur dbms_sqltune.sqlset_cursor;
3 BEGIN
4 OPEN stscur FOR
5 SELECT VALUE(P)
6 FROM TABLE(dbms_sqltune.select_cursor_cache(
7 ’sql_text like ”SELECT /*+TEST_SPA%”’,
8 null, null, null, null, null, null, ‘ALL’)) P;
9 — populate the sqlset
10 dbms_sqltune.load_sqlset(:sts_name, stscur);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select sql_id,plan_hash_value, substr(sql_text,1,40) text, executions
2 from dba_sqlset_statements
3 where sqlset_name =’SH_10204′
4 order by sql_id, plan_hash_value;
SQL_ID PLAN_HASH_VALUE TEXT EXECUTIONS
————- ————— ————————————————– ———-
5930m31ryy4b1 3901800458 SELECT /*+TEST_SPA*/ p.prod_category, t. 1
5tbr81fmrnyc0 1679814994 SELECT /*+TEST_SPA*/ p.prod_name, s.time 1
In order to transport the SQL tuning set that we have just created, we need to create a staging table to hold the contents of the STS and then we will use the export utility to generate a dump file which will then be copied to the target 11g environment.
SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>’SH_STS’);
PL/SQL procedure successfully completed.
SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => ‘SH_10204′,staging_table_name => ‘SH_STS’);
PL/SQL procedure successfully completed.
SQL> select count (*) from sh_sts;
COUNT(*)
———-
2
Read More …..
Posted in Administration, Oracle 11g | Tagged: 11g real application testing, performance analyzer, sql performance analyzer, sql tuning set, sts | Leave a Comment »
Posted by Gavin Soorma on September 11, 2009
CASE STUDY:
Using 11g Real Application Testing to analyse the impact of making the following changes:
1) Upgrade the database from 10g to 11g
2) Convert from single instance database to a two node RAC database
3) Convert from file system based storage to 11g ASM
Overview of Real Application Testing
One of the best new features in Oracle 11g is the Real Application Testing option which enables us to test the result of a change without actually implementing the same in a production environment. Changes such as database version upgrades, optimizer changes, hardware and storage changes all carry with them an element of risk and uncertainty. Using this feature much of that uncertainty and guesswork can be avoided.
Real Application Testing comprises of two components:
a) Database Replay
b) SQL Performance Analyzer (SPA)
SPA enables us to test the impact of a change at the level of a particular SQL statement to see if any improvements or regressions occurred in the execution of the SQL statement because of the change.
Database Replay enables us to capture workload real time in a production environment and then replay the same in a clone or copy of the changed production environment with the exact timings, concurrency and transaction characteristics found in the original workload. The AWR comparison and Database Replay reports can easily help us pinpoint any potential problems and errors as well any divergence in performance.
This feature will afford businesses increased confidence and surety when faced with a system change as well as significantly reducing the costs as well as resources associated with the testing phase.
Database Capture is now supported in versions 9.2.0.8 as well as 10.2.0.3 and 10.2.0.4 enabling us to test the impact of upgrades from both 9i as well as 10g to 11g. Note, however, that replay can only be performed in a Oracle 11g database.
At a very high level, the database capture and replay will involve the following steps:
1) Prepare the source database for capture
2) Capture the workload
3) Copy the workload capture files to the target machine
4) Prepare the target database for replay and process captured workload files
5) Start the workload replay clients
6) Once the workload replay has finished, examine the generated reports and make the analysis
Read More ….
Posted in Administration, Oracle 11g | Tagged: 11g real application testing, database capture, database replay, dbms_workload_capture, wrc | Leave a Comment »
Posted by Gavin Soorma on September 3, 2009
Although table compression was introduced in Oracle 9i and 10g, it was aimed more at bulk load operations for data warehouse environments. The overhead associated with compression and uncompression made it unsuitable for OLTP type shops.
In Oracle 11g, Advanced Compression (which is an additional licensed feature) includes OLTP compression capabilities as well as compression of unstructured data like images and text with Secure Files and also direct compression of the export dump file generated by Data Pump which is also directly read without any uncompress while doing an import.
The compression feature has been greatly enhanced so as to remove any overhead associated with dealing with compressed data while performing any DML activities.The COMPRESS FOR ALL OPERATIONS keyword needs to be used for enabling OLTP data compression.
While Oracle does claim compression ratios of 3:1, we were able to see clearly 2:1 ratios in terms of storage reductions and no real performance overhead while performing deletes and inserts on compressed data as shown in the example below.
So this feature could provide us both Storage as well as Performance gains due to the reduced number of blocks which will need to be read while performing I/O.
UNCOMPRESSED DATA
SQL> create table myobjects
2 as select * from all_objects;
Table created.
SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS’;
SUM(BYTES)/1048576
——————
21
SQL> insert into myobjects
2 select * from all_objects;
56261 rows created.
Elapsed: 00:00:09.39
SQL> select count(*) from myobjects where object_type=’TABLE’;
COUNT(*)
———-
444
Elapsed: 00:00:00.66
SQL> delete myobjects;
168780 rows deleted.
Elapsed: 00:00:12.22
COMPRESSED DATA
SQL> create table myobjects_compress
2 compress for all operations
3 as select * from all_objects;
SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS_COMPRESS’;
SUM(BYTES)/1048576
——————
9
SQL> insert into myobjects_compress
2 select * from all_objects;
56261 rows created.
Elapsed: 00:00:09.08
SQL> select count(*) from myobjects_compress where object_type=’TABLE’;
COUNT(*)
———-
444
Elapsed: 00:00:00.21
SQL> delete myobjects_compress;
168783 rows deleted.
Elapsed: 00:00:10.96
Compression of Data Pump Exports
While doing an export, we can use the parameter COMPRESSION=ALL (other options are DATA_ONLY or METADATA) and we see that the size of the dumpfile has reduced very significantly while using this compression feature.
The uncompressed dumpfile was 113 MB while the compressed dumpfile was just 13 MB. This feature can be very useful when we need to take a large export on disk but are limited by disk space. The earlier methods required us to use a pipe while performing an export, but we had to then uncompress the file while performing the import as well which is a very costly operation in terms of time and resources.
$ expdp directory=data_file_dir dumpfile=uncompress.dmp schemas=SH compression=ALL
middba1:/u01/oracle/product/11.1.0/db/demo/schema/sales_history> ls -l *.dmp
-rw-rw—- 1 oracle dba 113610752 Sep 3 12:25 uncompress.dmp
-rw-rw—- 1 oracle dba 13426688 Sep 3 12:21 compress.dmp
Posted in Administration, Oracle 11g | Tagged: advanced compression, compression, compression=all | Leave a Comment »
Posted by Gavin Soorma on September 2, 2009
One of the very good new features in Oracle 11g is the Snapshot Standby database where we can basically convert the physical standby database into a read-write real time clone of the production database and we can then temporarily use this environment for carrying out any kind of development testing, QA type work or to test the impact of a proposed production change on an application.
Basically, the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.
So we need to keep in mind that whatever limitations are present in the Flashback database technology, the same will also be inherent in the Snapshot database fetaure.
Let us illustrate this feature by using an example where we would like to test the impact of the change of some important optimizer related parameters on a key application query using the actual production data.
In production, the SQL query below is shown to be peforming a full table/partition scan and using hash joins. We would like to see if a nested loop join and index usage will prove beneficial to this SQL query that is used frequently by the application.
explain plan for
SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')GROUP BY c.cust_last_name
/
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 1248656060
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 519 | 23355 | 935 (6)| 00:00:12 | | |
| 1 | HASH GROUP BY | | 519 | 23355 | 935 (6)| 00:00:12 | | |
|* 2 | HASH JOIN | | 99644 | 4378K| 927 (5)| 00:00:12 | | |
| 3 | INDEX FULL SCAN | PRODUCTS_PK | 72 | 288 | 1 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 99644 | 3989K| 924 (5)| 00:00:12 | | |
|* 5 | TABLE ACCESS FULL | CUSTOMERS | 766 | 18384 | 408 (1)| 00:00:05 | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| 6 | PARTITION RANGE ALL| | 918K| 14M| 507 (6)| 00:00:07 | 1 | 28 |
| 7 | TABLE ACCESS FULL | SALES | 918K| 14M| 507 (6)| 00:00:07 | 1 | 28 |
------------------------------------------------------------------------------------------------------
We will now convert the physical standby database to a Snapshot Standby database and note that after the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode. Also note the DATABASE_ROLE column.
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2139784 bytes
Variable Size 415238520 bytes
Database Buffers 96468992 bytes
Redo Buffers 8089600 bytes
Database mounted.
Database opened.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME GUA
------------------------------------------------------------ ---
SNAPSHOT_STANDBY_REQUIRED_09/02/2009 10:12:21 YES
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY
We will now make some changes to some optimizer related parameters:
SQL> alter system set optimizer_index_cost_adj=20;
System altered.
SQL> alter system set optimizer_index_caching=10;
System altered.
SQL> alter system set optimizer_mode=FIRST_ROWS;
System altered.
We run the same query again and we can see that the plan has changed and the optimizer is now using the index scan with nested loop joins.
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 519 | 23355 | 588 (11)| 00:00:08 |
| 1 | HASH GROUP BY | | 519 | 23355 | 588 (11)| 00:00:08 |
| 2 | NESTED LOOPS | | 99644 | 4378K| 580 (10)| 00:00:07 |
|* 3 | HASH JOIN | | 99644 | 3989K| 577 (10)| 00:00:07 |
|* 4 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 766 | 18384 | 241 (2)| 00:00:03 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 6 | BITMAP INDEX FULL SCAN | CUSTOMERS_GENDER_BIX | | | | |
| 7 | PARTITION RANGE ALL | | 918K| 14M| 326 (12)| 00:00:04 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 918K| 14M| 326 (12)| 00:00:04 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX | | | | |
|* 11 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
While the original physical standby database has been now converted to snapshot database, some changes are happening on the Primary database and those changes are shipped to the standby site but not yet applied. They will accumulate on the standby site and will be applied after the snapshot standby database is converted back to a physical standby database.
SQL> create table mytest as select * from sales;
Table created.
SQL> update mytest set prod_id=1;
918843 rows updated.
On the standby site, we will now convert the shapshot standby database to the original mode of physical standby database in Active Data Guard mode.
Note that after the physical standby database is opened, changes that were made on the Primary database while it was open in shapshot standby mode are now applied as well.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2139784 bytes
Variable Size 415238520 bytes
Database Buffers 96468992 bytes
Redo Buffers 8089600 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2139784 bytes
Variable Size 415238520 bytes
Database Buffers 96468992 bytes
Redo Buffers 8089600 bytes
Database mounted.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
Elapsed: 00:01:06.29
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select count(*) from sh.mytest;
COUNT(*)
----------
918843
SQL> select distinct prod_id from sh.mytest;
PROD_ID
----------
1
Posted in High Availability, Oracle 11g | Tagged: active data guard, convert to snapshot, snapshot standby | Leave a Comment »
Posted by Gavin Soorma on August 31, 2009
This example demonstrates how to transport a tablespace from a 10g ASM on Windows environment to 11g ASM on AIX environment using the DBMS_FILE_TRANSFER package.
The source database on Windows is GENUA1 and the target database is a 11g RAC database RACDB1.
Create the tablespace and the test object in the source database
SQL> create tablespace test_asm
2 datafile size 100m;
Tablespace created.
SQL> select file_name from dba_data_files
2 where tablespace_name='TEST_ASM';
FILE_NAME
--------------------------------------------------------------------------------
+DATA/genua1/datafile/test_asm.376.696333127
SQL> conn system/xxx
Connected.
SQL> create table myobjects
2 tablespace test_asm
3 as select * from dba_objects;
Table created.
SQL> select count(*) from system.myobjects;
COUNT(*)
----------
56504
Create a database link from the source database to the target database
SQL> CREATE DATABASE LINK DBS2 CONNECT TO
2 system identified by oracle
3 using 'racdb1';
Database link created.
SQL> select * from dual@dbs2;
D
-
X
Create a directory to hold the converted datafile on the source
SQL> create directory source_dir as '+DATA/genua1/xtransport/';
Directory created.
Create a directory to hold the tablespace meta data dump on the source
SQL> create directory source_dir_2 as '+DATA';
Directory created.
Create an O/S level directory for the data pump export logfile
SQL> create directory test_asm_log as 'd:\oracle\';
Directory created.
Create a directory on the target to hold the transported datafile
SQL> CREATE OR REPLACE DIRECTORY target_dir AS '+DATA';
Directory created.
Create an O/S level directory for the data pump import logfile
create directory test_asm_log as '/u01/oracle/';
Directory created.
Make the tablespace which is going to be transported read only
SQL> alter tablespace test_asm read only;
Tablespace altered.
Export the tablespace meta data using Data Pump
C:\Documents and Settings\bwdba_cbduat>expdp directory=source_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_check=Y logfile=test_asm_log:exp.log
Export: Release 10.2.0.4.0 - Production on Monday, 31 August, 2009 10:41:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=sour
ce_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_che
ck=Y logfile=test_asm_log:exp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
+DATA/test_asm.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:42:16
Use DBMS_FILE_TRANSFER to transfer the export dump file to the target
SQL> begin
2 dbms_file_transfer.put_file
3 (source_directory_object => 'source_dir_2',
4 source_file_name => 'test_asm.dmp',
5 destination_directory_object => 'target_dir',
6 destination_file_name => 'test_asm.dmp',
7 destination_database => 'dbs2');
8 end;
9 /
PL/SQL procedure successfully completed.
Use RMAN to convert the tablespace to the target AIX 64 bit format
Note that the converted datafile will reside in the directory we created in an earlier step - +DATA/genua1/xtransport
RMAN> convert tablespace test_asm to
2> platform 'AIX-Based Systems (64-bit)'
3> format '+DATA';
Starting backup at 31/AUG/09
configuration for DISK channel 2 is ignored
configuration for DISK channel 3 is ignored
configuration for DISK channel 4 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=470 instance=genua11 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00224 name=+DATA/genua1/datafile/test_asm.376.696333127
converted datafile=+DATA/genua1/xtransport/test_asm.266.696338369
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31/AUG/09
Use DBMS_FILE_TRANSFER to copy the converted datafile to the target
SQL> begin
2 dbms_file_transfer.put_file
3 (source_directory_object => 'source_dir',
4 source_file_name => 'test_asm.266.696338369',
5 destination_directory_object => ' target_dir',
6 destination_file_name => 'tts1_db1.dbf',
7 destination_database => 'dbs2');
8 end;
9 /
PL/SQL procedure successfully completed.
On the target import the datafile meta data using Data Pump
The imp.par import parameter file has the following contents
directory=target_dir
dumpfile=test_asm.dmp
logfile=test_asm_log:imp.log
TRANSPORT_DATAFILES='+DATA1/tts1_db1.dbf'
keep_master=y
middba1:/u01/oracle> impdp parfile=imp.par
Import: Release 11.1.0.6.0 - 64bit Production on Monday, 31 August, 2009 11:39:32
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02": system/******** parfile=imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully completed at 11:39:48
On the target database RACDB1, check that the TEST_ASM tablespace has been plugged in and the MYOBJECTS table is present
middba1:/u01/oracle> sql
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 31 11:41:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
SQL> select file_name from dba_data_files where tablespace_name='TEST_ASM';
FILE_NAME
--------------------------------------------------------------------------------
+DATA/tts1_db1.dbf
SQL> select count(*) from system.myobjects;
COUNT(*)
----------
56504
Posted in Administration, Oracle 11g | Tagged: ASM, tts, cross platform transportable tablespace, dbms_file_transfer | Leave a Comment »
Posted by Gavin Soorma on August 21, 2009
While one of the performance best practices that is always recommended is the use of bind variables over the literals, Optimizer plan unstability was one of the problems faced by many Oracle 10g shops. When queries that normally perform well, suddenly stopped performing, very often it is found that this bad performance is being observed only for a few or specific parameters or values. And this is particularly so on tables with highly skewed data especially the columns which are being referenced via the WHERE clause.
In Oracle 11g however, with Adaptive Cursor Sharing, if the optimizer observes a sub-optimal plan for a particular bind variable value, it will enable particular bind variables or range of bind variable values to use a different execution plan for the same SQL statement.
To illustrate the 11g Adaptive Cursor Sharing, we will create a table called MYOBJECTS which is based on the ALL_OBJECTS view. The data in the OBJECT_TYPE column is skewed – for example there are more than 50,000 rows with the value ‘SYNONYM’ and only 1 row with the value ‘RULE’. The table has about 100,000 rows.
SQL> conn sh/sh
Connected.
SQL> create table myobjects
2 as select * from all_objects;
Table created.
SQL> create index myobject_ind on myobjects (object_type);
Index created.
We then gather statistics on the table and see that histograms have been created on the table which has the skewed data.
SQL> exec dbms_stats.gather_table_stats(null,'MYOBJECTS', METHOD_OPT => 'FOR ALL COLUMNS SIZE 10');
PL/SQL procedure successfully completed.
SQL> SELECT column_name, histogram FROM user_tab_cols WHERE table_name ='MYOBJECTS';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OWNER HEIGHT BALANCED
OBJECT_NAME HEIGHT BALANCED
SUBOBJECT_NAME HEIGHT BALANCED
OBJECT_ID HEIGHT BALANCED
DATA_OBJECT_ID HEIGHT BALANCED
OBJECT_TYPE HEIGHT BALANCED
CREATED HEIGHT BALANCED
LAST_DDL_TIME HEIGHT BALANCED
TIMESTAMP HEIGHT BALANCED
STATUS FREQUENCY
TEMPORARY FREQUENCY
GENERATED FREQUENCY
SECONDARY FREQUENCY
If we query the MYOBJECTS Table using the predicate ‘RULE’, we find that an INDEX RANGE SCAN using the MYOBJECTS_IND index is being carried out by the optimizer.
If we change the predicate to ‘SYNONYM’ we find that a FULL TABLE SCAN is being performed instead which is the expected behaviour given the distribution of rows in the table.
SQL> explain plan for
2 select * from myobjects where object_type='RULE';
Explained.
SQL> set linesize 120
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2533318700
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1612 | 147K| 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 1612 | 147K| 56 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MYOBJECT_IND | 1612 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> explain plan for
2 select * from myobjects where object_type='SYNONYM';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2581838392
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34398 | 3157K| 284 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 |
-------------------------------------------------------------------------------
Let us now see how the optimizer handles bind variables and how it performs the task of bind variable peeking. In the first case, the variable v_obj_type is assigned the value ‘SYNONYM’ and in the second case the same variable is assigned the value ‘RULE’.
Remember, when literals were being used, using the value ‘RULE’ in the WHERE clause resulted in an index range scan, while usage of the value ‘SYNONYM’ resulted in a full table scan.
SQL> VARIABLE v_obj_type VARCHAR2(10)
SQL> EXEC :v_obj_type := 'SYNONYM';
PL/SQL procedure successfully completed.
SQL> select * from myobjects where object_type=:v_obj_type;
40156 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 5utj7m6zxjdsc, child number 0
-------------------------------------
select * from myobjects where object_type=:v_obj_type
Plan hash value: 2581838392
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 284 (100)| |
|* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 |
-------------------------------------------------------------------------------
Although the predicate is now the value ‘RULE’ based on the bind variable that is being used here, we find that the optimizer is still performing a FULL TABLE SCAN, while in fact it should be actually performing an index scan.
But since bind peeking is being performed here, the optimizer will use the same plan even though the value of the variable is different. If the data is skewed as in this case, it can cause a lot of performance problems and optimizer unstability depending on which bind variable value is assigned at hard parse.
SQL> VARIABLE v_obj_type VARCHAR2(10)
SQL> EXEC :v_obj_type := 'RULE';
PL/SQL procedure successfully completed.
SQL> select * from myobjects where object_type=:v_obj_type;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 5utj7m6zxjdsc, child number 0
-------------------------------------
select * from myobjects where object_type=:v_obj_type
Plan hash value: 2581838392
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 284 (100)| |
|* 1 | TABLE ACCESS FULL| MYOBJECTS | 34398 | 3157K| 284 (2)| 00:00:04 |
-------------------------------------------------------------------------------
But if we query the V$SQL view, we find that for this particular SQL_ID, the column IS_BIND_SENSITIVE shows ‘Y’ but the column ‘IS_BIND_AWARE’ still shows ‘N’.
This means that Oracle is aware that this query can require different execution plans based on the bind variable values but has not yet acted on this at this stage.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
from v$sql
WHERE sql_text ='select * from myobjects where object_type=:v_obj_type'; 2 3
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
5utj7m6zxjdsc 0 Y N
We then execute the same SQL statement once again using the same bind variable value, and we find that this time a more optimal execution plan has been chosen which in this case is an INDEX RANGE SCAN
SQL> VARIABLE v_obj_type VARCHAR2(10)
SQL> EXEC :v_obj_type := 'RULE';
PL/SQL procedure successfully completed.
SQL> select * from myobjects where object_type=:v_obj_type;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 5utj7m6zxjdsc, child number 1
-------------------------------------
select * from myobjects where object_type=:v_obj_type
Plan hash value: 2745750972
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 823 | 83946 | 30 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IND | 823 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
If we now query the V$SQL view, we find that this optimizer change is also reflected in the column IS_BIND_SENSITIVE showing the value ‘Y’ instead of ‘N’ which was the earlier value.
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
from v$sql
WHERE sql_text ='select * from myobjects where object_type=:v_obj_type'; 2 3
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
5utj7m6zxjdsc 0 Y N
5utj7m6zxjdsc 1 Y Y
Posted in Oracle 11g | Tagged: 11G, adaptive cursor, bind peeking | Leave a Comment »
Posted by Gavin Soorma on August 19, 2009
While creating the ASM instance using DBCA in an 11g RAC environment on 64 bit AIX 5L, we got the ORA-27504 error along with others as shown below.
The problem was caused by incorrect UDP and TCP packet settings and is documented in Metalink note 300956.1
As root, the following changes need to be made
# no -o tcp_sendspace=262144
# no -o tcp_recvspace=262144
# no -o udp_sendspace=65536
# no -o udp_recvspace=262144
# no -o rfc1323=1
Adding these entries to the /etc/rc.net will ensure that these parameters take effect on every machine reboot.
Posted in Oracle 11g, Real Application Clusters | Tagged: ASM, no -o, ora-27504, RAC | Leave a Comment »
Posted by Gavin Soorma on August 18, 2009
While relinking the Oracle 11g RAC software on 64 bit AIX platform, we got the error as shown below:
Looking at the installation log we found the following lines:
ld: 0706-006 Cannot find or open library file: -l ha_gs_r
ld:open(): A file or directory in the path name does not exist.
ld: 0706-006 Cannot find or open library file: -l ha_em_r
ld:open(): A file or directory in the path name does not exist.
make: 1254-004 The error code from the last command is 255
The cause of this error is that a particluar fileset rsct.basic.rte which is required specifically for a 11g RAC installation has not been installed at the OS level. After the fileset was installed we could relink the software without the same ld errors as mentioned above.
We can check if the fileset has been installed by running the command ‘lslpp’ as shown below.
testdb:/u02/oradata/testdb> lslpp -l rsct.basic.rte
lslpp: Fileset rsct.basic.rte not installed.
After the fileset is installed we can confirm the same…
middba1:/u01/oracle/dump> lslpp -l rsct.basic.rte
Fileset Level State Description
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
rsct.basic.rte 2.4.10.0 COMMITTED RSCT Basic Function
This package is part of the OS Patch IY98287 which is one of the mandatory required patches to be installed on AIX if we are configuring a 11g RAC environment.
This is also documented in Metalink Note 471701.1
Posted in Oracle 11g, Real Application Clusters | Tagged: 11g RAC, ld:0706, relink, rsct.basic.rte | Leave a Comment »
Posted by Gavin Soorma on August 14, 2009
This example illustrates Transparent Application Failover (TAF) in a two node AIX 11g RAC configuration.
The database name is racdb and the two instances are racdb1 and racdb2.
Node 1 – middba1 which hosts instance racdb1.
Node2 – middba2 which hosts instance racdb2
We have defined two services racdb1 and racdb2. For service racdb1 the preferred instance is racdb1 and
for service racdb2 the preferred instance is racdb2.
The following srvctl commands illustrates the same.
middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb1
Service racdb1 is running on instance(s) racdb1
middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb1
racdb1 PREF: racdb1 AVAIL: racdb2
middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb2
Service racdb2 is running on instance(s) racdb2
middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb2
racdb2 PREF: racdb2 AVAIL: racdb1
From a SQL*PLUS client, we establish a session as user SYSTEM using the service racdb1. Note the machine name where this service is running from.
testdb:/u01/oracle> sqlplus system/xxx@racdb1
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 14 10:37:03 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
SQL> select host_name from v$instance;
HOST_NAME
----------------------------------------------------------------
middba1
We then execute a long running SELECT statement
SQL> select * from sh.sales;
While this statement is running we reboot the node middba1 where this service is currently connected to.
We will note that the screen will momentarily freeze while the service gets relocated to the other functional node racdb2 and then the query continues from where it got interrupted. The session will not get disconnected – so for the end user it is transparent from which instance the query is being serviced.
If we check the status of the cluster resources, we will find that the VIP from node middba1 has now relocated to middba2. Other resources that were originally running from node middba1 are now offline.
middba2:/u01/oracle> crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....A1.lsnr application ONLINE OFFLINE
ora....ba1.gsd application ONLINE OFFLINE
ora....ba1.ons application ONLINE OFFLINE
ora....ba1.vip application ONLINE ONLINE middba2
ora....SM2.asm application ONLINE ONLINE middba2
ora....A2.lsnr application ONLINE ONLINE middba2
ora....ba2.gsd application ONLINE ONLINE middba2
ora....ba2.ons application ONLINE ONLINE middba2
ora....ba2.vip application ONLINE ONLINE middba2
ora.racdb.db application ONLINE ONLINE middba2
ora....cdb1.cs application ONLINE ONLINE middba2
ora....b1.inst application ONLINE OFFLINE
ora....db1.srv application ONLINE ONLINE middba2
ora....cdb2.cs application ONLINE ONLINE middba2
ora....b2.inst application ONLINE ONLINE middba2
ora....db2.srv application ONLINE ONLINE middba2
If we check the status of the service, we will find that the service racdb1 is now running on node middba2 connecting to the instance racdb2.
middba2:/u01/oracle> srvctl status service -d racdb -s racdb1
Service racdb1 is running on instance(s) racdb2
Once the node middba1 which was rebooted finally does come online, we will find that the VIP will relocate
to its original node middba1 once the cluster services are brought online after the reboot.
middba2:/u01/oracle> crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE middba1
ora....A1.lsnr application ONLINE ONLINE middba1
ora....ba1.gsd application ONLINE ONLINE middba1
ora....ba1.ons application ONLINE ONLINE middba1
ora....ba1.vip application ONLINE ONLINE middba1
ora....SM2.asm application ONLINE ONLINE middba2
ora....A2.lsnr application ONLINE ONLINE middba2
ora....ba2.gsd application ONLINE ONLINE middba2
ora....ba2.ons application ONLINE ONLINE middba2
ora....ba2.vip application ONLINE ONLINE middba2
ora.racdb.db application ONLINE ONLINE middba2
ora....cdb1.cs application ONLINE ONLINE middba2
ora....b1.inst application ONLINE ONLINE middba1
ora....db1.srv application ONLINE ONLINE middba2
ora....cdb2.cs application ONLINE ONLINE middba2
ora....b2.inst application ONLINE ONLINE middba2
ora....db2.srv application ONLINE ONLINE middba2
Note however, that the service racdb1 will continue running on node middba2 (instance racdb2)even though the original node which it was running on middba1 has been brought online.
We will have to relocate the service back to its original node using the srvctl command
middba1:/u01/oracle> srvctl relocate service -d racdb -s racdb1 -i racdb2 -t racdb1
Posted in High Availability, Oracle 11g, Real Application Clusters | Tagged: 11G, failover, service relocate, TAF | Leave a Comment »
Posted by Gavin Soorma on August 12, 2009
While installing the 11g Clusterware on 64 bit AIX 5L, we encountered the CRS-0184 error while running the root.sh on the second node.
The error was …
Cluster Synchronization Services is active on all the nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes0:CRS-0184: Cannot communicate with the CRS daemon
The crsd.log file showed the following lines ….
2009-08-10 14:18:36.059: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:19:12.143: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:20:15.037: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:21:11.379: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
The fix for this error was to increase the Shell Limits for the user ‘root’ as the Clusterware processes are started as root.
The documentation does mention that we need to so the same for the ‘oracle’ user account and fails to mention that we need to do it for the root account as well.
After changing the shell limits for root (on BOTH nodes) the CRS-0184 problem was resolved.
middba1 # ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) unlimited
Note that since the root.sh on the second node will internally launch VIPCA and this part did not run to successful completion, we had to manually run the vipca command as root to get the vip,ons amd gsd resources online.
Posted in Administration, Oracle 11g, Real Application Clusters | Tagged: 11g RAC, crs, CRS-0184, ulimit | Leave a Comment »
Posted by Gavin Soorma on August 3, 2009
In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR.
The ADR is the Automatic Diagnostic Repository which is a file based hierarchical respository of data and information related the the database alert, trace and dump files, health monitor reports (new in 11g), network trace and log information, diagnostic data related to the 11g Incident Packaging Service etc.
The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or via the ADR command line interpreter adrci
The DIAGNOSTIC_DEST parameter identifies the location of the ADR and it defaults to the $ORACLE_HOME/log or $ORACLE_BASE if this environment variable has been set.
Let us assign this location the environment variable $ADR_DUMP.
The alert log is stored in both XML as well as standard text format in the ADR. In this example the database name is ‘apex’.
The XML alert.log is located at $ADR_DUMP/diag/rdbms/apex/apex/alert and is called ‘log.xml’. The current alert log contents will be found in the file ‘log.xml’ while the older entries are archived every 10 MB into files like ‘log_1.xml’, ‘log_2.xml’ and so on.
The standard text alert log file (alert_apex.log) is located at $ADR_DUMP/diag/rdbms/apex/apex/trace and is mantained for backward compatability purposes.
The view V$DIAG_INFO also has information on the location of the various components of the ADR.
SQL> col value format a50
SQL> SELECT NAME,VALUE FROM v$diag_info
2 WHERE NAME LIKE '%Alert%';
NAME VALUE
-------------------- --------------------------------------------------
Diag Alert /u01/oracle/diag/rdbms/apex/apex/alert
Using the ADR CLI to view Alert Log Information
The command ‘adrci’ will take us to the prompt.
apex:/u01/oracle/diag/rdbms/apex/apex/alert> adrci
ADRCI: Release 11.1.0.6.0 – Beta on Mon Aug 3 09:46:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = “/u01/oracle”
adrci>
Many of the adrci commands are very similar to the standard Unix vi commands. For example
SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)
show alert -p “message_text like ‘%ORA-07445%’” (to show all occurences of the string ‘ORA-07445′ in the alert log.)
Purging Alert Log Content
The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:
adrci > purge -age 10080 -type ALERT
Posted in Oracle 11g | Tagged: 11G, adr, adrci, diagnostic_dest | Leave a Comment »
Posted by Gavin Soorma on July 30, 2009
In Oracle 11g, we can ensure that the same proven execution plans are used by the Optimizer regardless of any change which can effect the optimizer like a version change, index drop or recreate, init.ora parameter change etc.
Once plans are marked as enabled and accepted, the optimizer will not use any new plans unless there is proven performance benefits of adopting a new plan. Plans can also be manually adopted or “evolved”.
One of the dangers of upgrading from 10g to 11g (or between any release) is that execution plans may change in the new version which could affect application performance.
But with this new 11g feature, if we have tried and trusted 10g execution plans in place, we can ensure that the same plans will be used by the optimizer even after the migration unless new 11g plans are found to offer better performance. This can be achieved by capturing 10g plans in SQL tuning sets (STS) and exporting those to the 11g database.
Let us demonstrate the same with a small example using the SALES table in the SH schema.
We need to enable the use of SQL Plan management by setting the parameter optimizer_capture_sql_plan_baselines to TRUE. The default value is FALSE.
Initially the OPTIMIZER_MODE is set to FIRST_ROWS which should optimize the statement for best response time. So in this case the index on the SALES table is used.
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';
Session altered.
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM SALES
3 WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
| Id | Operation | Name | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 5063 (2)| 00:01:01 | | |
| 1 | SORT ORDER BY | | 29 | 5063 (2)| 00:01:01 | | |
| 2 | PARTITION RANGE ALL | | 29 | 5062 (2)| 00:01:01 | 1 | 28 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 29 | 5062 (2)| 00:01:01 | 1 | 28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX | | | | 1 | 28 |
---------------------------------------------------------------------------------------------------------------
if we change the OPTIMIZER_MODE to ALL_ROWS, we now see from the explain plan that a FULL TABLE SCAN is being used as the statement is optimized for best throughput.
SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';
Session altered.
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM SALES
3 WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3147563666
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 1196 (3)| 00:00:15 | | |
| 1 | SORT ORDER BY | | 1 | 29 | 1196 (3)| 00:00:15 | | |
| 2 | PARTITION RANGE ALL| | 1 | 29 | 1195 (3)| 00:00:15 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 1 | 29 | 1195 (3)| 00:00:15 | 1 | 28 |
----------------------------------------------------------------------------------------------
We will run the query – initially with OPTIMIZER_MODE set to FIRST_ROWS and then the same query with OPTIMIZER_MODE set to ALL_ROWS. We will use a label ‘GAVIN’ in the SQL statement to help us easily identify it from the other SQL statements in the shared pool.
We explicitly flush the shared pool to force a hard parse the next time the same query is run but with the ALL_ROWS optimizer session setting.
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';
Session altered.
SQL> SELECT /* GAVIN */
2 *
3 FROM SALES WHERE QUANTITY_SOLD > 40
4 ORDER BY PROD_ID;
no rows selected
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';
Session altered.
SQL> SELECT /* GAVIN */
2 *
3 FROM SALES WHERE QUANTITY_SOLD > 40
4 ORDER BY PROD_ID;
no rows selected
We will notice that the second execution of the query has encountered a change in the execution plan. But it has not been ‘accepted as yet even though it has been ‘enabled’.
SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
WHERE sql_text like '%GAVIN%'; 2
Ena-
SQL_HANDLE PLAN_NAME bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803511df68d0 YES YES
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803554bc8843 YES NO
We can use the EVOLVE_SQL_PLAN_BASELINE function to compare the performance between the two plans. We note that the second plan has not been automatically evolved or accepted because it does not pass the performance improvement criteria which has been laid down
SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f3dcd88c7488035') FROM dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F3DCD88C7488035')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_6f3dcd88c7488035
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_c748803554bc8843
-----------------------------------
Plan was verified: Time used 1.457 seconds.
Failed performance criterion: Compound improvement ratio <= 1.16.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 1036 304 3.41
CPU Time(ms): 1020 300 3.4
Buffer Gets: 1929 1727 1.12
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
We can also manually evolve the plan if we ourselves feel that we know that the plan is a better one
SQL> var spm number;
SQL> exec :spm := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SYS_SQL_6f3dcd88c7488035',
- plan_name =>'SYS_SQL_PLAN_c748803554bc8843', attribute_name => 'ACCEPTED',attribute_value => 'YES');
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
2 WHERE sql_text like '%GAVIN%';
Ena-
SQL_HANDLE PLAN_NAME bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803511df68d0 YES YES
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803554bc8843 YES YES
Posted in Oracle 11g, Performance Tuning | Tagged: 11G, DBMS_SPM, optimizer plan stability, plan baselines, SPM | Leave a Comment »
Posted by Gavin Soorma on July 7, 2009
Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.
We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to it’s earlier state as a physical standby database.
While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.
After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.
Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.
Initially we have a Data Guard broker configuration in place where APEX is the Primary database
and APEXDG is the Standby database.
DGMGRL> show configuration
Configuration
Name: gavin
Enabled: YES
Protection Mode: MaxAvailability
Databases:
apex - Primary database
apexdg - Physical standby database
Fast-Start Failover: DISABLED
Current status for "gavin":
SUCCESS
We now convert the physical standby database to a snapshot standby
DGMGRL> convert database 'apexdg' to snapshot standby;
Converting database "apexdg" to a Snapshot Standby database, please wait...
Database "apexdg" converted successfully
DGMGRL> show configuration
Configuration
Name: gavin
Enabled: YES
Protection Mode: MaxAvailability
Databases:
apex - Primary database
apexdg - Snapshot standby database
Fast-Start Failover: DISABLED
Current status for "gavin":
SUCCESS
We now connect to the snapshot standby database which is open in read-write mode and we create a table
apex:/u01/oracle/scripts> export ORACLE_SID=apexdg
apexdg:/u01/oracle/scripts> sqlplus sh/SH
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test_snapshot
2 as select * from all_objects;
Table created.
SQL> select count(*) from test_snapshot;
COUNT(*)
----------
56467
At this point in time, we revert back to the original state
DGMGRL> convert database 'apexdg' to physical standby;
Converting database "apexdg" to a Physical Standby database, please wait...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Continuing to convert database "apexdg" ...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Database "apexdg" converted successfully
DGMGRL>
We now connect to the physical standby database and see that all the changes we made while
the standby database was functioning as a snapshot standby have been rolled back. The table
which we created in the snapshot standby database is not physically present in the physical
standby database.
apexdg:/u01/oracle/scripts> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open read only;
Database altered.
SQL> conn sh/SH@apexdg
Connected.
SQL> select count(*) from test_snapshot;
select count(*) from test_snapshot
*
ERROR at line 1:
ORA-00942: table or view does not exist
Posted in High Availability, Oracle 11g | Tagged: 11G, convert database, data guard, snapshot standby | Leave a Comment »
Posted by Gavin Soorma on July 2, 2009
In Oracle 11g, security has been enhanced by restricting access to packages that were used in earlier releases like UTL_SMTP and UTL_HTTP to send emails and connect over the network to mail servers etc. By default, attempt to use these packages will result in an ORA-24247 (network access denied by access control list).
Using Access Control Lists or ACL’s, administrators can have control over which ports are opened for ‘public’ access.
This example below will show how we can use the DBMS_NETWORK_ACL_ADMIN package to enable us to send emails from an APEX 3.1 application which connects to an Oracle 11g database.
Create the mailserver_acl procedure which calls the DBMS_NETWORK_ACL_ADMIN package
set serveroutput on
show user;
create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line(‘ACL dropped…..’);
exception
when others then
dbms_output.put_line(‘Error dropping ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line(‘ACL created…..’);
exception
when others then
dbms_output.put_line(‘Error creating ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line(‘ACL assigned…..’);
exception
when others then
dbms_output.put_line(‘Error assigning ACL: ‘||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line(‘ACL commited…..’);
end;
/
show errors
Now we need to grant the database user ‘FLOWS_030100′ and the application owner ‘MONITOR’ the required privileges to interact with network services – in this case to access the SMTP server FRMWEB02 using port 25.
begin
mailserver_acl(
‘mailserver_acl.xml’,
‘ACL for used Email Server to connect’,
‘MONITOR‘,
TRUE,
‘connect’,
‘FRMWEB02.BANKWEST.COM’,
25);
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘mailserver_acl.xml’,‘FLOWS_030100′,TRUE,’connect’);
commit;
end;
/
Posted in Oracle 11g | Tagged: 11g ACL, access control list, apex, DBMS_NETWORK_ACL_ADMIN, mail, UTL_SMTP | 2 Comments »
Posted by Arjun Raja on June 25, 2009
USING RMAN REPAIR ADVISOR:
Export ORACLE_SID=test1
sqlplus “sys as sysdba”
Shutdown immediate.
Go to c:\app\bb16872\test1 and rename SYSTEM01.DBF to SYSTEM01.old
Next try to startup database…will fail with error that SYSTEM01.DBF is missing.
Y:\>rman target /
Recovery Manager: Release 11.1.0.6.0 – Production on Wed Apr 30 10:51:03 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST1 (DBID=1030433487, not open)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08 System datafile 1: ‘C:\APP\BB16872\T
EST1\SYSTEM01.DBF’ is missing
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened
RMAN>
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file C:\APP\BB16872\TEST1\SYSTEM01.DBF was unintentionally renamed or
moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
RMAN> repair failure noprompt;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
executing repair script
Starting restore at 30-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\BB16872\TEST1\SYSTEM01.DB
F
channel ORA_DISK_1: reading from backup piece C:\APP\BB16872\BACKUP\1_TEST1_6533
95264
channel ORA_DISK_1: piece handle=C:\APP\BB16872\BACKUP\1_TEST1_653395264 tag=TAG
20080430T104104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 30-APR-08
Starting recover at 30-APR-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-APR-08
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened.
In case of loss of controlfile….
Follow same steps above –remove controlfile and then try to startup.
RMAN> repair failure noprompt;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_3514023340.hm
contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘C:\APP\BB16872\TEST1\CONTROL03.CTL’;
sql ‘alter database mount’;
executing repair script
Starting restore at 30-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file name=C:\APP\BB16872\TEST1\CONTROL01.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL02.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL03.CTL
Finished restore at 30-APR-08
sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
database opened
RMAN> exit
Recovery Manager complete.
Y:\>sqlplus “sys as sysdba”
SQL*Plus: Release 11.1.0.6.0 – Production on Wed Apr 30 11:17:04 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter password:
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 file_name from dba_Data_Files;
FILE_NAME
——————————————————————————–
C:\APP\BB16872\TEST1\SYSTEM01.DBF
C:\APP\BB16872\TEST1\SYSAUX01.DBF
C:\APP\BB16872\TEST1\UNDOTBS01.DBF
C:\APP\BB16872\TEST1\USERS01.DBF
SQL>
Posted in Backup and Recovery, Oracle 11g | Tagged: 11G, 11g RMAN, rman, RMAN Repair Advisor | Leave a Comment »
Posted by Gavin Soorma on June 24, 2009
11g has a very good new feature for SQL*PLUS called PIVOT and UNPIVOT - it is very useful for
creating cross tabular reports and aggregates.
In earlier Oracle versions this would have needed a lot of coding using the DECODE function ....
Objective - find the count of employees in each department broken down by the job performed.
SQL>
select * from
(select deptno,job from emp
)
PIVOT
(
count(*)
for job in ('SALESMAN','CLERK','MANAGER','ANALYST')
)
;
SQL>
DEPTNO 'SALESMAN' 'CLERK' 'MANAGER' 'ANALYST'
---------- ---------- ---------- ---------- ----------
30 4 1 1 0
20 0 2 1 2
10 0 1 1 0
This operation can be reversed using the UNPIVOT command
SQL>
select * from pivot_emp
UNPIVOT
(employee_count for job in ("'SALESMAN'","'CLERK'","'MANAGER'","'ANALYST'")
);
DEPTNO JOB EMPLOYEE_COUNT
---------- ---------- --------------
30 'SALESMAN' 4
30 'CLERK' 1
30 'MANAGER' 1
30 'ANALYST' 0
20 'SALESMAN' 0
20 'CLERK' 2
20 'MANAGER' 1
20 'ANALYST' 2
10 'SALESMAN' 0
10 'CLERK' 1
10 'MANAGER' 1
10 'ANALYST' 0
12 rows selected.
Objective - find count which year were most employees hired in
SQL> select empno,hiredate from emp;
EMPNO HIREDATE
---------- ---------
7369 17-DEC-80
7499 20-FEB-81
7521 22-FEB-81
7566 02-APR-81
7654 28-SEP-81
7698 01-MAY-81
7782 09-JUN-81
7788 19-APR-87
7839 17-NOV-81
7844 08-SEP-81
7876 23-MAY-87
7900 03-DEC-81
7902 03-DEC-81
7934 23-JAN-82
14 rows selected.
SQL>
select count(*), extract (year from hiredate) year_hired from emp
group by extract (year from hiredate);
COUNT(*) YEAR_HIRED
---------- ----------
1 1982
2 1987
1 1980
10 1981
Posted in Oracle 11g | Tagged: 11G, PIVOT, UNPIVOT | Leave a Comment »
Posted by Gavin Soorma on June 24, 2009
11g New Feature – Standby Database creation from active database without having to take a backup ….
Note the command output – APEXDG is created by copying the datafiles directly from an open database APEX without having to restore the backup from tape
RMAN> run {
allocate channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database for standby from active database
dorecover
nofilenamecheck;
}
Posted in Backup and Recovery, Oracle 11g | Tagged: 11g standby, active database, rman duplicate | Leave a Comment »
Posted by Arjun Raja on June 22, 2009
In 11g – Passwords are case sensitive but this can be controlled by an init.ora parameter -
SQL> show parameter SEC_CASE_SENSITIVE_LOGON ;
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE
SQL> create user arjun identified by ARJUN; – Password in Upper Case.
User created.
SQL> grant create session to arjun;
Grant succeeded.
SQL> conn arjun/arjun; – Tried to connect with lower case password – FAILS.
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> exit
apex:/u01/oracle> sqlplus “sys as sysdba”
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn arjun/ARJUN – CONNECTS –
Connected.
SQL>exit
Sqlplus “sys as sysdba”
drop user arjun cascade;
Now alter the parameter to FALSE – so case sensitive passwords are not required.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> create user arjun identified by ARJUN;
User created.
SQL> grant create session to arjun;
Grant succeeded.
SQL> conn arjun/arjun; – Connects with lower case password although password at time of creation was in UPPER case.
Posted in Oracle 11g | Tagged: 11G, case sensitive password, password case sensitive | Leave a Comment »
Posted by Arjun Raja on June 22, 2009
Active Standby :
PRIMARY DB- APEX
STANDBY DB- APEXDG
Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:
1. Stop the managed recovery process on STANDBY database :
In APEXDG ( Standby database )
SQL > alter database recover managed standby database cancel;
Database altered.
2. Open the APEXDG – standby database as read-only:
SQL > alter database open read only;
Database altered.
3. Restart the managed recovery process ON STANDBY DATABASE :
SQL > alter database recover managed standby database using current logfile disconnect;
Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.
4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:
In PRIMARY database –
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;
no rows selected
On STANDBY
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;
no rows selected
In APEX database – PRIMARY
SQL > create table arjun (col1 number);
5. After a few seconds, check the existence of the table in the standby database:
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;
TABLE_NAME
——————————
ARJUN
The table will be present !!
The standby database is open in read-only mode, but it is still applying the logs from the primary database.
This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.
6. To confirm the application of redo logs on the primary database, first switch the log file:
alter system switch logfile;
7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:
$ adrci
show alert -tail –f
kcrrvslf: active RFS archival for log 6 thread 1 sequence 15694
RFS[7]: Successfully opened standby log 5: ‘/u02/oradata/apexdg/standby02.log’
Media Recovery Log /u02/oradata/apexdg/arch/APEXDG/archivelog/2008_12_05/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15695 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u02/oradata/apexdg/standby02.log
Conclusion
The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database.
With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database.
Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably.
And because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile.
Posted in Oracle 11g | Tagged: 11G, ACTIVE STANDBY | Leave a Comment »
