# Add new disk as a logical volume # Create partition fdisk -l fdisk /dev/sdb n-1-rtn-rtn # Create a physical volume pvcreate /dev/sdb1 pvdisplay # Create a virtual group VolGroup01 vgcreate VolGroup01 /dev/sdb1 # Get PE size from vgdisplay vgdisplay Total PE 4094 # Create a logical volume lvcreate -n vol01 -l 4094 VolGroup01 lvdisplay mkfs.ext3 /dev/VolGroup01/vol01 # Add to fstab vi /etc/fstab /dev/VolGroup01/vol01 /u01 ext3 defaults 0 0 mount /u01 |
Friday, 11 September 2009
Linux : add additonal disk as a logical volume
Posted by Ook at 07:11 0 comments
Labels: Linux
Thursday, 10 September 2009
BOXI : Restore to point in time with oracle
|
Posted by Ook at 09:43 0 comments
Labels: Oracle
Oracle : Refresh the dataguard instance and re-enable
shudown immediate
Note
|
Posted by Ook at 09:41 0 comments
Labels: Oracle
Wednesday, 9 September 2009
Oracle : 11GR2 - File Watchers
Trigger scheduler jobs when a glob-matched file is created in a directory |
Posted by Ook at 06:27 0 comments
Labels: Oracle
Oracle : 11GR2 - IGNORE_ROW_ON_DUPKEY_INDEX Hint
Silently ignore new rows that violate a single unique index.
Bulk operations? Multiple rows with same UK being inserted - which get in - arbitrary ? |
Posted by Ook at 04:20 0 comments
Labels: Oracle
Oracle : 11GR2 - Recursive subqueries for hierarchy traversal
Recursive Subquery Example =========================== WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1 FROM reports_to_101 r /* MAGIC IS HERE */ ,employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL ---------- ------------------------- ---------- ----------- 101 Kochhar 100 0 108 Greenberg 101 1 200 Whalen 101 1 203 Mavris 101 1 204 Baer 101 1 205 Higgins 101 1 109 Faviet 108 2 110 Chen 108 2 111 Sciarra 108 2 112 Urman 108 2 113 Popp 108 2 206 Gietz 205 2 Ref |
Posted by Ook at 04:11 0 comments
Labels: Oracle
Oracle : 11GR2 - LISTAGG - the long missing string concatentation built-in
Finally LISTAGG Example =============== SELECT department_id "Dept.", LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees" FROM employees GROUP BY department_id; Dept. Employees ------ ------------------------------------------------------------ 10 Whalen 20 Hartstein; Fay 30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 40 Mavris 50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers; Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle 60 Austin; Pataballa; Lorentz; Ernst; Hunold 70 Baer
with base_data as ( Ref |
Posted by Ook at 04:09 0 comments
Labels: Oracle
Oracle : 11GR2 - Selected new features
Drawn from the 11GR2 New features doc Definitely Interesting 1.2.2.4 IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement 1.3.1.1 Analytic Functions 2.0 1.3.1.2 Recursive WITH Clause 1.3.2.2 Preprocessing Data for ORACLE_LOADER Access Driver in External Tables 1.5.2.1 E-mail Notification 1.5.2.2 File Watcher 1.5.2.4 Remote Database Jobs 1.5.3.1 Data Pump Legacy Mode Possibly Interesting 1.2.3.2 Configurable Real-Time Query Apply Lag Limit 1.9.2.3 Automatic Patching of Time Stamp With Time Zone Data 1.3.4.2 Minimal Effort Parallel Execution - Auto Degree of Parallelism (DOP) and Queuing 1.9.2.10 Enable Sampling for Active Data Guard 1.9.2.12 Global Oracle RAC ASH Report + ADDM Backwards Compatibility RAC - Possibly Interesting 1.4.1.6 Grid Plug and Play (GPnP) 1.4.1.7 Oracle Restart 1.4.1.10 Role-Separated Management 1.4.1.13 Enterprise Manager-Based Clusterware Resource Management 1.4.1.14 Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters 1.4.1.17 Configuration Assistant Support for Removing Oracle RAC Installations 1.4.1.18 Oracle Universal Installer Support for Removing Oracle RAC Installations 1.4.1.22 Out-of-Place Oracle Clusterware Upgrade 1.4.1.26 SRVCTL Support for Single-Instance Database in a Cluster Definitely Interesting 1.2.2.4 IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows. This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition. 1.3.1.1 Analytic Functions 2.0 New and enhanced analytical functions are introduced in this release. A new ordered aggregate, LISTAGG, concatenates the values of the measure column. The new analytic window function NTH_VALUE (a generalization of existing FIRST_VALUE and LAST_VALUE functions) gives users the functionality of retrieving an arbitrary (or nth) record in a window. The LAG and LEAD functions are enhanced with the IGNORE NULLS option. The new and enhanced SQL analytical functions allow more complex analysis in the database, using (simpler) SQL specification and providing better performance. See Also: Oracle Database Data Warehousing Guide for details 1.3.1.2 Recursive WITH Clause The SQL WITH clause has been extended to enable formulation of recursive queries. Recursive WITH clause complies with the American National Standards Institute (ANSI) standard. This makes Oracle ANSI-compatible for recursive queries. See Also: Oracle Database SQL Language Reference for details 1.3.2.2 Preprocessing Data for ORACLE_LOADER Access Driver in External Tables The syntax for the ORACLE_LOADER access driver is extended in this release to allow specification of a program to preprocess the data files that are read for the external table. The access parameters can specify the name of a directory object and the name of an executable file in that directory object. When the access driver needs to read data from a file, it creates a process that runs the specified program, passing in the name of the data file. The output from the program is passed into the access driver which parses the data into records and columns. The initial use of this feature is by a customer who needs to load data that is stored in compressed files. The user specifies the name of the program used to decompress the file as part of the access parameters. The access driver reads the output of the decompression program. Large customers want to load data from compressed files which requires less disk space and uses the I/O bandwidth between the disk and memory more efficiently. 1.5.2.1 E-mail Notification Oracle Database 11g Release 2 (11.2) users can now get e-mail notifications on any job activity. This feature improves efficiency by enabling users to be notified of any job activity that is of interest to them without having to constantly monitor the job. 1.5.2.2 File Watcher File watcher enables jobs to be triggered when a file arrives on a given machine. This feature improves efficiency and ease-of-use. Jobs with file dependencies are automatically triggered when the specified file is received instead of constantly monitoring for the file. 1.5.2.4 Remote Database Jobs This feature enables users to run PL/SQL blocks or stored procedures that reside in a remote database as a job. This is a key feature for Enterprise Manager scheduling. It improves efficiency and ease-of-use by enabling job scheduling in a distributed environment to be managed centrally. 1.5.3.1 Data Pump Legacy Mode Data Pump Legacy Mode provides backward compatibility for scripts and parameter files used for original Export and Import scripts. This feature enables users to continue using original Export and Import scripts with Data Pump Export and Import. Development time is reduced as new scripts do not have to be created. Possibly Interesting 1.2.3.2 Configurable Real-Time Query Apply Lag Limit A physical standby database can be open for read-only access while redo apply is active only if the Oracle Active Data Guard option is enabled. This capability is known as real-time query. The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale. 1.9.2.3 Automatic Patching of Time Stamp With Time Zone Data Time stamp with time zone data could become stale in the database tables when the time zone version file is updated. Today, users have to manually fix the affected data. This feature updates the system and user data transparently with minimal downtime and provides automatic and transparent patching of time stamp with time zone data whenever a time zone file is updated. Also, when a server time zone version is patched, all of the clients that communicate with the server need to be patched as well. With this feature, OCI, JDBC, Pro*C, and SQL*Plus clients can now continue to work with the server without having to update their client-side files. This new feature provides automatic and transparent patching of time stamp with time zone data whenever a time zone file is updated. 1.3.4.2 Minimal Effort Parallel Execution - Auto Degree of Parallelism (DOP) and Queuing When activated, Oracle determines the optimal degree of parallelism (DOP) for any given SQL operation based on the size of the objects, the complexity of a statement, and the existing hardware resources. 1.9.2.10 Enable Sampling for Active Data Guard Active Session History (ASH) is now available on standby systems. Having ASH data available on standby systems for Data Guard environments allows customers to troubleshoot performance problems specific to their standby environments. 1.9.2.12 Global Oracle RAC ASH Report + ADDM Backwards Compatibility The Active Session History (ASH) report now includes cluster-wide information, greatly enhancing it's utility in identifying and troubleshooting performance issues that span nodes for a cluster database. RAC - Possibly Interesting 1.4.1.6 Grid Plug and Play (GPnP) Grid Plug and Play (GPnP) eliminates per-node configuration data and the need for explicit add and delete nodes steps. This allows a system administrator to take a template system image and run it on a new node with no further configuration. This removes many manual operations, reduces the opportunity for errors, and encourages configurations that can be changed easily. Removal of the per-node configuration makes the nodes easier to replace, because they do not need to contain individually-managed state. Grid Plug and Play reduces the cost of installing, configuring, and managing database nodes by making their per-node state disposable. It allows nodes to be easily replaced with regenerated state. 1.4.1.7 Oracle Restart Oracle Restart improves the availability of your single-instance Oracle database. Oracle Restart automatically restarts the database instance, the Automatic Storage Management (ASM) instance, the listener, and other components after a hardware or software failure or whenever your database host computer restarts. Server Control (SRVCTL) is the command line interface to manage Oracle processes that are managed by Oracle Restart on a standalone server. This feature provides improved reliability and automated management of a single-instance Oracle database and the management of any process or application running on the database server. 1.4.1.10 Role-Separated Management Role-separated management for Oracle Clusterware allows certain administrative tasks to be delegated to different people, representing different roles in the company. It is based on the idea of a clusterware administrator. The administrator may grant administrative tasks on a per resource basis. For example, if two databases are placed into the same cluster, the clusterware administrator can manage both databases in the cluster. But, the clusterware administrator may decide to grant different administrative privileges to each DBA responsible for one of those databases. Role-separated management allows multiple applications and databases to share the same cluster and hardware resources, but ensures that different administration groups do not interfere with each other 1.4.1.13 Enterprise Manager-Based Clusterware Resource Management New in this release is an Enterprise Manager graphical user interface (GUI) to manage various Oracle Clusterware resources with full lifecycle support. In addition to allowing the creation and configuration of resources within Oracle Clusterware, it also helps to monitor and manage resources once deployed in the cluster. Using Oracle Enterprise Manager as a GUI to monitor and manage various Oracle Clusterware resources eases the daily management in high availability environments. 1.4.1.14 Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters Enterprise Manager provisioning introduces procedures to easily scale up or scale down Oracle Clusterware and Oracle Real Application Clusters. Ease-of-implementation and management for a clustered database environment can be achieved through utilizing the terprise Manager provisioning framework. 1.4.1.17 Configuration Assistant Support for Removing Oracle RAC Installations Database Configuration Assistant (DBCA), Database Upgrade Assistant (DBUA), and Net Configuration Assistant (NETCA) have been updated to support the complete deinstallation and deconfiguration of Oracle RAC databases and listeners. This support improves the manageability of an Oracle RAC environment through automation of deinstallation and deconfiguration of Oracle RAC databases. 1.4.1.18 Oracle Universal Installer Support for Removing Oracle RAC Installations The installer can clean up a failed Oracle Clusterware installation or upgrade of an environment prior to reattempting the operation. This ensures that the reattempted operation is done over a clean environment, thereby eliminating the chances of errors related to environmental inconsistencies. Easily cleaning up an environment provides improved Oracle RAC manageability and deployment. 1.4.1.22 Out-of-Place Oracle Clusterware Upgrade A new version of Oracle Clusterware is now installed into a separate home from the current installation. This reduces the downtime required to upgrade a node in the cluster and facilitate the provisioning of clusters within an enterprise. The benefit is a reduction in planned outage time required for cluster upgrades which assists in meeting availability service levels. This also makes it easier to provide a standard installation across the enterprise. 1.4.1.26 SRVCTL Support for Single-Instance Database in a Cluster Using SRVCTL, you can register a single-instance database to be managed by Oracle Clusterware. Once registered, Oracle Clusterware starts, stops, monitors, and restarts the database instance. This feature provides an improved management interface which makes it easy to provide higher availability for single-instance databases that are running on a server that is part of a cluster. Fini |
Posted by Ook at 04:03 0 comments
Labels: Oracle
Thursday, 3 September 2009
Oracle: Exporting Grid Control Repository Database
Resolve EXP-00079 EXP-00056 (exp) and ORA-28112 (expdp) Errors when Exporting Grid Control Repository Database using Traditional Export or Datapump Export (1oG+) grant exempt access policy to EXPORTING_USER; |
Posted by Ook at 08:23 0 comments
Labels: Oracle
Oracle : Archivelog mode 2
# For RAC bring down other instances srvctl stop instance -d NTPDB1 -i NTPDB12 # On remaining instance alter system set log_archive_dest_1='location=/u02/volntpdblogs01/oradata/NTPDB1/archive' scope=spfile alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile shutdown immediate; startup mount; alter database archivelog; alter database open; ALTER SYSTEM SWITCH LOGFILE; # For RAC bring back other instances srvctl start instance -d NTPDB1 -i NTPDB12 |
Posted by Ook at 03:29 0 comments
Labels: Oracle
Tuesday, 1 September 2009
Oracle : Drop corrupt AQ tables
alter session set events '10851 trace name context forever, level 2'; This disables ORA-24005 message then table can be dropped using conventional SQL DROP |
Posted by Ook at 05:57 0 comments
Labels: Oracle
Subscribe to:
Posts (Atom)