Oracle Restart – new in 11g R2

Similar to the clusterware processes in a RAC environment, in 11g R2 even for a standalone instance, functionality is now available to automatically restart components like the database, listener, ASM diskgroup, service etc in the event of their failure.

This feature is called Oracle Restart and it runs out of the Grid Infrastructure home which is separate from the database home.

Oracle Restart is managed by CRSCTL utility and it uses Oracle High Availability Services to start and stop the components managed by Oracle Restart.

Using Oracle Restart, we can stop or start all the components running out of a single Oracle Home with a single srvctl command as well as Oracle Restart will automatically start all components on machine reboot in the proper order taking into account dependencies like ASM instance and the managed database instances. So no more do we have to use the dbstart and dbstop scripts to manage this.

Oracle Restart can be extended to Data Guard configurations as well by integrating with Data Guard Broker to manage the start and stop of database services following a data guard role transition.

Let us examine some of the srvctl commands which can be executed in a single instance environment as well as see the use of the crsctl command – normally previously only used for RAC environments.

-bash-3.2$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA1.dg   ora....up.type ONLINE    ONLINE    redhat64
ora....ER.lsnr ora....er.type ONLINE    ONLINE    redhat64
ora....WARE.dg ora....up.type ONLINE    ONLINE    redhat64
ora.asm        ora.asm.type   ONLINE    ONLINE    redhat64
ora.cssd       ora.cssd.type  ONLINE    ONLINE    redhat64
ora.diskmon    ora....on.type ONLINE    ONLINE    redhat64
ora.eons       ora.eons.type  ONLINE    ONLINE    redhat64
ora.ons        ora.ons.type   ONLINE    ONLINE    redhat64


bash-3.2$ ./crsctl check has
CRS-4638: Oracle High Availability Services is online

-bash-3.2$ ./crsctl check css
CRS-4529: Cluster Synchronization Services is online

-bash-3.2$ ./srvctl start listener

-bash-3.2$ ps -ef |grep tns
oracle   14899     1  0 14:08 ?        00:00:00 /u02/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit

-bash-3.2$ ./srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): redhat64

-bash-3.2$ ./srvctl status asm
ASM is running on redhat64

-bash-3.2$ ./srvctl status diskgroup -g DATA1
Disk Group DATA1 is running on redhat64

-bash-3.2$ ./srvctl config asm
ASM home: /u02/app/oracle/product/11.2.0/grid
ASM listener: LISTENER
Spfile: +DATA1/asm/asmparameterfile/registry.253.700932479
ASM diskgroup discovery string: /dev/raw/raw*

In case a process dies unexpectedly, Oracle Restart will automatically start the process. In the example below, we kill the PMON process of the ora11gr2 instance and find that in less than 5 seconds the instance has been restarted by the Oracle High Availablity Service running in the background constantly monitoring the health of the managed components.

-bash-3.2$ ps -ef |grep pmon
oracle   12710     1  0 Oct22 ?        00:00:01 asm_pmon_+ASM
oracle   15752     1  0 14:14 ?        00:00:00 ora_pmon_ora11gr2
oracle   15975 18271  0 14:15 pts/2    00:00:00 grep pmon

-bash-3.2$ kill -9  15752

-bash-3.2$ ps -ef |grep pmon
oracle   12710     1  0 Oct22 ?        00:00:01 asm_pmon_+ASM
oracle   16679     1  0 14:22 ?        00:00:00 ora_pmon_ora11gr2
oracle   16775 18271  0 14:22 pts/2    00:00:00 grep pmon

In this example we stop all the components running out of a particular home by executing the srvctl stop home command. After stopping the database home, we find that only the ASM instance is running as it is managed by a seperate home which is the Grid Infrastructure home. After the database home is started, the database instance ora11gr2 also is automatically started.

[oracle@redhat346 ~]$ srvctl stop home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /tmp/state

[oracle@redhat346 ~]$ ps -ef |grep pmon
oracle   19841     1  0 Sep18 ?        00:01:24 asm_pmon_+ASM
oracle   31843 27855  0 15:19 pts/1    00:00:00 grep pmon

[oracle@redhat346 ~]$ srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /tmp/state

[oracle@redhat346 ~]$ ps -ef |grep pmon
oracle   19841     1  0 Sep18 ?        00:01:24 asm_pmon_+ASM
oracle   26690     1  0 Oct08 ?        00:00:41 ora_pmon_emrep
oracle   32054     1  0 15:20 ?        00:00:00 ora_pmon_testdb
oracle   32344     1  0 15:20 ?        00:00:00 ora_pmon_ora11gr2
oracle   32620 27855  0 15:22 pts/1    00:00:00 grep pmon

Why doesn't Oracle use my index?

