Data access methods using data pump utility
Data access methods using data pump utility
A Data Pump export and import job can access the table data in either of the two ways:
Direct-Path :
This access uses the Direct Path API
Direct-path exports and imports lead to improved performance, since the direct path internal stream format is the same as the format stored in Oracle dump files. This leads to reduced need for data conversion
Oracle always uses direct-path method to load or unload data except under some conditions:
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns
It is mandatory to use direct-path access methods under the following conditions :
Table has any LONG data
External-tables :
This feature lets oracle read data from and write data to operating system files that lie outside the database.
Direct-path doesn’t support intra-partition parallelism and it is imperative to use external tables in such cases.
Under following conditions only external tables can be used :
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns
Note :
The data file format is identical in direct-path access method and external tables. So we can easily export data with one method and import data with other method.
Oracle 10g RAC databases on Linux Red Hat 3.0 single server
Oracle 10g RAC databases on Linux Red Hat 3.0 single server
Discover easy and very affordable way to install and configure a fully functional Oracle 10g RAC database on one only un-expensive single node running Linux Red Hat.
If you are serious experienced Oracle DBA interested in setting an High Availability Oracle 10g RAC database testing environment than this paper shall give you the insides to accomplish it step by step using only a single Red Hat 3.0 server without additional investment in hardware and time to setup and maintain a second server to form a cluster and expensive shared storage.
Background / Overview
With the evolution of the Oracle High Availability solutions, now-a-days becoming the key to provide the desired zero downtime and nearzero time to repair functionality essential for the business continuity, and their relatively high cost and demand for a high end hardware platforms face the Seniors DBA toward the greatest challenge to look for an inexpensive solutions to practice, test and prototype their architecture and solutions. This article addresses the needs by providing a working Oracle 10g RAC database architecture based entirely on a single server with four Network Interface Cards (NIC) running a Linux Red Hat 3.0. The article shall cover in great details the prerequisites such as preliminary Linux Red Hat 3.0 setup, required by Oracle preliminary kernel parameters setup, preliminary NIC configuration for RAC setup. The paper shall place an emphasis on the installation of the Cluster Ready Services and Oracle RDBMS at a screen level and last but not least shall finish with the database creation with dbca and manual adjustments required for the second, third, n-th instance. The following article is a step-by-step guide (Cookbook) with tips and information for installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3. The primary objective of this article in it’s current version is to demonstrate a quick installation of Oracle 10g with RAC on RH AS 3.0 using a single server.
Step 1: Preliminary Linux Red Hat 3.0 Setup
Here I’ll describe the process of installation and configuration of the Linux Red Hat server on a PC. The configuration used is RAM 1024MB, two 120GB disks and four (4) Netwok Interface Cards (NIC). Install Red Hat 3.0 on the server using the Install Everything option to ensure that you have all of the necessary components. Install the kernel-unsupported-2.4.21-4.EL.i686.rpm from the Red Hat Disk3 on the server having device support for many nonstandard devices. Add the following lines to the /etc/modules.conf on the server.
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
Create Linux group dba and users for the Oracle Cluster Ready Services and Oracle RDBMS respectively oracle10crs and oracle10 members of the dba group as in the /etc/passwd and /etc/group files. Set user equivalence for the users in /etc/hosts.equiv or .rhosts as specified in the sample /etc/hosts.equiv. Create /var/opt/oracle directory with read write permissions for the Oracle users. Create the directories for the $ORACLE_BASE with respective permissions. Setup the profiles for the users as in the example of ~oracle10crs/.bash_profile and ~oracle10/.bash_profile to include the variables for the $ORACLE_SID, $ORACLE_HOME, $ORACLE_BASE and $PATH to include the $ORACLE_HOME/bin.
Step 2: Preliminary kernel parameter setup
Set the minimal required kernel parameters in the /etc/sysctl.conf as in the sample provided. Reboot the server for the completion of the preliminary setup of the Linux and Kernel to take effect.
Step 3: Network Configuration
$ redhat-config-network
Do not
configure the network alias names for the public VIP. This will be done by Oracle’s Virtual Internet Protocol Configuration Assistant (VIPCA). When the network configuration is done, it is important to make sure that the name of the public RAC nodes is displayed when you execute the following command:
$ hostname
pub1
You can verify the new configured NICs by running the command:
/sbin/ifconfig -a
Look at the sample /etc/hosts file.
Step 4: Cluster Ready Services Installation
To install CRS, insert the “Cluster Ready Services (CRS) R1 (10.1.0.2)” CD and as root issue the following commands:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– Inventory directory and credentials:
Click Next
– Unix Group Name: Use “dba”.
– Root Script Window: Open another window, login as root, and run /tmp/orainstRoot.sh
on the node where you launched runInstaller.
After you’ve run the script, click Continue.
– File Locations: I used the recommended default values:
Destination Name: OraCr10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/crs_1
Click Next
– Language Selection: Click Next
– Cluster Configuration:
Cluster Name: crs
Cluster Nodes: Public Node Name: pub1 Private Node Name: priv1
Click Next
– Private Interconnect Enforcement:
Interface Name: eth0 Subnet: 192.168.1.113 Interface Type: Public
Interface Name: eth1 Subnet: 192.168.2.112 Interface Type: Public
Interface Name: eth2 Subnet: 192.168.1.111 Interface Type: Private
Interface Name: eth3 Subnet: 192.168.2.110 Interface Type: Private
NOTE: See the output of the ifconfig –a
Click Next
– Oracle Cluster Registry:
OCR Location: /u02/oradata/ OCRFile
Click Next
– Voting Disk: Voting disk file name: /u02/oradata/ CSSFile
Click Next
– Root Script Window:
Open another window, login as root, and execute
/u01/app/oracle/oraInventory/orainstRoot.sh on ALL RAC Nodes!
NOTE: For any reason Oracle does not create the log directory
“/a02/app/oracle/product/10.1.0/crs_1/log”. If there are problems with
CRS, it will create log files in this directory, but only if it exists.
Therefore make sure to create this directory as oracle:
oracle$ mkdir /a02/app/oracle/product/10.1.0/crs_1/log
After you’ve run the script, click Continue.
– Setup Privileges Script Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/crs_1/root.sh on the server !
Note that his can take a while. On the last RAC node, the output of the
script was as follows:
…
CSS is active on these nodes.
Pub1
CSS is active on all nodes.
Oracle CRS stack installed and running under init(1M)
Click OK
– Summary: Click Install
– When installation is completed, click Exit.
One way to verify the CRS installation is to display all the nodes where CRS was installed:
oracle$ /a02/app/oracle/product/10.1.0/crs_1/bin/olsnodes -n
pub 1
Step 5: Installing Oracle Database 10g Software R1 (10.1.0.2) with Real Application Clusters (RAC)
To install the RAC Database software, insert the Oracle Database 10g R1 (10.1.0.2) CD “), and mount it on pub1:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– File Locations: I used the default values:
Destination Name: raDb10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/db_2
Click Next.
– Hardware Cluster Installation Mode:
Select “Cluster Installation”
Click “Select All” to select all servers: pub1
Click Next
– Installation Type:
I selected “Enterprise Edition”.
Click Next.
– Product-specific Prerequisite Checks:
Make sure that the status of each Check is set to “Succeeded”.
Click Next
– Database Configuration:
I selected “Do not create a starter database” since we have to create the
database with dbca. Oracle Database 10g R1 (10.1) OUI will not be able to
discover disks that are marked as Linux ASMLib. For more information, see
http://otn.oracle.com/tech/linux/asmlib/install.html#10gr1
Click Next
– Summary: Click Install
– Setup Privileges Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/db_1/root.sh the server !
NOTE: Make also sure that X is relinked to your local desktop since this
script will launch the “VIP Configuration Assistant” tool which is a
GUI based utility!
VIP Configuration Assistant Tool:
(This Assistant tool will come up only once when root.sh is executed the
first time in your RAC cluster)
– Welcome Click Next
– Network Interfaces: I selected both interfaces, eth0 and eth1.
Click Next
– Virtual IPs for cluster notes:
(for the alias names and IP address, see Setting Up the /etc/hosts File)
Node Name: pub1
IP Alias Name: vip1
IP address: 192.168.1.13
Subnet Mask: 255.255.255.0
Click Next
– Summary: Click Finish
– Configuration Assistant Progress Dialog:
Click OK after configuration is complete.
– Configuration Results:
Click Exit
Click OK to close the Setup Privilege Window.
– End of Installation:
Click Exit
Step 6: Installing Oracle Database 10g with Real Application Cluster (RAC)
To install the RAC database and the instances on the server run runInstaller on pub1.
Login as oracle10 and before you execute dbca, make sure that $ORACLE_HOME and $PATH are set:
oracle10$ . ~oracle10/.bash_profile
oracle10$ dbca
– Welcome Screen: Select “Oracle Real Application Clusters database”
Click Next
– Operations: Select “Create Database”
Click Next
– Node Selection: Click “Select All”. Make sure pub1 is selected!
Click Next
– Database Templates: I selected “General Purpose”.
Click Next
– Database Identification:
Global Database Name: orcl
SID Prefix: orcl
Click Next
– Management Option: Selected “Use Database Control for Database Management”.
Click Next
– Database Credentials:
I selected “Use the Same Password for All Accounts”. Enter the password and
make sure the password does not start with a digit number.
Click Next
– Storage Options: I selected “Cluster File System
Click Next
– Database File Location: Select ” Use common Location for All Database Files”
Enter the Database File Location “/a02/oradata”
Click Next
– Recovery Configuration:
Select the “Specify Flash Recovery Area” with defaults and “Enable Archiving”.
Click Next
– Database Content: Select Sample Schemas but not Custom Scripts.
Click Next
– Database Services: Click “Add” and enter a Service Name: I entered “orcltest”.
I selected TAF Policy “Basic”.
Click Next
– Initialization Parameters:
Change settings as needed.
Click Next
– Database Storage: Change settings as needed.
Click Next
– Creation Options: Check “Create Database”
Click Finish
– Summary: Click OK
Now the database is being created.
Your RAC cluster should now be up and running. To verify, try to connect to each instance from one of the RAC nodes:
$ sqlplus system@orcl1
After you connected to an instance, enter the following SQL command to verify your connection:
SQL> select instance_name from v$instance;
Step 7: Create a second, third, n-th instance
Create a parameter file from the service parameter file and add the parameters above. Modify the parameter file to include the parameters for second instance. For second instance add
orcl2.instance_number=2
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS2′
Restart orcl1 instance with the above parameter file. Create an undo tablespace UNDOTBS2 with the same specifications as UNDOTBS1. Create a redo log thread 2 and enable it using again instance orcl1.
create undo tablespace undotbs2 datafile ‘/a02/oradata/orcl/undotbs02.dbf’ size 31465472;
alter database add logfile thread 2
‘/a02/oradata/orcl/redo11.log’ size 10486272, ‘/a02/oradata/orcl/redo12.log’ size 10486272,
‘/a02/oradata/orcl/redo13.log’ size 10486272;
alter database enable thread 2;
Create a second telnet session loged in as oracle10 and set ORACLE__SID to orcl2. Copy the password file from the first instance to password file for the second instance. Startup the orcl2 instance. Create srvctl configuration or the second instance.
srvctl add instance -d orcl -i orcl2 -n pub1
srvctl status database -d orcl
srvctl start instance -d orcl -i orcl1
srvctl status database -d orcl
Use same approach for a third, fourth , n-th instance on the same server.
Step 8: Transparent Application Failover
Sample script testtaf.sql is used to test the Transparent application Failover. The NET tnsnames.ora and listener.ra are also in the content section. Connect using the orcltaf tnsnames alias and run he script testtaf.sql. From another telnet session kill the pmon of the database where the previous session is connected to and observe the output a select statement application failover below.
[oracle10@pub1 admin]$ sqlplus system/sys1@orcltaf
SQL*Plus: Release 10.1.0.2.0 – Production on Fri Sep 24 18:59:01 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> @/tmp/testtaf
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
250 74 SELECT BASIC NO
INSTANCE_NAME
—————-
orcl1
COUNT(*)
———-
292188
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
251 9 SELECT BASIC YES
INSTANCE_NAME
—————-
orcl2
COUNT(*)
———-
292188
Conclusion
Having implemented the solution described above the reader probably have noticed that has under his/hers belt a fully functional very cost effective High Availability Oracle 10g RAC database architecture for testing, prototyping and prove of concept. The article s extremely suitable for in-house training, consultants sand desk labs and class trainings which can not afford expensive clustered hardware solutions. Once a solution is piloted and configurations tested next essential step will be to build a real test and production environments resulting in considerable reduction of the time, cost solution reusability and much faster learning curve and plenty of hands on knowledge in the course of rolling over into production versus a full fledged prototyping requiring the pairs of servers for clusters and shared disk storage.
At the end of the file, after the text of the article, please supply the following:
- Content of the /etc/sysctl.conf, /etc/hotsts, /etc/hosts.equiv, /etc/passwd, /etc/group .bash_profile, listener.ora tnsnames.ora, testtaf.sql output from ifconfig files for the root, oracle10crs and oracle10 users.
Initoracl.ora
*.background_dump_dest=’/a02/app/oracle/admin/orcl/bdump’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’10.1.0.2.0′
*.control_files=’/a02/oradata/orcl/control01.ctl’,’/a02/oradata/orcl/control02.ctl’,’/a02/oradata/orcl/control03.ctl’
*.core_dump_dest=’/a02/app/oracle/admin/orcl/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/a02/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
orcl1.instance_number=1
orcl2.instance_number=2
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=250
*.remote_listener=’LISTENERS_ORCL’
*.remote_login_passwordfile=’exclusive’
*.shared_pool_size=99614720
*.sort_area_size=65536
orcl1.thread=1
orcl2.thread=2
*.undo_management=’AUTO’
orcl1.undo_tablespace=’UNDOTBS1′
orcl2.undo_tablespace=’UNDOTBS2′
*.user_dump_dest=’/a02/app/oracle/admin/orcl/udump’
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
/etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.113 pub1 zeus
192.168.1.112 pub2
192.168.1.111 priv1
192.168.1.110 priv2
192.168.1.13 vip1
192.168.1.12 vip2
ipconfig -a
eth0 Link encap:Ethernet HWaddr 00:C0:4F:36:E1:E7
inet addr:192.168.1.113 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:1428 dropped:0 overruns:1 frame:2079
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:960 (960.0 b)
Interrupt:11 Base address:0xcc00
eth1 Link encap:Ethernet HWaddr 00:30:BD:71:EE:4A
inet addr:192.168.1.112 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:5 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:300 (300.0 b) TX bytes:240 (240.0 b)
Interrupt:9 Base address:0x9c00
eth2 Link encap:Ethernet HWaddr 00:30:BD:72:04:B8
inet addr:192.168.1.111 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:38 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3549 (3.4 Kb) TX bytes:240 (240.0 b)
Interrupt:11 Base address:0xb800
eth3 Link encap:Ethernet HWaddr 00:30:BD:72:04:0F
inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:23 errors:0 dropped:0 overruns:0 frame:0
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2649 (2.5 Kb) TX bytes:960 (960.0 b)
Interrupt:9 Base address:0xd400
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:2903 errors:0 dropped:0 overruns:0 frame:0
TX packets:2903 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:154137 (150.5 Kb) TX bytes:154137 (150.5 Kb)
/etc/hosts.equiv
+pub1 oracle10
+pub2 oracle10
+priv1 oracle10
+priv2 oracle10
+pub1 oracle10crs
+pub2 oracle10crs
+priv1 oracle10crs
+priv2 oracle10crs
/etc/passwd
radvd:x:75:75:radvd user:/:/sbin/nologin
oracle10crs:x:500:501::/home/oracle10crs:/bin/bash
oracle10:x:501:501::/home/oracle10:/bin/bashswd
/etc/group
oracle10crs:x:500:
dba:x:501:oracle10crs,oracle10
/etc/modules.conf
alias eth0 3c59x
alias eth1 8139too
alias eth2 8139too
alias eth3 8139too
alias usb-controller usb-uhci
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
root’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
BASH_ENV=$HOME/.bashrc
USERNAME=”root”
export LD_ASSUME_KERNEL=2.4.19
export USERNAME BASH_ENV PATH
oracle10crs’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/crs_2
PATH=$PATH:$HOME/bin
export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
oracle10’s .bash_oprofile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/db_2
export LD_ASSUME_KERNEL=2.4.19
PATH=$PATH:$HOME/bin
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
[oracle10@pub1 oracle10]$
Testtaf.sql
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
Listener.ora
# listener.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_PUB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_PUB1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl1)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl2)
)
)
tnsnames.ora
# tnsnames.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENERS_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcltest)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLTAF =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(GLOBAL_NAME = ORCL)
(FAILOVER_MODE =
(TYPE=SELECT)
(METHOD=BASIC)
(BACKUP=ORCL)
)
)
)
- Links to more information about the technology at otn.oracle.com
Guenadi Nedkov Jilevski (gjilevski@spearnet.net) is a [Senior Infrastructure ] at Perot Systems, (http://www.ps.com) a [Lake Success, NY] based [global provider of technology-based business solutions].
New features in 11g for DBA
New features in 11g for DBA
This post will discuss about new features of oracle database 11g administration. Every Oracle database major release comes with new features and step by step some new features are discussed here briefly that comes with Oracle database 11g.
1)Improved Automatic Memory Management:
In 10g in case of automatic memory management we knew that if SGA_TARGET is set then all dynamic SGA components will be automatically managed and if PGA_AGGREGATE_TARGET is set then pga components will be automatically managed. But starting with oracle 11g both pga and sga memory can be managed automatically by setting only MEMORY_TARGET parameter. However you can still assign minimum sizes individually for the SGA and instance PGA.
2)New fault diagnosability infrastructure:
The fault diagnosability infrastructure are for easy diagnosis for any critical problems. After a problem has been occurred it gather necessary information against the problem and then an Incident Packaging Service packages all diagnostic data for a problem into a zip file so that you can transmit that to Oracle Support and thus reduce time to gather information about the problems.
3)Invisible Indexes:
Now in 11g you can make an index as invisible state instead of dropping it or mark them unusable. It is very effective in order to test performance of a query to see whether removing index will improve the performance. By default an invisible index is ignored by an optimizer but is maintained during DML statements. You can change the initialization parameter to cause the optimizer to use invisible indexes.
4)Virtual Columns:
In tables you can now include virtual columns. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.
5)Enhanced Security:
In 11g now by default your password is case sensitive. So you can use mixed case password. Though by editing initialization parameter you can make password case-insensitive.
6)Database resident connection pooling:
Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling significantly reduces database resources needed to support a large number of client connections and thereby boosting the scalability of both middle-tier and database tiers.
7)Tablespace level Encryption:
You can now encrypt a tablespace that contains sensitive data. Tablespace encryption is completely transparent to your application and when you encrypt a tablespace, all tablespace blocks are encrypted also.
8)Invalidation of dependent schema objects are greatly reduced:
Before 11g if we make any changes in the main object then dependent object become invalid. In 11g invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced and thus increasing application availability during maintenance, upgrades, and online table redefinition.
9)Enhanced automated maintenance task infrastructure:
Though in Oracle 10g you had automatic optimizer statistics gathering, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. In 11g you can now exercise finer control over automated maintenance task scheduling and those task ran automatically.
10)Transparent data encryption enhanced:
Now in 11g using the transparent data encryption feature you can now encrypt SecureFile LOBs.
11)Table compression now supported in OLTP environments:
On compressed tables you can now run DML operations and adding or dropping columns of a compressed tables.
12)Query result cache in sga:
Now query results can be cached in sga memory in the result cache. The database can then use cached results to answer future executions of that query. It is also true for query fragments. As because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
13)Default automatic undo management mode:
In 10g if you create database manually then the parameter UNDO_MANAGEMENT became MANUAL as by default UNDO_MANAGEMENT is manual in oracle 10g. However if you created database by dbca it makes the parameter AUTO. In 11g by default UNDO_MANAGEMENT is set to AUTO. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.
14)Enhancements to Oracle Database Resource Manager:
15)Enhancements to Oracle Scheduler:
16)Enhanced online index creation and rebuild:
Before oracle 11g we can do online index creation and rebuild operation but it required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. The DML blocking lock can lock other DMLs and thus performance problem can arise while doing online index creation and rebuild operation. In 11g this lock is no longer required, making these online index operations fully transparent.
17)Tables with materialized view logs can now be redefine online:
Prior to 11g online redefinition of a table that have materialized view logs were not possible. But in 11g tables with materialized view logs can now be redefined online. Materialized view logs are now considered one of the dependent objects. So they can be copied to interim table using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.
18)Facility to make a table read only mode:
Now to avoid any update or insert or delete on a table you don’t need to make whole tablespace in read only mode. In 11g with ALTER TABLE statement you can place a single table into read only mode.
19)DDL commands can wait for locks:
Prior to 11g if you have any DML operation running against the table then you can’t do DDL commands on the table and instantly ORA-54 message would return as demonstrated in ORA-54 Resource Busy. But in 11g you can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails. So we might not need wait for prompting further command to test whether we can run DDL commands. Oracle will check and if no DML operations DDL commands will be performed if it get resource free within DDL_LOCK_TIMEOUT time.
20)Enhancements to initialization parameter management:
There are several enhancement in the initialization parameters.
Upon startup, values of initialization parameters are written to the alert log in
such a way as to make it easy to copy and paste them to create a new PFILE.
The name and path of the PFILE or SPFILE used to start the instance is written
to the alert log so we need not to search which one is used to startup database.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS