tag:blogger.com,1999:blog-50858505899142486812024-03-14T07:20:15.948+01:00Managing OracleThis blog is about Managing Oracle databases, a day-to-day job for many.FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-5085850589914248681.post-80808986502227067832024-01-12T09:43:00.003+01:002024-01-12T09:43:54.170+01:00Dataguard Setup<div>1.<span style="white-space: pre;"> </span>General information</div><div><br /></div><div>With the ever increasing level of security, the need to keep up to date with the latest patches of the Oracle database also increases.</div><div>Patching of an Oracle database becomes easier and easier with the release of Oracle 19c and the AutoUpgrade tool, but that subject is covered in another document. </div><div><br /></div><div>In this document we describe the setup of a Dataguard environment and the patching of it. At Kramp we use Dataguard for the RMAN catalog database, to ensure that this database is always available. The RMAN catalog database is a very important database, as without it backups of a database can not be created.</div><div><br /></div><div>Next to the setup of the Dataguard environment we will describe the patching of it. Understandably it differs from patching a Single Instance database, because of the redo apply process.</div><div><br /></div><div>Note that in normal situations the use of dataguard is a licensed option. Since we however use it here on the database used for the RMAN catalog, which is a free-to-use database, also the use of dataguard is free of charge.</div><div>Note that we’re talking here about standard dataguard. Active dataguard where a standby database would be opened up read-only is a must-license feature always.</div><div><br /></div><div>This document does not cover the contents of the database involved in this setup. </div><div>2.<span style="white-space: pre;"> </span>Setup of Dataguard</div><div><br /></div><div>In this section the setup of the dataguard environment is described. In the examples the setup of the test environment for the RMAN catalog database is used for the screenshots and statements.</div><div><br /></div><div>The basic setup of the environment consists of two Linux servers:</div><div>vtl1g002: the primary server, running an Oracle 19c EE database</div><div>vtl1g003: the secondary server, running the exact same Oracle version</div><div><br /></div><div>It is important for a Dataguard setup that both primary and secondary servers run the same Oracle version and patch level.</div><div><br /></div><div>On the primary server an empty database is created in the CDB/PDB setup called TSMT01C.</div><div>The OSuser for this installation is called “tsmt01”, so all file system locations are based on the Kramp standard: /opt/tsmt01/xxx</div><div><br /></div><div>In the below sub sections the setup is explained in steps. </div><div> </div><div>2.1.<span style="white-space: pre;"> </span>Enable archive logging</div><div><br /></div><div>Dataguard relies on the shipping of redo information from primary server to the secondary server. To support this, both the primary and secondary server databases must have archived log mode enabled. </div><div><br /></div><div>Start SQLplus on the primary server database and execute the following commands:</div><div><br /></div><div><span style="font-family: courier;">shutdown immediate;</span></div><div><span style="font-family: courier;">startup mount;</span></div><div><span style="font-family: courier;">alter database archivelog;</span></div><div><span style="font-family: courier;">alter database open;</span></div><div><br /></div><div>After enabling archivelog mode, make sure the database is backed up and archived logfiles are cleaned out. </div><div><br /></div><div>After that the database must be set into Force Logging mode, so that all actions done on the database will result in redo-generation, even if a statement uses the NOLOGGING option. </div><div>To enable force logging, execute the following statement:</div><div><br /></div><div><span style="font-family: courier;">alter database force logging;</span></div><div><span style="font-family: courier;">alter system switch logfile;</span></div><div><br /></div><div><br /></div><div>2.2.<span style="white-space: pre;"> </span>Create standby logfiles</div><div><br /></div><div>Now create standby redo logs on the primary database in case of switchovers. The standby redo logs should be as big as the largest online redo logfile and there should be one extra group per thread. In our case we have 6 online redo file of 200Mb each. </div><div>This means we create 7 standby redo logs of the same size. They will be located in a subdirectory of the online log location. </div><div>First create this directory on both the primary and secondary server:</div><div><br /></div><div><span style="font-family: courier;">$ mkdir /opt/tsmt01/re01/standby</span></div><div><br /></div><div> </div><div>Now we can create standby log file from within SQLplus. </div><div>This is only done on the primary database, as we will copy this database in a later step completely to the secondary server:</div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;</span></div><div><br /></div><div><br /></div><div>2.3.<span style="white-space: pre;"> </span>Initialization Parameters</div><div>For a dataguard solution both the primary &amp; standby databases should have the same name (db_name).</div><div>They should however distinguish themselves based on db_unique_name. </div><div>In our case, the primary database is in the Amsterdam datacentre and will therefore be called TSMT01C_AMS. The standby is located in the Enschede datacentre and is called TSMT01C_ENS.</div><div>In this step we will already name the primary instance. After we copy the primary to the standby server, we will rename it then,</div><div><br /></div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">alter system set db_unique_name=’TSMT01C_AMS’ scope=spfile;</span></div><div><br /></div><div>Bounce the database to activate the above change.</div><div><br /></div><div><br /></div><div>Next parameter to be changed is “standby_file_management”. When set to “AUTO”, it makes sure that a physical change on the primary database, like adding or extending an tablespace is also reflected to the standby database.</div><div>This parameter is dynamic, so it does not need a restart (again):</div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">alter system set standby_file_management=AUTO scope=both;</span></div><div><br /></div><div>Enable this both on the primary and on standby database as this feature should also work after a failover or switchover.</div><div><br /></div><div>If this parameter is not set to AUTO is will result in redo-apply on the standby server being unable to apply changes when dataguard is up and running.</div><div>If e.g. a tablespace is added on the primary database, the datafile(s) belonging to that new tablespace will not be present on the standby server. If this happens, the below error is seen in the DGMGRL console when looking at the configuration.</div><div><br /></div><div><br /></div><div>DGMGRL>; show configuration;</div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration - tsmt01c_config</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;"> Protection Mode: MaxPerformance</span></div><div><span style="font-family: courier;"> Members:</span></div><div><span style="font-family: courier;"> tsmt01c_ams - Primary database</span></div><div><span style="font-family: courier;"> tsmt01c_ens - Physical standby database</span></div><div><span style="font-family: courier;"> Error: ORA-16766: Redo Apply is stopped</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Fast-Start Failover: Disabled</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration Status:</span></div><div><span style="font-family: courier;">ERROR (status updated 37 seconds ago)</span></div><div> </div><div><br /></div><div>2.4.<span style="white-space: pre;"> </span>Duplicate the database </div><div><br /></div><div>If the database is shutdown, then first copy the contents of the spfile into a pfile.</div><div><br /></div><div><span style="font-family: courier;">SQL> create pfile from spfile;</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">File created.</span></div><div><br /></div><div>Change directory to the $ORACLE_HOME/dbs directory and copy the pfile to the standby server:</div><div><br /></div><div><span style="font-family: courier;">$ scp initTSMT01C.ora vtl1g003:`pwd`</span></div><div><br /></div><div>On the standby server copy the created initfile to ‘/tmp’ for use with RMAN as temporary initfile.</div><div><br /></div><div><span style="font-family: courier;">$ cp /opt/tsmt01/ora/19.0.0.0/dbs/initTSMT01C.ora /tmp/inittemp.ora</span></div><div><br /></div><div>Now start the instance on the standby server in NOMOUNT mode </div><div><br /></div><div><span style="font-family: courier;">SQL> startup nomount pfile=’/tmp/inittemp.ora’;</span></div><div><br /></div><div>Then use RMAN on the standby server connecting to the primary database as target and to the -to be- standby as auxiliary.</div><div><br /></div><div><span style="font-family: courier;">$ rman target sys/<password>@tsmt01c_ams auxiliary sys/<password>@tsmt01c_ens</span></div><div><br /></div><div>Issue the following RMAN commands to copy the database:</div><div><br /></div><div><span style="font-family: courier;">duplicate target database</span></div><div><span style="font-family: courier;"> for standby</span></div><div><span style="font-family: courier;"> from active database</span></div><div><span style="font-family: courier;"> dorecover</span></div><div><span style="font-family: courier;"> spfile</span></div><div><span style="font-family: courier;"> set db_unique_name='TSMT01C_ENS' </span></div><div><span style="font-family: courier;"> nofilenamecheck;</span></div><div><br /></div><div>Note the option “for standby”, telling the duplicate command that this database is used for a standby database. It will thus not change a DBID after the clone.</div><div>The duplicate command can ofcourse be extended with db_file_name_convert or log_file_name_convert options, if needed.</div><div><br /></div><div>Wait for the command to finish before continuing.</div><div><br /></div><div><br /></div><div>Finally create a password file on the standby server, with the same password as used for SYS on the primary database server.</div><div>Preferably we copy the password file from the primary server to the standby server, to make sure that it contains the same password.</div><div><br /></div><div>On primary server:</div><div><span style="font-family: courier;">$ cd $ORACLE_HOME/dbs</span></div><div><span style="font-family: courier;">$ scp orapwTSMT01C vtl1g003:`pwd`</span></div><div><br /></div><div><br /></div><div>As soon as the standby database has been created, add the same standby logfiles as on the primary:</div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;</span></div><div><span style="font-family: courier; font-size: x-small;">alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;</span></div><div><br /></div><div><br /></div><div> </div><div>2.5.<span style="white-space: pre;"> </span>Listener.ora</div><div><br /></div><div>Edit the listener.ora on both the primary and secondary server to contain a static entry for the databases. This is needed for the dataguard broker to be able to access the database, even if it is not open.</div><div>Remember that only open databases will automatically register with a listener. When a database is in mounted mode. </div><div><br /></div><div>Most documentation found on the Internet name a standby database to contain the “_stdby” extension, like “cdb1_stdby”</div><div>This is however not really smart, especially if a failover takes place and suddenly the standby database called “cdb1_stdby” suddenly becomes the primary databases. </div><div>This is why we name the databases following the physical location.</div><div><br /></div><div>On the primary database add the following entry to the listener.ora:</div><div><br /></div><div><span style="font-family: courier;">SID_LIST_TSMT01 =</span></div><div><span style="font-family: courier;"> (SID_LIST =</span></div><div><span style="font-family: courier;"> (SID_DESC =</span></div><div><span style="font-family: courier;"> (GLOBAL_DBNAME = TSMT01C_AMS_DGMGRL)</span></div><div><span style="font-family: courier;"> (ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)</span></div><div><span style="font-family: courier;"> (SID_NAME = TSMT01C)</span></div><div><span style="font-family: courier;"> (ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> )</span></div><div><br /></div><div>On the standby server add the following:</div><div><br /></div><div><span style="font-family: courier;">SID_LIST_TSMT01 =</span></div><div><span style="font-family: courier;"> (SID_LIST =</span></div><div><span style="font-family: courier;"> (SID_DESC =</span></div><div><span style="font-family: courier;"> (GLOBAL_DBNAME = TSMT01C_ENS_DGMGRL)</span></div><div><span style="font-family: courier;"> (ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)</span></div><div><span style="font-family: courier;"> (SID_NAME = TSMT01C)</span></div><div><span style="font-family: courier;"> (ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> )</span></div><div> </div><div>Note the use of CAPITALS in the database names. This is important, as it must match how it is used later in the Dataguard Broker setup. </div><div>Restart both listener processes:<br /><br /></div><div><span style="font-family: courier;">lsnrctl stop</span></div><div><span style="font-family: courier;">lsnrctl start</span></div><div><br /></div><div><br /></div><div>2.6.<span style="white-space: pre;"> </span>Tnsnames.ora</div><div><br /></div><div>Update the tnsnames.ora file on both servers to have entries for both db_unique_names and the static “gloval_name” as defined in the listener.ora. The following entries therefore need to be added to the tnsnames.ora file on both servers:</div><div><br /></div><div><span style="font-family: courier;">TSMT01C_AMS =</span></div><div><span style="font-family: courier;"> (DESCRIPTION =</span></div><div><span style="font-family: courier;"> (ADDRESS_LIST =</span></div><div><span style="font-family: courier;"> (ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g002.kramponline.com)(PORT = 1521))</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> (CONNECT_DATA =</span></div><div><span style="font-family: courier;"> (SERVER = DEDICATED)</span></div><div><span style="font-family: courier;"> (SID = TSMT01C)</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">TSMT01C_ENS =</span></div><div><span style="font-family: courier;"> (DESCRIPTION =</span></div><div><span style="font-family: courier;"> (ADDRESS_LIST =</span></div><div><span style="font-family: courier;"> (ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g003.kramponline.com)(PORT = 1521))</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> (CONNECT_DATA =</span></div><div><span style="font-family: courier;"> (SERVER = DEDICATED)</span></div><div><span style="font-family: courier;"> (SID = TSMT01C)</span></div><div><span style="font-family: courier;"> )</span></div><div><span style="font-family: courier;"> )</span></div><div><br /></div><div><br /></div><div>Note the use of SID i.o SERVICE_NAME as the Dataguard Broker will need to connect to the databases when they are down. In that case the services are not available. </div><div><br /></div><div><br /></div><div>3.<span style="white-space: pre;"> </span>Enable the Broker</div><div>At this point we are ready to start the Dataguard synchronization process. The first step is to start the Dataguard Broker on both databases.</div><div>Since we want to prevent the standby database from becoming its own incarnation, we only start it in “mount” mode. </div><div>The primary database can be opened in “open” mode.</div><div><br /></div><div><br /></div><div>3.1.<span style="white-space: pre;"> </span>Start the broker process</div><div>On the primary database:</div><div><br /></div><div><span style="font-family: courier;">$ sqlplus / as sysdba</span></div><div><br /></div><div>Connected to an idle instance.</div><div><br /></div><div><span style="font-family: courier;">SQL> startup</span></div><div><span style="font-family: courier;">ORACLE instance started.</span></div><div><span style="font-family: courier;">….</span></div><div><span style="font-family: courier;">….</span></div><div><span style="font-family: courier;">Database mounted.</span></div><div><span style="font-family: courier;">Database opened.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">SQL> alter system set dg_broker_start=true scope=both;</span></div><div><br /></div><div><br /></div><div>On the standby database:</div><div><br /></div><div><span style="font-family: courier;">$ sqlplus / as sysdba</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Connected to an idle instance.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">SQL> startup mount</span></div><div><span style="font-family: courier;">ORACLE instance started.</span></div><div><span style="font-family: courier;">….</span></div><div><span style="font-family: courier;">….</span></div><div><span style="font-family: courier;">Database mounted.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">SQL> alter system set dg_broker_start=true scope=both;</span></div><div> </div><div>3.2.<span style="white-space: pre;"> </span>Create Broker configuration</div><div><br /></div><div>On the primary server start the dataguard broker to register this server.</div><div><br /></div><div><span style="font-family: courier;">$ dgmgrl sys/<password>@tsmt01c_ams</span></div><div><span style="font-family: courier;">DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Dec 5 16:27:35 2023</span></div><div><span style="font-family: courier;">Version 19.16.0.0.0</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Welcome to DGMGRL, type "help" for information.</span></div><div><span style="font-family: courier;">Connected to "TSMT01C_AMS"</span></div><div><span style="font-family: courier;">Connected as SYSDBA.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">DGMGRL> create configuration tsmt01c_config as primary database is TSMT01C_AMS connect identifier is TSMT01C_AMS;</span></div><div><span style="font-family: courier;">Configuration "tsmt01c_config" created with primary database "tsmt01c_ams"</span></div><div><span style="font-family: courier;">DGMGRL></span></div><div><br /></div><div>Now add the standby database:</div><div><br /></div><div><span style="font-family: courier;">DGMGRL> add database TSMT01C_ENS as connect identifier is TSMT01C_ENS;</span></div><div><span style="font-family: courier;">Database "tsmt01c_ens" added</span></div><div><span style="font-family: courier;">DGMGRL></span></div><div><br /></div><div>It is important to use capitals in these statements for the database name if there are also capitals in the listener configuration. </div><div><br /></div><div>If there is somewhere a discrepancy between these configurations, one can expect an ORA-01017 when trying to setup the dataguard configuration.</div><div><br /></div><div>You can check the configuration using the “show configuration” command:</div><div><br /></div><div><span style="font-family: courier;">DGMGRL> show configuration</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration - tsmt01c_config</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;"> Protection Mode: MaxPerformance</span></div><div><span style="font-family: courier;"> Members:</span></div><div><span style="font-family: courier;"> tsmt01c_ams - Primary database</span></div><div><span style="font-family: courier;"> tsmt01c_ens - Physical standby database</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Fast-Start Failover: Disabled</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration Status:</span></div><div><span style="font-family: courier;">DISABLED</span></div><div><span style="font-family: courier;">Configuration status is “DISABLED” which means that dataguard is not active yet.</span></div><div><span style="font-family: courier;">We need to enable the configuration to let the standby database become and stay in sync with the primary database.</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">DGMGRL> enable configuration;</span></div><div><span style="font-family: courier;">Enabled.</span></div><div><span style="font-family: courier;">DGMGRL></span></div><div><br /></div><div>This commando may take a while depending on the amount of data that has to be synced.</div><div><br /></div><div><br /></div><div>3.3.<span style="white-space: pre;"> </span>Tnsnames.ora</div><div>Before this installation can be successfully used and before we can do e.g. a switchover or a failover, clients connecting to this Dataguard database need to be made aware about the existence of two hosts, possibly serving the primary database.</div><div><br /></div><div>To solve this an extra host address needs to be added to the tnsnames.ora entry for this database. There where a single instance usually only has one address line in tnsnames.ora, for a Dataguard setup you need to add one more.</div><div>The tnsnames entry for the above configuration therefor looks like this:</div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">TSMT01 =</span></div><div><span style="font-family: courier; font-size: x-small;"> (DESCRIPTION =</span></div><div><span style="font-family: courier; font-size: x-small;"> (ADDRESS_LIST =</span></div><div><span style="font-family: courier; font-size: x-small;"> (ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g002.kramponline.com)(PORT = 1521))</span></div><div><span style="font-family: courier; font-size: x-small;"> (ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g003.kramponline.com)(PORT = 1521))</span></div><div><span style="font-family: courier; font-size: x-small;"> )</span></div><div><span style="font-family: courier; font-size: x-small;"> (CONNECT_DATA =</span></div><div><span style="font-family: courier; font-size: x-small;"> (SERVICE_NAME = TSMT01)</span></div><div><span style="font-family: courier; font-size: x-small;"> )</span></div><div><span style="font-family: courier; font-size: x-small;"> )</span></div><div><br /></div><div><br /></div><div>This way this entry enables the client to probe for which of the hosts is the active primary host and connections will always succeed</div><div><br /></div><div><br /></div><div> </div><div>3.4.<span style="white-space: pre;"> </span>Monitoring</div><div>The configuration and activity can basically be monitored from the Dataguard Broker command line. More detailed monitoring and alerting on issues is better done from Oracle Enterprise Manager.</div><div><br /></div><div>The fastest way to check the status of the Dataguard setup is to use the “show configuration” command from the Dataguard Broker command line.</div><div>This looks this:</div><div><br /></div><div><br /></div><div><span style="font-family: courier;">DGMGR> show configuration</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration - tsmt01c_config</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;"> Protection Mode: MaxPerformance</span></div><div><span style="font-family: courier;"> Members:</span></div><div><span style="font-family: courier;"> tsmt01c_ens - Primary database</span></div><div><span style="font-family: courier;"> tsmt01c_ams - Physical standby database</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Fast-Start Failover: Disabled</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration Status:</span></div><div><span style="font-family: courier;">SUCCESS (status updated 44 seconds ago)</span></div><div><br /></div><div>It is important that the status show “SUCCESS”</div><div><br /></div><div>If one looks in detail to the above screen output, it is visible that it is different from that on the previous page. There the tsmt01c_ams was primary database. Now we have switched roles and tsmt01c_ens is the primary database. </div><div>In the next chapter this is explained in detail </div><div><br /></div><div>3.5.<span style="white-space: pre;"> </span>Switchover</div><div>From a dataguard setup, the members can switch roles. This can be done manually using a switchover, or automatically in case of a failover.</div><div>A failover can occur when e.g. de primary database server physically fails. The broker can in that case decide to promote the standby to be the primary database. </div><div><br /></div><div>A failover or the setup of it, is not discussed in this document. </div><div><br /></div><div><br /></div><div>A switchover can be convenient in case of maintenance. If e.g. one of the servers needs to be shutdown, patched or something else, the standby database can take over the role of primary database. This makes it possible to shutdown the “before” primary database server.</div><div><br /></div><div>A switchover making the tsmt01c_ams the primary database server looks like this:</div><div><br /></div><div><span style="font-family: courier;">DGMGRL> switchover to tsmt01c_ams;</span></div><div><span style="font-family: courier;">Performing switchover NOW, please wait...</span></div><div><span style="font-family: courier;">New primary database "tsmt01c_ams" is opening...</span></div><div><span style="font-family: courier;">Operation requires start up of instance "TSMT01C" on database "tsmt01c_ens"</span></div><div><span style="font-family: courier;">Starting instance "TSMT01C"...</span></div><div><span style="font-family: courier;">Connected to an idle instance.</span></div><div><span style="font-family: courier;">ORACLE instance started.</span></div><div><span style="font-family: courier;">Connected to "TSMT01C_ENS"</span></div><div><span style="font-family: courier;">Database mounted.</span></div><div><span style="font-family: courier;">Connected to "TSMT01C_ENS"</span></div><div><span style="font-family: courier;">Switchover succeeded, new primary is "tsmt01c_ams"</span></div><div><span style="font-family: courier;">DGMGRL></span></div><div><br /></div><div>Confirming the switchover by looking at the resulting configuration shows:</div><div><br /></div><div><span style="font-family: courier;">DGMGRL> show configuration</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration - tsmt01c_config</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;"> Protection Mode: MaxPerformance</span></div><div><span style="font-family: courier;"> Members:</span></div><div><span style="font-family: courier;"> tsmt01c_ams - Primary database</span></div><div><span style="font-family: courier;"> tsmt01c_ens - Physical standby database</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Fast-Start Failover: Disabled</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">Configuration Status:</span></div><div><span style="font-family: courier;">SUCCESS (status updated 51 seconds ago)</span></div><div><span style="font-family: courier;">DGMGRL></span></div><div><br /></div><div><br /></div><div>3.6.<span style="white-space: pre;"> </span>Failovers</div><div>With Dataguard it is also possible to let the configuration automatically failover to the standby node in case of a failure of the primary node. </div><div>This is called Fast Start Fail Over, of FSFO</div><div><br /></div><div>To configure this, we should setup a so-called observer-server, a standalone server that is responsible for monitoring the primary database.</div><div>In case the observer notices that the primary database is not responding for a couple of seconds, for what kind of reason, it instructs the dataguard broker to switch the configuration to the standby database.</div><div><br /></div><div>For the current purpose of our Dataguard Setup, we do not need automatic failover, so we skip this configuration for now.</div><div><br /></div>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-46064644312843300582024-01-12T07:59:00.102+01:002024-01-12T08:44:17.199+01:00Patching Dataguard<div><span style="font-size: medium;"><b>1.<span style="white-space: pre;"> </span>Patching</b></span></div><div><br /></div><div>When patching a database it is normal to bring down the database, patch the ORACLE_HOME and finally run ‘datapatch’ to apply any database changes introduced with the patch to the database.</div><div>With Out-Of-Place patching a new ORACLE_HOME is installed next to the existing and the database is started with that HOME.</div><div><br /></div><div>But how do we do that with Dataguard?</div><div>Where we know that Dataguard applies changes done in the primary database onto the standby database using the redo-apply, we can’t patch the Dataguard the regular way.</div><div><br /></div><div>If we would patch the primary database the usual way, it will apply database changes to the standby database of a patch that may be unknown to the ORACLE_HOME of this standby database. </div><div><br /></div><div>1.1.<span style="white-space: pre;"> </span>The basics</div><div>This is why Oracle has introduced the so-called “Standby First Patching method”.</div><div>With this method, the ORACLE_HOME of the standby database is patched first. But we can’t run datapatch on this ORACLE_HOME as the database is not open to receive these changes. </div><div>So, after we have patched the ORACLE_HOME of the standby server, we switch roles, making the standby the primary. Then we can patch the ORACLE_HOME of the previous primary, now standby server.</div><div>Finally we run datapatch on the new primary and switch back.</div><div><br /></div><div>In steps:</div><div>1)<span style="white-space: pre;"> </span>Disable redo apply on standby database</div><div>2)<span style="white-space: pre;"> </span>Shutdown standby database</div><div>3)<span style="white-space: pre;"> </span>Patch the ORACLE_HOME on the standby database</div><div>4)<span style="white-space: pre;"> </span>Start the standby database </div><div>5)<span style="white-space: pre;"> </span>Start the redo apply</div><div><br /></div><div>6)<span style="white-space: pre;"> </span>Perform a switchover. The patched standby database now becomes primary database, but without that datapatch has- run.</div><div><br /></div><div>7)<span style="white-space: pre;"> </span>Now repeat step 1 to 5 for the “new” standby database</div><div>8)<span style="white-space: pre;"> </span>When finished, switch back roles</div><div>9)<span style="white-space: pre;"> </span>Finally run datapatch from the primary database.</div><div><br /></div><div>That concludes the in-place patching of a dataguard environment.</div><div>The above steps are only useful when patching an existing ORACLE_HOME, so-called in-place patching. </div><div>At Kramp we prefer- however out-of-place patching, where we install a new ORACLE_HOME with the patch already applied.</div><div><br /></div><div>With out-of-place patching the steps needed to be done are:</div><div>1)<span style="white-space: pre;"> </span>Install a new patched home on both the primary and secondary server into a new directory.</div><div>2)<span style="white-space: pre;"> </span>Disable redo apply on standby database</div><div>3)<span style="white-space: pre;"> </span>Shutdown standby database</div><div>4)<span style="white-space: pre;"> </span>Copy password file, spfile and dataguard config files from the </div><div>5)<span style="white-space: pre;"> </span>Change environment to the new ORACLE_HOME</div><div>6)<span style="white-space: pre;"> </span>OLD_ORACLE_HOME/dbs to the NEW_ORACLE_HOME/dbs</div><div>7)<span style="white-space: pre;"> </span>Start the database using the new ORACLE_HOME in mount mode</div><div>8)<span style="white-space: pre;"> </span>Start redo-apply</div><div>9)<span style="white-space: pre;"> </span>Switchover the primary database role to the new started standby database.</div><div>10)<span style="white-space: pre;"> </span>Repeat steps 1 to 7 for the former primary database server, now being the standby</div><div>11)<span style="white-space: pre;"> </span>When ready, run datapatch from the primary database</div><div>12)<span style="white-space: pre;"> </span>Switchover to let the initial primary database taking this role again</div><div><br /></div><div><br /></div><div>On the next pages both processes are seen with commands and screen output.</div><div><br /></div><div><br /></div><div>1.2.<span style="white-space: pre;"> </span>In place patching </div><div>In this section the real patch process of in-place patching is discussed.</div><div><br /></div><div>1)<span style="white-space: pre;"> </span>The first step is to disable the redo apply on the standby database. The command to use from SQLplus is: </div><div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiknwKXSaZu5BIYMOLin9Eif6TcxLrdRDWsdhxRux67rm2xsvBUN5B7rWJopUfS9aUET8OSkpshXRasc9LFJTi8wEG_KqT28yB1DA_wBRPpDtF0eYvGvEAMX2SloWqVKctiO-dRg17utPKh5iqHo8_oFiv5YZmMAAoxPB8ADjshFI6wh2dvWtzcZWQjAXY/s675/121.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="88" data-original-width="675" height="77" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiknwKXSaZu5BIYMOLin9Eif6TcxLrdRDWsdhxRux67rm2xsvBUN5B7rWJopUfS9aUET8OSkpshXRasc9LFJTi8wEG_KqT28yB1DA_wBRPpDtF0eYvGvEAMX2SloWqVKctiO-dRg17utPKh5iqHo8_oFiv5YZmMAAoxPB8ADjshFI6wh2dvWtzcZWQjAXY/w589-h77/121.PNG" width="589" /></a></div></div><br /></div><div>2)<span style="white-space: pre;"> </span>Now we can shutdown the Standby database following step 2:</div><div> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWpAnavOeVliH_W-Jss6lleEDxyZA701QhvmedhfCM_2f35bmVNGtYpx8bYbXqjtsOK49kSBh93ZryDQzRZcfrvEQxKzHNkGwF_l6oAU29IXYbN4IWLbr0u9wloJPKpZV15MlR8i_sZ-IEjpltx77vkCJrROvz3bMR-Xk6L68NaSTgeU-kgQSmNshKhUw/s593/122.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="185" data-original-width="593" height="139" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWpAnavOeVliH_W-Jss6lleEDxyZA701QhvmedhfCM_2f35bmVNGtYpx8bYbXqjtsOK49kSBh93ZryDQzRZcfrvEQxKzHNkGwF_l6oAU29IXYbN4IWLbr0u9wloJPKpZV15MlR8i_sZ-IEjpltx77vkCJrROvz3bMR-Xk6L68NaSTgeU-kgQSmNshKhUw/w446-h139/122.PNG" width="446" /></a></div><br /></div><div>Note that the “ORA-01109” confirms that we are connected to the standby database. </div><div>Stopping the database includes stopping the listener:</div><div> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyQ1r9htj0-Z6omE0juOYDylHG-FZIQMg9v35-ZTOgCx5AL_zcBAhFzxj2w7E7onFJMk4MacwY7scFZQ6ZU5XrwVRXtGlFWeg3xpWf-mY4sGB7dbFuQGTjVSbWYg3va5ob6GiZgxPhiFShfSdluDX1iiMfUqNZvtseYXYXa3fgxymbNhSNtygC0YjAjGM/s675/123.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="135" data-original-width="675" height="101" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyQ1r9htj0-Z6omE0juOYDylHG-FZIQMg9v35-ZTOgCx5AL_zcBAhFzxj2w7E7onFJMk4MacwY7scFZQ6ZU5XrwVRXtGlFWeg3xpWf-mY4sGB7dbFuQGTjVSbWYg3va5ob6GiZgxPhiFShfSdluDX1iiMfUqNZvtseYXYXa3fgxymbNhSNtygC0YjAjGM/w505-h101/123.PNG" width="505" /></a></div><br /></div><div>3)<span style="white-space: pre;"> </span>At this point we assume that we have extracted the patch on a location with sufficient space, e.g. /opt/tsmt01/exp.</div><div>Also the OPatch version in the ORACLE_HOME has been replaced with the newest version.</div><div><br /></div><div>Now we can apply the patches for database and ojvm:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjlvdMyR2O9vwSCRg1DiKgFFAzG6dJ8jUGlUnWVrtGJmyz5X_KpPmyxDoeFh8EUH_LPvSeucg4WrpWfrhkUbG194nR_auIC91MlTOaXyfj6Tj7HgsoYg_y9Tz410c_8csE6b2MPW4BwORby1BMCdrq_sFORolpOSg3CP-W6FbCbluHvzcbHgwWOHMFt78/s675/124.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="214" data-original-width="675" height="154" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjlvdMyR2O9vwSCRg1DiKgFFAzG6dJ8jUGlUnWVrtGJmyz5X_KpPmyxDoeFh8EUH_LPvSeucg4WrpWfrhkUbG194nR_auIC91MlTOaXyfj6Tj7HgsoYg_y9Tz410c_8csE6b2MPW4BwORby1BMCdrq_sFORolpOSg3CP-W6FbCbluHvzcbHgwWOHMFt78/w488-h154/124.png" width="488" /></a></div><div><br /></div><div>When finished, optionally clean out old replace patch-sets and release updates.</div><div>The statements to use are :</div><div><br /></div><div><span style="font-family: courier;">opatch util listorderedinactivepatches</span> </div><div><br /></div><div>This lists the inactive patches and release updates. </div><div>You can actually delete them, using the command:</div><div><br /></div><div><span style="font-family: courier;">opatch util deleteinactivepatches</span></div><div><br /></div><div>4)<span style="white-space: pre;"> </span>Then start the listener again.<br />Also start the standby database in MOUNT mode. </div><div>Do NOT run datapatch !!!</div><div><br /></div><div>5)<span style="white-space: pre;"> </span>Start the redo apply services again:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCFZgay-VwUdjNa3p82vbZec1kg1Ih_-13R3gKa6LF43J_-7Ens1cmSZ9UBilzMZ6RS3q4tQwwK6fq31BGqAVxU_F5Yf_hwFobCSrg0Y-_ghYf3plFCXhmsEa5h924UaTKYVAzCsFNKKR_2CoeVGGfQvNOK0o9bCcy9YWnSx7CjAgR8Cju7J5mvPCMNY4/s670/125.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="125" data-original-width="670" height="91" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCFZgay-VwUdjNa3p82vbZec1kg1Ih_-13R3gKa6LF43J_-7Ens1cmSZ9UBilzMZ6RS3q4tQwwK6fq31BGqAVxU_F5Yf_hwFobCSrg0Y-_ghYf3plFCXhmsEa5h924UaTKYVAzCsFNKKR_2CoeVGGfQvNOK0o9bCcy9YWnSx7CjAgR8Cju7J5mvPCMNY4/w485-h91/125.png" width="485" /></a></div><br /><div><br /></div><div>It may, like in this screenshot, give an error is redo apply has already been started.</div><div><br /></div><div>6)<span style="white-space: pre;"> </span>At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYIEbHkUPJZtys1uRgKQnBxTeopCuo0WgjLWn1ZlmxcbB66OKOai5n92jWCkDJbQOvc_vXEqimfQ4inN5lLO-MgjeO-5mfAPzy2ZT5vNKGC-bYBCsp_j4ENGOAkLDjzy8yEqf7M9cgK8nQVMQaSN2pizTs9w0gyP_YQhecEGlpZI-ro-4o5P7vJ2PnmE/s670/126.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="247" data-original-width="670" height="164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYIEbHkUPJZtys1uRgKQnBxTeopCuo0WgjLWn1ZlmxcbB66OKOai5n92jWCkDJbQOvc_vXEqimfQ4inN5lLO-MgjeO-5mfAPzy2ZT5vNKGC-bYBCsp_j4ENGOAkLDjzy8yEqf7M9cgK8nQVMQaSN2pizTs9w0gyP_YQhecEGlpZI-ro-4o5P7vJ2PnmE/w446-h164/126.PNG" width="446" /></a></div><div><br /></div><div>7)<span style="white-space: pre;"> </span>So… step 7 is skipped, as it would display everything from step 1 to 5 again</div><div>Step 7 finishes with the switchover to the initial primary database again. </div><div><br /></div><div>8)<span style="white-space: pre;"> </span>In step 8 we’re going to run datapatch to apply the database changes that have come with the patch</div><div><br /></div><div>Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwDAwDSDNgTVBpTTn5q0Td4ITiO8MZVIVYFMR1PDOc347ipzuwbUL2drC8-FkRgNpU9FH1BvZl8UIvOEN2nubIi3TwbaFay60Rnvo1cd21EXr-rqXOjzz5bLySTvzsezEDHaJHOmWJpfhEr0zUzuAMsu5886j-fMiu7tq9fk3Q8t1CW_HBlxTRlm-x7dQ/s667/127.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="320" data-original-width="667" height="215" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwDAwDSDNgTVBpTTn5q0Td4ITiO8MZVIVYFMR1PDOc347ipzuwbUL2drC8-FkRgNpU9FH1BvZl8UIvOEN2nubIi3TwbaFay60Rnvo1cd21EXr-rqXOjzz5bLySTvzsezEDHaJHOmWJpfhEr0zUzuAMsu5886j-fMiu7tq9fk3Q8t1CW_HBlxTRlm-x7dQ/w447-h215/127.PNG" width="447" /></a></div><div><br /></div><div>This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.</div><div><br /></div><div><br /></div><div><br /></div><div>1.3.<span style="white-space: pre;"> </span>Out of Place patching</div><div><br /></div><div>With out of place patching the downtime can be even more minimized. This method of patching uses a new patched installation as a new ORACLE_HOME.</div><div>The running database from the 1st ORACLE_HOME has to be brought down and can be immediately started from the 2nd ORACLE_HOME.</div><div><br /></div><div>Because downtime is only very short, it is questionable if a switchover would not result in the same amount of downtime, as there also both databases are briefly down together while switching roles. </div><div><br /></div><div>1)<span style="white-space: pre;"> </span>In this section we assume that the new ORACLE_HOME is already in place. </div><div>2)<span style="white-space: pre;"> </span>The first step is to disable the redo apply on the standby database. The command to use from SQLplus is: </div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiknwKXSaZu5BIYMOLin9Eif6TcxLrdRDWsdhxRux67rm2xsvBUN5B7rWJopUfS9aUET8OSkpshXRasc9LFJTi8wEG_KqT28yB1DA_wBRPpDtF0eYvGvEAMX2SloWqVKctiO-dRg17utPKh5iqHo8_oFiv5YZmMAAoxPB8ADjshFI6wh2dvWtzcZWQjAXY/s675/121.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="88" data-original-width="675" height="70" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiknwKXSaZu5BIYMOLin9Eif6TcxLrdRDWsdhxRux67rm2xsvBUN5B7rWJopUfS9aUET8OSkpshXRasc9LFJTi8wEG_KqT28yB1DA_wBRPpDtF0eYvGvEAMX2SloWqVKctiO-dRg17utPKh5iqHo8_oFiv5YZmMAAoxPB8ADjshFI6wh2dvWtzcZWQjAXY/w536-h70/121.PNG" width="536" /></a></div><br /><div><br /></div><div>3)<span style="white-space: pre;"> </span>Now we can shutdown the database and the listener</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWpAnavOeVliH_W-Jss6lleEDxyZA701QhvmedhfCM_2f35bmVNGtYpx8bYbXqjtsOK49kSBh93ZryDQzRZcfrvEQxKzHNkGwF_l6oAU29IXYbN4IWLbr0u9wloJPKpZV15MlR8i_sZ-IEjpltx77vkCJrROvz3bMR-Xk6L68NaSTgeU-kgQSmNshKhUw/s593/122.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="185" data-original-width="593" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWpAnavOeVliH_W-Jss6lleEDxyZA701QhvmedhfCM_2f35bmVNGtYpx8bYbXqjtsOK49kSBh93ZryDQzRZcfrvEQxKzHNkGwF_l6oAU29IXYbN4IWLbr0u9wloJPKpZV15MlR8i_sZ-IEjpltx77vkCJrROvz3bMR-Xk6L68NaSTgeU-kgQSmNshKhUw/w419-h131/122.PNG" width="419" /></a></div><br /><div><br /></div><div>4)<span style="white-space: pre;"> </span>Copy all parameter files, both for the database and the dataguard configuration, and the password file to the new ORACLE_HOME/dbs, e.g.</div><div><br /></div><div><span style="font-family: courier;">$ cd $OLD_ORACLE_HOME/dbs</span></div><div><span style="font-family: courier;">$ cp * $NEW_ORACLE_HOME/dbs</span></div><div><br /></div><div><br /></div><div>5)<span style="white-space: pre;"> </span>Set the environment tot the new ORACLE_HOME:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgy-uSZZEqa20BHcRwizktCkcaujNahErkP5Dys6uprPfJj1e2VWup7bRbqdc3Alh0Qi2Io1Q75hovlSnColeGk5e3n59kN4C6LYjvkkx65t-ZvanaZ3yA7wKxHS9zDcYFBb8HWU9zh57bYc0lHwCfnSSuiN7lqBThPxwFj_CDm4joAjrbE8Tf1x4J41-k/s909/134.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="85" data-original-width="909" height="56" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgy-uSZZEqa20BHcRwizktCkcaujNahErkP5Dys6uprPfJj1e2VWup7bRbqdc3Alh0Qi2Io1Q75hovlSnColeGk5e3n59kN4C6LYjvkkx65t-ZvanaZ3yA7wKxHS9zDcYFBb8HWU9zh57bYc0lHwCfnSSuiN7lqBThPxwFj_CDm4joAjrbE8Tf1x4J41-k/w600-h56/134.png" width="600" /></a></div><div><br /></div><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><p class="MsoNoSpacing" style="text-align: left;"><span lang="EN-US"><b>Also change the /etc/oratab and re-login
to make sure that also the PATH variable is set correctly. Otherwise binaries
from the OLD_ORACLE_HOME will be used to start the database again.</b></span></p></blockquote><div><br /></div><div>6)<span style="white-space: pre;"> </span>Edit the listener.ora file and update the ORACLE_HOME to point to the new location.</div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span>Then start the listener again with the new HOME.</span></div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span>Also start the standby database in MOUNT mode. </span></div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span>Do NOT run datapatch !!!</span></div><div>7)<span style="white-space: pre;"> </span>Start the redo apply services again:</div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCFZgay-VwUdjNa3p82vbZec1kg1Ih_-13R3gKa6LF43J_-7Ens1cmSZ9UBilzMZ6RS3q4tQwwK6fq31BGqAVxU_F5Yf_hwFobCSrg0Y-_ghYf3plFCXhmsEa5h924UaTKYVAzCsFNKKR_2CoeVGGfQvNOK0o9bCcy9YWnSx7CjAgR8Cju7J5mvPCMNY4/s670/125.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="125" data-original-width="670" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCFZgay-VwUdjNa3p82vbZec1kg1Ih_-13R3gKa6LF43J_-7Ens1cmSZ9UBilzMZ6RS3q4tQwwK6fq31BGqAVxU_F5Yf_hwFobCSrg0Y-_ghYf3plFCXhmsEa5h924UaTKYVAzCsFNKKR_2CoeVGGfQvNOK0o9bCcy9YWnSx7CjAgR8Cju7J5mvPCMNY4/w517-h97/125.png" width="517" /></a></div><br /></div><div><br /></div><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div style="text-align: left;">It may, like in this screenshot, give an error is redo apply has already been started.</div></blockquote><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div style="text-align: left;"> </div></blockquote><div>8)<span style="white-space: pre;"> </span>At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYIEbHkUPJZtys1uRgKQnBxTeopCuo0WgjLWn1ZlmxcbB66OKOai5n92jWCkDJbQOvc_vXEqimfQ4inN5lLO-MgjeO-5mfAPzy2ZT5vNKGC-bYBCsp_j4ENGOAkLDjzy8yEqf7M9cgK8nQVMQaSN2pizTs9w0gyP_YQhecEGlpZI-ro-4o5P7vJ2PnmE/s670/126.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="247" data-original-width="670" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYIEbHkUPJZtys1uRgKQnBxTeopCuo0WgjLWn1ZlmxcbB66OKOai5n92jWCkDJbQOvc_vXEqimfQ4inN5lLO-MgjeO-5mfAPzy2ZT5vNKGC-bYBCsp_j4ENGOAkLDjzy8yEqf7M9cgK8nQVMQaSN2pizTs9w0gyP_YQhecEGlpZI-ro-4o5P7vJ2PnmE/w537-h198/126.PNG" width="537" /></a></div><br /><div><br /></div><div>9)<span style="white-space: pre;"> </span>Repeat steps 1 through 7, to relocate the now running standby database to the new ORACLE_HOME</div><div>10)<span style="white-space: pre;"> </span>Switchover to let the initial primary database take this role again</div><div>11)<span style="white-space: pre;"> </span>Run datapatch to apply the database changes that have come with the patch</div><div><br /></div><div>Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:</div><div><br /></div><div> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwDAwDSDNgTVBpTTn5q0Td4ITiO8MZVIVYFMR1PDOc347ipzuwbUL2drC8-FkRgNpU9FH1BvZl8UIvOEN2nubIi3TwbaFay60Rnvo1cd21EXr-rqXOjzz5bLySTvzsezEDHaJHOmWJpfhEr0zUzuAMsu5886j-fMiu7tq9fk3Q8t1CW_HBlxTRlm-x7dQ/s667/127.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="320" data-original-width="667" height="270" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwDAwDSDNgTVBpTTn5q0Td4ITiO8MZVIVYFMR1PDOc347ipzuwbUL2drC8-FkRgNpU9FH1BvZl8UIvOEN2nubIi3TwbaFay60Rnvo1cd21EXr-rqXOjzz5bLySTvzsezEDHaJHOmWJpfhEr0zUzuAMsu5886j-fMiu7tq9fk3Q8t1CW_HBlxTRlm-x7dQ/w561-h270/127.PNG" width="561" /></a></div><br /></div><div><br /></div><div>This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.</div><div><br /></div><div><br /></div><div><span style="font-size: medium;"><b>2.<span style="white-space: pre;"> </span>Troubleshooting</b></span></div><div>In this section, issues encountered during testing and writing of this blog are discussed. </div><div>Actually only two issues occurred.</div><div><br /></div><div>2.1.<span style="white-space: pre;"> </span>Listener configuration</div><div>The first was during the out-of-place patching where the primary was unable to find the standby database, showing:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVecUzqj6SS2gTwzWyEfI5R5dwjVDyev8gSHN0IN99mFRbVjpY9hWnWfzgFZJNAKqoF1cJvhWy_Cp3JagKfmYyy2RhfXrziXw32pp2-fISiz-zdWcIW9RtNYOscrtDu3Untv3ilfYZ_LKd8n4hh3aFad9uSaBfpdJ9m5FYOOEF5yoXRuYA2woRwpGbdvs/s910/211.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="267" data-original-width="910" height="170" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVecUzqj6SS2gTwzWyEfI5R5dwjVDyev8gSHN0IN99mFRbVjpY9hWnWfzgFZJNAKqoF1cJvhWy_Cp3JagKfmYyy2RhfXrziXw32pp2-fISiz-zdWcIW9RtNYOscrtDu3Untv3ilfYZ_LKd8n4hh3aFad9uSaBfpdJ9m5FYOOEF5yoXRuYA2woRwpGbdvs/w579-h170/211.png" width="579" /></a></div><div><br /></div><div>If you encounter this error, when having done out of place patching of the standby and the database and listener have been started on the standby server, then you probably have not updated the listener.ora file, having the ORACLE_HOME pointing to the new location in the static listener entry:</div><div>If the above is true, this is confirmed in the dataguard broker logfile “drcTSMT01C.log”</div><div>There is may say:</div><div><br /></div><div><span style="font-family: courier;">Data Guard Broker Status Summary:</span></div><div><span style="font-family: courier;"> Type Name Severity Status</span></div><div><span style="font-family: courier;"> Configuration tsmt01c_config Warning ORA-16607: one or more members have failed</span></div><div><span style="font-family: courier;"> Primary Database tsmt01c_ams Error ORA-16778: redo transport error for one or more members</span></div><div><span style="font-family: courier;"> Physical Standby Database tsmt01c_ens Error ORA-01034: ORACLE not available</span></div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjt0JT5LceMHKPUkaD8oZTuSrvVlN_yazrsrCzI2N8GTJ3ztsbVLgxkmgV-02yUyKGjdQPl4MPafP-9CsraJQc-ZQIifFhHw_Bz530bB53VTGnutG1_bXoFkZFtWA8XpWRy5slJM4XbF1xAnLyZqvOG3YwDyDVe9GSvPtBoz8oPnkgxrhazVEMYxz_oJ60/s791/212.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="468" data-original-width="791" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjt0JT5LceMHKPUkaD8oZTuSrvVlN_yazrsrCzI2N8GTJ3ztsbVLgxkmgV-02yUyKGjdQPl4MPafP-9CsraJQc-ZQIifFhHw_Bz530bB53VTGnutG1_bXoFkZFtWA8XpWRy5slJM4XbF1xAnLyZqvOG3YwDyDVe9GSvPtBoz8oPnkgxrhazVEMYxz_oJ60/w394-h233/212.PNG" width="394" /></a></div>The problem is caused by the dataguard broker connecting the the standby listener, which in turn can not find a running database from the old ORACLE_HOME.</div><div><br /></div><div>To solve this problem, stop the Standby database and listener again, and update the ORACLE_HOME entry in the listener.ora as seen here. After updating the listener.ora restart the listener and database and all should work as expected.</div><div><br /></div><div><br /></div><div>2.2.<span style="white-space: pre;"> </span>Switchover failing</div><div>During the patch process the configuration is switched over from primary to standby two times. This ensures that the database will remain available for the clients</div><div><br /></div><div>It may happen that a switchover fails, in restarting the two databases in the correct mode. An error in Dataguard Broker is then seen:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaMRA8pTbkaeQwme4QUe-vsGp_c5ab2l5GKZUd4oM6fERxyuOFQ_gM55XJzfrscZeSdTKKnB2CO_YlUemCM45wpYqCPpBGJIvw05z06i1rTFTGeeFQxjoMlOg2iNIB2jzoZ-MouKk-flR_5npvl8SW1Pjvs5UIASlsOChoyWcCazjO5hDbDY2kQ5crS4Q/s1027/221.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="392" data-original-width="1027" height="248" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaMRA8pTbkaeQwme4QUe-vsGp_c5ab2l5GKZUd4oM6fERxyuOFQ_gM55XJzfrscZeSdTKKnB2CO_YlUemCM45wpYqCPpBGJIvw05z06i1rTFTGeeFQxjoMlOg2iNIB2jzoZ-MouKk-flR_5npvl8SW1Pjvs5UIASlsOChoyWcCazjO5hDbDY2kQ5crS4Q/w651-h248/221.PNG" width="651" /></a></div><br /><div><br /></div><div>Somehow the switchover was partly done. </div><div>Before acting upon this and blindly follow the action step, make sure that you check the other database if it is open in read-write mode and also if it is primary. </div><div>This can be done using this query:</div><div><br /></div><div><span style="font-family: courier;">SQL> column open_mode format a20</span></div><div><span style="font-family: courier;">SQL> column database_role format a20;</span></div><div><span style="font-family: courier;">SQL> set linesize 100</span></div><div><span style="font-family: courier;">SQL> select open_mode, database_role from v$database;</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;">OPEN_MODE DATABASE_ROLE</span></div><div><span style="font-family: courier;">-------------------- --------------------</span></div><div><span style="font-family: courier;">READ WRITE PRIMARY</span></div><div><br /></div><div>It should return the same as seen here. Otherwise manual intervention is needed to really dig in to logfiles to find out where the switchover has gone of track.</div><div><br /></div><div>If it returns the same output as seen here, you can safely start the standby in mount mode as indicated which will solve the problem.</div><div><br /></div>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-14222736323205868162023-04-18T10:56:00.011+02:002023-11-07T22:22:36.531+01:00Performance degrade after upgrade from 11.2 to 19c on AIX because of Materialized View Logs<p>Over the last couple of weeks we have been working at the upgrade of an AIX based database. This database now has been running on 11.2.0.4 for many years. Because of application restrictions any higher database version was not possible until now.</p><p>Some specs:</p><p>Server is an LPAR on a IBM PSeries P9<br />It has 8 logical cores and 48Gb of memory.<br />All storage is SSD or better.<br />All file systems are mounted read/write, without CIO</p><p>The upgraded 19c database is a Single Instance database, no container/multitenant.</p><p>The challenge here is that we see a performance degradation of approx. 2 to 3 times after upgrade to 19c<br />There where a huge update statement runs 18 minutes on 11.2, the same statement takes 37 to 45 minutes on 19c!<br />The statement updates some 65 million rows in a table, using a where clause on an non-indexed column.</p><p>What we're clearly observing from AWR report is the fact that on 11.2 there are no significant wait seen for the inserts on the MVlog, as if it is something implicit.<br />On 19c the inserts into the MVlog are much more substantial in wait time, I/O usage etc..</p><p>The above has been written on April 18th 2023.<br />It is now mid-June and after weeks of investigating and trying to get Oracle Support in line with our findings, we still did not find a solution. <br /><br /></p><p>Update:<br />It is October 2023 and Oracle Support has filed a bug-request.<br />Now we wait for the Development team to research the matter</p><p></p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-71109089667929076392022-11-11T10:34:00.007+01:002023-11-07T22:21:54.154+01:00Refreshable PDBs, a DR setup made easy<p>With the introduction of Oracle CDB-PDB architecture, DBA's had to get used to a whole way of thinking about databases. Databases became more flexible, more volatile in a sense that you can do many cool things with PDB's, easier upgrades, cloning etc...</p><p>With the release of Oracle 12.2 the cloning of PDB's got extended with a option to refresh a remote PDB with data from a local source PDB. The clone you created in a remote location can now be updated automatically with changes from its local partner (source) by applying redo that is retrieved over a database link.</p><p>This is called a <b>Refreshable Clone PDB</b>. <br /><br />When thinking about these kind of options, DBA's may start to think of Dataguard, the Standby Database solution for Oracle Enterprise Editions. <br />The Refreshable Clone PDB is much of the same, but then free of any extra costs and very easy to setup and maintain. Even better, the Refreshable Clone PDB also works on Standard Edition, so an Enterprise Edition License is not needed here. <br />Of course the remote database server must be licensed correctly to run this database.</p><p>There are a number of advantages of having a Refreshable Clone PDB:</p><p></p><ul style="text-align: left;"><li>When the Clone is on another server, you can use it as a Disaster Recovery solution. The <b>RPO </b>is then dependent on the refresh rate you specify when creating the remote clone. <br /><b>RTO </b>is depending on the amount of work needed, to change the role of the clone to a production status in case of a disaster. </li><li>When there is no disaster and when there is sufficient archivelog space on the source database, one can even open up the clone in a "read only" state for reporting purposes, thus unloading the source database. Keep in mind that the clone does not get updated when it is open and thus gets out of sync with its source. The redo can only be applied, when the clone is closed.<br />Opening a database for reporting like this, hugely increases RTO and RPO.</li></ul>A Refreshable Clone PDB can either be refreshed automatically or manually. When creating the clone this is specified in the "CREATE PLUGGABLE DATABASE" command. Of course the behavior can also changed afterwards. Manual refreshes can be run anyway, even in automatic refresh mode. <p></p><p>Some requirements before you can create a Refreshable Clone PDB:</p><p></p><ul style="text-align: left;"><li>A CDB on the remote location must already exist having the same patch version and components as the source CDB</li><li>A database link must be created from remote CDB, connecting to the local (source) CDB, using the SYSTEM account</li><li>The SYSTEM account in the source CDB must have "CREATE PLUGGABLE DATABASE" rights. In Oracle 12.2, this was the case, however in 19c this right is by default only granted to SYS.</li><li>The remote CDB must have the PDB_FILE_NAME_CONVERT parameter set in the spfile, if the directory structure of the clone does not match that of the source.</li></ul><br /><p></p><p><b>Example:</b><br /><br />In this example we assume that we have two CDBs, a CDB1 as the source and a CDB2 as the remote location. The PDB in the source is called HR_PROD. It is up to you if you want to name the Clone PDB the same. For DR this may be convenient, as this eases a failover. When used for reporting the Clone PDB may get another name. <br /><br />We assume that you have entered the correct connection string (named CDB1) in the tnsnames.ora on the remote server, pointing to the source server. <br /><br />We start with creating the database link:</p><p><span style="font-family: courier;">SQL> create database link TO_CDB1 <br /></span><span style="font-family: courier;"> connect to system identified by yourpassword <br /> using 'CDB1';</span></p><p>Now we are creating the Refreshable Clone PDB:</p><p><span style="font-family: courier;">SQL> alter system set <br /></span><span style="font-family: courier; font-size: small;"> pdb_file_name_convert='/u01/oradata/hrprod','/u01/oradata/hrclone' <br /></span><span style="font-family: courier; font-size: small;"> scope=both;</span></p><p><span style="font-family: courier;">SQL> create pluggable database HR_CLONE from HR_PROD@TO_CDB1<br /></span><span style="font-family: courier; font-size: small;"> refresh mode every 5 minutes;</span></p><p>That's it !</p><p>Checking the result shows:</p><p><span style="font-family: courier;">SQL> show pdbs;<br /></span><span style="font-family: courier; font-size: small;"><br /> CON_ID CON_NAME OPEN MODE RESTRICTED<br /></span><span style="font-family: courier; font-size: small;">---------- ------------------------------ ---------- ----------<br /></span><span style="font-family: courier; font-size: small;"> 2 PDB$SEED READ ONLY NO<br /></span><span style="font-family: courier; font-size: small;"> 3 HR_CLONE MOUNTED</span></p><div><br /></div><div>If you now start monitoring the alert_log of the CDB2, then messages like this will appear:</div><div><br /></div><div><div><div><span style="font-family: courier;">HR_CLONE(3):Serial Media Recovery started</span></div><div><span style="font-family: courier;">HR_CLONE(3):max_pdb is 3</span></div><div><span style="font-family: courier;">2022-11-11T09:23:34.574917+01:00</span></div><div><span style="font-family: courier;">HR_CLONE(3):Media Recovery Log /u01/oradata/hrprod/fr01/CDB1/archivelog/2022_11_11/o1_mf_1_3270_kpw1ft17_.arc</span></div><div><span style="font-family: courier;">2022-11-11T09:23:34.618074+01:00</span></div><div><span style="font-family: courier;">HR_CLONE(3):Media Recovery Log /u01/oradata/hrprod/fr01/CDB1/foreign_archivelog/HR_PROD/2022_11_11/o1_mf_1_3271_kpw1n6gy_.arc</span></div><div><span style="font-family: courier;">2022-11-11T09:23:34.671022+01:00</span></div><div><span style="font-family: courier;">HR_CLONE(3):Incomplete Recovery applied until change 104159016 time 11/11/2022 09:23:34</span></div><div><span style="font-family: courier;">2022-11-11T09:23:34.672222+01:00</span></div><div><span style="font-family: courier;">HR_CLONE(3):Media Recovery Complete (CDB2)</span></div><div><span style="font-family: courier;">HR_CLONE(3):Completed: alter pluggable database refres</span></div></div></div><div><br /></div><div>Some other commands to manage the Refreshable Clone PDB are:</div><p>Manual refresh:<br /><span style="font-family: courier;">SQL> alter pluggable database HR_CLONE refresh;</span></p><p>Change the refresh interval:<br /><span style="font-family: courier;">SQL> alter pluggable database HR_CLONE refresh mode manual;</span><br />or<br /><span style="font-family: courier;">SQL> alter pluggable database HR_CLONE refresh mode every 30 minutes;</span></p><p>Open the database:<br /><span style="font-family: courier;">SQL> alter pluggable database HR_CLONE open read only;</span></p><p>Thinking about this, it may be convenient to first put refresh mode to manual, before opening the database. This prevent you from getting lots of errors in the alert_log;</p><div>To resume the replication of the redo, the PDB must be closed again and a refresh interval set.</div><p><br /></p><p><b>Switchover</b></p><p>Because a Refreshable Clone PDB is not a Dataguard solution, a switchover is something manual, to be performed by the DBA. With Dataguard, failovers can be automated with near zero loss of data. <br />When however doing a switchover correctly, the latest transactions are taken into account, also the switchover of a Cloned PDB must be possible with near zero dataloss.</p><p>We need to take into account that in the above database the refresh rate is set to 5 minutes. This means that every 5 minutes the source database is probed for changes. Whenever we do a switchover in between two such probes / refreshes, then we may loose changes applied in the source database during that interval. <br /><br />First: Switchover can be done by the database by using an ALTER PLUGGABLE DATABASE .... SWITCHOVER command, but this feature is restricted to Enterprise Edition on Exadata and all editions on Oracle Cloud Services.<br />To overcome this for on-premise databases a hidden parameter can be set:<br /><br /><span style="font-family: courier;">SQL> alter system set "_exadata_feature_on"=true scope=spfile;</span></p><p>A test using an on-premise Standard Edition runs into -yet- unresolvable errors, even when setting that parameter. It may be of the Standard Edition, but it may also be that the available documentation on the Internet is incomplete. <br /><br /><br />A manual switchover however is possible. Whenever we shutdown or loose the source database, we can disable the Refresh Mode on the Clone and open it in Read/Write mode to start using it.<br />This then looks like this:</p><p><span style="font-family: courier;">SQL> alter pluggable database HR_CLONE refresh;<br /><br />Pluggable database altered.<br /><br />SQL> alter pluggable database HR_CLONE refresh mode none;<br /><br />Pluggable database altered.<br /><br />SQL> alter pluggable database HR_CLONE open;<br /><br />Pluggable database altered.</span></p><div>If you do not disable the automatic refresh mode, the pluggable database can not be opened in Read/Write mode and the command errors out.</div><div><br />After opening the PDB in Read/Write mode it can not be reverted to Refreshable Clone. <br />It has to be recreated.</div><div><br /></div><div><br /></div><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-85037227103460584432022-10-13T13:48:00.006+02:002023-11-07T22:22:51.673+01:00DIAGNOSTIC & TUNING packs automatically used<p>We have configured an Oracle Enterprise Manager report that shows us the use of EE-options where we don't have a license.</p><p>What shows up, immediately started to confuse us. <br />It showed that the DIAGNOSTIC+TUNING option was used in several databases:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitxBjZ_WLH6hK48hlyVLRTcQR80lhQu9Gp6zVj57OxvkfleZOoxDcrpzEmM3VS6g6yTfUdgvLxKBVCCl8Gvyx0chcYy7AGj2r-Wuj_8VLtT6be1P5iQSHJwv_ymS_AgXyfNZ1e22QcrzohRdsk3OUbbz4GUartjY4BTuKeMTQ-GdiKQw1R7qZUm0ca/s461/OEMlist.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="204" data-original-width="461" height="172" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitxBjZ_WLH6hK48hlyVLRTcQR80lhQu9Gp6zVj57OxvkfleZOoxDcrpzEmM3VS6g6yTfUdgvLxKBVCCl8Gvyx0chcYy7AGj2r-Wuj_8VLtT6be1P5iQSHJwv_ymS_AgXyfNZ1e22QcrzohRdsk3OUbbz4GUartjY4BTuKeMTQ-GdiKQw1R7qZUm0ca/w389-h172/OEMlist.png" width="389" /></a></div><div class="separator" style="clear: both; text-align: left;">When checking the settings from within Oracle Enterprise Manager => Management Pack Usage, it showed that the packs were not in use, the radio-button was not active:</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLkHLmKawRRW1NDDOvEc1JLfxXYCyL086Hmv77Ur4YMhcoQrEp-oAGpx1ddOR65zjiglIo78BD05YyabYBQ6JSPC3ZT8n7XKQjfQ6oyO7Xk9Dy6kl2IyIDWqwj4OSRh-Rg-mUrhcIsZuoMLsxe-ep8zMrIL7F8Qs8ZDHmSIned_QmgcKNIPxpNneZg/s624/OEMusages.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="177" data-original-width="624" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLkHLmKawRRW1NDDOvEc1JLfxXYCyL086Hmv77Ur4YMhcoQrEp-oAGpx1ddOR65zjiglIo78BD05YyabYBQ6JSPC3ZT8n7XKQjfQ6oyO7Xk9Dy6kl2IyIDWqwj4OSRh-Rg-mUrhcIsZuoMLsxe-ep8zMrIL7F8Qs8ZDHmSIned_QmgcKNIPxpNneZg/w523-h127/OEMusages.png" width="523" /></a></div><br /><div class="separator" style="clear: both; text-align: left;">When clicking on the Edit button next to the radio-button, it surprised us to see this:</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLq8V8mIorHt_jbHXZwvivYOBpB3REp0qSx2_kfmcDnjP2skvxnBo_35262HNz4hp7YDEXZnGvaGpAc3_1fS9_CkYICtMxjYPnGpCARyxZ2eQLOWralwE-n8p0uaBPN84b659-uDxxwgEoDzK9rdlwBVva7_A0pNy12KWdiDmUejxSX2p142UNx9mv/s1006/packusage.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="98" data-original-width="1006" height="50" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLq8V8mIorHt_jbHXZwvivYOBpB3REp0qSx2_kfmcDnjP2skvxnBo_35262HNz4hp7YDEXZnGvaGpAc3_1fS9_CkYICtMxjYPnGpCARyxZ2eQLOWralwE-n8p0uaBPN84b659-uDxxwgEoDzK9rdlwBVva7_A0pNy12KWdiDmUejxSX2p142UNx9mv/w744-h50/packusage.JPG" width="744" /></a></div>So it looks to enabled, unless the previous screen did not show that.<div><br /></div><div>When looking at the database itself, we see the following:</div><div><br /></div><div><div><span style="font-family: courier; font-size: x-small;">SQL> show parameter control_management_pack_access</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">NAME TYPE</span></div><div><span style="font-family: courier; font-size: x-small;">------------------------------------ ---------------------------------</span></div><div><span style="font-family: courier; font-size: x-small;">VALUE</span></div><div><span style="font-family: courier; font-size: x-small;">------------------------------</span></div><div><span style="font-family: courier; font-size: x-small;">control_management_pack_access string</span></div><div><span style="font-family: courier; font-size: x-small;">DIAGNOSTIC+TUNING</span></div><div><span style="font-family: courier; font-size: x-small;">SQL></span></div><div><br /></div><div>So, it is active.</div><div><br /></div><div>Checking the spfile it is not defined there. So activation is probably a default. </div><div>Then we found this <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/CONTROL_MANAGEMENT_PACK_ACCESS.html#GUID-A5CA11BA-AE1F-4A2E-9C59-A98BA1F4E5C2" target="_blank">document</a>:<br /><br /></div><div>where is states:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGlsG-jMU7V1X8W4PwT1zl6Q7P9NC-g9KeJrrYWTxZnwPt5uUFS35cP71DBC5_Ci02YrHH_1tGqo_bRR0ClvVwW1952pRxmpPBH9h95mai4lJgu1oprIq9kxZF6XoVKmy2w8sS3x_fswWs8T7Gb2PVBUKtvmBxWk7WkEAkGH3XKjTj__t4CmxVg9Jm/s785/PackDoc.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="285" data-original-width="785" height="136" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGlsG-jMU7V1X8W4PwT1zl6Q7P9NC-g9KeJrrYWTxZnwPt5uUFS35cP71DBC5_Ci02YrHH_1tGqo_bRR0ClvVwW1952pRxmpPBH9h95mai4lJgu1oprIq9kxZF6XoVKmy2w8sS3x_fswWs8T7Gb2PVBUKtvmBxWk7WkEAkGH3XKjTj__t4CmxVg9Jm/w376-h136/PackDoc.jpg" width="376" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><div>So, when installing an Enterprise Edition of Oracle, and you don't have any pack licenses, you should explicitly set this option to NONE.</div><div><br /></div><div>I myself find this default value very much up to discussion. When a junior DBA installs a database for the company he starts working for, creates unknowingly a huge risk of extra costs when subjected to a License Audit. <br /><br />The solution is easy, but also easily forgotten when installing a new database release:<br /><br /><div><span style="font-family: courier; font-size: x-small;">SQL> alter system set control_management_pack_access=NONE scope=both;</span></div><div><br /></div><div><br /></div></div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div><br /><br /><p></p></div><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-17340965514304672192022-09-14T16:25:00.003+02:002023-11-07T22:23:07.017+01:00Using Oracle Wallets<p>An Oracle Wallet is a file that contains encrypted credentials for databases. This prevents the use of hardcoded passwords in scripts and/or on the command line. <br />When using an Oracle Wallet the username and password are retrieved from this file on setting up the connection. </p><p>The goal of using a wallet is to increase security. There were scripting in small companies usually contain plain-text passwords, the use of an Oracle Wallet can change this, when e.g. a company increases and more employees are using these scripts.</p><p>A small disadvantage of using an Oracle Wallet is that the wallet can contain only one set of credentials per database name. <br />Since it however uses TNS entries to connect to a database, one can store this one set of credentials per TNS name. <br />So in the case that you have a database called ORADB, TNS entries for each user group can be created in tnsnames.ora and thus credentials for each group. <br />Using e.d. credentials for batch jobs and other credentials for an application, two entries in tnsnames.ora would be sufficient to support his, e.g. ORADB_BATCH & ORADB_APP.</p><p>Setting up the use of a Wallet is easy. First of course a wallet has to be created. Use the following command to do so:<br /></p><p><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -create</span></p><p style="text-align: left;">The entered location is a directory where the wallet is created. Make sure the directory used exists.<br />The command will ask you to enter a password. This password is used to encrypt the Wallet. Store this Wallet password somewhere on a safe place. It is needed every time when adding/removing or changing entries in the wallet. </p><div>Adding/Deleting or changing entries in a wallet uses a similar syntax. Every type of action you perform on a wallet uses the "mkstore" command. <br />Adding an entry is done like this:</div><div><br /></div><div><span style="font-size: x-small;"><span style="font-family: courier;">mkstore -wrl </span><span style="font-family: courier;">/u01/oradata/wallet</span><span style="font-family: courier;"> -createCredential TNS_NAME USER PASSWORD</span></span></div><div><br /></div><div>Example:</div><div><span style="font-family: courier; font-size: x-small;">mksore -wrl /u01/oradata/wallet -createCredential ORADB_APP scott tiger</span></div><div><br /></div><div><br /></div><div>But now how do scripts use the wallet?</div><div><br /></div><div>First we need to setup sqlnet.ora This file is usually found in the location defined by $TNS_ADMIN. IF it is not present, then create a new file and add the following contents to it:</div><div><br /></div><div><div><span style="font-family: courier; font-size: x-small;">WALLET_LOCATION =</span></div><div><span style="font-family: courier; font-size: x-small;"> (SOURCE =</span></div><div><span style="font-family: courier; font-size: x-small;"> (METHOD = FILE)</span></div><div><span style="font-family: courier; font-size: x-small;"> (METHOD_DATA =</span></div><div><span style="font-family: courier; font-size: x-small;"> (DIRECTORY = /u01/oradata/wallet)</span></div><div><span style="font-family: courier; font-size: x-small;"> )</span></div><div><span style="font-family: courier; font-size: x-small;"> )</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">SQLNET.WALLET_OVERRIDE = TRUE</span></div><div><br /></div></div><div><br /></div><div>The WALLET_LOCATION part is self-explaining. <br />The WALLET_OVERRIDE parameter allows this method to override any existing OS authentication configuration. </div><div>When adding an sqlnet.ora file or making changes to it, a listener restart is required to activate it.</div><div>This means that switching to the use of a wallet, requires a brief unavailability of the database for the business.</div><div><br /></div><div>Using the wallet is now easy. <br />Any connections to the database not using a credential, will use the stored credential in the wallet, e.g.:</div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">sqlplus /@ORADB_APP</span></div><div><br /></div><div>will succesfully login to the database.</div><div><br /></div><div>To manage the wallet over time, the following commands can be needed:</div><div><br /></div><div><div>1. List all wallet entries</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -listCredential</span></div><div><br /></div><div>2. Show password for specific entry</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -listCredential</span></div><div><br /></div><div>Using presented list, use the INDEXnumber , e.g.:</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -viewEntry oracle.security.client.password13</span></div><div><br /></div><div>3. Manual entry creation (TNS connection name must be unique) within wallet</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -createCredential TNS_NAME USER PASSWORD</span></div><div><br /></div><div>Example:</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -createCredential oradb_app scott tiger</span></div><div><br /></div><div>4. Manual entry deletion</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -deleteCredential TNS_NAME</span></div><div><br /></div><div>This will delete "all", entries for this TNS_NAME, but since a wallet can store only one credential per TNS_NAME, it will delete that credential</div><div><br /></div><div>5. Wallet deletion</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -delete</span></div><div><br /></div><div>Note that this will delete the complete wallet. All entries are lost !!</div><div><br /></div><div>6. Modifying an Entry:</div><div><span style="font-family: courier; font-size: x-small;">mkstore -wrl /u01/oradata/wallet -modifyCredential TNS_NAME USER PASSWORD</span></div><div><br /><br />The wallet is also usable for e.g. RMAN in combination with a catalog database, like:</div></div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">$ORACLE_HOME/bin/rman target / catalog /@RMANCAT</span></div><div><br /></div><div><br /></div><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-19831557614178293602022-04-29T11:51:00.003+02:002023-11-07T22:24:00.691+01:00DBSAT and the risks for EBS databases<p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>A couple of months ago we went on investigating the capabilities of Oracle's DBSAT, the DataBase Security and Assessment Tool.</p><p>The tool itself if powerful, it generates detailed information about vulnerabilities in your databases. The information is presented in readable HTML format that enables quick handling to reduce seurity risks.</p><p><br /></p><p>However....</p><p>One special point of attention is needed here and we already made a mistake here.<br />DBSAT recognizes the installation of the so-called "sample schema's" in the scanned database and reports on this detailed, looking like this:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYdc9UDQA6XUFJ6YiPXLD2hPpKlqwUqprRukVZcfVyO38X5UzkIM2DoJx5iJ-2I50J5JPCGqKHFJJF72NCNUx4qiRfr-s13jTDyYqYF44VqXvW3nWyMJS1s1oWPpjkIU6KsUPM-uH1Rp_kifnj8Dw_eogPfrcJKWimV2Z2x-UHob7FTchcrz7yoROw/s397/Capture.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="112" data-original-width="397" height="140" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYdc9UDQA6XUFJ6YiPXLD2hPpKlqwUqprRukVZcfVyO38X5UzkIM2DoJx5iJ-2I50J5JPCGqKHFJJF72NCNUx4qiRfr-s13jTDyYqYF44VqXvW3nWyMJS1s1oWPpjkIU6KsUPM-uH1Rp_kifnj8Dw_eogPfrcJKWimV2Z2x-UHob7FTchcrz7yoROw/w499-h140/Capture.PNG" width="499" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>The special point of attention is regarding EBS database, as they have HR and OE schema's but then for real usage !!</p><p><br /></p><p>The reporting of DBSAT that Sample Schema's are installed in EBS databases, which need to be dropped, is a so-called False Positive.</p><p>For EBS database DO NOT DROP these schema's, as it will render the EBS application unusable </p>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-31480997409933432282021-11-05T11:17:00.003+01:002023-11-07T22:24:27.717+01:00CIO mount on AIX and Oracle 19c<p>Today we faced a really strange issue. <br />We are in the midst of upgrading all our databases to the Oracle 19c release, or patching existing installations to the latest patch level.</p><p>After working through a lot of databases on Redhat 7.x without any problems, the first database to be upgraded on IBM AIX7.2 was next. <br />Release installation went fine, patching to the latest level also went without issues.</p><p>However when creating an empty CDB to house/upgrade the 12.1 PDB, we ran into strange problems. DBCA threw an error ORA-00205: error identifying control file:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-bxJ73C8ZYDE/YYTbtTI0sbI/AAAAAAABi_U/bX-UqwOoPaIMlVA5m9uypP8G00a7UKHjQCLcBGAsYHQ/s805/dbca1.JPG" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="636" data-original-width="805" height="314" src="https://1.bp.blogspot.com/-bxJ73C8ZYDE/YYTbtTI0sbI/AAAAAAABi_U/bX-UqwOoPaIMlVA5m9uypP8G00a7UKHjQCLcBGAsYHQ/w397-h314/dbca1.JPG" width="397" /></a></div><br /><p>Looking at the file system location where the databases was planned to be, control files were present as expected. <br />Oracle support suggested to run a DBverify on the file, but that also sh<span style="font-family: times;">owed n</span>o errors:</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /><br /></p><p><span style="font-family: courier; font-size: x-small;">$ORACLE_HOME/bin/dbv file=/opt/hisd01/db01/control01.ctl blocksize=16384</span></p><p><span style="font-family: courier; font-size: x-small;">DBVERIFY: Release 19.0.0.0.0 - Production on Fri Nov 5 08:54:12 2021</span></p><p><span style="font-family: courier; font-size: x-small;">Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.</span></p><p><span style="font-family: courier; font-size: x-small;">DBVERIFY - Verification starting : FILE = /opt/hisd01/db01/control01.ctl</span></p><p><span style="font-family: courier; font-size: x-small;">DBVERIFY - Verification complete</span></p><p><span style="font-family: courier; font-size: x-small;">Total Pages Examined : 880<br /></span><span style="font-family: courier; font-size: small;">Total Pages Processed (Data) : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Failing (Data) : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Processed (Index): 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Failing (Index): 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Processed (Other): 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Processed (Seg) : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Failing (Seg) : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Empty : 880<br /></span><span style="font-family: courier; font-size: small;">Total Pages Marked Corrupt : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Influx : 0<br /></span><span style="font-family: courier; font-size: small;">Total Pages Encrypted : 0<br /></span><span style="font-family: courier; font-size: small;">Highest block SCN : 0<br /><br /></span></p><p><span style="font-family: times;">DBverify did not show any problems with the file. All pages were empty, but that is explained by the fact that the control files were newly created.</span></p><p><span style="font-family: times;">So, then what is the problem?<br />Why does the same action on Redhat 7.2 work like a charm, but throws this strange error on IBM AIX ?</span></p><p>For the answer we need to go back in time:</p><p>With the older Oracle releases running on IBM AIX, it was common to mount the file systems that contained database files and/or redo-log files with the CIO option. This enabled Concurrent I/O for the Oracle database, bypassing the file system cache.<br />As of Oracle 10g this requirement has changed.<br />I must admit that we never spend much attention on this change, as our UNIX system-admins were still delivering us systems with file systems that were CIO-mounted and performance was OK on all servers.</p><p>A very detailed explanation about CIO on AIX can be found <a href="http://aix4admins.blogspot.com/2020/05/oracle-tuning-resource-limits-ulimits.html" rel="nofollow" target="_blank">here</a>:<br /><br /><br />The first time we hit a problem regarding CIO mounted file systems, was when we copied an existing database-server running on Oracle 11.2.0.4 to a new IBM Storage System (FS7200). As this was a full clone, were nothing on server side changed, we were puzzled by the fact that we could not start the database anymore, after the copy. <br />Similar errors appeared about the control files that suddenly would be corrupted. <br /><u>Because </u>it was a full clone, we never suspected the database itself. It had to be in the move to the new storage system. We then decided to remove the CIO option from the mount-point which solved the problem.</p><p>The whole problem is related to the fact that the default value of the filesystemio_options parameter has changed with the release of 19c. In Oracle 12c ( 12.1.0.2) the value of this parameter was default set to "async", which enables asynchronous I/O where possible.</p><p>In release 19c the value of the filesystemio_options parameter is default set to "none" (on Linux), which disables all I/O performance enhancements.<br />When upgrading our first databases from Oracle 12c to 19c on Redhat 7.x, we ran into a huge degrade of I/O performance, because of this. The databases suddenly went from Asynchronous I/O to Synchronous I/O.<br />Investigating the issue, it revealed that this parameter had changed with the new release. We then decided to set it to the recommended value of "setall", which enables Direct I/O and Asynchronous I/O where possible. <br />I/O speed on Redhat immediately increased back to what we expected, resulting in the fact that we decided to make this value the default for all our databases.</p><p><br />And there the problem with IBM AIX was originating. As the database want to open a file using Direct I/O and the file system is mounted with CIO, the files can not be read. <br /><br />Conclusion:<br />- When using Oracle set the filesystemio_options parameter to "setall"<br />- When using Oracle on AIX, do not mount the file systems with the CIO option (anymore)</p><p><br /></p><p><br /><br /></p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-80653315049963354952020-11-06T11:00:00.009+01:002023-11-07T22:25:10.249+01:00Configuring Clients for Database Gateway for Websphere MQ 19c<p>In this post, I will try to explain the upgrade of Database Gateway for MQ to version 19c. <br />It comes with two problems that makes an upgrade from e.g. Database Gateway 12c to 19c a bit more challenging than one would expect.</p><p><br /></p><p><b>No support for PDBs</b></p><p>The first problem to address is that Database Gateway setup scripts for the clients do still have no support for Pluggable Databases (PDBs). Already the 12c version scripts, used to deploy the code in a client database had to be manually edited to correct the connection string. Back then this was still understandable as the major bulk of existing client database was a single instance database. <br />In version 19c, I would expect to see some kind of support for the use of PDBs, but there is not. In both versions you still have to edit the deployment files when using it with a PDB. Otherwise the packages will be installed in the CDB, where they are not accessible from the PDBs.</p><p>The first thing that needs to be fixed, is that the pgmdeploy.sql program simply exits without doing a thing. This is caused by the "whenever sqlerror exit" in the beginning of the script, followed by a connection attempt using the "scott/tiger" account, that (should) nowhere exist. <br />The general way to solve this is to comment out the line that has this connect command. It is around line 65 in the script. Simply put two dashes in front of it, to comment it out.</p><p>The second line that needs to be edited is found on line 111 of the pgmdeploy script. There the connection string used, should be appended with the PDB name the installation will take place in.</p><p>Change the lines:</p><p><span style="font-family: courier;">PROMPT Verifying SYS Password...<br />connect SYS/&INPASS2 as sysdba</span></p><div>into</div><div><br /><span style="font-family: courier;">PROMPT Verifying SYS Password...<br />connect SYS/&INPASS2@<PDB NAME> as sysdba</span></div><div><br /></div><p>For the pgmundeploy.sql the same needs to be done on line 91.</p>Remark: There were the scripts used with a single instance database were able to continue, by simply using the [Enter] key only, with a PDB you actually have to specify a SYS password.<br /><p>Now the deploy and undeploy scripts should work OK, for Oracle 19c clients, but.....<br /><br /><br /><b>No support for lower level databases</b></p><p>We all know that Oracle 12.1 is out of support, not even speaking about Oracle 11 databases. <br />When you because of that, decide to upgrade Database Gateway 12c to Database Gateway 19c, you will run into a major but solvable issue, when the client databases you use are on Oracle 12.1 or even lower.<br /><br />If you however start using Database Gateway now for the first time and you decide to start with the latest (19c) release, then this issue is not easy to solve.<br /><br />The problem is that as of release 12.2 ( 19c is 12.2.0.4), the DBMS_STANDARD package has been extended with a new variable <span style="background-color: #eeeeee; font-family: monospace; font-size: 16px;">ORA_MAX_NAME_LEN</span> and some dependent functions. <br />Oracle Database Gateway 19c uses this new variable, which does not exist on lower level databases. <br /><br />This makes the deploy of the PGM packages on Oracle 11 and Oracle 12.1 database impossible.<br />Only if you have or can get the deploy scripts for an earlier Database Gateway release then you can use Database Gateway 19c<br /><br /><br /><b>Solution</b></p><p>To use an Oracle Database 11g or 12.1 with Database Gateway 19c, you should use the Database Gateway 12.1 deploy scripts.<br />As soon as they are deployed, you need to replace the library definition for LIBDG4MQ that has been created, making it point to the new 19c location of the library. <br /><br /></p><p>Use a statement similar to this and execute it as SYS in the client database ( PDB or single instance):<br /><br /><span style="font-family: courier;">create or replace library LIBDG4MQ as <br />'[your dbgateway_home]/lib/libdg4mqc.so' <br />transactional;</span> </p><p><br />Afterwards recompile all PGM packages under the SYS schema and your done. Note that the PGM_UTL8 package remains Invalid. This is expected.</p><p><br /></p><p></p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-34393556235908087092020-11-03T17:08:00.006+01:002023-11-07T22:26:31.800+01:00DBCA hangs after entering Database Location (19c)<p>Today we were installing a new Oracle server on IBM Pseries running AIX 7.2.<br />The installation was done with the, at this moment, newest release of Oracle, being 19.9</p><p>The installation of the base release 19.3 and the release update went successful.</p><p>Creating a database however using dbca got stuck after entering the database file location in Step 5.<br />The trace.log file of dbca only showed:<br /><br /></p><p><span style="font-family: courier;">INFO: Nov 03, 2020 3:29:54 PM oracle.install.commons.util.StatusControl showMessage<br />INFO: Validating writability of the storage location provided</span></p><div><br />Since this was a new server and we already had installed multiple servers with this Oracle release but on Redhat Enterprise Linux 7, we suspected the AIX server to be the cause of this problem.<br /></div><div><br />UNIX administrators were involved and together we started searching for the cause.<br />The file location was writable, the process state using "truss" showed a continues loop of checks, with certain repeating functions like "ptx_get_cpuinfo", "ptx_get_wparsysinfo" and "ptx_get_wparsysstats" etc..etc..</div><div><br /></div><div>Oracle Support gave some result on these functions, but these results where linked to 'srvctl' and not 'dbca'. After entering a more detailed search description: <br /><br /><span style="font-family: courier;">"dbca hang database files location"</span></div><div><br /></div><div><br /></div><div>the first result was <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?id=2706037.1&displayIndex=1#FIX" target="_blank">Doc ID 2706037.1</a> with the title:<br /><br /><span style="font-family: courier;">Database creation via DBCA hangs when the database files location does not include {DB_UNIQUE_NAME}</span><span style="font-family: courier; font-size: x-small;"> </span></div><div><br /></div><div>It turns out that according to this Doc that DBCA as of release 19.3 requires a DB_UNIQUE_NAME in the file location, whenever you deviate from the given default. <br /><br />Again, on Linux we have never seen this issue, and all our systems have the same non-oracle-default setup. <br /><br />After appending our default location, with this DB_UNIQUE_NAME, dbca continued as expected.<br /><br /><br />Conclusion: On AIX it seems that DBCA can't work with a non-default database location, if it does not contain the DB_UNIQUE_NAME. <br />It does however also not generate an error message to tell you about this. <br />It simply gets stuck. <br /><br />However AIX is a very stable and reliable operating system, I tend to move all databases to Linux, since it is more reliable and thrustfull.<br /><br /></div><div><br /></div><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-13926224876430091422020-10-16T10:53:00.004+02:002023-11-07T22:26:52.105+01:00Top Activity option in OEM13 gone<p> After an upgrade from Oracle Enterprise Manager 12c to version 13c, we noticed that we had lost the "Top Activity" option, that used to be in the Performance Menu of the database page.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-vfB7T1BaQUE/X4ldMziQ4BI/AAAAAAABdhQ/Em4aTvOu_S4tgpczajVGi5k7gBgbuJTcQCLcBGAsYHQ/s479/PerformanceMenu.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="329" data-original-width="479" src="https://1.bp.blogspot.com/-vfB7T1BaQUE/X4ldMziQ4BI/AAAAAAABdhQ/Em4aTvOu_S4tgpczajVGi5k7gBgbuJTcQCLcBGAsYHQ/s320/PerformanceMenu.JPG" width="320" /></a></div><br /><p>Seen here is the Performance Menu of the version 13 of Oracle Enterprise Manager.</p><p>Below the "Performance Home" option the "Top Activity" option was available in version 12c</p><p>As you can see however, we are still able to access the screen.<br />It does still exist. It is only that the menu option is gone.<br />Luckily we kept the old http-link from version 12c, which we had saved as a Favorite in the browser.<br /><br />Accessing this page was now possible by simply editing this saved link and changing the server name.</p><p>For all of you who didn't save this link and also upgraded to the new version, the link looks like this:</p><p><br /></p><p>https://<your oem13 server>:7803/em/faces/sdk/nonFacesWrapper?target=<SID>&_em.coBM=%2Fconsole%2Fdatabase%2Finstance%2FwaitDetails%3F_em.coIFR%3Dtrue%26event%3DdoLoad%26target%3D<SID>%26type%3Doracle_database%26datasource%3DSQL%26waitClass%3DOverview&type=oracle_database</p><p><br /></p><p>Replace <your oem13 server> with the hostname of your Oracle Enterprise Manager server and <SID> with the database instance you want to monitor.</p><p><b>Keep in mind that this page requires the Oracle Diagnostic Pack license to access.</b> <br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-YaxCgxQFHmM/X4lfAX0FUHI/AAAAAAABdhc/TuQ4ehOVkVkAEI8YFGIUTyXGXbuibrIXgCLcBGAsYHQ/s595/ManagementPackAccess.JPG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="595" data-original-width="466" height="320" src="https://1.bp.blogspot.com/-YaxCgxQFHmM/X4lfAX0FUHI/AAAAAAABdhc/TuQ4ehOVkVkAEI8YFGIUTyXGXbuibrIXgCLcBGAsYHQ/s320/ManagementPackAccess.JPG" /></a></div><br />If you have the license, you can enable the Diagnostic Pack via Setup => Management Pack => Management Pack Access.<p></p><p>You can also enable it from SQLplus by entering the command:</p><p><span style="font-family: courier; font-size: x-small;">SQL> alter system set control_management_pack_access='DIAGNOSTIC' scope=both;</span></p><p><br /></p><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-3528670805084295942019-09-26T16:25:00.001+02:002023-11-07T22:28:07.716+01:00ORA-01017 on CONNECT / AS SYSDBA after changing OS user/groupFrom an existing database server the database Instance name and the OS-user and -group running the database had to change, as the database itself went from Development environment to Test environment.<br />
The database was called "STID01", its OS-user "stid01" with the group "dbad01"<br />
<br />
Everything was running just fine before the change.<br />
It was intended to change the database name to "STIT01" and its OS-user to "stit01", with the OS-group set to "dbat01".<br />
So a very simple change.<br />
<br />
We brought down the database and the UNIX admins changed the OS-username, its OS-group and renamed file system mount points also, as they also contain the Dev environment name.<br />
<br />
When I got the machine back, I ran a command to find all files on the system that belonged to the old user and changed their ownership to the new username and group.<br />
After that I relinked the Oracle binaries, using the "<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">$ORACLE_HOME/bin/relink all</span>" command.<br />
<br />
Finally I updated the /etc/oratab, renamed and edited the initSTID01.ora file to match the new configuration and I also created a new password file.<br />
<br />
Now trying to connect to the database, it generated an ORA-01017 error on connect as "/ as sysdba":<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">[stit01@vtl1g152 oraInventory]$ sqlplus / as sysdba</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 26 14:50:57 2019</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">Copyright (c) 1982, 2014, Oracle. All rights reserved.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ERROR:</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-01017: invalid username/password; logon denied</span><br />
<div>
<br /></div>
<br />
Connecting as user SYS with its password, entered in the password file did work.<br />
So I started working on renaming the Instance and datafile locations using a generated control file script:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter database backup controlfile to trace as '/tmp/crctrlfile.sql';</span><br />
<div>
<br /></div>
<div>
Afer renaming the database using the generated script the database was opened with the RESETLOGS option.</div>
<div>
<br /></div>
<div>
Now I updated /etc/oratab, /etc/oraInst.loc and the .bash_profile file and reconnected.</div>
<div>
<br /></div>
<div>
Connecting with "/ as sysdba" still generated the same ORA-01017 however</div>
<br />
Remembering a similar issue on IBM AIX, I went browsing the Internet.<br />
Looking up things on Google, the main line of thinking in the results was based on the change of the OS-group. The user running the database, or wanting to connect as sysdba without a password should be member of a privileged dba-group set during the installation.<br />
<br />
During the installation of this ORACLE_HOME this group was understandably set to "dbad01".<br />
Now that we are part of group "dbat01" this OS authentication fails.<br />
<br />
This brought me into looking into the installation and setup of the ORACLE_HOME.<br />
From ORACLE_HOME I ran a "find" command for all files containing the OS-group "dbad01".<br />
Two files came up as suspicious:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">./install/chainedInstall/globalcontext.xml</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">./rdbms/lib/config.c</span><br />
<br />
<br />
Especially the first one got my attention as a line in this file looked like:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-33jCQbjTG2o/XYzP_4VJQjI/AAAAAAABYQM/0mbHlQFuX6smPoSJiVIfwP9vCF76h7OsgCLcBGAsYHQ/s1600/GlobalContextXml.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="120" data-original-width="778" src="https://1.bp.blogspot.com/-33jCQbjTG2o/XYzP_4VJQjI/AAAAAAABYQM/0mbHlQFuX6smPoSJiVIfwP9vCF76h7OsgCLcBGAsYHQ/s1600/GlobalContextXml.JPG" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
Changing this file, setting the VALUE to "dbat01", I ran a "relink all" again.<br />
Still no luck, again an ORA-01017 when trying to connect.<br />
<br />
So, back to editing the next file.<br />
Editing the file $ORACLE_HOME/rdbms/lib/config.c showed a whole list of variables set to "dbad01". Looking at the location, this file is used for recompiling the library files.<br />
<br />
So we "relink all" again....and.... :<br />
<br />
sqlplus / as sysdba<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 26 16:08:01 2019</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">Copyright (c) 1982, 2014, Oracle. All rights reserved.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">Connected to an idle instance.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL></span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>Yes, we fixed it</b>. This confirms that the OS-group is the variable that controls password less authentication at SYSDBA level.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Now this post reads more or less like a "try or die" post, but in fact it wasn't.</div>
<div>
All changes before I went looking for the group-definition in the ORACLE_HOME are basic, well understandable changes that have to be done, when the OS-user and OS-group change on an Oracle database server</div>
<div>
<br /></div>
<div>
For the remainder, I had seen and done this before on IBM AIX, but not yet on Linux. </div>
<div>
The Post is therefore written following me searching for the right file.</div>
<div>
<br /></div>
<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-45807150408001922062019-05-17T14:43:00.001+02:002019-05-17T14:43:48.687+02:00Calculating Table Sizes OracleIn this post I will discuss the growth of tables, and the ways to calculate the space used.<br />
For the newbies among us, an Oracle databases exists of tablespaces, the defined space consisting of datafiles on disk, that hold the databases objects, like tables and indexes.<br />
<br />
When a table grows, a tablespace can become full. The origin of this document is from one of these occassions where a tablespace is/was full and I was trying to free up space.<br />
<br />
<u>But first some theory:</u><br />
<br />
How do tables grow in Oracle?<br />
<br />
Well that's an easy question, one would think, but depending on the way a table is used, the table can either grow slowly, grow very fast, or keep approximately the same size.<br />
<ul>
<li>When, in the last example, a table consists of very volatile data, so many inserts and deletes occur during a certain period of time, the database will use the space freed by the deletes for the new rows.</li>
<li>When there are more rows added than there are deleted, a table will grow, as new rows are added at the end of the table, behind the so-called high-water-mark of the table.</li>
<li>When an application uses a 'direct-insert' method, mostly seen in bulk-loads, the data is inserted after the high water mark anyway, so the database does in that case not look for free space within the existing occupied space.</li>
</ul>
<br />
That about how a table can grow, and of course this covers not all aspects that are involved.<br />
We will address more dependencies, as we discuss the table's actual size.<br />
<br />
First we need make a small sidestep and discuss "statistics".<br />
<br />
Oracle database statistics contain information about your data. Statistics information consists e.g. of the number of rows in a table, the average length of a row, the spreading of the data in the different columns and more. Via <a href="https://www.dummies.com/programming/databases/oracle/oracle-database-statistics/" target="_blank">this</a> link, you can find "Oracle Database Statistics for Dummies".<span id="goog_698627528"></span><br />
<br />
Now that we now, we can find the number of rows in a table on another way than running a "select count(*)..", then we are able to calculate the size of the data within the table.<br />
With that we can make a seperation between the actual physical size and the used size of a table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-P_id1dgAY4w/XMwIJr4xxZI/AAAAAAABVaU/6KyLMUQLWIkkWyu6fONgqHgzhUOp0qgWwCLcBGAs/s1600/peasbowl.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="533" data-original-width="640" height="166" src="https://3.bp.blogspot.com/-P_id1dgAY4w/XMwIJr4xxZI/AAAAAAABVaU/6KyLMUQLWIkkWyu6fONgqHgzhUOp0qgWwCLcBGAs/s200/peasbowl.jpg" width="200" /></a></div>
Imagine this bowl being a table. When we have calculated statistics on this bowl, we know that there are -approx.- 100 rows (peas) in this table, with an average row-size of 1 pea.<br />
<br />
The physical size of the bowl can be compared with the physical size of the table. This physical size consists of a 100 rows (peas) and a lot of free space, in this case air.<br />
<br />
If we wouldn't have table statistics, we would not know the occupied size of the table, but only the physical size. In that case we would not be able to tell anything of the tables usage.<br />
<br />
However when we do have statistics, we can tell more about the table's actual size. Oracle has options to shrink a table back to its used size, in order to free up space in the database but also to increase performance of the table, especially with inserts. If a table has a lot of free space, most of the time very fragmented, then the database possibly needs to spend lots of time to find a suitable empty spot in the table, where a row that is inserted would fit.<br />
If we shrink the table to its used size, the database does not need to spent much time on looking for a free place and put the row at the end of the table. This is of course valid for so long, as there will be done new deletes again, which free up usable space again.<br />
<br />
Now imagine this situation of a huge table, having a lot of free space in it. The below query shows us that the table INVOICES only has 49% of actual used space:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">select ds.owner </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , ds.segment_name tabname</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , ds.bytes</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , dt.num_rows</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , dt.avg_row_len</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , dt.num_rows * dt.avg_row_len usedsize_bytes</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , round(dt.num_rows * dt.avg_row_len / ds.bytes * 100,1) "%used"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">from dba_segments ds</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> , dba_tables dt</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">where dt.tablespace_name = 'DATA'</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> and ds.tablespace_name = dt.tablespace_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> and dt.owner = ds.owner</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> and dt.table_name = ds.segment_name</span><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">;</span><br />
<br />
Result:<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">OWNER TABNAME BYTES NUM_ROWS AVG_ROW_LEN USEDSIZE_BYTES %used</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------- -------- ------------ ---------- ----------- -------------- ----------</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FINANCE INVOICES 9768534016 1204707 3976 4789915032 49</span><br />
<br />
<br />
Now we can use the 'alter table <table_name> shrink space' command to reduce the physical size of the table. </table_name><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter table FINANCE.INVOICES enable row movement;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter table FINANCE.INVOICES shrink space; </span><br />
<br />
This will take some time, depending on the size of a table, but it will shrink the table to its smallest possible size. That is, when everything goes as expected. In our case it didn't. When running the above query again, the numbers now showed:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">OWNER TABNAME BYTES NUM_ROWS AVG_ROW_LEN USEDSIZE_BYTES %used</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------- -------- ------------ ---------- ----------- -------------- ----------</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FINANCE</span><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> </span><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">INVOICES </span><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 9753395200 1204707 3976 4789915032 49</span><br />
<br />
The table's physical size decreased just a little, but not as much as expected.<br />
So what causes this behavior, what extra factors can/must be taken into account and why can this table not be brought down to its actual data size ?<br />
<br />
<br />
The table's definition here is causing the problem itself. The table is setup as following:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">CREATE TABLE FINANCE.INVOICES</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> INVOICE_ID NUMBER(10) NOT NULL,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> CUSTNO NUMBER(8) NOT NULL,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> INVOICE_DESC VARCHAR2(4000 CHAR) NOT NULL,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> INV_AMOUNT NUMBER(10)</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">)</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">TABLESPACE DATA</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">PCTUSED 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">PCTFREE 10</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">INITRANS 1</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">;</span><br />
<br />
With a database blocksize of 8192, it can easily be determined that only one row of the table fits in a block. The actual available blocksize for row-storage is 8192 - 10% ( pctfree ) = 7372 bytes<br />
The average row length of the table is 3972, the maximum row_length is approx. 4028 bytes (not taking into account UTF encoding or whatever)<br />
<br />
So, it is easily calculated that every block of this table only holds one row, and no more. This is confirmed by looking at the number of rows in the table compared to the number of blocks the table occupies:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select sum(blocks) from user_Extents where segment_name = 'INVOICES'</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">2> union all</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">3> select num_rows from user_Tables where table_name = 'INVOICES';</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> CNT</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">----------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 1190600</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 1206840</span><br />
<br />
So this table holds approximately 8192 - 3972 = 4220 bytes of free space in each block it allocates.<br />
That is 3400 bytes of free row space + 10% PCTFREE space = 820 bytes !<br />
<br />
We can double check if this is correct, by doing the following math:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">physical table size / ( db block size * average_row_length) = 4729063200</span><br />
<br />
This value (almost) matches the value of the USEDSIZE in the above table being 4789915032<span style="font-family: courier new, courier, monospace; font-size: x-small;">.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<b>Can we optimize these tables in any way at all??</b><br />
<br />
Well, in our case for every row the database needs to read from disk, a whole block needs to read.<br />
<span style="font-family: inherit;">Put it the other way around, we only get one row when we read one block from disk.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">If we would create a seperate tablespace for this table with 16kB blocksize, then it would make a difference. With still a PCTFREE of 10% we then would be able to store 3 rows in a single block, a 33% decrease of occupied space!!</span><br />
<span style="font-family: inherit;"><br /></span>
A small test showed us that with the current setting (8k blocksize), a test table filled with 69000 rows occupies 68608 blocks ( approx. 536Mb )<br />
<br />
If we recreate the same table in a 16k blocksize tablespace, then the same table only occupies 23040 blocks ( approx. 360Mb )<br />
This saved 33% of physical diskspace as expected.<br />
<br />
<br />
<b>Conclusion:</b><br />
<br />
Table growth is not only about rows in a table. Tables that are known for frequent data changes, ( inserts/deletes and updates) can benefit from a shrink every now and then.<br />
However, if this does not bring the expected result as seen above, then the DBA or Data Architects can look at other solutions like different blocksize or maybe a change in the structure of a table.<br />
<br />
<br />
<br />
<span style="font-family: inherit;"><br /></span>
<br />FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-73786229673717358862018-07-18T16:17:00.003+02:002023-11-07T22:28:52.807+01:00Using Flashback Data Archive for Changed Data Capture<b>What is Change Data Capture?</b><br />
When a row in a table gets updated the content of the table changes. This is also true when data gets inserted or deleted. With Change Data Capture (CDC) you would like to capture all changes made to a certain amount of data.<br />
<br />
<a href="https://3.bp.blogspot.com/-4oi0_r0UZrw/W09LSdo06iI/AAAAAAABQzs/N7XFs2gwHmgPJYCNSVcoUZgIHL2bnL0lwCLcBGAs/s1600/dollargraph.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="768" data-original-width="1024" height="150" src="https://3.bp.blogspot.com/-4oi0_r0UZrw/W09LSdo06iI/AAAAAAABQzs/N7XFs2gwHmgPJYCNSVcoUZgIHL2bnL0lwCLcBGAs/s200/dollargraph.jpg" width="200" /></a>Take for example the value of the Dollar on the Stock Exchange. In relation to other coin-values the value of the Dollar can change many times during one day.<br />
If the value is stored in a single row of a table, you only get the current value when looking at it. You never know what happened to it in the period between two look ups.<br />
If you do need to know what happened in the mean time, it can be very useful to track and record all changes that happened on the value of the Dollar and thus on the table containing the value.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
With Oracle 9i Change Data Capture was introduced, and it made it possible to track changes to tables and store these changes in a separate table.<br />
Other processes then could "consume" this captured data for further processing. CDC worked by creating triggers on the source tables, that transferred data synchronously to the staging tables. This caused processing overhead and, because it was synchronous, had effect on the applications working on the source tables.<br />
<br />
In Oracle 10g Asynchronous CDC was introduced, that captured data from the online redo log files. The triggers that captured the transaction activity were no longer needed and therefore the performance impact was reduced. This redo log capturing of data was combined with Oracle Streams, requiring a certain configuration for each CDC change set.<br />
Unfortunately the Oracle 10g CDC is only available with Oracle Enterprise Editions. Because the changes where only captured from the redo log files after a commit was received, a minor delay is seen between the commit on the source tables and arrival of the changed data.<br />
<br />
In Oracle 11g the Oracle Streams functionality still exists but as of Oracle 12c this functionality along with CDC has been deprecated. It has been replaced by Oracle Golden Gate, which comes as a very expensive per core licensed solution.<br />
<br />
<b>Oracle Flashback</b><br />
With Oracle 10g Oracle introduced the Flashback functionality. With Flashback the database recycle-bin was also introduced, enabling Database Administrators to retrieve dropped tables from within the recycle-bin. But the most important functionality of Flashback was the ability to look back in time. Default, depending on the size of the UNDO tablespace and its retention, a DBA was able to e.g restore deleted data from a table shortly after it got deleted.<br />
<br />
When flashback was completely enabled on the database, the database would start creating flashback log files, keeping a record of all before-change values of the database up to a certain retention time.<br />
More information on Oracle Flashback Technology can be found <a href="http://www.oracle.com/technetwork/database/features/availability/flashback-overview-084412.html" target="_blank">here</a>.<br />
<br />
<b>Flashback Data Archive</b><br />
The Flashback functionality in Oracle Database has been extended with Flashback Data Archive in Oracle 11g. With Flashback Data Archive (FDA) it is possible to store all changed data on tables involved for a certain retention period or indefinitely.<br />
There were the standard Flashback functionality writes a before-image of a row-change to a flashback logfile, using FDA this change is written to a table directly related to the table that has configured with FDA.<br />
<br />
<b>How does it work:</b><br />
Flashback Data Archive is not only the name of the functionality. It is also a real archive object in the database that has to be created and that will hold all staging tables containing changed data. The archive is preferably created in its own tablespace to be able to manage the size of the archive.<br />
All is explained by the following example.<br />
<br />
First create a tablespace to hold the Flashback Archive:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">create tablespace fb_storage </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">datafile '/u01/oradata/fb_storage01.dbf' </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">size 100m autoextend on next 100m maxsize 5G;</span><br />
<br />
<br />
Within the tablespace a Flashback Archive is created. At this point the decision has to be made if a database default archive is to be created, or a user-personal archive will be the best option.<br />
To create a database default archive, user the tag "default" in the statement and execute it as user SYS:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">create flashback archive </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">default fb_archive </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">tablespace fb_storage </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">quota 4 g retention 1 day;</span><br />
<br />
This creates Flashback Arvchive named "fb_archive". The option "default" makes this archive the default archive of the database. It is possible to have more archives, but only one can be the default.<br />
The qouta is set to 4G and the retention of the kept data is set to 1 day;<br />
This quota is the maximum size of the archive in the given tablespace. If the archive becomes full then operations on the tracked tables will fail.<br />
If the archive reaches 90% of its given quota, the database will start issuing "out of space" warnings, thus enabling the DBA to purge old data or increase the archive size.<br />
<br />
If a user specific archive is to be created, then this user needs to receive the rights to do so first (as user SYS):<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">grant flashback archive administer to ehda;</span><br />
<span style="font-family: "courier new", courier, monospace;">alter user scott quota unlimited on fb_storage;</span><br />
<br />
After this the user can create its own archive:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">create flashback archive fb_archive_scott_1day </span><br />
<span style="font-family: Courier New, Courier, monospace;">tablespace fb_storage </span><br />
<span style="font-family: Courier New, Courier, monospace;">quota 4G retention 1 day;</span><br />
<br />
It is recommended to put the name of the owner and the retention into the name of the archive. This makes working with archives easier, especially when more than one exists.<br />
<br />
The below query shows the just created archive in its tablespace:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select flashback_archive_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> , flashback_archive#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> , tablespace_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> , quota_in_mb</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">from dba_flashback_archive_ts</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">order by flashback_archive_name;</span><br />
<br />
In order to use the default FDA on a certain table, rights have to be granted to the user owning that table(s) to use the FDA and its tablespace:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">grant flashback archive on fb_archive to scott;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">alter user scott quota unlimited on fb_storage;</span><br />
<br />
The above steps are not needed if a user specific archive has been created.<br />
<br />
Now connect as user scott and enable flashback archive on a table that has to be tracked:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">alter table scott.emp flashback archive;</span><br />
<br />
or for his personal archive, name the archive to be used:<br />
<br />
<span style="font-family: "courier new", courier, monospace;">alter table scott.emp flashback archive </span><span style="font-family: courier new, courier, monospace;">fb_archive_scott_1day</span><span style="font-family: "courier new", courier, monospace;">;</span><br />
<span style="font-family: "courier new", courier, monospace;"><br /></span>
Enabling and Disabling a flashback archive functionality on a table can be done by the table owner.<br />
Disabling is done by a privileged user when using a database default archive.<br />
<br />
With the next change of data in the scott.emp table, a old-value ( before image ) is stored in the flashback archive.<br />
Keep in mind that there is no "before image" for inserted data, so an inserted row is only seen in the flashback archive if it has undergone an update afterwards.<br />
<br />
The general documentation about FDA now would show a query about looking at changed data using the following query:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select * from scott.emp </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">versions between timestamp sysdate-4/1440 </span><span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">and sysdate;</span><br />
<br />
This will show the contents of the scott.emp table including all the updated rows over the last 4 minutes. If the table started empty and only a couple of rows were inserted and changed, then this overview would still be quite readable. If the table however was already populated with a lot of data, you can only find changed data easily if you know (the primary key values of ) the rows that have changed.<br />
To overcome this, it is also possible to directly query the staging table in the archive which holds only the changed rows. To find this staging table use the following query:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select * from</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> dba_flashback_archive_tables </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> where table_name ='EMP' </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> and owner='SCOTT';</span><br />
<br />
It will return someting similar to this:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">---------- ---------- ------------------------- -------------------- ----------</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">EMP SCOTT FB_ARCHIVE SYS_FBA_HIST_7785 ENABLED </span><br />
<br />
Here the archive table name for the SCOTT.EMP table is shown as SYS_FBA_HIST_7785;<br />
Every table that is tracked with FDA has its own archive table.<br />
Note that naming convention of archive tables start with "SYS_FBA". When looking up, knowing this, it shows us that there are in total 3 tables associated with the SCOTT.EMP table in the FDA:<br />
<br />
<table>
<tbody>
<tr><td>Table</td><td>Description</td></tr>
<tr><td><span style="font-size: normal;">SYS_FBA_DDL_COLMAP_7785</span></td><td>Table that holds a mapping of the columns of the history table to the original table.Because of the administrational overhead, adding or removing a column takes much longer on a tracked table compared to a normal table. </td></tr>
<tr><td><span style="font-size: normal;">SYS_FBA_HIST_7785</span></td><td>This table hold, as mentioned, the before images of the actual rows that have changed.</td></tr>
<tr><td><span style="font-size: normal;">SYS_FBA_TCRV_7785</span></td><td>This table shows the STARTSCN & ENDSCN of the changes, inluding the OPeration that was involved (U=update, I=insert, etc..)</td></tr>
</tbody></table>
<br />
<br />
The SYS_FBA_HIST_xxxx tables are structural identical to a join of the SYS_FBA_TCRV table and the original SCOTT.EMP table.<br />
<br />
<br />
<b>Retrieving changed data:</b><br />
As the archive tables hold the changed data along with SCN numbers, it is not hard to retrieve this data ordered in time, e.g:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select empno,ename,job from SYS_FBA_HIST_7785 order by endscn;</span><br />
<br />
Select the maximum SCN number and retrieve all rows that have been archived before that SCN:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select max(endscn)</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">from sys_fba_hist_7785;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">ENDSCN</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">-----------------</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> 10640031020116</span><br />
<div>
<br /></div>
And then:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">select empno,ename,job from sys_fba_hist_7785 where endscn < 10640031020116;</span><br />
<br />
<br />
<b>Purging the Flashback archive:</b><br />
Besides the configured retention which would purge old data from the archive it is also possible to manually purge the data.<br />
According to the Oracle Documentation, purging is possible based on SCN or TIMESTAMP, or by iussuing the command "purge all", deleting all data.<br />
<br />
<b>Warning:</b> What the official documentation fails to mention is that there is a bug in Oracle 11.2 and 12.1 which prevents purging the FDA based on SCN or TIMESTAMP. Only in 12.2 this bug is solved (Doc ID 16898135.8).<br />
There is however a one-off patch available for Oracle 11.2.0.3 and and 11.2.0.4.<br />
<br />
So until this patch is installed, only "purge all" is a working solution, but that brings a timing problem as to when it is suitable to purge all data looking at the application that might be populating the table in the mean time. One can imagine that between reading the captured data and executing a "purge all", new data might have been archived, so an<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">alter flashback archive fb_archive purge all;</span><br />
<br />
can possibly lead to data loss if the application that is responsible for populating the table is not shutdown beforehand.<br />
<br />
<br />
<br />
<br />
<b>DDL Limitations:</b><br />
Before explaining on how to disable the tracking of data changes and dropping the archive, one DDL restrictions on tracked tables has to be mentioned.<br />
<br />
<ul>
<li>Dropping the table</li>
</ul>
<br />
Simple, isn't it??<br />
There is documentation found on the Internet, even within well-known Blogs where it is stated that a tracked table can also not be truncated or renamed or undergo column changes.<br />
This is however not true. Only dropping the table is not allowed and will generate an error:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">drop table scott.emp;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">Error at line 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">ORA-55610: Invalid DDL statement on history-tracked table</span><br />
<br />
<br />
<b>Disable / Remove the Flashback Data Archive:</b><br />
<br />
Before an FDA can be removed, all tables that are tracked need to be decoupled, using the statement:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">alter table ehda.cohead</span><span style="font-family: "courier new" , "courier" , monospace; font-size: normal;"> no flashback archive;</span><br />
<br />
In tests I ran, a common user ( owner of the table ) can enable the flashback archive function on a table, but disabling this generates an error:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">ORA-55620: No privilege to use Flashback Archive</span><br />
<br />
A privileged user has to execute this command.<br />
<br />
As soon as all tables have be decoupled from the FDA, the FDA can be dropped:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">drop flashback archive fb_archive;</span><br />
<br />
After that also the tablespace can be dropped. Make sure before using the below command that the tablespace is really empty.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: normal;">drop tablespace fb_storage including contents and datafiles;</span><br />
<br />
<br />
<b>Conclusion:</b><br />
I was really excited to learn about this -for me- new functionality. It brings a very easy way to capture changed data. The solution is easy to implement, comes with no downtime, or impact to the application or database.<br />
Unfortunately there are still some bugs that prevent flawless use, unless the database is upgraded to 12.2<br />
<br />
<br /><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com2tag:blogger.com,1999:blog-5085850589914248681.post-70410702118749427102018-05-22T11:41:00.001+02:002023-11-07T22:29:28.696+01:00Automatic opening pluggable databasesWhen upgrading more and more databases from Oracle 11.2 to Oracle 12c we build more and more databases with the 12c CDB model.<br />
Single Instande user databases have been converted to Pluggable DataBases ( PDB).<br />
To make working with CDB's and PDB's easier, Oracle created an option to automatically open the PDB's as soon as the CDB is opened.<br />
This save lots of work and makes automatic restarts from scripts easier.<br />
<br />
To achieve this, the state of a PDB can be saved, so that the CDB knows which PDB to open after a startup.<br />
The command to save this state is to be run from the PDB, so assuming that we have a CDB called ORACDB and a PDB called PLUG1, the command to execute would be:<br />
<br />
<br />
$ export ORACLE_SID=ORACDB<br />
$ sqlplus / as sysdba<br />
<br />
SQL> alter pluggable database plug1 open;<br />
SQL> alter session set container=plug1;<br />
SQL> alter plugguble database plug1 save state;<br />
<br />
<br />
Now, as soon as the CDB is started, the PDB is immediately accessible through the Listener.
<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-1745189240512620222017-06-14T14:26:00.000+02:002017-06-14T14:26:00.407+02:00ORA-15036: disk '/dev/asmdata01' is truncated<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">Today I encountered one of these that you don't want to encounter.</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">We have this database server running Oracle 12c ( 12.1.0.2 ) on AIX 7.1</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">This server hosts 4 databases all around 400GB in size.</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">The databases are located on one ASM LUN connected through SAN storage of 1.5Tb</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">The need for yet another database on this server made us decide to increase the ASM disk to 2.1 TB and there we ran into problems. </span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">The increased diskspace on Storage level and OS level was not recognized by ASM. It kept showing a size of 1.5 TB.</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">We had to reboot the server anyway, becuase we also had to increase the memory size and from there on we were in trouble.</span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">Starting ASM went fine, but the disk was not mountable, diplaying the error:</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">ORA-15036: disk '/dev/asmdata01' is truncated</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">The disksize on OS level showed it as the expected 2.1Tb, within ASM it showed as 0 Mb !!!</span><br />
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">The 2Tb disk-limit was a known limit for databases up to Oracle 11.2. </span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">Since we are on Oracle 12.1 I did not expect to run into this problem.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">The diskgroup however was created in the past with an 11.2 ASM and afterwards upgraded to 12.1</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">The diskgroup compatibility settings were also set to 12.1, but still the 2Tb limit seemed to be in effect.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">The only solution we had was:</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">* Recreate the diskgroup and restore all database from backup, or</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">* Reduce the disksize (LUNsize) on the SAN and hope it gets reduced from the end of the disk where it just had been extended. </span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">We chose option 2 above option 1.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">If option 2 failed, we were any convicted to option 1.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">Luckily for us it worked!!</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">Reducing the LUN to 1.9 Tb, enabled ASM to recognize the disk and mount it.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">All databases were OK afterwards, so no data was lost.</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">There was one step left in the end, whereas the disksize to ASM still was 1.5GTb</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select total_mb from v$asm_diskgroup where name='DB01';</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> TOTAL_MB</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">----------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="background-color: #fff9d7;"></span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 1572864</span><br />
<div>
<br /></div>
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;">so we had to resize the disk and in the end all is OK now</span></span><br />
<span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><span style="background-color: #fff9d7;"><br /></span></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> alter diskgroup db01 resize all;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Diskgroup altered.</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SQL> select total_mb from v$asm_diskgroup where name='DB01';</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> TOTAL_MB</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">----------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="background-color: #fff9d7;"></span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 204697</span><span style="font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;">6</span><br />
<div>
<br /></div>
<span style="background-color: #fff9d7; font-family: Tahoma, Verdana, Helvetica, sans-serif; font-size: x-small;"><br /></span>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-1631498947951278602017-03-22T16:55:00.004+01:002023-11-07T22:30:07.002+01:00ORA-15067: command or option incompatible with diskgroup redundancyAt our company we had to replace the storage cabinets of a two node RAC cluster. The RAC cluster uses ASM diskgroups with NORMAL redundancy.<br />
All diskgroups exist of two failgroups, with each failgroup located on one of the storage cabinets, for redundancy purposes, as seen here:<br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace;">SQL> select distinct a.name </span><span style="font-family: "courier new" , "courier" , monospace;">, b.failgroup, count(*) numdisks</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 2 from v$asm_diskgroup a</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 3 , v$asm_disk b</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 4 where a.group_number = b.group_number</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 5 order by name;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">NAME FAILGROUP NUMDISKS</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------------ --------------------- ----------</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">DB01 DB01_0000 14</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">DB01 DB01_1000 14</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FB01 FB01_0000 5</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FB01 FB01_1000 5</span><br />
<div>
<br /></div>
<div>
While all disks in FAILGROUPS DB01_0000 and FB01_0000 are on one storage cabinet the command to drop all disks on one 'side' of the ASM diskgroup at once would be the first option to choose:</div>
<div>
<br /></div>
<div>
However:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter diskgroup fb01 drop disks in failgroup FB01_0000;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">alter diskgroup fb01 drop disks in failgroup FB01_0000</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">*</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ERROR at line 1:</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-15067: command or option incompatible with diskgroup redundancy</span></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Since all data is mirrored across the two failure groups, one would expect to be able to drop one complete set of the mirror, so one complete failgroup.</div>
<div>
<br /></div>
<div>
All can be explained by the redundancy of the diskgroup. Using NORMAL redundancy, the diskgroup expects to have at least one disk available in the first failgroup that can be used as a destination for a failing disk in the other failgroup. This is also seen when querying the REQUIRED_MIRROR_FREE value of the diskgroup:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb,usable_file_mb FROM V$ASM_DISKGROUP;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------------------------------ ------ ---------- ---------- ----------------------- --------------</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FB01 NORMAL 512000 503598 51200 226199</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">DB01 NORMAL 819200 801612 102400 349606</span></div>
</div>
<div>
<br /></div>
<div>
The REQUIRED_MIRROR_FREE values are equal to the size of a single disk in these diskgroups as seen here:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> select distinct * from (</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 2 select b.name, a.total_mb</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 3 from v$asm_disk a</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 4 , v$asm_diskgroup b</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 5 where</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> 6 a.group_number=b.group_number);</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">NAME TOTAL_MB</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------------------------------ ----------</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">DB01 102400</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FB01 51200</span></div>
<div>
<br /></div>
</div>
<div>
So the NORMAL REDUNDANCY prevents us from dropping all disks at once in a failgroup.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>Solution:</b></div>
<div>
<br /></div>
<div>
Drop all disks but one in the failgroup and then first add at least one new disk from the new storage cabinet</div>
<div>
After that dropping the last disk from the old storage cabinet should be possible.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>
<br /></div>
FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-47114639727864248832017-03-17T15:41:00.005+01:002017-03-17T15:41:45.487+01:00Just Another Friday with DBCA<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-R1kKZToTysk/WMv1jPJzy_I/AAAAAAAAXdw/7wdnCsDgazwttyiMdYeqysuOKpZ6vAvKQCLcB/s1600/JustAnotherFriday.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="486" src="https://4.bp.blogspot.com/-R1kKZToTysk/WMv1jPJzy_I/AAAAAAAAXdw/7wdnCsDgazwttyiMdYeqysuOKpZ6vAvKQCLcB/s640/JustAnotherFriday.jpg" width="640" /></a></div>
<br />
And then 'dbca' crashes, and you can start all over again....FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-79452262634097416172017-02-09T08:49:00.002+01:002017-02-09T08:49:15.436+01:00ORA-12154 running dbca on a 12c RAC ClusterWhen setting up a Oracle RAC cluster on 12.1.0.2 ( in our case patchlevel 170117 ), creating a database using dbca can run into an ORA-12154 unable to complete the database creation.<br />
<br />
<br />
The setup of an Oracle RAC cluster is that you have first of all the Oracle Grid Infrastructure, the so-called clusterware. This software piece is the cluster, and it is responsible for everything else but the actual RAC database.<br />
Everything else also includes the Listener (SCAN-listener and normal listener). For this matter the default location of an Oracle Installation is used for the listener config file, being:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$GRID_HOME/network/admin</span><br />
<br />
The RDBMS part of the installation is another software installation, with its own default location for SQL-net files, being:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">$ORACLE_HOME/network/admin</span><br />
<br />
<br />
Now when running 'dbca' and while creating a database, a tnsnames.ora file is being created in the $ORACLE_HOME/network/admin directory for self-reference of this newly created database.<br />
As soon as the database is ready, it tries to register with the clusterware. At that moment the clusterware processes try to connect to the database and fail with an<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">ORA-12154 TNS Could not resolve service name</span><br />
<br />
<br />
The reason for this is, that the clusterware home did not receive the tnsnames.ora information about the created database, resulting in an unresolvable service name.<br />
<br />
<br />
<b>Solution:</b><br />
<br />
Before starting dbca, add support for the self-reference of the to-be created database into the $GRID_HOME/network/admin location, by creating an empty tnsnames.ora for the rdbms-user and a symbolic link to this file for the grid-user.<br />
<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">dbuser $ cd $ORACLE_HOME/network/admin</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">dbuser</span><span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> </span><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">$ echo “NAMES.DIRECTORY_PATH=(TNSNAMES)” > sqlnet.ora</span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: x-small;">dbuser $ touch tnsnames.ora</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">dbuser $ chmod g+w . tnsnames.ora</span><br />
<br />
Next:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">griduser $ cd $GRID_HOME/network/admin</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">griduser $ ln –s /opt/oraadmin/network/admin/tnsnames.ora tnsnames.ora</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">griduser $ ln –s /opt/oraadmin/network/admin/ sqlnet.ora sqlnet.ora</span><br />
<br />
In the above statements also a sqlnet.ora file is being created. This is (in our case) to prevent the grid-user from connecting to the still existing older version of this database by means of an EZCONNECT connection.<br />
<br />
Now when dbca creates an entry in the tnsnames.ora file the grid-user is also aware of its existence and the ORA-12154 does not appear<br />
<br />FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-85107418279680431402016-11-11T13:37:00.002+01:002017-02-23T10:06:05.695+01:00Opatchauto for GI complains about actions.xml missingOn Oracle 12.1 doing a GI CPU installation it is possible that, when trying to install one of the quarterly patch updates, that 'opatchauto' complains about an actions.xml file missing from one of the patch subdirectories.<br />
<br />
In DocID 2086760.1 on Oracle Support this problem is explained to be caused by a too low version of opatchauto.<br />
When using an opatch version 12.1.0.1.7 or higher this problem will be gone.<br />
<br />
However, today I ran into the same problem, having opatch version 12.2.0.1.8 !!<br />
<br />
After a long search for the cause, I found another error in the logfile, saying:<br />
<br />
<br />
<i>OPatchAutoBinary hit Binary Exception: oracle.opatchauto.code.binary.BinarySessionException: </i><br />
<i>ERROR: Unable to register this patch /opt/src/24433148. Select Patch is not valid.</i><br />
<br />
<br />
This made me look at the directory structure where the patch was extracted.<br />
There I noticed that I mistakenly had unzipped the patch as root-user, resulting in only root-permission files.<br />
<br />
After changing the ownership of the unzipped patch-directory to the grid-owner, the patch installation has successfully completed.<br />
<br />
So, permission problems on an unzipped patch directory can cause another problem to arise, making solving it a challenge.FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-8756681428884995612013-06-13T15:32:00.003+02:002023-11-07T22:31:27.506+01:00Oracle Manual Standby Revised ( no Dataguard) Because of all the comments and remarks I received about my Blog discussing a <a href="http://managingoracle.blogspot.nl/2012/04/oracle-manual-standby-no-dataguard.html">Manual Standby solution</a>, I decided to update this recovery process and make it more stable. My new findings are described below.<br />
A downloadable PDF version of the documentation can be found <a href="https://docs.google.com/file/d/0B-RnX_t9_f1yQmFKeGFUaFBKT3c/edit?usp=sharing">here</a>:<br />
<br />
<b><span style="color: red;">Note:</span></b><br />
<b><span style="color: red;">When implementing the below solution, keep in mind that both databases need to be correctly licensed!!</span></b><br />
<br />
<br /><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>
<b>Goal:</b><br />
<br />
The goal is to setup a temporary manual HA solution for Oracle databases. It will keep a copy of a certain database in sync using the archived redo-logs from that database. The gap a standby database will be behind its production master is depending on the amount of redo generated and the size of the redo-logfiles combined with the amount of time, the delay, between the several runs of the Recover Script.<br />
<br />
It is very important that the master and standby database have exact the same logical en physical configuration, i.e. database names should be the same, file system layout and placing of the files should be identical, owner ship of the database should be the same user, etc…<br />
<br />
<br />
<b>Basic steps for each run:</b><br />
<br />
In order to keep a standby copy of a database in sync with its master a couple of repeatedly executed steps have to be performed:<br />
* Connect to the master database and get the sequence# and start-time (a.k.a first_time) of the last archived redo-log file. The use of the first_time of the last archived redo-log ensures that we have all the required archived log information available at the standy site. This step is known in the script as the DetermineRecoverTime. It needs a configuration entry in the tnsnames.ora referring to the MASTER database (see configuration steps).<br />
<br />
* Create a recover-statement with the retrieved ‘start_time’. The retrieved start-time therefore is configured in the required syntax, being ‘YYYY-MM-DD HH24:MI:SS’. This start-time string is passed into a recover statement, making the recover script look like this:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">connect / as sysdba</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">recover database until time ‘yyyy-mm-dd hh24:mi:ss’ using backup controlfile;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">auto</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">quit;</span><br />
<br />
The “auto” option used here ensures the recover process automatically starts with the suggested files and continues until the database has been recovered until the desired recovertime is reached.<br />
Here the importance of identical physical layout of both database servers is clearly seen.<br />
The recover to a point in time is very usefull. At any moment between Recover script sessions the database can be made available by simply ‘opening’ it for use.<br />
Remember however that opening the database will probable result in an incarnation of the production database which can no longer be used for standby recovery!!<br />
<br />
After the recover steps are done, and all necessary archived logs have been applied, the script takes care of cleaning out all spend log files. It determines the first archived log file existing in the archivelog directory ( seen according to the sequence number in the file name). It then determines the highest used archived logfile from the recovery process by reading its logfile and –to be sure- subtract the value with 1, to always retain the last one used. All archived logfiles between these two sequence numbers will be deleted.<br />
<br />
<br />
<b>The Setup:</b><br />
<br />
The setup of the Recover Standby procedure consists of putting the script in /var/bhr/scripts/oracle, setting ownership to the database owner, and setting rights to 744. We don’t want this script executed by mistake by any other user. Note that if there are more databases on the same system that are standby databases, each database must have its own script. A copy/rename action is required in that case.<br />
<br />
The script(s) need(s) to be edited, changing the following parameters in the top of the script:<br />
<br />
<span style="font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace;">e</span><span style="font-family: "courier new" , "courier" , monospace;">xport ORACLE_SID=ORCL # The name of the database ( SID )</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ORACLE_USER=oracle # The owner of the database ( AIX userid )</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ARCHPATH=/opt/oracle/ar01 # The path where the archived logfile are on</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># the standby server</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SOURCE_SID=ORCL_SRC # The name of the tnsnames entry</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># referring to the master db</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># The hosts</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export MASTER=master_hostname # The UNIX hostname of the master site</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SLAVE=standby_hostname # the UNIX hostname of the standby site</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<br />
Note that all values here are examples of an existing configuration and have to be changed accordingly.<br />
<br />
Now determine how many archived logs in terms of storage are generated on average by the master database. Create or resize the archived log file system on the standby database accordingly.<br />
Mount this file system using NFS on the master server as an extra archived log location, e.g. /opt/oracle/ar02.<br />
Make sure it is writable by the master database owner.<br />
<br />
Important: Following Oracle License directives the above solution using an NFS mount is only allowed for Enterprise Edition database installations.<br />
On Standard Editions a second archive log destination is only allowed on a local file system.<br />
<br />
Add this extra archived log location to the configuration of the master database, using the following SQLplus statement:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL>alter system set log_archive_dest_2=’location=/opt/oracle/ar02’ scope=spfile;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL>alter system set log_archive_dest_state_2=’enable’ scope=spfile;</span><br />
<br />
The first parameter here is not dynamical. It needs a database restart.<br />
The second parameter however is dynamical, it can be changed at will during runtime of the database, e.g. when problems occur at the standby site and we don’t want an active archived log location to become full.<br />
<br />
Finally the login credentials of the masters database SYSTEM account needs to added to the ‘envusers’ file of the standby system. Also add an entry to the /etc/oratab file of the standby system, in order for the script to set the correct environment.<br />
<br />
<br />
<b>Run the script:</b><br />
<br />
Before the script can be run the standby database needs to be in mount mode.<br />
Using SQLplus, connect as sysdba user and use the<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> startup mount;</span><br />
<br />
command to do so.<br />
Do not open the database, or it will start a life of its own, making it an incarnation of the master and risking it to be further unrecoverable.<br />
The initial versions of the recover script also contained a ‘shutdown abort’ and ‘startup mount’ command in each recover run. This has been removed. It can however be a good thing to re-add it back to the script, if it turns out the standby database goes down too often, for no reason and Recovery can’t continue.<br />
<br />
The script can now be run, i.e. if all archived logfiles that are needed for the first run are available in the correct location on the standby server.<br />
As soon as the first run finishes, the script can be added to the root crontab, making it run for every x minutes. The value of x is highly depending on the amount of redo generated by the master database and the size of the archived log file system on the standby site.<br />
<br />
A crontab entry for the root-user where the script runs every half hour would look like:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">00,30 * * * * su - oracle -c /opt/oracle/recover/bin/RecoverStandby.sh >/dev/null</span><br />
<br />
Note that, although it is in the root-crontab, the script is executed by starting a shell as user ‘oracle. This user should be the same user as configured in the script as being the database owner.<br />
<br />
<br />
<b>Logging:</b><br />
<br />
The script generates two logfiles in /var/bhr/log.<br />
On Logfile named after the script but with a .log extension. It containes a continues log of every session.<br />
Another logfile is a last run only logfile, with only the output of the ‘recover database until….’ command.<br />
<br />
<br />
<br />
<b>Manual actions:</b><br />
<br />
The RecoverStandby script is autonomous. I.e. the script can run without user interference. If a run might fail, it will pick up work at the next run.<br />
<br />
Manual actions however are needed when physical configuration changes are made to the master database. An example of this is e.g. adding a new datafile to an existing tablespace, or simply creating a new tablespace.<br />
Since the standby database is in mount mode, such actions can not be performed there.<br />
And – as said before – we do NOT want to open this database, as it might deviate from the master database in terms of changes and log sequence numbering.<br />
<br />
This is a problem of keeping a database in sync manually.<br />
With Oracle Dataguard, all these action would be synchronized by the dataguard processes.<br />
In our case however the following steps need to be performed after a datafile change on the master site:<br />
<br />
* Shutdown the Standby database<br />
* Retain a backup copy of the control files of the Standby database<br />
* On the Master database put the tablespace of which a datafile has changed in backup mode, using:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter tablespace <tablespace_name> begin backup;</tablespace_name></span><br />
<br />
* Copy the changed datafile to the same location at the Stanby database<br />
* When the copy finishes, turn of backup mode at the Master database,using<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SQL> alter tablespace <tablespace_name> end backup;</tablespace_name></span><br />
<br />
* Copy the controlfile of the Master database to the Standby database<br />
* Restart the Standby database to mount mode.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>The script</b><br />
Below the script I created for this purpose.<br />
The script shown here is generic for documentation purposes. Adapt it to your own needs<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Recover a manual standby database to the current moment</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ORACLE_SID=ORCL</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ORACLE_USER=oracle</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ARCHPATH=/opt/oracle/ar01</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SOURCE_SID=ORACLE_SRC # Used to retrieve the restore-to timestamp</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export PROGRAM=`basename $0 | cut -d'.' -f1`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export LOG_NAME=/opt/oracle/log/${PROGRAM}.log</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SQLLOGIN="system/manager"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># The hosts</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export MASTER=primhost</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SLAVE=stdbhost</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORAENV_ASK=NO</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">. oraenv</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORAENV_ASK=YES</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">Log()</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo $1 >> ${LOG_NAME}</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span><br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">DetermineRecoverTime()</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set heading off;" > Archtime${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set linesize 200;" >> Archtime${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set feedback off;" >> Archtime${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "connect ${SQLLOGIN}@${SOURCE_SID}" >> Archtime${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "select sequence#, min(to_char(first_time,'YYYY-MM-DD HH24:MI:SS')) from v\$archived_log where sequence#=(select sequence# - 2 from v\$log where status='CURRENT') group by sequence#;" >> Archtime${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "quit;" >> Archtime${ORACLE_SID}.sql</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> OUTPUT=`${ORACLE_HOME}/bin/sqlplus -S /nolog @Archtime${ORACLE_SID}.sql |tail -1`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> SEQUENCE=`echo ${OUTPUT} | awk '{print $1}'`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ARCHTIME=`echo ${OUTPUT} | awk '{print $2" "$3}'`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> export ARCHTIME SEQUENCE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log SEQUENCE=${SEQUENCE}</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log ARCHTIME=${ARCHTIME}</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span><br />
<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CreateSqlRecoverScript()</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> RECOVERTIME=$1</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "connect / as sysdba" > Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "recover database until time '${RECOVERTIME}' using backup controlfile;" >> Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log "recover database until time '${RECOVERTIME}' using backup controlfile;"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "auto" >> Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "quit;" >> Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span><br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CleanUpArchfiles()</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set heading off;" > CleanUp${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set linesize 200;" >> CleanUp${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "set feedback off;" >> CleanUp${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "connect / as sysdba" >> CleanUp${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "select sequence# - 1 from v\$log where status = 'CURRENT';" >> CleanUp${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "quit;" >> CleanUp${ORACLE_SID}.sql</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> OUTPUT=`${ORACLE_HOME}/bin/sqlplus -S /nolog @CleanUp${ORACLE_SID}.sql |tail -1 | awk '{print $1}'`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> MINFILE=`ls ${ARCHPATH}/*arc | sort | head -1 | cut -d'_' -f2 | cut -d'.' -f1`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> MAXFILE=`cat /opt/oracle/log/Recover.log| grep sequence| tail -1 | cut -d'#' -f2 | awk '{print $1}' `</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> # Just to save 1 extra file... don't delete the last one used</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> MAXFILE=`expr ${MAXFILE} - 1`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log OUTPUT=$OUTPUT</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log MINFILE=$MINFILE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Log MAXFILE=$MAXFILE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> while [ ${MINFILE} -lt ${MAXFILE} ]</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> do</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> rm -f ${ARCHPATH}/${ORACLE_USER}_${MINFILE}.arc</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> MINFILE=`expr ${MINFILE} + 1`</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> done</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span><br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Main</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ARCHTIME=FALSE</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> DetermineRecoverTime</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> CreateSqlRecoverScript "${ARCHTIME}"</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">if [ ! "$ARCHTIME" = "FALSE" ]</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">then</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">echo Retrieve of Recovertime succeeded, continue with recover database</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ${ORACLE_HOME}/bin/sqlplus /nolog <<EOF<eof font=""></eof></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">spool /opt/oracle/log/Recover.log;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">@Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">quit</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">EOF</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Remove processed archive files</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CleanUpArchfiles</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Remove temporary files</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> rm -f Archtime${ORACLE_SID}.sql Recover${ORACLE_SID}.sql</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">fi</span><br />
<br />
<br />
<br />
<br />FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com14tag:blogger.com,1999:blog-5085850589914248681.post-14826632871184584422012-07-13T10:00:00.004+02:002012-07-13T10:16:46.673+02:00Hash Join Anti NAAnother simple day at the office.....<br />
<div>
<br /></div>
<div>
What was the case.</div>
<div>
A colleague approached me telling that he had two similar queries. One of them returning data, the other not.</div>
<div>
The "simplified" version of the two queries looked like:</div>
<div>
<br /></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SELECT col1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE col1 NOT IN (SELECT col1 FROM tab2);</span><br />
<br /></div>
<div>
This query returned no data, however he -and later on I also- was sure that there was a mismatch in the data, which should have returned rows. </div>
<div>
This was also proven/shown by the second query:</div>
<div>
<br /></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SELECT col1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE NOT EXISTS</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (SELECT col1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab2</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE tab1.col1 = tab2.col1);</span></div>
<div>
<br /></div>
<div>
This query returned the expected difference. And this query does in fact the same as the first query!!</div>
<div>
Even when we hardcoded an extra WHERE clause, the result was the same. No rows for:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SELECT *</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE tab1.col1 NOT IN (SELECT col1 FROM tab2)</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> AND tab1.col1 = 'car';</span></div>
<div>
<br /></div>
<div>
and the correct rows for:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SELECT *</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE NOT EXISTS</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (SELECT 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> FROM tab2</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> WHERE tab1.col1 = tab2.col1)</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> AND tab1.col1 = 'car';</span></div>
<div>
<br /></div>
</div>
</div>
<div>
After an hour searching, trying to reproduce the issue, I almost was about to give up and send it to Oracle Support qualifying it as a bug.</div>
<div>
However, there was one difference that I saw, that could be the cause of the problem. </div>
<div>
Allthough the statements are almost the same, the execution plan showed a slight difference. The execution plan for the NOT IN query looked like:<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background: white; mso-cellspacing: 0cm; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td colspan="3" style="background: #F0F0F0; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span style="font-family: Tahoma, sans-serif; font-size: 8pt;">Plan<o:p></o:p></span></b></div>
</td>
</tr>
<tr>
<td colspan="3" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">SELECT STATEMENT </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">ALL_ROWS </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 5 Bytes:
808 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td colspan="2" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">3 </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">HASH JOIN ANTI NA </span></b><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 5 Bytes: 808 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">1 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB1 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 606 Cardinality: 3 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">2 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB2 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 404 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
Whereas the execution plan of the query with the NOT EXISTS looked like:<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background: white; mso-cellspacing: 0cm; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td colspan="3" style="background: #F0F0F0; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span style="font-family: Tahoma, sans-serif; font-size: 8pt;">Plan<o:p></o:p></span></b></div>
</td>
</tr>
<tr>
<td colspan="3" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">SELECT STATEMENT </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">ALL_ROWS </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 5 Bytes:
808 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td colspan="2" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">3 </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">HASH JOIN ANTI </span></b><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 5 Bytes: 808 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">1 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB1 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 606 Cardinality: 3 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">2 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB2 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 404 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
<br />
See the difference?<br />
<br />
Not knowing what a "HASH JOIN ANTI NA" exactly was, I entered it as a search command into the knowledge base of My Oracle Support. Besides a couple of patch-set lists, I also found Document 1082123.1, which explains all about the HASH JOIN ANTI NULL_AWARE.<br />
<br />
In this document the behaviour we saw is explained, with the most important remark being:<br />
'If t2.n2 contains NULLs,do not return any t1 rows and terminate'<br />
<br />
And then it suddenly hit me as I was unable to reproduce the case using my own created test tables.<br />
<br />
In our case, it meant that if tab2.col1 would have contained any rows with a NULL value, the join between those two tables could not be made based on a "NOT IN" clause.<br />
The query would terminate without giving any results !!!<br />
And that is exactly what we saw.<br />
<br />
The query with the NOT EXISTS doesn't use a NULL_AWARE ANTI JOIN and therefore does return the results<br />
<br />
Also the mentioned workaround:<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">alter session set "_optimizer_null_aware_antijoin" = false;</span><br />
<br />
seems not to work. Allthought the execution plan changes to:<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background: white; mso-cellspacing: 0cm; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td colspan="3" style="background: #F0F0F0; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span style="font-family: Tahoma, sans-serif; font-size: 8pt;">Plan<o:p></o:p></span></b></div>
</td>
</tr>
<tr>
<td colspan="3" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">SELECT STATEMENT </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">ALL_ROWS </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 4 Bytes:
202 Cardinality: 1 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td colspan="2" style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">3 </span></b><b><span style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">FILTER </span></b><span style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">1 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB1 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 606 Cardinality: 3 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<br /></div>
</td>
<td style="border: none; padding: 1.5pt 1.5pt 1.5pt 1.5pt;"><div class="MsoNormal">
<b><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;">2 </span></b><b><span lang="EN-US" style="color: red; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE ACCESS FULL </span></b><b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">TABLE </span></b><span lang="EN-US" style="color: navy; font-family: 'MS Shell Dlg 2', sans-serif; font-size: 8pt;">PIM_KRG.TAB2 </span><span lang="EN-US" style="color: maroon; font-family: Tahoma, sans-serif; font-size: 8pt;">Cost: 2 Bytes: 404 Cardinality: 2 </span><span lang="EN-US" style="color: navy; font-family: Tahoma, sans-serif; font-size: 8pt;"><o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
it still returns no rows !!<br />
<br />
<br />
And Now??<br />
<br />
Since there is a document explaining the behaviour, I'm doubting if we can classify this as a bug. But in my opinion, if developers do not know about this strange behaviour, they will easily call it a bug.<br />
<br />
The "problem" is easily solved ( or worked around ) using the NOT EXISTS solution, or using NVL with the JOINed columns. However I would expect the optimizer to sort these things out himself.<br />
<br />
For anyone who wants to reproduce/investigate this case, I have listed my test-code. The database version we used was 11.1.0.7 on Windows 2008 R2. I'm sure the OS doesn't matter here.<br />
<br />
<br />
-- Create two tables, make sure they allow NULL values<br />
CREATE TABLE tab1 (col1 VARCHAR2 (100) NULL);<br />
CREATE TABLE tab2 (col1 VARCHAR2 (100) NULL);<br />
<br />
INSERT INTO tab1<br />
VALUES ('bike');<br />
<br />
INSERT INTO tab1<br />
VALUES ('car');<br />
<br />
INSERT INTO tab1<br />
VALUES (NULL);<br />
<br />
INSERT INTO tab2<br />
VALUES ('bike');<br />
<br />
INSERT INTO tab2<br />
VALUES (NULL);<br />
<br />
COMMIT;<br />
<br />
-- This query returns No results<br />
SELECT col1<br />
FROM tab1<br />
WHERE col1 NOT IN (SELECT col1 FROM tab2);<br />
<br />
-- This query return results<br />
SELECT col1<br />
FROM tab1<br />
WHERE NOT EXISTS<br />
(SELECT col1<br />
FROM tab2<br />
WHERE tab1.col1 = tab2.col1);<br />
<br />
<br />
<br />
Success!!<br />
<br />
<br />
</div>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com1tag:blogger.com,1999:blog-5085850589914248681.post-32620869828657826852012-05-15T15:10:00.002+02:002012-05-15T15:10:43.985+02:00Interval partitioning<br />
Interval
partitioning is a partitioning method introduced in Oracle 11g. This is a
helpful addition to range partitioning where Oracle automatically creates a
partition when the inserted value exceeds all other partition ranges.<br />
<div class="MsoNormal">
<span lang="EN-US">
The following restrictions apply:<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">You
can only specify one partitioning key column, and it must be of NUMBER or DATE
type.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">Interval
partitioning is NOT supported for index-organized tables.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">You
can NOT create a domain index on an interval-partitioned table.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">Partition
names are generated automatically with they Syntax “SYS_xxxx”<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US"><br /></span></div>
<span lang="EN-US">
Interval partitioning based on a numeric value needs a correct interval
definition. For e.g. the known “monthcode” value, a create table statement
would look like:</span><br />
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">CREATE TABLE autopartition</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">(</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> monthcode NUMBER(6)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">, employee VARCHAR2 (40)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">, salary NUMBER (10)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">PARTITION BY RANGE</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> (monthcode)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> INTERVAL ( 1)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> (</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> PARTITION p_first</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> VALUES LESS THAN (201202));</span></span></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-US">
In the above table, only the first partition is named. If we insert data beyond
the partition definition, a new partition is created.<br />
Because the partitions are named automatically, Oracle has added new syntax to
reference the specific partitions effectively by using either the generated
name:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">select</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"> *</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">from</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> autopartitionnum partition (SYS_P62);</span></span></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-US"> or the range of a partition:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">select</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"> *</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">from</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> autopartitionnum partition for (201201);</span></span></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-US">
Note that, because the partitions are names automatically, with an incremental
numbering, partition SYS_P62 can belong to table A, whereas partition SYS_P63
van belong to table B, etc..etc..<o:p></o:p></span></div>
<span lang="EN-US" style="font-family: Calibri, sans-serif; font-size: 11pt; line-height: 115%;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" />
</span>
<br />
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">Interval
partitioning for DATE columns can be used based on year or month bases, using
the <span style="color: orange;">NUMTOYM </span></span><span lang="EN-US">function in the </span><span style="color: orange;">INTERVAL </span>table definition.</div>
<div class="MsoNormal">
</div>
<span lang="EN-US">
For interval partitioning on a more distinctive level, like days, a function called <span style="color: orange;">NUMTODSINTERVAL </span></span><span lang="EN-US">is available. <br />
Creating an Interval partitioned table –seperated on month– then looks like:<o:p></o:p></span><br />
<span lang="EN-US">
Creating an Interval partitioned table –seperated on month– then looks like:<o:p></o:p></span><br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; color: blue; font-family: 'MS Reference Sans Serif', sans-serif; font-size: 8pt;"><span style="background-color: transparent;"><br /></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial;"><span style="background-color: transparent;"><span style="font-family: 'MS Reference Sans Serif', sans-serif;"></span></span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">CREATE TABLE autopartitionmonth</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">(</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> saldate DATE</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">, employee VARCHAR2 (40)</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">, salary NUMBER (10)</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">)</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;">PARTITION BY RANGE</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> (saldate)</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> (</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> PARTITION p_first</span></span></div>
<div class="MsoNormal" style="font-size: 11px; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="color: orange;"> VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
</div>
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><br /><span style="font-size: x-small;">Or, with a separation of 1 day:</span></span><br />
<div class="MsoNormal" style="color: blue; margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><br /></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">CREATE TABLE autopartitionday</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">(</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> saldate DATE</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">, employee VARCHAR2 (40)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">, salary NUMBER (10)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;">PARTITION BY RANGE</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> (saldate)</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> INTERVAL ( NUMTODSINTERVAL (1, 'DAY') )</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> (</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> PARTITION p_first</span></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));</span></span></span></div>
<br />
<br />
<div class="MsoNormal">
<span lang="EN-US"><br /><b>Conclusions:</b><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Interval
partitioning can be used to automatically add new partitions when data is
inserted into a partitioned table that is out of range of the available
partitions. <br />
We can easily switch to normal manual partitioning by omitting the “</span><span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; font-family: 'MS Reference Sans Serif', sans-serif; font-size: 8pt; line-height: 115%;"><span style="color: orange;">INTERVAL ( NUMTODSINTERVAL (1, 'DAY'))</span></span><span lang="EN-US">” line in the create statements<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Advantages:<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">No
more worries about partitions to be created automatically, oracle takes care of
it<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">A
partition “DEFAULT” for all values outside existing partitions is no longer
necessary. This partition in old releases often worked like a trashcan.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Disadvantage<o:p></o:p></span></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-US" style="font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><span lang="EN-US">Partition
naming is automatic, this means subsequently created partition names, can be
part of different tables.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<span lang="EN-US" style="font-family: Calibri, sans-serif; font-size: 11pt; line-height: 115%;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" />
</span>
<br />
<div class="MsoNormal">
<br /></div>
<h1>
<span lang="EN-US">Index partitioning<o:p></o:p></span></h1>
<div class="MsoNormal">
<span lang="EN-US"><br />
When using Indexes on partitioned tables, make sure to use LOCAL indexes as
much as possible. This means that the Index is also setup as a partitioned
Index, with manageable parts on each table partition. <br />
Where in the MISPRD all indexes where dropped before a certain table was
loaded, in Oracle 11g it is now possible to set a certain Index or part of an
index to “UNUSABLE”. This means the Index will not be updated during table
changes, and will also not be used during selects from that table. As soon as
the table changes are completed, the disabled Index or index parts cat simply
rebuilt.<br />
<br />
An example for the “autopartition” table above.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Let’s
assume we want to insert/update 1000’s of records of the current month (201205)
in this table. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Since we
know the current month, we can use a statement like mentioned above to disable
the Index for that particular partition:<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US"><br /></span></div>
<div class="MsoNormal">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px; line-height: 12px;">ALTER TABLE autopartition MODIFY PARTITION FOR (201205) UNUSABLE LOCAL INDEXES; </span></span></div>
<div class="MsoNormal">
<span lang="EN-US"><br /></span></div>
<div class="MsoNormal">
<span lang="EN-US">This sets
the Index for that partition to UNUSABLE. This is confirmed by selecting the
partition name with the high_value from the dba_ind_partitions view, like:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">SELECT index_name, partition_name, high_value</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"> FROM dba_ind_partitions</span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><span style="color: orange;"> WHERE status = 'UNUSABLE;</span></span></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-US">The result
looks like:<o:p></o:p></span></div>
<table border="1" cellpadding="0" cellspacing="1" class="MsoNormalTable" style="mso-cellspacing: .7pt; mso-yfti-tbllook: 1184; width: 77.76%;">
<tbody>
<tr>
<td style="background: silver; border: inset white 1.0pt; mso-border-alt: inset white .75pt; padding: .75pt .75pt .75pt .75pt; width: 22.14%;" width="22%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<b><span style="font-family: 'Times New Roman', serif; font-size: 10pt;">INDEX_NAME<o:p></o:p></span></b></div>
</td>
<td style="background: silver; border: inset white 1.0pt; mso-border-alt: inset white .75pt; padding: .75pt .75pt .75pt .75pt; width: 40.02%;" width="40%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<b><span style="font-family: 'Times New Roman', serif; font-size: 10pt;">PARTITION_NAME<o:p></o:p></span></b></div>
</td>
<td style="background: silver; border: inset white 1.0pt; mso-border-alt: inset white .75pt; padding: .75pt .75pt .75pt .75pt; width: 37.32%;" width="37%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<b><span style="font-family: 'Times New Roman', serif; font-size: 10pt;">HIGH_VALUE<o:p></o:p></span></b></div>
</td>
</tr>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt; width: 22.14%;" width="22%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<span style="font-family: 'Times New Roman', serif; font-size: 10pt;">AUTOPARTITION_X1<o:p></o:p></span></div>
</td>
<td style="padding: .75pt .75pt .75pt .75pt; width: 40.02%;" width="40%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<span style="font-family: 'Times New Roman', serif; font-size: 10pt;">SYS_P69<o:p></o:p></span></div>
</td>
<td style="padding: .75pt .75pt .75pt .75pt; width: 37.32%;" width="37%"><div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;">
<span style="font-family: 'Times New Roman', serif; font-size: 10pt;">201206<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-US">As soon as
the load action is completed, all indexes partitions that are set to unusable
can be found using the following query:<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US"></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;">SELECT 'alter index '||index_name||' rebuild partition '||partition_name||';'</span></span></div>
<div class="MsoNormal">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"> FROM dba_ind_partitions</span></span></div>
<div class="MsoNormal">
<span style="color: orange; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"> WHERE status = 'UNUSABLE';'</span></span></div>
<div>
<br /></div>
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'MS Reference Sans Serif', sans-serif;"><span style="font-size: 11px;"><br /></span></span></div>FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com5tag:blogger.com,1999:blog-5085850589914248681.post-61792561266147217822012-05-15T10:46:00.007+02:002023-11-07T22:31:51.566+01:00Global Temporary Tables<br />
Triggered
by the huge amount of archive files generated by several databases each night,
we have been looking for a solution to reduce this dramatically.<br />
<div class="MsoNormal">
<span lang="EN-US">This
solution exists !!<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">And it is
called “Global Temporary Tables”.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<h4>
<span lang="EN-US">The definition:<o:p></o:p></span></h4>
<div class="MsoNormal">
<span lang="EN-US"> Applications often use some form of temporary
data store for processes that are too complicated to complete in a single pass.
Often, these temporary stores are defined as database tables or PL/SQL tables. As
of Oracle 8i , the maintenance and management of temporary tables can be
delegated to the server by using Global Temporary Tables. <br />
The data in a global temporary table is <b><span style="color: orange;">private</span></b>,
such that data inserted by a session can only be accessed by that session. The
session-specific rows in a global temporary table can be preserved for the <b><span style="color: orange;">whole session</span></b>, or just for the current <b><span style="color: orange;">transaction</span></b>. <br />
Finally the data stored in global temporary tables generate NO REDO and thus
reduce the amount of ARCHIVE FILES.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<h4>
<span lang="EN-US">The usage<o:p></o:p></span></h4>
<div class="MsoNormal">
<span lang="EN-US">Above,
three words are marked in <b>bold</b>.
These words indicate the behavior of a temporary table. Although a global
temporary table can exists in a global schema, like EHDA_KRG, all data inserted
into that table is session-specific. This means that if more than one user
session is inserting/updating data in a global temporary table, only the data
of that user’s session is affected. So, although you’re working in the same
temporary table you’re working on your own data. This makes the table <b>private</b>.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">Furthermore
there are two major differences in behavior. Default the table only keeps the
data inserted/updated until the transaction is finished/committed. This is
known as “ON COMMIT DELETE ROWS”. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US">The other
behavior keeps all rows/changes in the table until the end of your session.
This behavior is known as “ON COMMIT PRESERVE ROWS”. If you want the temporary
table to behave this way, you have to include this command in the table create
statement. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US"><br />
</span></div>
<div class="MsoNormal">
The creation of a global temporary table, that preserves the data until the session is ended, looks like:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">CREATE GLOBAL TEMPORARY TABLE <tablename></tablename></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> SELECT * FROM .....</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ON COMMIT PRESERVE ROWS</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">AS .......</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
or</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">CREATE GLOBAL TEMPORARY TABLE <tablename></tablename></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">( <column list=""> )</column></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ON COMMIT PRESERVE ROWS ;</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
And the creation of a global temporary table, that preserves the data only within a transaction, looks like:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">CREATE GLOBAL TEMPORARY TABLE <tablename></tablename></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ON COMMIT DELETE ROWS</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">AS</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> SELECT * FROM .....</span></div>
<div class="MsoNormal">
or</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">CREATE GLOBAL TEMPORARY TABLE <tablename></tablename></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">( <column list=""> )</column></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ON COMMIT DELETE ROWS ;</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Some other features:</b></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
</div>
<ul>
<li>If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.</li>
<li>Data in temporary tables is stored in temp segments in the temp tablespace.</li>
<li>Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.</li>
<li>Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session. Indexes can however only be created on empty tables !!</li>
<li>Views can be created against temporary tables and combinations of temporary and permanent tables.</li>
<li>Temporary tables can have triggers associated with them.</li>
<li>Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.</li>
<li>There are a number of restrictions related to temporary tables but these are version specific.</li>
<li>A temporary table can only be dropped if all users that have data in it, have issued the 'truncate table' command against it, or disconnected their session. Otherwise an ORA-14452 is generated.</li>
</ul>
<br />
<br /><script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8537033140336719"
crossorigin="anonymous"></script>
<br />FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com0tag:blogger.com,1999:blog-5085850589914248681.post-5870253574254635772012-04-06T11:51:00.002+02:002017-07-14T08:45:46.719+02:00Oracle Manual Standby (no Dataguard )<span style="color: red;">This entry is outdated. A revised solution can be found here:</span><br />
<a href="http://managingoracle.blogspot.nl/2013/06/oracle-manual-standby-revised-no_13.html">http://managingoracle.blogspot.nl/2013/06/oracle-manual-standby-revised-no_13.html</a><br />
<br />
A new personal challenge. How to create a manual standby database using Oracle11gR2 Standard Edition.<br />
<br />
As everybody might know, the official Standby database solution is Oracle Dataguard, only available however as an option with Oracle's Enterprise Edition of the database software. Since we have a lot of Standard Edition databases and we didn't want to pay the license fee for Enterprise Edition and the Dataguard option, I went on searching myself for a solution.<br />
<br />
With dataguard a primary and a secondary (standby) database exist. These database servers know eachother as FAL_SERVER and FAL_CLIENT. The FAL_SERVER is the primary, serving archivelog information to the standby server, which in his turn 'replays' this information onto his mounted database, thus syncing the database with the primary.<br />
Because this is a dataguard automated process, completely covered by the socalled dataguard broker, there is no need for any crontab entry what-so-ever to get this working.<br />
<br />
In our case, where I don't want to use Dataguard, I don't have a broker process, responsible for transferring the archivelog information and replaying this on the standby database. I had to find a way, to get this done manually.<br />
Now the actual method is much the same. Without dataguard, the only way to have a similar result is by using a continuing process of database recovery.<br />
So simply said: Put the standby database in mount mode, get the archivelog file from the primary and recover the database until the last archivelog available. This process can be repeated as often as necessary.<br />
<br />
One remark has to be made upfront:<br />
<span style="color: #cc0000;">A manual standby database always lags behind the primary database. There where dataguard standby databases are usually in sync up to the last transaction made, this manual standby is in sync up to the beginning of the last archivelog received. </span><br />
<br />
So, there is always a gap to overcome, in case the standby database needs to become active. Depending on the scenario of the disaster, we need to cope with having lost database information of the size of the last archivelog and the active redo-log, or we need to replay the last archivelogs and possibly the redo-logs from primary to get the database completely in sync. As said, this is totally depending on the situation of the disaster.<br />
<br />
<br />
What did I use for this project:<br />
<ul>
<li>Two Redhat 6 Linux virtual machines, both setup with Oracle's Virtualbox. Both VM's have 1Gb of memory, and 30Gb of diskspace. </li>
<li>I named the VM's "dgmaster" for the primary and "dgslave" for the standby host. In this blog I will refer to them as such.</li>
<li>Both VM's are identically installed, with Oracle 11.2.0.3 SE, with the exact file system naming.</li>
<li>On dgmaster a database was configured, called ORCL.</li>
<li>On dgslave initially an offline copy of dgmaster's ORCL database was restored/copied and brought up into mounted state. This ofcourse brings the need of transferring the init.ora / spfile, and the creation of a password file.</li>
</ul>
<div>
<br /></div>
<div>
The goal was to set up a fully automated process that could run from thne crontab of the standby server "dgslave". To get this working we need to have the following setup:</div>
<div>
<ul>
<li>SSH equivalency between the two Oracle users on both nodes. The oracle user must be able to transfer archive files from dgmaster to dgslave, without the need of entering a password. Information about how to setup SSH equivalence can be found here:
<a href="http://managingoracle.blogspot.com/2010/06/ssh-equivalence-on-rac.html">http://managingoracle.blogspot.com/2010/06/ssh-equivalence-on-rac.html</a></li>
<li>Determine a way of automatic transfer of archivelogs and also determine the time to recover to, related to the last retrieved archivelog. I selected to use the 'first_time#' of the last archivelog received as the point to recover to. This makes sure that every time the recovery will succeed, as all needed information is available. </li>
<li>The master ( primary ) database is up and running in 'open' state. It is e.g. in use as production database.</li>
<li>The standby ( secondary) database is in mounted state, so it is closed</li>
<li>The primary database has archive logging enabled. The log_archive_format of the primary is copied to the spfile of the standby database. This makes sure that dgslave can find the files it has retrieved, when starting the recovery.</li>
<li>Make sure the primary database has forced logging enabled. This is necessary to be able to log really all changes in the redo files, as all changes need to be send to the standby server. This can be configured using the statement:</li>
<ul>
<li>ALTER DATABASE FORCE LOGGING;</li>
</ul>
</ul>
<div>
I'm sure every DBA can setup two Virtual machines running an Oracle installation. I'm also sure that they will succeed in setting up SSH equivalence, so when everything is setup, it is time to discuss the steps that need to be done over and over again.</div>
</div>
<div>
<br /></div>
<div>
1) Dgmaster executes a forced log-switch</div>
<div>
2) Dgmaster determines the 'first_time#' entry of the archivelog file written with this logswitch and sends it to dgslave.</div>
<div>
3) Dgslave fetches all missing archivelogs from the master </div>
<div>
4) With the retrieved 'first_time#' entry, the recovery of this mounted database can continue another step. </div>
<div>
<br /></div>
<div>
To make this run automatically, dgslave has the initiative. Dgslave runs the necessary scripts on dgmaster, by means of a ssh-connection. This makes it also possible to retrieve information sent by dgmaster.</div>
Below the script that is used on dgmaster is shown. This script is - in my test system - in the home-directory of the oracle user. You can put it anywhere you like on dgmaster, but then make sure to add that location to the script on dgslave:<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>#</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i># Force a logswitch to get the last archivelog to the standby host</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>#</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>ORACLE_SID=ORCL</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>ORAENV_ASK=NO</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>. oraenv >/dev/null 2>&1</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>SwitchLogfile()</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>{</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> #</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> # Do logswitch </i></span><br />
<i style="font-family: 'Courier New', Courier, monospace; font-size: small;"> #</i><br />
<i style="font-family: 'Courier New', Courier, monospace; font-size: small;"> RESULT=`echo "Alter system switch logfile;" | sqlplus -S / as sysdba | grep 'System altered'`</i><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> if [ "$RESULT" = "System altered." ]</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> then</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>export RETURN=1</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> else</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>export RETURN=0</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> fi</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> # Do we need to do something with this return value?</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> export RETURN</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>}</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>GetArchiveTime()</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>{</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> CURYEAR=`date +%Y`</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> echo "set heading off;" > temp.sql</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> echo "set termout off;" >> temp.sql</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> echo "select to_char(first_time,'YYYY-MM-DD HH24:MI:SS') from v\$archived_log where sequence#=(select sequence# - 1 from v\$log where status='CURRENT');" >> temp.sql</i></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i> sqlplus -S / as sysdba <<eof i=""></eof></i></span><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>spool tempres.txt</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>@temp.sql</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>quit</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>EOF</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>cat tempres.txt | grep ${CURYEAR} | grep -v grep | awk '{print $1" "$2}'</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>#rm -f temp.sql tempres.sql</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>} </i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>SwitchLogfile</i></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i>GetArchiveTime</i></span></i><br />
<i><br /></i>
<span style="font-family: inherit;">In this script two functions are recognized. The first is responsible for executing a log switch, making the database write the current redo-log file into and archived logfile. </span><br />
<span style="font-family: inherit;">The second function determines the timestamp of the 'first_time#' of this archived logfile. This is the timestamp of the first database change recorded in this file. One can understand that here the gap is created which causes the standby database to lag behind.</span><br />
<span style="font-family: inherit;">The above script is never executed by itself on the dgmaster. It is always executed by a remote call from dgslave. </span><br />
<span style="font-family: inherit;">The script used on dgslave looks like below:</span><br />
<i><span style="font-family: inherit;"><br /></span></i>
<br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Recover a manual standby database to the current moment</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ORACLE_SID=ORCL</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ORALCE_USER=oracle</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export ARCHPATH=/opt/mandg/ar01</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># The hosts</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export MASTER=dgmaster</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">export SLAVE=dgslave</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># The current date and time</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CURDATE=`date +%Y-%m-%d`</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CURTIME=`date +%H:%M:%S`</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ForceRemoteLogSwitch()</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{ </span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> # This function calls a remote script that forces a log-switch and return</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> # the 'first_time' of subsequently just written archive-file</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> #</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> ARCHTIME=`ssh ${MASTER} /opt/mandg/ora/home/RecoverDbSwitchLogfile.sh`</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> export ARCHTIME</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">GetArchives()</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> # </span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> # copy archives from remote host archive directory to the local d irectory</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> #</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> for remfile in `ssh ${MASTER} "ls ${ARCHPATH}/*arc"`</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> {</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> locfile=${ARCHPATH}/`echo ${remfile} | cut -d'/' -f5`</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> if [ ! -f ${locfile} ]</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> then</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> scp ${MASTER}:${remfile} ${ARCHPATH} </span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> fi</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> }</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">CreateSqlRecoverScript()</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">{</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> RECOVERTIME=$1</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "connect / as sysdba" > Recover${ORACLE_SID}.sql</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "shutdown abort;" >> Recover${ORACLE_SID}.sql</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "startup mount;" >> Recover${ORACLE_SID}.sql</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "recover database until time '${RECOVERTIME}' using backup controlfile;" >> Recover${ORACLE_SID}.sql</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "auto" >> Recover${ORACLE_SID}.sql</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> echo "quit;" >> Recover${ORACLE_SID}.sql </span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">}</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Main</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ARCHTIME=FALSE</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ForceRemoteLogSwitch</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></i>
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">echo {$ARCHTIME}</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">if [ ! "$ARCHTIME" = "FALSE" ]</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">then</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"># Logswitch Succeeded, get the Archives now and recover</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">#</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> GetArchives</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> CreateSqlRecoverScript "${ARCHTIME}"</span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> sqlplus /nolog <<EOF<eof font=""></eof></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">@Recover${ORACLE_SID}.sql</span></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">quit</span></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">EOF</span></span></i><br />
<i><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">fi</span></span></i><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><i><br /></i></span>
The script on dgslave has three main functions. The function ForceRemoteLogSwitch takes care of the remote logswitch. As we are assigning the output of the remote call to a variable ARCHTIME, we can use that later on for the recovery process.<br />
<br />
<br />
The second function GetArchives is responsible for transferring the needed archives from dgmaster to dgslave. As both VM's are configured identically, the ARCHPATH on both servers is the same. This function only copies archived logfiles from dgmaster to dgslave which have not been copied already before.<br />
<br />
The third function CreateSqlRecoverScript creates a local script which contains the recovery statements. As it is in fact unknown how the last recovery ended, we start the recovery with a 'shutdown abort' making sure we start recovery in a correctly mounted database. You can recognize here the usage of the retrieved ARCHTIME, called RECOVERTIME here, as it is passed to this function as argument $1.<br />
Also seen here is the usage of the 'auto' recover option. This will make the recovery uninteractive, but it needs a correctly configured log_archive_dest pointing to the location where the archive files have been put.<br />
<br />
Finally the Main part of this script, calls all above functions and finally starts an SQLplus session, with a call to the created Recoveryscript.<br />
That's it. Nothing really special or complex. It works like a charm!!<br />
<br />
<b>Some additional remarks:</b><br />
As we are using a database recovery method the process can not handle the creation of new tablespaces and or new datafile ( to an existing tablespace ).<br />
When we e.g. execute on the primary dgmaster the following statement:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">alter tablespace example add datafile '/opt/mandg/db01/example02.dbf' size 200m;</span><br />
<br />
it will result on the dgmaster during the next recovery step into an<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-01244: unnamed datafile(s) added to control file by media recovery</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">ORA-01110: datafile file 7: '/opt/mandg/db01/example02dbf' </span><br />
<br />
This is also true when e.g. creating a new tablespace. This can be solved using the following steps:<br />
<br />
Lookup the name of the datafile that was generated by the recovery process. It is identified by the fact that it resides within $ORACLE_HOME/dbs and is<br />
<br />
called UNNAMEDnnnnn, where nnnnn is a number.<br />
If the name is found, use the below statement to recreate the correct datafile, using:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">alter database create datafile </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">'/opt/mandg/ora/11.2.0.3/dbs/UNNAMED00007' </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">as '/opt/mandg/db01/example02dbf' ;</span><br />
<br />
In this example /opt/mandg/ora/11.2.0.3 is the ORACLE_HOME on the VM's we created.<br />
After that the automated recovery can continue.<br />
<div>
<br /></div>
FJ Franken (DBA)http://www.blogger.com/profile/14960974918564490086noreply@blogger.com43