Question:  I have a SQL query where Oracle is not using an index.  The explain plan shows TABLE ACCESS FULL.  I verified that the index exists, but I cannot seem to force Oracle to use the index.  Why does Oracle ignore an index?  How do you force an index to be used?
Answer:  The Oracle cost-based optimizer carefully evaluates every query when making the decision whether to invoke a full-table scan or index access, and you can force index usage with in index hint.  The goal of the SQL optimizer is to only force an index when it's the "best" access plan, given your optimization goals:


Some of the variables that influence the decision to force or ignore an index include:

• The number of blocks in the table - Small tables are accessed faster with a full scan and forcing index usage may hurt performance.
   
• System statistics - the dbms_stats.gather_system_stats procedure measures external timing for index access (sequential reads) and full-scan access (scattered reads).  If Oracle sees expensive index disk reads, it may ignore an index.
   
• Optimizer parms - You can adjust several optimizer parms to force Oracle to use an index:

optimizer_mode - The all_rows access method often favors a parallel full-table scan over an index scan. The first_rows optimizer_mode will often stop Oracle from ignoring an index because it favors index access over computing resources.
optimizer_index_cost_adj - This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the lower the cost of index access.

sort_area_size (if not using pga_aggregate_target) - The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over presorted index retrieval.

Oracle VM 3: Connecting to MySQL Backend Starting in Oracle VM 3.2.1 the built in database of the Oracle VM Manager was MySQL.  I had hoped that this change would also signal a change in the database schema.  In prior versions of OVM 3.x all data was populated in the database in a completely useless longblob form. # mysql ovs  -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' As we can see it is attempting and failing to use /var/lib/mysql/mysql.sock as the connection.  So lets take a look at the process and see if it has any clues. # ps -ef | grep mysql oracle    2234  1778  1 Jan23 ?        00:47:09 /usr/sbin/mysqld --defaults-file=/u01/app/oracle/mysql/data/my.cnf --basedir=/usr --datadir=/u01/app/oracle/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=oracle --log-error=/u01/app/oracle/mysql/data/mysqld.err --pid-file=/u01/app/oracle/mysql/data/mysqld.pid --socket=/u01/app/oracle/mysql/data/mysqld.sock --port=49500 Above we see a couple of key pieces of information.  We now know that the socket is /u01/app/oracle/mysql/data/mysqld.sock and we also see that our configuration file is /u01/app/oracle/mysql/data/my.cnf.  So based on this new socket we can attempt to connect to mysql again. # mysql ovs -S /u01/app/oracle/mysql/data/mysqld.sock -u root -p Enter password: mysql>

Now we are connected to the backend, here comes the bad news.  The database is completely worthless, they are still using longblobs for everything.

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | ovs                | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)

Use the ovs database so we can look at its content.

mysql> use ovs; Database changed

Next we will show all tables so that we can get an idea of what the schema looks like.

