Simple Streams
Oracle Streams in five minutes
There are many ways to implement replication in Oracle environment. Materialized views, GoldenGate, CDC and since Oracle 9i, Oracle Streams. Streams ain’t the most brilliant invention in my mind, as it has had plenty of bugs. But it’s for free. Here I run go through simple configuration, how to setup Streams replication between two databases. What I replicate, is HR schema, from example tablespace. It comes with the database if one has installed it with the examples. Environment is
nostromo, primary database ORCL, OEL Linux, Oracle 11.2.0.1
narcissos, secondary database TOKADB, OEL Linux, Oracle 11.2.0.1
Difference between hosts is, that TOKADB does not have example schema installed.
Now, let’s first set up Streams at primary site.
Adding supplemental log data means additional key information is added to redo stream, which will be helpful determining duplicates. Force Logging means nologging -clauses will have no effect. Otherwise such operations would not be reflected at secondary site as they would be not in the redo-stream.
connect / as sysdbaalter database add supplemental log data (all) columns;alter database force logging;
It’s recommended practice to create separate tablespace for logminer, which mines the redo for the capture process. I create own tablespace for streams administrator as well instead of using system default user tablespace. Dbms_logmnr_d.set_tablespace procedure call will set new tablespace for logminer.
create tablespace STREAMS_ADM datafile '/u01/app/oracle/oradata/ORCL/streams_adm.dbf' size 100M autoextend on maxsize 2000M;create tablespace LOGMINER_TS datafile '/u01/app/oracle/oradata/ORCL/logminer_ts.dbf' size 100M autoextend on maxsize 2000M;begin dbms_logmnr_d.set_tablespace( NEW_TABLESPACE => 'LOGMINER_TS'); end; /
By default, there is no user given streams_admin privilege, so create new user that has the privilege.
create user strm_admin identified by oracle
default tablespace STREAMS_ADM
temporary tablespace TEMP
quota unlimited on STREAMS_ADM;Also, give some additional rights (dba, imp/exp full database)
grant dba, exp_full_database, imp_full_database to strm_admin;Then create strm_admin user, which will be my streams administrator. Do not use system built-in accounts, like sys user to do this. Calling dbms_streams.grant_admin_privilege procedure, one is given streams administrator privileges.
begin
dbms_streams_auth.grant_admin_privilege(
GRANTEE => 'STRM_ADMIN',
GRANT_PRIVILEGES => TRUE);
end;
/Add tnsnames entry (by default at $ORACLE_HOME/network/admin or in place pointed by $TNS_ADMIN variable) for the destination. So your local naming configuration should have your local as well remote entries. This is similar on both sites, so copy it over to secondary site.
TOKADB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = narcissos)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TOKADB)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nostromo)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
Then, add database link from primary site to the secondary site. You can check by ‘select sysdate from dual@TOKADB’ for example to make sure it is set up correctly.
connect strm_admin/oracle create database link TOKADB connect to strm_admin identified by oracle using 'TOKADB';
Then repeat setting up on destination site.Only changes to previous steps, is the adding of example tablespace which is only on primary, and database link direction from secondary->primary. I reprint it revised here for clarity:
connect / as sysdba alter database add supplemental log data (all) columns; alter database force logging; create tablespace STREAMS_ADM datafile '/u01/app/oracle/oradata/TOKADB/streams_adm.dbf' size 100M autoextend on maxsize 2000M; create tablespace LOGMINER_TS datafile '/u01/app/oracle/oradata/TOKADB/logminer_ts.dbf' size 100M autoextend on maxsize 2000M; create tablespace EXAMPLE datafile '/u01/app/oracle/oradata/TOKADB/example01.dbf' size 100M autoextend on maxsize unlimited; begin dbms_logmnr_d.set_tablespace( NEW_TABLESPACE => 'LOGMINER_TS'); end; create user strm_admin identified by oracle default tablespace STREAMS_ADM temporary tablespace TEMP quota unlimited on STREAMS_ADM; grant dba, exp_full_database, imp_full_database to strm_admin; begin dbms_streams_auth.grant_admin_privilege( GRANTEE => 'STRM_ADMIN', GRANT_PRIVILEGES => TRUE); end; connect strm_admin/oracle create database link ORCL connect to strm_admin identified by oracle using 'ORCL';
Ok, then we are good to go. Next set call maintain_schemas at primary site. HR schema is relatively small, so we can use expdp over network link to dump it. Default directory object is used for the dump. Take a look into the script str_setup.sql which the procedure creates. It gives you insight what happens in the setup.
begin dbms_streams_adm.maintain_schemas( SCHEMA_NAMES =>'HR', SOURCE_DIRECTORY_OBJECT =>'DATA_PUMP_DIR', DESTINATION_DIRECTORY_OBJECT=>'DATA_PUMP_DIR', SOURCE_DATABASE => 'ORCL', DESTINATION_DATABASE => 'TOKADB', PERFORM_ACTIONS => true, SCRIPT_NAME =>'str_setup.sql', DUMP_FILE_NAME=>'expset.dmp', SCRIPT_DIRECTORY_OBJECT =>'DATA_PUMP_DIR', CAPTURE_NAME=>'CAP_ORCL', CAPTURE_QUEUE_TABLE=>'CAP_TABLE', CAPTURE_QUEUE_NAME=>'CAP_QUE', CAPTURE_QUEUE_USER=>'STRM_ADMIN', PROPAGATION_NAME=>'PROP_ORCL', APPLY_NAME=>'APP_TOKADB', APPLY_QUEUE_TABLE=>'APP_TABLE', APPLY_QUEUE_NAME=>'APP_QUE', APPLY_QUEUE_USER=>'STRM_ADMIN', LOG_FILE=>'exp.log', BI_DIRECTIONAL=>FALSE, INCLUDE_DDL=>FALSE, INSTANTIATION=>DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK); end; /
It will set up relevant secure queues, dump the data over the network link, and set scn at destination site for the imported tables. It will also start relevant capture, propagation and apply processes.
Let’s test it. Find out last employee_id from hr.employees table. Then, increase the value with one and insert a new one and commit changes.
SQL> select max(employee_id) from hr.employees;
MAX(EMPLOYEE_ID)
----------------
206
SQL> insert into hr.employees(employee_id,last_name,email,hire_date,job_id)
values(207,'gates','BGATES',to_date('11-01-95','DD-MM-YYYY'), 'ST_CLERK');
1 row created.
SQL> commit;
Commit complete.
Now, go to the destination site, and see what is there.
SQL> select * from hr.employees where employee_id=207; EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- ------------------------- EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY ------------------------- -------------------- --------- -------- ------- COMMISSION_PCT MANAGER_ID DEPARTMENT_ID -------------- ---------- ------------- 207 gates BGATES 11-JAN-95 ST_CLERK
there it goes. There is much more to streams, downstream capture, customized handlers and so forth, but this simple example should give you an idea, what Oracle Streams can do for you. Hopefully you find it helpful.
Upgrading OS in RAC environment
There are not many articles written about OS upgrades in mission critical database environments, so I try to enlighten the area. When there is Oracle RAC involved, things can get bit easier, and minimize and/or remove downtime required to upgrade the operating system. RAC one-node is also one possible helper here, as well as replication (streams, goldengate) or Data Guard. Yet simplest solutions with least moving parts are usually the best. I present here a method that I’ve used myself to upgrade OS on several RAC production environments.
What is meant with upgrade here, is what most sysadmins prefer. Updates are done against live system, while upgrades from major version to another, are mostly done by erasing old system entirely and installing new from scratch.
Here I have two environments, hosts tiku and taku – running Oracle Enterprise Linux 4. There is ASM instances configured. There is no multipathing defined nor extra network interfaces for failover/load-balancing purposes defined for simplicity. Remember those, as they will (read: they should) be in your production environment.
Oracle version used is 10.2.0.5.0 along the way. Database version will stay the same. Operating system is initially Red Hat Enterprise Linux AS release 4 (Nahant Update 8). I will update it to Oracle Enterprise Linux 5 update 5.
[oracle@taku ~]$ uname -a Linux taku 2.6.9-89.0.0.0.1.EL #1 Tue May 19 05:21:57 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux [oracle@tiku ~]$ uname -a Linux tiku 2.6.9-89.0.0.0.1.EL #1 Tue May 19 05:21:57 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux [oracle@taku ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....C1.inst application ONLINE ONLINE tiku ora....C2.inst application ONLINE ONLINE taku ora.RACDB.db application ONLINE ONLINE tiku ora....SM2.asm application ONLINE ONLINE taku ora....KU.lsnr application ONLINE ONLINE taku ora.taku.gsd application ONLINE ONLINE taku ora.taku.ons application ONLINE ONLINE taku ora.taku.vip application ONLINE ONLINE taku ora....SM1.asm application ONLINE ONLINE tiku ora....KU.lsnr application ONLINE ONLINE tiku ora.tiku.gsd application ONLINE ONLINE tiku ora.tiku.ons application ONLINE ONLINE tiku ora.tiku.vip application ONLINE ONLINE tiku
If you haven’t already stopped everything in the first node to be upgraded, do it now.
[oracle@taku ~]$ srvctl stop nodeapps -n tiku [oracle@tiku ~]$ srvctl stop instance -d RACDB -i RAC1 [oracle@tiku ~]$ srvctl stop asm -n tiku
This is where be begin with. What I will do, is I take first node (tiku) away from the cluster. Install perfectly new operating system, prepare it to install a new RAC node, and then spread the cluster back to a re-established node. Then I repeat same steps to second node in mirrored fashion. Notice that if configurations differ between the hosts, you may want to back them up before cleaning up. In any case, you want to back up unique things to that node – at least this means the ssh -keyring (yet you can also just recreate it during the re-install). Most cases, you can use “surviving” nodes information to re-establish things over when you get new node back up. Notice that you will want to also get information like database service information somewhere, before erasing it. Last but not least, back up your OCR before beginning, just in case. Your autobackup of OCR files are by default under clusterware home/cdata/crs directory.
First, again – create backup of your OCR. Make it in the node, that won’t be erased (!). You must run the tool as superuser privileges.
[root@taku ~]# ocrconfig -export beforeupgrade -s online [root@taku ~]# ls -l beforeupgrade -rw-r--r-- 1 root root 85222 Feb 9 12:35 beforeupgrade
Then it’s time to remove references to the node that is going to get upgraded/re-installed. Basically, there is two ways to do this, either Oracle way, which is documented here (how to remove RAC node from a cluster 10gR2). The other option is to remove the node as it would have already been removed or decommissioned from the cluster, so all commands are run from the “surviving nodes”. It’s slightly faster method, and I describe it here. The steps are as follows :
Remove cluster public interface and interconnect from registry, first check if they are global or not. If they are global, you can skip this step and move on.
[oracle@tiku ~]$ oifcfg getif eth0 192.168.0.0 global public eth1 10.1.9.0 global cluster_interconnect
If they are not global, delete them manually from OCR by
[oracle@taku ~]$ oifcfg delif -node tiku
Next remove Oracle ONS (Oracle Notification Service) information for that node using racgons command. What you need to know, is the name of the node, and its remote port value from ONS configuration, as follows. They are then given as arguments to racgons command.
[root@taku ~]# cat /u01/app/oracle/crs/opmn/conf/ons.config localport=6100 remoteport=6200 loglevel=3 useocr=on [root@taku ~]# racgons remove_config tiku:6200 racgons: Existing key value on tiku = 6200. racgons: tiku:6200 removed from OCR.
Next we proceed to remove instance, asm and listener information about the first node (tiku) to be upgraded/erased. Let’s get rid of listener information from OCR for node tiku
[root@taku ~]# crs_unregister < /u01/app/oracle/crs/bin/crs_stat | grep -i listener|grep -i tiku|sed -e 's/^.*NAME=//'
Then remove the instance and ASM information
[root@taku ~]# srvctl remove instance -d RACDB -i RAC1 [root@taku ~]# srvctl remove asm -n tiku -i +ASM1
Find out the node numbers (which are numbered at the time of install) and delete node information from OCR
[root@taku ~]# /u01/app/oracle/crs/bin/olsnodes -n tiku 1 taku 2 [root@taku ~]# /u01/app/oracle/crs/install/rootdeletenode.sh tiku,1 CRS-0216: Could not stop resource 'ora.tiku.ons'. CRS-0216: Could not stop resource 'ora.tiku.vip'. CRS-0216: Could not stop resource 'ora.tiku.gsd'. CRS nodeapps are deleted successfully clscfg: EXISTING configuration version 3 detected. clscfg: version 3 is 10G Release 2. Successfully deleted 14 values from OCR. Key SYSTEM.css.interfaces.nodetiku marked for deletion is not there. Ignoring. Successfully deleted 5 keys from OCR. Node deletion operation successful. 'tiku,1' deleted successfully
As last step, update inventory information. Run once for each HOME using CRS binaries (Database, Clusterware and ASM if separate from DB), and notice to give node name(s) that will stay as argument.
[oracle@taku ~]$ /u01/app/oracle/crs/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/crs "CLUSTER_NODES=taku" CRS=TRUE Starting Oracle Universal Installer...No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed. The inventory pointer is located at /etc/oraInst.loc The inventory is located at /u01/app/oracle/oraInventory 'UpdateNodeList' was successful. [oracle@taku ~]$ /u01/app/oracle/crs/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/ "CLUSTER_NODES=taku" Starting Oracle Universal Installer... No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed. The inventory pointer is located at /etc/oraInst.loc The inventory is located at /u01/app/oracle/oraInventory 'UpdateNodeList' was successful.
Now it’s the time to install new operating system. RAC Database will continue to run on remainging node(s) and provide continous service to people and applications using it.
I won’t go into details of installing Oracle enterprise Linux here. Installation over base system will require installing necessary packages (in case of OEL, which oracle-validated can help a lot). Configuring groups, users and raw mappings, kernel parameters and so forth as well as installing ASM device drivers just like in the other nodes in this case. I don’t reprint it here.
First with clean system, set up user-equivalency, copying public keys to all nodes, so oracle user can use ssh between the nodes without password authentication.
[oracle@tiku ~]$ cd /home/oracle [oracle@tiku ~]$ mkdir .ssh [oracle@tiku ~]$ chmod 700 .ssh [oracle@tiku ~]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/oracle/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/oracle/.ssh/id_rsa. Your public key has been saved in /home/oracle/.ssh/id_rsa.pub. The key fingerprint is: 26:74:68:71:7e:df:cb:de:64:78:5e:25:c4:90:95:55 oracle@tiku [oracle@tiku ~]$ ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/home/oracle/.ssh/id_dsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/oracle/.ssh/id_dsa. Your public key has been saved in /home/oracle/.ssh/id_dsa.pub. The key fingerprint is: 8c:6d:d5:25:9c:3c:7c:3a:3a:4f:f9:b2:b7:e7:b0:b8 oracle@tiku [oracle@tiku ~]$ scp oracle@taku:/home/oracle/.ssh/authorized_keys ~/.ssh/authorized_keys oracle@taku's password: authorized_keys 100% 8670 8.5KB/s 00:00 [oracle@tiku ~]$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys [oracle@tiku ~]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys [oracle@tiku ~]$ scp ~/.ssh/authorized_keys oracle@taku:/home/oracle/.ssh/
authorized_keys 100% 8670 8.5KB/s 00:00
[oracle@tiku ~]$ ssh localhost
The authenticity of host ‘localhost (127.0.0.1)’ can’t be established.
RSA key fingerprint is 37:d7:70:24:ce:08:f6:88:5e:0c:1c:32:93:18:60:0c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘localhost’ (RSA) to the list of known hosts.
[oracle@tiku ~]$ ssh taku
RSA key fingerprint is cc:8b:27:8a:85:5f:8a:1b:9b:33:7c:be:a3:e3:61:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘taku,192.168.0.106′ (RSA) to the list of known hosts.
Then check that everything is ready for clusterware installation from the existing node
[oracle@taku home]$ /u01/app/oracle/crs/bin/cluvfy stage -pre crsinst -n tiku -r 10gR2
Correct any obvious errors before continuing. Create directory for Oracle installation in the new node, and give ownership for oracle user. Otherwise, the installation of new node will fail.
Now, as our /etc/hosts looks like this
[root@taku etc]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost 192.168.0.105 tiku 192.168.0.106 taku 10.1.9.201 tiku-priv 10.1.9.202 taku-priv 192.168.0.107 tiku-vip 192.168.0.108 taku-vip
We can see the names from here, and use them in addnodes.sh script to extend our Clusterware from existing node to another.
[oracle@taku bin]$ cd /u01/app/oracle/crs/install/oui/bin
[oracle@taku bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={tiku}"
"CLUSTER_NEW_PRIVATE_NODE_NAMES={tiku-priv}"
"CLUSTER_NEW_VIRTUAL_HOSTNAMES={tiku-vip}"
Starting Oracle Universal Installer...No pre-requisite checks found in
oraparam.ini, no system pre-requisite checks will be executed.
Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.
Performing tests to see whether nodes tiku are available
.................................................100% Done.
----------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /u01/app/oracle/crs
New Nodes
Space Requirements
New Nodes
tiku
/: Required 1.50GB : Available 14.02GB
Installed Products
Product Names
Oracle Clusterware 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
Oracle Required Support Files 32 bit 10.2.0.0.0
Oracle Clusterware RDBMS Files 10.2.0.1.0
Bali Share 1.1.18.0.0
Oracle Extended Windowing Toolkit 3.4.38.0.0
Buildtools Common Files 10.2.0.1.0
Oracle Notification Service 10.1.0.3.0
Oracle RAC Required Support Files-HAS 10.2.0.1.0
SQL*Plus Required Support Files 10.2.0.1.0
DBJAVA Required Support Files 10.2.0.1.0
XDK Required Support Files 10.2.0.1.0
Agent Required Support Files 10.2.0.1.0
Parser Generator Required Support Files 10.2.0.1.0
Precompiler Required Support Files 10.2.0.1.0
Platform Required Support Files 10.2.0.1.0
Oracle Core Required Support Files 10.2.0.1.0
Perl Interpreter 5.8.3.0.2
Oracle Ice Browser 5.2.3.6.0
Oracle JFC Extended Windowing Toolkit 4.2.33.0.0
SSL Required Support Files for InstantClient 10.2.0.1.0
Oracle Help For Java 4.2.6.1.0
Oracle Net Required Support Files 10.2.0.1.0
LDAP Required Support Files 10.2.0.1.0
RDBMS Required Support Files for Instant Client 10.2.0.1.0
RDBMS Required Support Files 10.2.0.1.0
Enterprise Manager Minimal Integration 10.2.0.1.0
Oracle Locale Builder 10.2.0.1.0
Oracle Globalization Support 10.2.0.1.0
HAS Common Files 10.2.0.1.0
Cluster Ready Services Files 10.2.0.1.0
Required Support Files 10.2.0.1.0
Sun JDK 1.4.2.14.05
Java Runtime Environment 1.4.2.14.0
Installer SDK Component 10.2.0.5.0
Oracle One-Off Patch Installer 10.2.0.4.2
Oracle Universal Installer 10.2.0.5.0
Bali Share 1.1.19.0.0
Buildtools Common Files 10.2.0.5.0
Parser Generator Required Support Files 10.2.0.5.0
Enterprise Manager Minimal Integration 10.2.0.5.0
Oracle Required Support Files 32 bit Patch 10.2.0.5.0
Platform Required Support Files 10.2.0.5.0
Oracle Core Required Support Files Patch 10.2.0.5.0
Oracle Globalization Support Patch 10.2.0.5.0
SSL Required Support Files for InstantClient Patch 10.2.0.5.0
SQL*Plus Required Support Files Patch 10.2.0.5.0
RDBMS Required Support Files Patch 10.2.0.5.0
RDBMS Required Support Files for Instant Client Patch 10.2.0.5.0
Oracle RAC Required Support Files-HAS Patch 10.2.0.5.0
Precompiler Required Support Files Patch 10.2.0.5.0
LDAP Required Support Files Patch 10.2.0.5.0
DBJAVA Required Support Files Patch 10.2.0.5.0
Agent Required Support Files Patch 10.2.0.5.0
XDK Required Support Files Patch 10.2.0.5.0
Oracle Net Required Support Files Patch 10.2.0.5.0
Oracle Clusterware RDBMS Files Patch 10.2.0.5.0
Cluster Ready Services Files Patch 10.2.0.5.0
HAS Common Files Patch 10.2.0.5.0
Oracle Clusterware Patch 10.2.0.5.0
Oracle Notification Service Patch 10.2.0.5.0
-----------------------------------------------------------------------------
Instantiating scripts for add node (Wednesday, February 9,
2011 4:16:16 PM EET)
100% Done.
Instantiation of add node scripts complete
Copying to remote nodes (Wednesday, February 9, 2011
4:16:19 PM EET)
Home copied to new nodes
Saving inventory on nodes (Wednesday, February 9, 2011
4:18:37 PM EET)
Save inventory complete.
WARNING:A new inventory has been created on one or more nodes in this session. However,
it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script at
'/u01/app/oracle/oraInventory/orainstRoot.sh'
with root privileges on nodes 'tiku'.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user in each cluster node.
#!/bin/sh
#Root script to run
/u01/app/oracle/oraInventory/orainstRoot.sh #On nodes tiku
/u01/app/oracle/crs/install/rootaddnode.sh #On nodes taku
/u01/app/oracle/crs/root.sh #On nodes tiku
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster node
The Cluster Node Addition of /u01/app/oracle/crs was successful.
Please check '/tmp/silentInstall.log' for more details.
[root@tiku ~]# /u01/app/oracle/oraInventory/orainstRoot.sh
Creating the Oracle inventory pointer file (/etc/oraInst.loc)
Changing permissions of /u01/app/oracle/oraInventory to 770.
Changing groupname of /u01/app/oracle/oraInventory to oinstall.
The execution of the script is complete
[root@taku bin]# /u01/app/oracle/crs/install/rootaddnode.sh
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Attempting to add 1 new nodes to the configuration
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 3: tiku tiku-priv tiku
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
/u01/app/oracle/crs/bin/srvctl add nodeapps -n tiku
-A tiku-vip/255.255.255.0/eth0 -o /u01/app/oracle/crs
[root@tiku ~]# /u01/app/oracle/crs/root.sh
WARNING: directory '/u01/app/oracle' is not owned by root
WARNING: directory '/u01/app' is not owned by root
WARNING: directory '/u01' is not owned by root
No value set for the CRS parameter CRS_OCR_LOCATIONS.
Using Values in paramfile.crs
Checking to see if Oracle CRS stack is already configured
OCR LOCATIONS = /dev/raw/raw1
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/u01/app/oracle' is not owned by root
WARNING: directory '/u01/app' is not owned by root
WARNING: directory '/u01' is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: tiku tiku-priv tiku
node 2: taku taku-priv taku
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
taku
tiku
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Ok, check that everything is in order with the new clusterware installation
[oracle@taku bin]$ /u01/app/oracle/crs/bin/cluvfy stage -post crsinst -n tiku
And then we will spread database binaries to the new node
[oracle@taku bin]$ cd /u01/app/oracle/product/10.2.0/db_1/oui/bin
[oracle@taku bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={tiku}"
Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system
pre-requisite checks will be executed.
Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.
Performing tests to see whether nodes tiku are available
................................................ 100% Done.
----------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /u01/app/oracle/product/10.2.0/db_1
New Nodes
Space Requirements
New Nodes
tiku
/: Required 1.94GB : Available 12.63GB
Installed Products
Product Names
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
Sun JDK extensions 10.1.2.0.0
Perl Interpreter 5.8.3.0.2
Bali Share 1.1.18.0.0
SSL Required Support Files for InstantClient 10.2.0.1.0
Oracle Display Fonts 9.0.2.0.0
Enterprise Manager Repository DB 10.2.0.1.0
Oracle Net Required Support Files 10.2.0.1.0
Enterprise Manager Agent DB 10.2.0.1.0
Enterprise Manager Common Files 10.2.0.1.0
Oracle Extended Windowing Toolkit 3.4.38.0.0
regexp 2.1.9.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle JFC Extended Windowing Toolkit 4.2.33.0.0
SQL*Plus Required Support Files 10.2.0.1.0
DBJAVA Required Support Files 10.2.0.1.0
Oracle OLAP RDBMS Files 10.2.0.1.0
Oracle OLAP API 10.2.0.1.0
XDK Required Support Files 10.2.0.1.0
RDBMS Required Support Files for Instant Client 10.2.0.1.0
RDBMS Required Support Files 10.2.0.1.0
Oracle RAC Required Support Files-HAS 10.2.0.1.0
Agent Required Support Files 10.2.0.1.0
Parser Generator Required Support Files 10.2.0.1.0
Sample Schema Data 10.2.0.1.0
Oracle Starter Database 10.2.0.1.0
Oracle Message Gateway Common Files 10.2.0.1.0
Oracle Database 10G 32 bit 10.2.0.1.0
XML Parser for Oracle JVM 10.2.0.1.0
Precompiler Required Support Files 10.2.0.1.0
XML Parser for Java 10.2.0.1.0
Oracle UIX 2.1.22.0.0
Oracle Help For Java 4.2.6.1.0
Enterprise Manager Baseline 10.2.0.1.0
Oracle Data Mining RDBMS Files 10.2.0.1.0
Oracle Database 10g interMedia Files 10.2.0.1.0
Oracle interMedia Java Advanced Imaging 10.2.0.1.0
SQLJ Runtime 10.2.0.1.0
Oracle interMedia Annotator 10.2.0.1.0
Oracle JDBC/OCI Instant Client 10.2.0.1.0
Oracle interMedia Locator RDBMS Files 10.2.0.1.0
Platform Required Support Files 10.2.0.1.0
Oracle Core Required Support Files 10.2.0.1.0
Database Workspace Manager 10.2.0.1.0
Oracle Containers for Java 10.2.0.1.0
Oracle Locale Builder 10.2.0.1.0
JDBC Common Files 10.2.0.1.0
Oracle Globalization Support 10.2.0.1.0
Oracle Code Editor 1.2.1.0.0I
Oracle Required Support Files 32 bit 10.2.0.0.0
Oracle interMedia Client Option 10.2.0.1.0
Oracle JDBC Thin Driver for JDK 1.2 10.2.0.1.0
Oracle JDBC Thin Driver for JDK 1.4 10.2.0.1.0
LDAP Required Support Files 10.2.0.1.0
Character Set Migration Utility 10.2.0.1.0
Required Support Files 10.2.0.1.0
PL/SQL Embedded Gateway 10.2.0.1.0
OLAP SQL Scripts 10.2.0.1.0
Database SQL Scripts 10.2.0.1.0
Oracle ODBC Driver 10.2.0.1.0
Secure Socket Layer 10.2.0.1.0
Precompiler Common Files 10.2.0.1.0
Oracle Database User Interface 2.2.13.0.0
Enterprise Manager Minimal Integration 10.2.0.1.0
Oracle Wallet Manager 10.2.0.1.0
Oracle Clusterware RDBMS Files 10.2.0.1.0
HAS Common Files 10.2.0.1.0
Oracle Help for the Web 1.1.10.0.0
Enterprise Manager plugin Common Files 10.2.0.1.0
iSQL*Plus 10.2.0.1.0
SQL*Plus 10.2.0.1.0
Oracle Recovery Manager 10.2.0.1.0
Oracle Java Client 10.2.0.1.0
Oracle LDAP administration 10.2.0.1.0
Installation Common Files 10.2.0.1.0
Buildtools Common Files 10.2.0.1.0
Enterprise Manager plugin Common Files 10.2.0.1.0 Beta
Oracle Notification Service 10.1.0.3.0
Assistant Common Files 10.2.0.1.0
Oracle Net 10.2.0.1.0
PL/SQL 10.2.0.1.0
Enterprise Manager Repository Core 10.2.0.1.0
Oracle Advanced Security 10.2.0.1.0
Generic Connectivity Common Files 10.2.0.1.0
Oracle Database Utilities 10.2.0.1.0
Oracle Text 10.2.0.1.0
Oracle XML Development Kit 10.2.0.1.0
Oracle interMedia Locator 10.2.0.1.0
Database Configuration and Upgrade Assistants 10.2.0.1.0
Oracle JVM 10.2.0.1.0
Enterprise Manager Agent Core 10.2.0.1.0
Oracle interMedia 10.2.0.1.0
Oracle Programmer 10.2.0.1.0
Oracle Call Interface (OCI) 10.2.0.1.0
Oracle Internet Directory Client 10.2.0.1.0
HAS Files for DB 10.2.0.1.0
Oracle Net Listener 10.2.0.1.0
Oracle Database 10g 10.2.0.1.0
Oracle Net Services 10.2.0.1.0
Oracle Enterprise Manager Console DB 10.2.0.1.0
Oracle OLAP 10.2.0.1.0
Oracle Spatial 10.2.0.1.0
Oracle Partitioning 10.2.0.1.0
Enterprise Edition Options 10.2.0.1.0
Sun JDK 1.4.2.14.05
Java Runtime Environment 1.4.2.14.0
Installer SDK Component 10.2.0.5.0
Oracle One-Off Patch Installer 10.2.0.4.2
Oracle Universal Installer 10.2.0.5.0
Oracle Configuration Manager 10.3.2.1.0
Oracle Real Application Testing 10.2.0.5.0
Bali Share 1.1.19.0.0
Oracle UIX 2.2.24.6.0
Buildtools Common Files 10.2.0.5.0
Parser Generator Required Support Files 10.2.0.5.0
Enterprise Manager Minimal Integration 10.2.0.5.0
Oracle interMedia Annotator 10.2.0.5.0
Database Workspace Manager 10.2.0.5.0
JDBC Common Files 10.2.0.5.0
Oracle Required Support Files 32 bit Patch 10.2.0.5.0
Oracle LDAP administration patch 10.2.0.5.0
Enterprise Manager Repository DB 10.2.0.5.0
Oracle Containers for Java 10.2.0.5.0
Enterprise Manager Repository Core patch 10.2.0.5.0a
Enterprise Manager Common Core Patch 10.2.0.5.0a
Enterprise Manager Agent Core Patch 10.2.0.5.0a
Oracle Message Gateway Common Files Patch 10.2.0.5.0
Platform Required Support Files 10.2.0.5.0
Oracle Core Required Support Files Patch 10.2.0.5.0
Oracle Globalization Support Patch 10.2.0.5.0
Installation Common Files Patch 10.2.0.5.0
SSL Required Support Files for InstantClient Patch 10.2.0.5.0
SQL*Plus Required Support Files Patch 10.2.0.5.0
RDBMS Required Support Files Patch 10.2.0.5.0
RDBMS Required Support Files for Instant Client Patch 10.2.0.5.0
Oracle RAC Required Support Files-HAS Patch 10.2.0.5.0
Precompiler Required Support Files Patch 10.2.0.5.0
LDAP Required Support Files Patch 10.2.0.5.0
DBJAVA Required Support Files Patch 10.2.0.5.0
Agent Required Support Files Patch 10.2.0.5.0
XDK Required Support Files Patch 10.2.0.5.0
Oracle Enterprise Manager Console DB 10.2.0.5.0
Enterprise Manager Baseline 10.2.0.5.0
Enterprise Manager Agent DB 10.2.0.5.0
XML Parser for Oracle JVM Patch 10.2.0.5.0
SQLJ Runtime Patch 10.2.0.5.0
Oracle interMedia Java Advanced Imaging Patch 10.2.0.5.0
Oracle JDBC Thin Driver for JDK 1.4 Patch 10.2.0.5.0
Oracle JDBC Thin Driver for JDK 1.2 Patch 10.2.0.5.0
Assistant Common Files Patch 10.2.0.5.0
XML Parser for Java Patch 10.2.0.5.0
Oracle interMedia Locator Patch 10.2.0.5.0
Oracle Database Utilities Patch 10.2.0.5.0
Oracle Recovery Manager Patch 10.2.0.5.0
PL/SQL 10.2.0.5.0
Oracle OLAP RDBMS Files Patch 10.2.0.5.0
Oracle Call Interface (OCI) Patch 10.2.0.5.0
Oracle interMedia Locator RDBMS Files Patch 10.2.0.5.0
Sample Schema Data Patch 10.2.0.5.0
Oracle Starter Database Patch 10.2.0.5.0
Oracle Net Required Support Files Patch 10.2.0.5.0
Generic Connectivity Common Files Patch 10.2.0.5.0
Oracle Data Mining RDBMS Files Patch 10.2.0.5.0
Database SQL Scripts Patch 10.2.0.5.0
Oracle Clusterware RDBMS Files Patch 10.2.0.5.0
Oracle Text Patch 10.2.0.5.0
Oracle XML Development Kit Patch 10.2.0.5.0
PL/SQL Embedded Gateway Patch 10.2.0.5.0
Oracle interMedia Patch 10.2.0.5.0
Oracle Database 10g interMedia Files Patch 10.2.0.5.0
Oracle interMedia Client Option Patch 10.2.0.5.0
OLAP SQL Scripts Patch 10.2.0.5.0
Oracle OLAP API Patch 10.2.0.5.0
Oracle OLAP Patch 10.2.0.5.0
Oracle ODBC Driver Patch 10.2.0.5.0
Secure Socket Layer Patch 10.2.0.5.0
Precompiler Common Files Patch 10.2.0.5.0
Oracle Wallet Manager Patch 10.2.0.5.0
Oracle Net Listener Patch 10.2.0.5.0
Oracle Net Patch 10.2.0.5.0
Oracle Advanced Security Patch 10.2.0.5.0
iSQL*Plus 10.2.0.5.0
Oracle Internet Directory Client Patch 10.2.0.5.0
Oracle JVM Patch 10.2.0.5.0
Oracle Java Client Patch 10.2.0.5.0
HAS Files for DB Patch 10.2.0.5.0
HAS Common Files Patch 10.2.0.5.0
SQL*Plus 10.2.0.5.0
Enterprise Manager plugin Common Files Patch 10.2.0.5.0
Oracle Database 10g Patch 10.2.0.5.0
Oracle JDBC/OCI Instant Client Patch 10.2.0.5.0
Oracle Database 10g Patch 10.2.0.5.0
Character Set Migration Utility Patch 10.2.0.5.0
Database Configuration and Upgrade Assistants Patch 10.2.0.5.0
Oracle Notification Service Patch 10.2.0.5.0
-----------------------------------------------------------------------------
Instantiating scripts for add node 1% Done.
Instantiation of add node scripts complete
Copying to remote nodes
................................................................. 96% Done.
Home copied to new nodes
Saving inventory on nodes 100% Done.
Save inventory complete
WARNING:
The following configuration scripts need to be executed as the
"root" user in each cluster node.
#!/bin/sh
#Root script to run
/u01/app/oracle/product/10.2.0/db_1/root.sh #On nodes tiku
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster nodeThe Cluster
Node Addition of /u01/app/oracle/product/10.2.0/db_1 was successful.
Please check '/tmp/silentInstall.log' for more details.
And finally, root.sh on the node to be added
[root@tiku ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh Running Oracle 10g root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed.
Now last thing to do against the node to be added, is to add ASM instance and Database instance. One command can accomplish this.
[oracle@taku bin]$ dbca -silent -addInstance -nodeList tiku -gdbName RACDB -instanceName RAC1 -sysDBAUserName sys -sysDBAPassword oracle Adding instance 1% complete 2% complete 6% complete 13% complete 20% complete 26% complete 33% complete 40% complete 46% complete 53% complete 66% complete Completing instance management. 70% complete 73% complete 76% complete 86% complete 90% complete 100% complete Look at the log file "/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/RACDB/RACDB.log" for further details.
That’s it. Now we have again a two node cluster, where 10gR2 is running on one RH4 and one OEL5. What must be done now, is to do exactly same to the other node(s) left in the cluster, to bring them all up to OEL5. Remember that although this does work, this kind of configuration is temporary purposes only.
Now would be also a good point to take another backup of the OCR if you haven’t already done so before proceeding.
FGA in action
FGA in action
Audit records are mandatory in many environments for security reasons. It is often desired, that auditing would need as less system resources as possible. Here is where Fine Grained Auditing comes into picture. Often there are just few important tables, views that need to be audited, often just certain columns that are touched upon make those interesting for us. Auditing actions could be also limited to only certain type of changes in data, which again decreases the set of statements we need to monitor.
So as an example, we make auditing for table in eBusiness suite AR schema, where account numbers are stored. We are not interested about SELECT statements, so we can safely omit them. We want all DML statements captured. So I start adding FGA policy to table using SQL
begin
dbms_fga.add_policy(
object_schema=>ʼARʼ,
object_name=>ʻRA_CUST_TRX_TYPES_ALLʼ,
policy_name=>ʻSEC_CUSTTYPESʼ,
statement_types=>ʻINSERT,UPDATE,DELETEʼ,
audit_trail=>DBMS_FGA.DB+DBMS_FGA.EXTENDED);
end;
/
Database administrator could see from DBA_FGA_AUDIT_TRAIL the SQL that took place (with itʼs bind values (extended flag set)), with some other information.
It could be also useful that if we are immediately notified about policy violations. Hereby one can create small function that sends an e-mail to predefined mailing list or address, when policy violation occurs.
create or replace rt_alert(
p_object_schema varchar2,
p_object_name varchar2,
p_policy_name varchar2)
as
v_email_text#varchar2(4096);
v_email_host varchar2(100):=ʻposti.clientdomain.fiʼ;
v_email_connection utl_smtp.connection;
v_from varchar2(100):=ʻuser@dbmachine.fiʼ;
v_to varchar2(100):=ʻadmin@host.fiʼ;
begin
v_email_text:=ʻpolicy violation for ʻ || p_policy_name ||
ʻ: User=ʼ || USER ||
ʻ Schema=ʼ || p_object_schema ||
ʻ Object=ʼ || p_object_name ||
ʻ Date=ʼ || to_char(SYSDATE,ʼdd-mm-yyyy hh24:mi:ss) ||
ʻ SQL=ʼ || sys_context(ʻuserenvʼ,ʻcurrent_sqlʼ);
v_email_connection:=utl_smtp.open_connection(v_email_host,25);
utl_smtp.helo(v_email_connection,v_email_host);
utl_smtp.mail(v_email_conncetion,v_from);
utl_smtp.rcpt(v_email_connection,v_to);
utl_smtp.data(v_email_connection,utl_tcp.crlf ||
# ʻsubject: Policy violationʼ || utl_tcp.crlf ||
# ʻto: ʻ || v_to || utl_tcp.crlf ||
# v_email_text);
utl.smtp.quit(v_email_connection);
end;
/
When sending mail with UTL_SMTP, from field that cannot be translated will cause an error. So it cannot be foobar@somewhere.dot.com -address. The error code from smtp will help to identify the possible problem.
then modify FGA policy (drop and recreate) to point to the new handler
begin
dbms_fga.add_policy(
object_schema=>ʼARʼ,
object_name=>ʻRA_CUST_TRX_TYPES_ALLʼ,
policy_name=>ʻSEC_CUSTTYPESʼ,
statement_types=>ʻINSERT,UPDATE,DELETEʼ,
audit_trail=>DBMS_FGA.DB+DBMS_FGA.EXTENDED,
handler_schema=>ʻSYSʼ,
handler_module=>ʻRT_ALERTʼ);
end;
/
Virtual Private Database in a nutshell
VPD (Virtual Private Database) has been along Oracle Database in various forms a long time. It was first introduced in Oracle 8i, as FGAC (Fine Grained Access Control) as part of row-level security features (RLS). In 10g, Iit got re-introduced as VPD in it’s current form.
Idea of VPD is pretty straightforward, you can create policies that enforce pre-created functions whenever user access certain objects. It does also work together with label security, flashback queries and editions. Attached to a policy is a function, that returns the additional where -clause to be appended to the sql -statement. It can apply to all DML statements, select, insert, update and delete.
VPD works in the way, that when queries are executed against table it kicks in when defined conditions are met. Say executing statement could be ‘select
* from foobar’. VPD policy could be enforced to modify the query in the fly, while it’s in execution pipeline. We could for example add a clause ‘where id=1′
into the statement using query rewrite. Therefore, actual statement executing will be ‘select * from foobar where id=1′. Result of that rewritten query would be also the one returned to the client.
real life case
I just not so long time ago a customer case with telco -operator, that had pretty big data-warehouse. They had few dozens of terabytes data in there, and they had to store certain amount of days historical data for use for both billing and more to the government use (by European Union legislation). However, these periods were different, and duplicating huge sets of data was not meaningful nor cost-effective. With VPD it’s possible to limit one or more applications visibility on the data, without any need to modify the application code.
Setting it up
For the demo, I create two users. Appuser will be owner of the object, and has full privileges for it. BO user will have DML -privileges for the object, but his visibility will be limited to last 150 days of history.
SQL> create user appuser identified by appuser default tablespace EXAMPLE quota 10M on EXAMPLE; User created. SQL> create user bo identified by bo default tablespace EXAMPLE quota 5m on EXAMPLE; User created. SQL> grant create session,create table to appuser,bo; Grant succeeded. SQL> grant execute on dbms_rls to appuser; Grant succeeded. SQL> grant create procedure to appuser; Grant succeeded.
Let’s create dummy table to present our data
SQL> conn appuser/appuser
Connected.
SQL> create table test(
pvm date,
num number(11,0));
Table created.
SQL> begin
for i in 1..1000
loop
insert into test values(sysdate-i,i);
end loop;
end;
/
Filtering Rows
Now we define that application user BO should not be able to see more than 150 days of data (limited by pvm column) from current date backward at any time. Basically, we want that when BO executes any statement (including updates, deletions and inserts), it will be limited to last 150 days.
SQL> select count(*) from test where pvm > sysdate-151;
COUNT(*)
----------
150
Next we create a function that returns the where -predicate for us, in case connected user is ‘BO’ from examining the sys_context. In real life, consider packaging everything for clarity and ease of administration.
SQL> conn appuser/appuser
Connected.
create or replace function rls_test(owner varchar2,name varchar2)
return varchar2
as
user varchar2(32);
predicate varchar2(255);
begin
user:=SYS_CONTEXT('USERENV','SESSION_USER');
if user='BO' then
predicate:='PVM > SYSDATE-151';
else
predicate := null;
end if;
return (predicate);
end rls_test;
/
function created.
It’s essential that privileges to objects, including the predicate -function is given.
SQL> grant execute on rls_test to bo;
Grant succeeded.
SQL> grant select,update,delete,insert on test to bo;
Grant succeeded.
Then we create the VPD policy. There can be more than one policies for one object, they will joined together using logical AND operator. One policy can be also shared amongst more than one object. Notice that policies by default are exempt from SYSDBA privileges, and users with EXEMPT ACCESS POLICY privileges. Creating VPD policy against SYS objects is prohibited.
SQL> conn appuser/appuser
Connected.
SQL> begin
dbms_rls.add_policy(
object_schema=>'APPUSER',
object_name=>'TEST',
policy_name=>'TEST_150',
function_schema=>'APPUSER',
policy_function=>'RLS_TEST',
policy_type=>dbms_rls.STATIC,
statement_types=>'INSERT,UPDATE,SELECT,DELETE',
update_check=>TRUE,
enable=>TRUE);
end;
/
PL/SQL procedure successfully completed.
POLICY_TYPE tells if predicate function is parsed and called for each query, or if resulting hash is cached. They can be shared, dynamic or context_sensitive. Here application context won’t change, so there is no need for dynamic policy. One should also think if computing predicate is something wished to computer again and again, rather than stored in sys context or some other cheaper approach. STATEMENT_TYPES allow you to control on which DML statements this rule is applied to. UPDATE_CHECK checks the policy against before and after the operation is done, i.e. If user is going to insert or update values that would be out of his visibility. In this example, it would mean user BO could not insert or update values older than SYSDATE-150 days.
Now, let’s test the Policy:
SQL> conn bo/bo
Connected.
SQL> select min(pvm),max(pvm),count(*) from appuser.test;
MIN(PVM) MAX(PVM) COUNT(*)
--------- --------- ----------
27-FEB-10 26-JUL-10 150
SQL> conn appuser/appuser
Connected.
SQL> select min(pvm),max(pvm),count(*) from appuser.test;
MIN(PVM) MAX(PVM) COUNT(*)
--------- --------- ----------
31-OCT-07 26-JUL-10 1000
Filtering columns
Pretty straightforward. This was example of filtering out by rows, yet VPD can be used to filter out column values as well. However, it’s restricted to SELECT statements. Exception is UPDATE as SELECT that updates rows only that user has right to see. Here’s s small demonstration how to mask columns
First, let’s create another function, which only purpose is simply return predicate that will never return rows if user in syscontext is BO.
create or replace function rls_test2(owner varchar2,name varchar2)
return varchar2
as
user varchar2(32);
predicate varchar2(255);
begin
user:=SYS_CONTEXT('USERENV','SESSION_USER');
if user='BO' then
predicate:='1=2';
else predicate:= '2>1';
end if;
return (predicate);
end rls_test2;
/
then we add policy, that is triggered whenever column ‘NUM‘ is selected. With the always false predicate, it will mean that we erase the column from the result.
begin
dbms_rls.add_policy(
object_schema=>'APPUSER',
object_name=>'TEST',
policy_name=>'HIDE_NUM',
function_schema=>'APPUSER',
policy_function=>'RLS_TEST2',
sec_relevant_cols=>'NUM',
sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
end;
/
Now when you go to BO user and query the test table, you will see no more than 150 days of information, plus column NUM is kept hidden from you.
SQL> conn bo/bo
Connected.
SQL> select * from appuser.test where rownum<5;
PVM NUM
--------- ----------
26-JUL-10
25-JUL-10
24-JUL-10
23-JUL-10
You can always see predicates and rules from DBA and v$views, as an example v$vpd_policy
PREDICATE POLICY
-------------------- --------------------
TEST_150 PVM > sysdate -151
HIDE_NUM 1=2
When you create complex rules, group policies and use more advanced techniques. Avoid recursion if possible. For performance, check your access paths for proper indexing on tables that have VPD policy. Keep is simple, test, test and test more. This should get you started with Oracle Virtual Private Database. For more information, see Oracle Security Administration guide.
Changing database character set
It’s fairly straightforward yet rare to change database character set. That is, when it’s possible. Usually this may occur in situation, for example, where database has been (re)created with wrong character set.
Changing charset is most of the time a one way street, and from smaller set to larger (sometimes referred as superset). As an example, there is no problem changing 7-bit ASCII character set to wider UTF-8 or ISO-Latin-1 counterpart. Yet there will be problems if same operation is tried reversed. It would mean one bit slashed off from every byte, and in plain english that translates as data loss. It depends of the actual values in the data used in the database, whether conversion can be done.
Before converting a RAC database, you should bring it up on single node with cluster_database=false. Changing national character set is exactly done in the same way (and can be done same time, if possible and wished upon) – just alter database statement has keyword ‘national‘ included.
Here is the “old” plain way how it was done pre-10g :
STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 246902784 bytes
Fixed Size 1335780 bytes
Variable Size 159387164 bytes
Database Buffers 83886080 bytes
Redo Buffers 2293760 bytes
Database mounted.
ALTER SYSTEM ENABLE RESTRICED SESSION;
System altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER DATABASE CHARACTER SET WE8ISO8859P15;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 246902784 bytes
Fixed Size 1335780 bytes
Variable Size 159387164 bytes
Database Buffers 83886080 bytes
Redo Buffers 2293760 bytes
Database mounted.
Database opened.
Oracle 10g and 11g comes with rather handy utility called CSSCAN (Character Set Scanner). It also can do all the necessary tricks for the DBA as well, plus possibly avoids few pitfalls along the way. Most importantly, it shows out precisely where are the problems one should take care of before seamless data migration to another character set can take place.
Start the scanner by issuing a command csscan from the command line. If you encounter an error (for example, error while loading shared libraries: libclntsh.so.11.1: cannot open shared library) check out that you have turned secure Linux off (sudo – root setenforce 0) and LD_LIBRARY_PATH is correctly defined in your enviroment.
[oracle@orange ~]$ csscan \'sys/oracle as sysdba\'
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
This tells us, that we don’t have necessary object created in our database by default. Those can be created via running csminst.sql from $ORACLE_HOME/rdbms/admin.
SQL> @?/rdbms/admin/csminst.sqlAfter the script has run, user can start to use character set scanner to alter database character set, and determine possible problems in conversion. In Unix systems, you have to escape citation marks with backslash because of the slash in arguments.
[oracle@orange ~]$ csscan \"sys/oracle as sysdba\"
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Thu May 20 15:20:47 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1
Current database character set is US7ASCII.
Enter new database character set name: > WE8ISO8859P15
Enter array fetch buffer size: 1024000 > 200000
Enter number of scan processes to utilize(1..64): 1 > 1
Enumerating tables to scan...
. process 1 scanning SYSTEM.LOGMNR_PARTOBJ$[AAACSNAACAAABwYAAA]
. process 2 scanning SYS.JAVASNM$[AAAAI5AABAAAA7AAAA]
. process 1 scanning SYS.INDCOMPART$[AAAAJbAABAAAA/QAAA]
. process 2 scanning SYS.CDC_CHANGE_COLUMNS$[AAAAKuAABAAABJIAAA]
. process 1 scanning SYS.OLAP_IMPL_OPTIONS$[AAAAObAABAAABcYAAA]
.. < excessive output cut off > ...
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
DBA should check if cscan.err file was created in same directory the tool was invoked from, and the contents of cscan.txt which is printout of exact results of the scan. If there are lines stating ‘lossy conversion‘ it means data would be lost in conversion, so it’s a no-go. Fix issues before continuing conversion. See metalink note #258904.1 if it’s helpful (Solving Convertible or Lossy data in Data Dictionary objects when changing the NLS_CHARACTERSE). When all seems fine, one can continue to the next step. First you can check what is the character set of the database from v$nls_parameters. Then you can start the actual conversion by calling out script $ORACLE_HOME/rdbms/admin/csalter.plb. Run it as sys user, and I recommend restarting the database instance after succesful completion. If it stops because of an error, no changes are made.
select parameter, value from v$nls_parameters where parameter in('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER ------------------------------------------------- VALUE -------------------------------------------------- NLS_CHARACTERSET US7ASCII NLS_NCHAR_CHARACTERSET AL16UTF16@?/rdbms/admin/csalter.plb 4 rows created. Function created. Function created. Procedure created. This script will update the content of the Oracle Data Dictionary. Please ensure you have a full backup before initiating this procedure. Would you like to proceed (Y/N)? Y old 6: if (UPPER('&conf') <> 'Y') then new 6: if (UPPER('Y') <> 'Y') then Checking data validity... begin converting system objects PL/SQL procedure successfully completed. Alter the database character set... CSALTER operation completed, please restart database PL/SQL procedure successfully completed. 4 rows deleted. Function dropped. Function dropped. Procedure dropped.
SQL> select parameter, value from v$nls_parameters
where parameter in('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); 2
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P15
NLS_NCHAR_CHARACTERSET
AL16UTF16
further considerations
Note that changing character set takes only place when data is re-formed into SQL, changing database character set parameter changes only value in the data dictionary. Therefore, also when transportable tablespaces are used, both source and destination must use same characterset. Will small sets of data export/import tools can be utilized. Yet csscan can be useful when one needs to determine if data is mangled in transport when such techniques are used. Logical standby databases, Oracle Streams nor Oracle data integrator will be useful in more complex situations, when migrating or moving data between different character sets.
11g Data guard broker essentials
11g Data Guard Broker essentials
In this tutorial I have two hosts
apple, running Oracle Enterprise Linux, kernel 2.6.18-164.el5
orange, running Oracle Enterprise Linux, kernel 2.6.18-164.el5
Apple is host having our primary database, ORCL. Database version used is 11.2.0.1, and tutorial begins from situation that database instance is up and open, and listener is running. Database binaries are installed on both hosts and directory structures of binaries are identical.
Directory structure in example is
Oracle home /u01/app/oracle/product/11.2.0/dbhome_1
Oracle datafiles /u01/app/oracle/oradata/ORCL
Flash recovery area /u01/app/oracle/flash_recovery_area
Audit dump destination /u01/app/oracle/admin/ORCL/adump
so let’s start, on primary :
alter database force logging;
this enforces logging regardless nologging is used when doing operations in primary, it ensures all neccessary operations are recorded in redo, and will reflect on standby site.
on primary, if it’s not in archivelog mode and if you are not put the database flashback on, do
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch/' scope=spfile;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL > alter database flashback on;
SQL> alter database open;you can check with archive log list and making couple of log switches that all is well
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/
Oldest online log sequence 263
Next log sequence to archive 265
Current log sequence 265
SQL>alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/
Oldest online log sequence 264
Next log sequence to archive 266
Current log sequence 266
then, add entries for primary site local naming configuration, tnsnames.ora. Copy entries also to the standby site, so network configuration is similar on both hosts.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = apple)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
))
ORCL_STB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orange)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCLSTB)
))then, let’s prepare standby site, add to it’s listener configuration a static entry for database duplication
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCLSTB)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORCLSTB)
))start listener on standby host, or if it was running already, reload the configuration
lsnrctl startcreate configuration for standby database, go to $ORACLE_HOME/dbs and create a file named initORCLSTB.ora
DB_NAME=NNNNNNNcontents has only one parameter, DB_NAME – and it doesn’t matter what it says, as long there are less than 8 characters and it does not start with numeric characters. RMAN will later on override it.
Then create also a password file, where SYS user password must be the same as it is on primary host.
orapwd file=orapwORCLSTB password=oracle entries=5Then export database instance SID, and startup the standby instance in nomount mode
[oracle@orange dbs]$ export ORACLE_SID=ORCLSTB
[oracle@orange dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:43:58 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionslet’s also add directory structures similar to primary site to standby site
[oracle@orange oradata]$ pwd
/u01/app/oracle
[oracle@orange oracle]$ mkdir arch
[oracle@orange oracle]$ mkdir -p oradata/ORCL
[oracle@orange oracle]$ mkdir -p admin/ORCL/adump
[oracle@orange oracle]$ mkdir -p flash_recovery_area/ORCLDirectory stucture conforms the one on the primary site.
From primary site duplication can be now executed.
if you get RMAN-05537 errors, check that your target and auxiliary database are correct. Also make sure auxiliary (standby) instance is started with pfile. If you encounter ORA-09925 (Unable to create audit trail file) make sure you created audit destination directory in destination. Also make sure that your flash_recovery_area is on standby host as is on primary.
In busy production systems, you may want to disable jobs that clean out archivelogs from the disk during the duplication, which is often the case. 11G with ‘from active database’ helps with this a bit. Previous versions, you may want to use cloning or duplication to certain scn. Now on primary site we issue
rman target sys/oracle@ORCL auxiliary sys/oracle@ORCLSTB
connected to target database: ORCL (DBID=1239076189)
connected to auxiliary database: NNNN (not mounted)
run{
duplicate target database for standby from active database
spfile
set 'db_unique_name'='orclstb'
nofilenamecheck;
}
Starting Duplicate Db at 08-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCL' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCLSTB' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCL.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCLSTB.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCLSTB.ora''";
}
executing Memory Script
Starting backup at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 08-APR-10
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCLSTB.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =''orclstb'' comment='''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''orclstb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 246902784 bytes
Fixed Size 1335780 bytes
Variable Size 88083996 bytes
Database Buffers 155189248 bytes
Redo Buffers 2293760 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/ORCL/control01.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/ORCL/control02.ctl' from '/u01/app/oracle/oradata/ORCL/control01.ctl';
}
executing Memory Script
Starting backup at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORCL.f tag=TAG20100408T143527 RECID=4 STAMP=715790128
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-APR-10
Starting restore at 08-APR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-APR-10
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to "/u01/app/oracle/oradata/ORCL/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to "/u01/app/oracle/oradata/ORCL/system01.dbf";
set newname for datafile 2 to "/u01/app/oracle/oradata/ORCL/sysaux01.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/ORCL/undotbs01.dbf";
set newname for datafile 4 to "/u01/app/oracle/oradata/ORCL/users01.dbf";
backup as copy
reuse
datafile 1 auxiliary format "/u01/app/oracle/oradata/ORCL/system01.dbf"
datafile 2 auxiliary format "/u01/app/oracle/oradata/ORCL/sysaux01.dbf"
datafile 3 auxiliary format "/u01/app/oracle/oradata/ORCL/undotbs01.dbf"
datafile 4 auxiliary format "/u01/app/oracle/oradata/ORCL/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
output file name=/u01/app/oracle/oradata/ORCL/system01.dbf tag=TAG20100408T143542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf tag=TAG20100408T143542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf tag=TAG20100408T143542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users01.dbf
output file name=/u01/app/oracle/oradata/ORCL/users01.dbf tag=TAG20100408T143542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-APR-10
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=715790282 file name=/u01/app/oracle/oradata/ORCL/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=715790283 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=715790283 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=715790283 file name=/u01/app/oracle/oradata/ORCL/users01.dbf
Finished Duplicate Db at 08-APR-10
RMAN> exit
Recovery Manager complete.
[oracle@apple dbs]$now we can query standby database from sqlplus
SQL> select NAME,OPEN_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBYFor other than maximum performance mode, standby logfiles are added to both sites. There should be one more standby logfile than online redo logs. Making them all to be same size as online logs is recommended practice. Execute on all sites
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stblog01.rdo' size 10M;
Database altered.
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stblog02.rdo' size 10M;
Database altered.
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stblog03.rdo' size 10M;
Database altered.
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stblog04.rdo' size 10M;
Database altered.Data Guard broker daemon (DMON) must be started as prequisite to utilize it, In all instances say
SQL> alter system set dg_broker_start=true scope=both;New processes are spawned, ora_dmon_ORCL on primary, and ora_dmon_ORCLSTB on standby.
Also put flashback mode to on in new standby, it’ll be good for failovers we do later on.
SQL > alter database flashback on;Now we can enter data guard broker (dgmgrl). Connect as user sys using appropriate password. Sqlplus style ‘/ as sysdba’ cannot used with broker. At any time, you can use help command in dgmgrl to get command list, or example of using a command by syntax help [command]
[oracle@apple dbs]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> create configuration orcl_dg as
> primary database is ORCL
> connect identifier is ORCL;
Configuration "orcl_dg" created with primary database "orcl"two configuration files appear under $ORACLE_HOME/dbs, they are actually multiplexed copies of each other. This gives an option to reduncancy in production environments. Creation place can be controlled via parameter DG_BROKER_CONFIG_FILEn.
Now add physical standby instance ORCLSTB
DGMGRL> add database ORCLSTB
as connect identifier is ORCLSTB
maintained as physical;
Database "orclstb" added
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orclstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLEDnow we can enable the newly created configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orclstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESSyou can also query more specific information by adding keyword verbose to the command, say
DGMGRL> show database verbose ORCLSTB
Database - orclstb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
ORCLSTB
Properties:
DGConnectIdentifier = 'orclstb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'orange'
SidName = 'ORCLSTB'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orange)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclstb_DGMGRL)(INSTANCE_NAME=ORCLSTB)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/arch/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESSLet’s now change protection mode to maximum availability to demonstrate using of dgmgrl
DGMGRL> show database ORCLSTB LogXptMode
LogXptMode = 'ASYNC'we change both in preparation for switchover
DGMGRL> edit database ORCLSTB set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMRRL> edit database ORCL set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit configuration set protection mode as maxAvailability;
Succeeded.
DGMGRL> show configuration;
Configuration - orcl_dg
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
orclstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESSTesting switchovers from primary
DGMGRL> switchover to ORCLSTB;
Performing switchover NOW, please wait...After it completes, issue startup mount at old primary site, which is now new standby.
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxAvailability
Databases:
orclstb - Primary database
orcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status
SUCCESSyou can switch roles back by issuing a new switchover command from dgmgrl
DGMGRL> switchover to ORCL;
...
DGMGRL>
Configuration - orcl_dg
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
orclstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESSAnd issue again startup mount on previous primary after it has been brought down if neccessary.
Then, we do failover. Primary is lost, unaccessible or some other type of fault and we need to brind former standby open as new primary. If old primary will come alive after failover, it will need either flashed back to the scn when the failover took place, or completely re-built from the new primary by duplication.
DGMGRL> failover to orclstb;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orclstb"
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxAvailability
Databases:
orclstb - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
orcl - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNINGFor re-instation purposes, we had the flashback database turned on. Using manual methods, we could find out when new primary fell over, flashback previous standby to that scn, and convert the database to physical standby. Broker does simplify this process as well – in old primary database bring it inmount mode and issue reinstate command in dgmgrl. If broker can not reinstate the former primary, it will tell you. Then your only option is to rebuild it from scratch. Neverthless, remember that you are not protected by a standby before you have reinstanted your standby instance.
DGMGRL> reinstate database orcl;
Reinstating database "orcl", please wait...
Operation requires shutdown of instance "ORCL" on database "orcl"
Shutting down instance "ORCL"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL" on database "orcl"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Reinstating database "orcl", please wait...
Reinstatement of database "orcl" succeeded
DGMGRL> show configuration
Configuration - orcl_dg
Protection Mode: MaxAvailability
Databases:
orclstb - Primary database
orcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESSUsing oracle-validated from local DVD
You can use oracle-validated “dummy package” to pull in all needed packages to install for example, Oracle 11gR2. This package can be obtained freely from here.
In case you are installing your OEL from original media, it’s already there shipping with the Oracle Enterprise Linux. You can use it without connection or subscription to OLN as well. All you need is configure yum to fetch packages from local repository, which translates to your installation media.
First, create mountpoint and mount your installation media
mkdir /media/dvd
mount /dev/cdrom /media/dvd
first we add few lines in /etc/yum.conf after the line stating “PUT YOUR REPOS HERE OR IN separate files ..”
[EL5DVD]
name=EnterpriseLinuxDVD
baseurl=file:///media/dvd/Server
gpgcheck=0
enabled=1
now, when you run yum, you can use yum to install any package from your media mounted in directory /media/dvd
so, to proceed with oracle-validated -metapackage, just command
[root@apple]# yum install oracle-validated*
Loaded plugins: security
EL5DVD | 1.1 kB 00:00
EL5DVD/primary | 795 kB 00:00
EL5DVD 2278/2278
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracle-validated.i386 0:1.0.0-18.el5 set to be updated
--> Processing Dependency: compat-gcc-34 for package: oracle-validated
--> Processing Dependency: libXp for package: oracle-validated
--> Processing Dependency: compat-libstdc++-33 for package: oracle-validated
--> Processing Dependency: libaio-devel for package: oracle-validated
--> Processing Dependency: sysstat for package: oracle-validated
--> Processing Dependency: unixODBC for package: oracle-validated
--> Processing Dependency: unixODBC-devel for package: oracle-validated
--> Processing Dependency: compat-db for package: oracle-validated
--> Processing Dependency: compat-gcc-34-c++ for package: oracle-validated
--> Running transaction check
---> Package compat-db.i386 0:4.2.52-5.1 set to be updated
---> Package compat-gcc-34.i386 0:3.4.6-4 set to be updated
---> Package compat-gcc-34-c++.i386 0:3.4.6-4 set to be updated
---> Package compat-libstdc++-33.i386 0:3.2.3-61 set to be updated
---> Package libXp.i386 0:1.0.0-8.1.el5 set to be updated
---> Package libaio-devel.i386 0:0.3.106-3.2 set to be updated
---> Package sysstat.i386 0:7.0.2-3.el5 set to be updated
---> Package unixODBC.i386 0:2.2.11-7.1 set to be updated
---> Package unixODBC-devel.i386 0:2.2.11-7.1 set to be updated
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
oracle-validated i386 1.0.0-18.el5 EL5DVD 15 k
Installing for dependencies:
compat-db i386 4.2.52-5.1 EL5DVD 1.7 M
compat-gcc-34 i386 3.4.6-4 EL5DVD 4.1 M
compat-gcc-34-c++ i386 3.4.6-4 EL5DVD 11 M
compat-libstdc++-33 i386 3.2.3-61 EL5DVD 232 k
libXp i386 1.0.0-8.1.el5 EL5DVD 22 k
libaio-devel i386 0.3.106-3.2 EL5DVD 11 k
sysstat i386 7.0.2-3.el5 EL5DVD 170 k
unixODBC i386 2.2.11-7.1 EL5DVD 830 k
unixODBC-devel i386 2.2.11-7.1 EL5DVD 743 k
Transaction Summary
================================================================================
Install 10 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 19 M
Is this ok [y/N]: y
Downloading Packages:
--------------------------------------------------------------------------------
Total 3.2 GB/s | 19 MB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : compat-gcc-34 1/10
Installing : unixODBC 2/10
Installing : compat-gcc-34-c++ 3/10
Installing : libXp 4/10
Installing : compat-libstdc++-33 5/10
Installing : compat-db 6/10
Installing : sysstat 7/10
Installing : unixODBC-devel 8/10
Installing : libaio-devel 9/10
Installing : oracle-validated 10/10
Installed:
oracle-validated.i386 0:1.0.0-18.el5
Dependency Installed:
compat-db.i386 0:4.2.52-5.1 compat-gcc-34.i386 0:3.4.6-4
compat-gcc-34-c++.i386 0:3.4.6-4 compat-libstdc++-33.i386 0:3.2.3-61
libXp.i386 0:1.0.0-8.1.el5 libaio-devel.i386 0:0.3.106-3.2
sysstat.i386 0:7.0.2-3.el5 unixODBC.i386 0:2.2.11-7.1
unixODBC-devel.i386 0:2.2.11-7.1
Complete!
[root@apple]#
And your OS is good to go to install a Oracle Database product.