mysql> show tables; +--------------------------------+ | Tables_in_ovs                  | +--------------------------------+ | Mgr_AbcStore                   | | Mgr_AccessManager              | | Mgr_ActionEngineProperties     | | Mgr_ActionManager              | | Mgr_ArchiveManager             | | Mgr_BackupManager              | | Mgr_BalancerControl            | | Mgr_BindingMismatchEvent       | | Mgr_BondPort                   | | Mgr_BusinessManager            | | Mgr_Cluster                    | | Mgr_Coherence                  | | Mgr_ControlDomain              | | Mgr_CpuCompatibilityGroup      | | Mgr_CreateStatisticLog         | | Mgr_CreatedEvent               | | Mgr_DeletedEvent               | | Mgr_DiscoverEngineProperties   | | Mgr_DiscoverManager            | | Mgr_EthernetNetwork            | | Mgr_EthernetPort               | | Mgr_EventEngineProperties      | | Mgr_EventLog                   | | Mgr_EventManager               | | Mgr_FibreChannelStorageArray   | | Mgr_FileManager                | | Mgr_FileSystemMount            | | Mgr_FileSystemPlugin           | | Mgr_Foundry                    | | Mgr_HashMap                    | | Mgr_InformationalEvent         | | Mgr_InternalJob                | | Mgr_InternalPort               | | Mgr_InternalSystemLog          | | Mgr_InternalTaggingObject      | | Mgr_IscsiStorageArray          | | Mgr_IscsiStorageInitiator      | | Mgr_Iterator                   | | Mgr_JobConstructingEvent       | | Mgr_JobDoneEvent               | | Mgr_JobRunningEvent            | | Mgr_LinkedList                 | | Mgr_LocalFileServer            | | Mgr_LocalFileSystem            | | Mgr_LocalStorageArray          | | Mgr_LocalStorageInitiator      | | Mgr_LocalStoragePath           | | Mgr_LogEngineProperties        | | Mgr_LogManager                 | | Mgr_LogStore                   | | Mgr_ModelEngineProperties      | | Mgr_ModelManager               | | Mgr_NetworkFileServer          | | Mgr_NetworkFileSystem          | | Mgr_NetworkSelectionManager    | | Mgr_ObjectChangeEvent          | | Mgr_ObjectCheckerTask          | | Mgr_OdofManager                | | Mgr_OvfAssembly                | | Mgr_PathDownEvent              | | Mgr_PathUpEvent                | | Mgr_PerfManager                | | Mgr_PortDownEvent              | | Mgr_PortUpEvent                | | Mgr_Processor                  | | Mgr_Properties                 | | Mgr_QueuedJobCreateEvent       | | Mgr_QueuedServerUpdateNtpServe | | Mgr_QueuedServerYumRepositoryU | | Mgr_RasEngineProperties        | | Mgr_RasManager                 | | Mgr_RefreshRepoFileSystemsTask | | Mgr_Repository                 | | Mgr_RestoreManager             | | Mgr_RoleService                | | Mgr_RootStatisticLog           | | Mgr_RulesEngineProperties      | | Mgr_RulesManager               | | Mgr_SchedulableTaskProperties  | | Mgr_Server                     | | Mgr_ServerClusterStateDownEven | | Mgr_ServerDefaultInfo          | | Mgr_ServerDisconnectErrorEvent | | Mgr_ServerDiscoverScanEvent    | | Mgr_ServerNotification         | | Mgr_ServerOfflineEvent         | | Mgr_ServerOutofDateEvent       | | Mgr_ServerPool                 | | Mgr_ServerPoolMasterMissingEve | | Mgr_ServerRunningEvent         | | Mgr_ServerSelectionManager     | | Mgr_ServerStartingEvent        | | Mgr_ServerStoppedEvent         | | Mgr_ServerUserMissingEvent     | | Mgr_ServerVersionMismatchWarni | | Mgr_ServerYumRepositoryInforma | | Mgr_ServerYumUpdateCheckingEve | | Mgr_SeverityChangeEvent        | | Mgr_StatisticManager           | | Mgr_StatisticSubjectLog        | | Mgr_StatisticTypeLog           | | Mgr_StatsIntervalAdjusterTask  | | Mgr_StorageArrayPlugin         | | Mgr_StorageDeviceUpEvent       | | Mgr_StorageElement             | | Mgr_StorageSelectionManager    | | Mgr_Tag                        | | Mgr_TaskEngineProperties       | | Mgr_TaskManager                | | Mgr_TreeMap                    | | Mgr_TreeStore                  | | Mgr_User                       | | Mgr_UserAccount                | | Mgr_UserStore                  | | Mgr_VirtualCdrom               | | Mgr_VirtualDisk                | | Mgr_VirtualMachine             | | Mgr_VirtualMachineCfgFile      | | Mgr_VirtualMachineDisconnectEr | | Mgr_VirtualMachineRunningEvent | | Mgr_VirtualMachineStartingEven | | Mgr_VirtualMachineStoppedEvent | | Mgr_VirtualMachineStoppingEven | | Mgr_VirtualMachineSuspendedEve | | Mgr_VirtualMachineTemplate     | | Mgr_VmApiMessages              | | Mgr_VmCloneDefinition          | | Mgr_VmCloneNetworkMapping      | | Mgr_VmCloneStorageMapping      | | Mgr_VmDiskMapping              | | Mgr_VmSelectionManager         | | Mgr_Vnic                       | | Mgr_VnicManager                | | Mgr_VnicManagerProperties      | | Mgr_VolumeGroup                | | Mgr_XenHypervisor              | | Mgr_YumRepoOutofDateEvent      | | Mgr_YumUpdateCheckerTask       | | Odof_id_to_type                | | Odof_not_tabled                | | Odof_sys_properties            | | Odof_type_to_class             | | WL_LLR_ADMINSERVER             | +--------------------------------+ 143 rows in set (0.00 sec)

Now lets look at the columns of the Mgr_VirtualMachine table.

mysql> describe Mgr_VirtualMachine; +--------+------------+------+-----+---------+-------+ | Field  | Type       | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | m_id   | bigint(20) | NO   | PRI | 0       |       | | m_data | longblob   | YES  |     | NULL    |       | +--------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

Now lets look at the columns of the Mgr_Server table.

mysql> describe Mgr_Server; +--------+------------+------+-----+---------+-------+ | Field  | Type       | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | m_id   | bigint(20) | NO   | PRI | 0       |       | | m_data | longblob   | YES  |     | NULL    |       | +--------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

Here is a command to pull the whole schema, and every single table has two tables, m_id and m_data with the m_data being longblog.

mysqldump --no-data ovs -S /u01/app/oracle/mysql/data/mysqld.sock -u root -p

Related posts:

1. MySQL: Describe All Tables
2. Solaris 11: Installing MySQL
3. Install MySQL Server Community Edition on Fedora 20
4. Oracle VM: Using Oracle VM Manager 3 from Ubuntu Linux
5. Installing Oracle VM Manager 3.1.1