Oracle11gR2 RAC: FAQ

Can I change a node’s hostname?

Yes, however, the node must be removed and added back to the cluster with the new name.

How do I define a service for a Policy-Managed Database?

When you define services for a policy-managed database, you define the service to a server pool where the database is running. You can define the service as either UNIFORM (running on all instances in the server pool) or SINGLETON (running on only one instance in the server pool). For SINGLETON services, Oracle RAC chooses on which instance in the server pool the service is active. If that instance fails, then the service fails over to another instance in the server pool. A service can only run in one server pool.

Services for administrator-managed databases continue to be defined by the PREFERRED and AVAILABLE definitions.

How do I convert from a Policy-Managed Database to Administrator-Managed Database?

You cannot directly convert a policy-managed database to an administrator-managed database. Instead, you can remove the policy-managed configuration using the 'srvctl remove database' and 'srvctl remove service' commands, and then create a new administrator-managed database with the 'srvctl add database' command.

What is Grid Plug and Play (GPnP)?

Grid Plug and Play (GPnP) eliminates per-node configuration data and the need for explicit add and delete node 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.

What is a Server Pool?

Server pools enable the cluster administrator to create a policy which defines how Oracle Clusterware allocates resources. An Oracle RAC policy-managed database runs in a server pool. Oracle Clusterware attempts to keep the required number of servers in the server pool and, therefore, the required number of instances of the Oracle RAC database. A server can be in only one server pool at any time. However, a database can run in multiple server pools. Cluster-managed services run in a server pool where they are defined as either UNIFORM (active on all instances in the server pool) or SINGLETON (active on only one instance in the server pool).

You should create redo log groups only if you are using administrator-managed databases. For policy-managed databases, increase the cardinality and when the instance starts, if you are using Oracle Managed Files and Oracle ASM, then Oracle automatically allocates the thread, redo, and undo.

If you remove an instance from your Oracle RAC database, then you should disable the instance’s thread of redo so that Oracle does not have to check the thread during database recovery.

For policy-managed databases, Oracle automatically allocates the undo tablespace when the instance starts if you have OMF enabled.

What is Run-Time Connection Load Balancing?

The run-time connection load balancing feature enables routing of work requests to an instance that offers the best performance, minimizing the need to relocate work. To enable and use run-time connection load balancing, the connection goal must be set to SHORT and either of the following service-level goals must be set:

· SERVICE_TIME—The Load Balancing Advisory attempts to direct work requests to instances according to their response time. Load Balancing Advisory data is based on the elapsed time for work done by connections using the service, as well as available bandwidth to the service. This goal is best suited for workloads that require varying lengths of time to complete, for example, an internet shopping system.

· THROUGHPUT—The Load Balancing Advisory measures the percentage of the total response time that the CPU consumes for the service. This measures the efficiency of an instance, rather than the response time. This goal is best suited for workloads where each work request completes in a similar amount of time, for example, a trading system.

Client-side load balancing balances the connection requests across the listeners by setting the parameter ‘LOAD_BALANCE=ON’ directive. When you set this parameter to ON, Oracle Database randomly selects an address in the address list, and connects to that node's listener. This balances client connections across the available SCAN listeners in the cluster. When clients connect using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect.

What are the different types of Server-Side Connection Load Balancing?

With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service by using the load balancing advisory. The two types of connection load balancing are:

· SHORT—Connections are distributed across instances based on the amount of time that the service is used. Use the SHORT connection load balancing goal for applications that have connections of brief duration. When using connection pools that are integrated with FAN, set the connection load balancing goal to SHORT. SHORT tells the listener to use CPU-based statistics.

· LONG—Connections are distributed across instances based on the number of sessions in each instance, for each instance that supports the service. Use the LONG connection load balancing goal for applications that have connections of long duration. This is typical for connection pools and SQL*Forms sessions. LONG is the default connection load balancing goal, and tells the listener to use session-based statistics.

How do I enable the Load Balancing Advisory (LBA)?

To enable the load balancing advisory, use the ‘-B’ option when creating or modifying the service using the ‘srvctl’ command.

How does the database register with the Listener?

When a listener starts after the Oracle instance starts, and the listener is listed for service registration, registration does not occur until the next time the Oracle Database process monitor (PMON) discovery routine starts. By default, PMON discovery occurs every 60 seconds.

To override the 60-second delay, use the SQL ‘ALTER SYSTEM REGISTER’ statement. This statement forces the PMON process to register the service immediately.

If you run this statement while the listener is up and the instance is already registered, or while the listener is down, then the statement has no effect.

Can I configure both failure notifications with Universal Connection Pool (UCP)?

Connection failure notification is redundant with Fast Connection Failover (FCF) as implemented by the UCP. You should not configure both within the same application.

Should I configure Transparent Application Failure (TAF) in my service definition if using Fast Connection Failure (FCF)?

Do not configure Transparent Application Failover (TAF) with Fast Connection Failover (FCF) for JDBC clients as TAF processing will interfere with FAN ONS processing.

Can I use Fast Connection Failover (FCF) and Transparent Application Failover (TAF) together?

No. Only one of them should be used at a time.

What is the status of Fast Connection Failover (FCF) with Universal Connection Pool (UCP)?

FCF is now deprecated along with the Implicit Connection Caching in favor of using the Universal Connection Pool (UCP) for JDBC.

Does Fast Connection Failover (FCF) support planned outages?

FCF does not support planned outages like service relocation (reference Doc ID: 1076130.1). It is designed to work for unplanned outages, where a RAC service is preferred on all the nodes in the cluster and one of the nodes goes down unexpectedly. When a planned outage like a service relocation is done from one node to the other, FCF does not work as expected and the result is unpredictable. There is no solution at present for this. Enhancement request 9495973 has been raised to address this limitation.

Should I user JDBC Thin driver or JDBC OCI driver?

Oracle thin JDBC driver is usually preferred by application developers because it is cross platform and has no external dependencies. However some applications require the high-performance, native C-language based Oracle Call Interface (OCI) driver. This driver is compatible with FCF and can alternatively use Transparent Application Failover (TAF) which operates at a lower level than FCF and can automatically resubmit SELECT queries in the event of a node failure. However for most applications, the ease of deployment of the thin driver with full FCF support will outweigh any benefits offered by the OCI driver.

How do I subscribe to HA Events?

If you are using a client that uses Oracle Streams Advanced Queuing, such as OCI and ODP.NET clients, to receive FAN events, you must enable the service used by that client to access the alert notification queue by using the ‘-q’ option via the ‘srvctl’ command.

FAN events are published using ONS and Oracle Streams Advanced Queuing. The service metrics received from the Oracle RAC load balancing advisory through FAN events for the service are automatically placed in the Oracle Streams AQ queue table, ALERT_QUEUE.

Use the following query against the internal queue table for load balancing advisory FAN events to monitor load balancing advisory events generated for an instance:

SET PAGES 60 COLSEP '|' LINES 132 NUM 8 VERIFY OFF FEEDBACK OFF

COLUMN user_data HEADING "AQ Service Metrics" FORMAT A60 WRAP

BREAK ON service_name SKIP 1

SELECT

TO_CHAR(enq_time, 'HH:MI:SS') Enq_time, user_data

FROM sys.sys$service_metrics_tab

ORDER BY 1 ;

What is Connection Affinity?

Connection affinity is a performance feature that allows a connection pool to select connections that are directed at a specific Oracle RAC instance. The pool uses run-time connection load balancing (if configured) to select an Oracle RAC instance to create the first connection and then subsequent connections are created with an affinity to the same instance.

What types of affinity does Universal Connection Pool (UCP) support?

UCP JDBC connection pools support two types of connection affinity: transaction-based affinity and Web session affinity.

What is Transaction-Based Affinity?

Transaction-based affinity is an affinity to an Oracle RAC instance that can be released by either the client application or a failure event. Applications typically use this type of affinity when long-lived affinity to an Oracle RAC instance is desired or when the cost (in terms of performance) of being redirected to a new Oracle RAC instance is high. Distributed transactions are a good example of transaction-based affinity. XA connections that are enlisted in a distributed transaction keep an affinity to the Oracle RAC instance for the duration of the transaction. In this case, an application would incur a significant performance cost if a connection is redirect to a different Oracle RAC instance during the distributed transaction.

Transaction-based affinity is strictly scoped between the application/middle-tier and UCP for JDBC; therefore, transaction-based affinity only requires that the setFastConnectionFailoverEnabled property be set to true and does not require complete FCF configuration. In addition, transaction-based affinity does not technically require run-time connection load balancing. However, it can help with performance and is usually enabled regardless. If run-time connection load balancing is not enabled, the connection pool randomly picks connections.

What is Web Session Affinity?

Web session affinity is an affinity to an Oracle RAC instance that can be released by either the instance, a client application, or a failure event. The Oracle RAC instance uses a hint to communicate to a connection pool whether affinity has been enabled or disabled on the instance. An Oracle RAC instance may disable affinity based on many factors, such as performance or load. If an Oracle RAC instance can no longer support affinity, the connections in the pool are refreshed to use a new instance and affinity is established once again.

Applications typically use this type of affinity when short-lived affinity to an Oracle RAC instance is expected or if the cost (in terms of performance) of being redirected to a new Oracle RAC instance is minimal. For example, a mail client session might use Web session affinity to an Oracle RAC instance to increase performance and is relatively unaffected if a connection is redirected to a different instance.

What is recommended for WebLogic Server?

Oracle recommends using WebLogic JDBC multi data sources to handle failover instead. While connect-time failover does not provide the ability to pre-create connections to alternate Oracle RAC nodes, multi data sources have multiple connections available at all times to handle failover.

Transparent Application Failover (TAF) is not supported for any WLS data source. TAF, as delivered via JDBC is currently not transparent. It is documented to affect some ongoing query results and PreparedStatements in unpredictable and unrecoverable ways. TAF JDBC requires specific recovery code at the application level and affects the integrity of statements that WebLogic might be caching.

Do I still need to backup my Oracle Cluster Registry (OCR) and Voting Disks?

You no longer have to back up the voting disk. The voting disk data is automatically backed up in OCR as part of any configuration change and is automatically restored to any voting disk added. If all voting disks are corrupted, however, you can restore.

Oracle Clusterware automatically creates OCR backups every four hours. At any one time, Oracle Database always retains the last three backup copies of OCR. The CRSD process that creates the backups also creates and retains an OCR backup for each full day and at the end of each week. You cannot customize the backup frequencies or the number of files that Oracle Database retains.

How is DBMS_JOB functionality affected by RAC?

DBMS jobs can be set to run either on database (i.e. any active instance), or a specific instance.

What is PARELLEL_FORCE_LOCAL?

By default, the parallel server processes selected to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster. By setting PARALLEL_FORCE_LOCAL to TRUE, the parallel server processes are restricted to just one node, the node where the query coordinator resides (the node on which the SQL statement was executed). However, in 11.2.0.1 when this parameter is set to TRUE the parallel degree calculations are not being adjusted correctly to only consider the CPU_COUNT for a single node. The parallel degree will be calculated based on the RAC-wide CPU_COUNT and not the single node CPU_COUNT. Due to this bug 9671271 it is not recommended that you set PARALLEL_FORCE_LOCAL to TRUE in 11.2.0.1, instead you should setup a RAC service to limit where parallel statements can execute.

What is the Service Management Policy?

When you use automatic services in an administrator-managed database, during planned database startup, services may start on the first instances to start rather than their preferred instances. Prior to Oracle RAC 11 g release 2 (11.2), all services worked as though they were defined with a manual management policy.

Why does my user appear across all nodes when querying GV$SESSION when my service does not span all nodes?

The problem is you are querying GV$SESSION as the ABC user and this results in the "strange" behaviour. If you select gv$session, 2 parallel servers are spawned to query the v$session on each node. This happens as the same user. Hence when you query gv$session as ABC you are seeing 3 (one real and 2 parallel slaves querying v$session on each instance). The reason you are seeing 1 on one node and 3 on the other is the order in which the parallel processes query the v$session. Take the sys (or any other) user to query the session of ABC and you will not see this problem.

How does Clustereare startup with OCR and Voting Disk in ASM?

The startup sequence has been changed/replaced, now being 2-phased, optimized approach:

Phase I

· OHASD will startup "local" resources first.

· CSSD uses GPnP profile which stores location of voting disk so no need to access ASM (voting disk is stored different within ASM than other files so location is known).

Simultaneously,

· ORAAGENT starts up and ASM instance is started (subset of information in OCR is stored in OLR, enough to startup local resources), and ORAROOTAGENT starts CRSD.

So the 1st phase of Clusterware startup is to essentially start up local resources.

Phase II

· At this point ASM and full OCR information is available and the node is "joined" to cluster.

What is the Oracle Database Quality of Service Management?

Oracle Database QoS Management is an automated, policy-based product that monitors the workload requests for an entire system. Oracle Database QoS Management manages the resources that are shared across applications and adjusts the system configuration to keep the applications running at the performance levels needed by your business. Oracle Database QoS Management responds gracefully to changes in system configuration and demand, thus avoiding additional oscillations in the performance levels of your applications. If you use Oracle Database Quality of Service Management (Oracle Database QoS Management), then you cannot have SINGLETON services in a server pool, unless the maximum size of that server pool is one.

Is a re-link required for the Clusterware home after an OS upgrade?

In 11.2, there are some executables in the GRID home that can and should be re-linked after an OS upgrade. The procedure to do this is:

#> cd GI_HOME/crs/install
#> perl rootcrs.pl -unlock

As the grid infrastructure for a cluster owner:

$> export ORACLE_HOME=Grid_home
$> $GI_HOME/bin/relink

As root again:

#> cd GI_HOME/crs/insta

How do I determine the “Master” node?

For the cluster synchronization service (CSS), the master can be found by searching $GI_HOME/log/cssd/ocssd.log. For master of an enqueue resource with Oracle RAC, you can select from v$ges_resource. There should be a master_node column.

What are the different types of failover mechanisms available?

· JDBC-THIN driver supports Fast Connection Failover (FCF)

· JDBC-OCI driver supports Transparent Application Failover (TAF)

· JDBC-THIN 11gR2 supports Single Client Access Name (SCAN)

What is recommendation on type of tablespaces?

You should use locally managed, auto-allocate tablespaces. With auto-allocate Oracle automatically grows the size of the extent depending on segment size, available free space in the tablespace and other factors. The extent size of a segment starts at 64 KB and grows to 1 MB when the segment grows past 1 MB, and 8 MB once the segment size exceeds 64 MB. So for a large table, the extent size will automatically grow to be large. The use of uniform extents is strongly discouraged for two reasons; space wastage and the impact that wasted space has on scan performance.

For large partitioned objects you should use multiple big file tablespaces to avoid file header block contention during parallel load operations. File header block contention appears as the ‘gc buffer busy’ enqueue wait event in an AWR report. Checking the buffer wait statistic will indicate if it is the file header block that is being contended for.

To evenly distribute a partitioned table among multiple big file tablespaces use the STORE IN clause.

What is the recommendation on column statistics?

Prior to loading any data it is advisable to run all queries against the empty tables to populate or seed the column usage statistics. Column usage statistics are used during optimizer statistics gathering to automatically determine which columns require histograms and the number of buckets that will be used. A column is a candidate for a histogram if it has been seen in a where clause predicate e.g. an equality, range, LIKE, etc. and if there is data skew in that column.

How do I size hash partitions?

Oracle uses a linear hashing algorithm to create sub-partitions. In order to ensure that the data gets evenly distributed among the hash partitions the number of hash partitions should be a power of 2 (i.e. 2 * # of CPU). However, each hash partition should be at least 16MB in size. Any smaller and they will not have efficient scan rates with parallel query. If the subpartitions are too small (from the 2 * # of CPU) considering using a smaller number of partitions (still an even number of partitions).

è What should be my block size?

8 KB is the default block size and is the block size used during all of Oracle's testing. Typically this is good enough for a data warehouse and transactional systems (good compromise or sweet spot). By doubling the default block size you can increase the chances of getting a good compression rate as Oracle applies data compression at the block level. The more rows in the block the greater the chance Oracle will find duplicate values within a block. (Reference: Oracle Sun Database Machine Application Best Practices for Data Warehousing, Doc ID 1094934.1)

What is the guideline on how to auto-extend data files?

When configuring a file to auto extend, the size of the extension should cover all disks in the ASM disk group to optimize balance. For example, with a 4 MB AU size and 128 disks, the size of the extension should be a multiple of 512MB (4*128).

Sessions per Instance using GV$SESSION

I've been doing some testing around Oracle 11.2 RAC and decided to share a few findings so I'll be trying to document them in my blog, which I've been too busy to update. I have to give my sincerest thanks and appreciation to all the bloggers who regularly update, I've no idea how they find the time but appreciate their efforts as I've found gems of information which have really helped me.

One of the first things I've seen with 11.2 RAC is that the information, blogs included, is a bit wanting... there simply are not a lot of good, useful or factual information, even the Oracle documentation is light or inaccurate. Yes, I have given feedback to the inaccuracies and they should be corrected so hopefully you wont even notice or see any evidence. This contrasts with 11.1 and previous version which have lots of blogs, and good, useful information. To me this indicates adoption is still a work in progress for this version, which makes sense since it does bring a lot to the table which needs to be thoroughly tested.

A little trinket of, hopefully, useful information which I found on the OTN forums (credit to Sebastian), which I had not previously realized, but was puzzled during my 11.2 RAC testing:

Say you have a query to get the number of sessions of a particular user across all the nodes (for example to see how load balancing is working):

SELECT inst_id ,username ,count(*) FROM GV$SESSION
FROM username = 'ABC' GROUP BY inst_id ,username


This results in "strange" behavior if you query the GV$SESSION table as the same user ABC, as 2 parallel servers are spawned to query the V$SESSION table on each node. This happens as the same user, i.e. ABC. Hence you will see 3 (1 real and 2 parallel) slaves querying the V$SESSION on each instance. The reason you will see 1 on one node and 3 on the other is the order in which the parallel processes query the V$SESSION.

If you use another user to query the sessions for ABC you will not see this problem and get an accurate session per instance count.

Hope that explains it.

Oracle Cluster Health Monitor (formerly IPD/OS)

Oracle Cluster Health Monitor (formerly known as Instantaneous Problem Detector for Clusters or IPD/OS) is designed to detect and analyze operating system (OS) and cluster resource related degradation and failures in order to bring more explanatory power to many issues that occur in clusters where Oracle Clusterware and Oracle RAC are running, such as node eviction.

It tracks the OS resource consumption at each node, process, and device level continuously. It collects and analyzes the cluster-wide data. In real time mode, when thresholds are hit, an alert is shown (i.e. you need to be looking at the screen in GUI mode). For root cause analysis, historical data can be replayed to understand what was happening at the time of failure.

I had a bit of trouble during the initial installation since it was not made clear in the README that you MUST unzip and run the subsequent installation process from the home directory of the user doing the installation. I forgot this during re-installation as well, which led to me spend quite a lot of time re-learning this important fact, and hence documenting this for reference.

Oracle Enterprise Manager (OEM) Grid Control, and other tools, provide similar functionality, but I've found that Cluster Health Monitor (CHM) is better in terms of real-time analysis, monitoring and its playback functionality. It is also free, as in no charge, and very easy to setup and maintain.

My environment:

• 4-node Oracle 11.2.0.1 RAC
• Hostnames: ucstst11, ucstst12, ucstst13, ucstst14
• OS: Oracle Enterprise Linux 5u4 x64

Installation Steps
1. Create 'crfuser' on all nodes in cluster - This can be any user except root, I do recommend however using a separate user from the regular 'oracle' for separation of duty. Perhaps a standard user used for monitoring tools. The user's group does not have to be oinstall, I just use it here to show that this user also owns 'Oracle' software, and will be an administrator.

#> useradd -d /opt/crfuser -m -s /bin/ksh -g oinstall crfuser


2. Setup password-less SSH user equivalence among all the users across the nodes - I wont go into this here since there are numerous sites as to how to set this up (including one of my own I believe).

Note: If you do have a previous installation of CHM installed you will need to uninstall before proceeding with the new installation. To install, on each node where there is a previous installation:

a. Disable CHM

#> /etc/init.d/init.crfd disable

b. Uninstall CHM

#> /usr/lib/oracrf/install/crfinst.pl -d

c. Remove the Berkely database or BDB (wherever it was installed), and the previus CHM installation home if it still exists.


3. Login as the CHM user ('crfuser') and unzip the install file, it can be obtained from Oracle Technology Network (OTN) here.

#> su - crfuser
#> unzip crfpack-linux.zip

Ensure you are in the 'crfuser' home directory when running the unzip command, or that the unzip is done to that directory.


4. Run the installer to setup the nodes - This step does not actually do any installation so if anything fails simply remove the files, fix the error, and try again. What this step does is to:

a. Run some checks for SSH access to all nodes
b. Assign a replica node
c. Generate a cluster-wide configuration file
d. Send the installation files across the other nodes.

To start the process, from any node:

$> cd ~/install
$> ./crfinst.pl -i ucstst11,ucstst12,ucstst13,ucstst14 -b /opt/oracrfdb -m ucstst11 -N RACDB

Replace the node list with those of your own, you will also need to designate one as a master node, i.e. '-m ucstst11'. If you leave out this argument you will be prompted to designate a master node. By default the cluster will be called 'MyCluster', I've used '-N RACDB' to name my cluster something more known to me, i.e. 'RACDB'. I've also specified (using '-b /opt/oracrfdb') that the location for the BDB database which stores all the captured data should be '/opt/oracrfdb'. This must be a separate file system or mount point, i.e. not under '/' directly otherwise the installation will complain and exit. I'm unsure as to where the default would be located or if there is one since I never tested this setup, but of course you can also work around using somewhere under '/' (if you are short on mounts or space) by modifying the appropriate line in the 'crfinst.pl' Perl script, or using a loopback filesytem.


5. Following this, you will then need to run on each node as the root user:

#> /opt/crfuser/install/crfinst.pl -f -b /opt/oracrfdb -N RACDB
#> /etc/init.d/init.crfd enable

Note: The above step will remove the files which were unzipped previously in the 'crfuser' home directory from the 'crfpack-linux.zip' file. So don't be surprised when this happens.


6. Install the GUI - It is not recommended to install the GUI on any of the RAC nodes due to performance (the GUI takes a few resources) and availability issues (the node running the GUI goes down), but in a simple setup this is okay. Ideally you would install only the GUI portion on any remote client machine. To install only the GUI:

a. Unzip the 'crfpack-linux.zip' file again, to the home directory of the 'crfuser' (or whatever user this time around).

$> cd /opt/crfuser
$> unzip crfpack-linux.zip

b. Run the GUI installation

$> mkdir oracrfgui
$> cd install
$> ./crfinst.pl -g /opt/crfuser/oracrfgui


7. Run the GUI. The below command starts the GUI by connecting to the master node, with a one second refresh time.

$> cd ~/oracrf/bin
$> ./crfgui -m ucstst11


Some Usage Tips
Refresh Rate
To modify the GUI refresh rate (default is 1 second) specify the '-r ' option. The below uses a 5 second refresh rate to update the GUI:

$> ./crfgui -r 5 -m ucstst11


Historical Mode
To do historical analysis specify the '-d ::' option which will display data from the database from the current time until the number of hours, minutes, and seconds specified in the past.


Creating a Loopback Filesystem for the BDB location

1. Create the local file on each node which must be at least 5GB per node, i.e. 5000 * N, or in my case 20000 (5000 * 4).

#> dd if=/dev/zero of=/mnt/oracrfdb.fs count=20000 bs=1M

2. Create a file system on the file, using a label of "ipdosbdb' or whatever is your choice:

#> mke2fs -F -j -L "ipdosbdb" /mnt/oracrfdb.fs

3. Edit the '/etc/fstab' file to ensure the filesystem will be re-mounted following a reboot by adding the following line:

/mnt/oracrfdb.fs /opt/oracrfdb ext3 rw,/dev/loop0 0 0

Specify an available loopback device substituting '/dev/loop0' as appropriate.

4. Mount the file system:

#> mount /opt/oracrfdb


Hope this helps. Feedback and comments welcome!

How to install Windows 7 from a USB Flash Drive

Requirement

• 4GB USB Drive (minimum)
• Windows 7 ISO (32-bit or 64-bit)
• Windows Vista
  
• MBRwiz (Windows XP diskpart utility does not detect USB pen drives so this free utility will make the USB drive bootable)
• MagicDisc (freeware utility to mount ISO as virtual drive)

Note:

My running OS during this setup (which worked) was Vista (32-bit for the 32-bit Win7). When I tried using XP, the process resulted in a bad boot sector so I could not use the USB Flash drive to load Windows 7. I've seen other blogs where the likely suspect is the 'bootsec.exe' command does not work properly when on XP (I've no idea why). Also, in Vista, you actually don't need the separate 'MBRwiz' program, as 'diskpart' will work just fine. The steps to use that process can be easily googled, or you can leave me a comment and I'll add those in this blog.

Steps

1. Connect your USB flash drive to your computer for formatting, and make note of the drive letter assigned. For the purposes of this tutotial we will use E:.

2. In Windows Explorer or other file management utility, right click on the USB drive and select 'Format…' from the context menu.

3. Click the 'Start' button to begin formatting your USB drive. This ensure there is nothing on the drive (starting from scratch if you will).

4. Open a command prompt window ('Start' menu -> 'Run…' -> cmd -> press 'ENTER') and type:

convert E: /fs:ntfs (remember E: is my drive letter so substitute with yours as appropriate).

5. Extract your download of MBRwiz (if not already done) to a location of your choice, and open a command window in the same location. Run the commands:

MBRWiz.exe /list (this gets a listing of all disks, so note down the disk number assigned to your USB drive which will be used in the next command)

MBRWiz.exe /disk=2 /active=1 (my disk was detected as '2', so replace this with whatever yours was detected as)

6. Install the MagicDisc software (if not already done) and mount your Windows 7 ISO as a virtual drive (mine was mounted as G:). You can of course use any other type of such software such as CD Anywhere, it does not matter. Alternatively, if you have a Windows 7 disk, or the full installation files otherwise you can use this, you do not need the ISO (I just happened to download it as such).



7. Open a command prompt window to the virtual drive, navigate below the 'boot' subdirectory and run the command:

bootsect /nt60 E: (E: was my drive letter substitute with yours as appropriate).

Note: This command will only work if the architectures match, i.e. I was using 32-bit Windows XP, and had a 32-bit Windows 7 media (and hence 'bootsect' file). When I tried using the same 'bootsect' command from the Windows 7 64-bit media it failed. I've not tested it yet, but it is likely that you can just run the 32-bit version of the 'bootsect /nt60 E:' command, and then continue with the steps for copying the 64-bit Winows 7 media to the USB drive and it should still allow installation from the USB (after all this command is only saying make the USB bootable to Windows 7, right?).

8. Copy all the files from your Windows 7 media (in my case ISO mounted virtual drive G:) to your USB drive. You should be able to use any file manager such as Windows Explorer (in my case I used Xplorer2 Lite which has dual pane views).



9. Before using your new USB drive with Windows 7 to boot and install Windows 7, ensure your BIOS is setup to boot from a USB drive. This is configured when rebooting by entering your BIOS, or just pressing F9 for a boot option screen in some cases. If all goes well Windows 7 installation should start from your USB drive.

How to use files for ASM disks

One of the issues I've seen is that many people do not have an environment in which they can learn ASM, i.e. they don't have numerous disk to support an ASM setup. For example, if you have a single disk which already has a file system and would like to play around or learn ASM, how do you accomplish this? Turns out this is quite simple in UNIX/Linux using the 'dd' command to create a set of files, which are then associated with loop devices using 'losetup', and associated with raw devices using the 'raw' command.

The steps below are in no way unique so I can not take credit, I also do know the actual originator. I came across the method maybe in 2007 when I was seeking just such a method, and have recently found an article I thought I'd publish in my own words with a bit more detail. I have heard there is a similar method for Windows (makes sense), but as that is not my preferred platform I did not seek to either verify or test out that method.

Note that this setup should not be used in a production environment. It is strictly for testing or training purposes.

1. Create a directory under your file system(s) to store the ASM files.

$> mkdir /u02/asm
$> cd /u02/asm

2. Create files full of zeros using 'dd' command. I've used a block size of 32KB (bs=32K) to improve the build performance, and a count of 983040 to get files of 32GB. The files are named 'asmdiskX' (of=asmdisk1) where X is 1 to 4. I ran the four commands in parallel in the background, which took a really long time (almost 3 hours). No doubt this was due to the fact I was using a shared USB attached 500GB drive (with other things happening) so the operations could have probably gone quicker in serial, with less happening on the drive.

$> dd if=/dev/zero of=asmdisk1 bs=32K count=983040 &
$> dd if=/dev/zero of=asmdisk2 bs=32K count=983040 &
$> dd if=/dev/zero of=asmdisk3 bs=32K count=983040 &
$> dd if=/dev/zero of=asmdisk4 bs=32K count=983040 &

Note that I have run the commands as 'oracle', so I did not need to change ownership. If you have run as root then run the command below on the files to change their ownership to 'oracle':

$> chown oracle:dba /u02/asm/asmdisk*

3. Use 'losetup' to associate loop devices with the regular files (or block devices).

$> losetup /dev/loop1 /u02/asm/asmdisk1
$> losetup /dev/loop2 /u02/asm/asmdisk2
$> losetup /dev/loop3 /u02/asm/asmdisk3
$> losetup /dev/loop4 /u02/asm/asmdisk4

I believe there is a limit on the number of loop devices so you should check before running the commands if you have them available. On my system it was all clear, I've not tested but I believe the command below would create loop devices:

# create a new loop device
$> mknod /dev/loop/300 b 7 300

4. Use the raw command to associate the character block device with a raw device.

$> raw /dev/raw/raw1 /dev/loop1
/dev/raw/raw1: bound to major 7, minor 1

$> raw /dev/raw/raw2 /dev/loop2
/dev/raw/raw2: bound to major 7, minor 2

$> raw /dev/raw/raw3 /dev/loop3
/dev/raw/raw3: bound to major 7, minor 3

$> raw /dev/raw/raw4 /dev/loop4
/dev/raw/raw4: bound to major 7, minor 4

5. Change the ownership of the raw devices to 'oracle' and group to 'dba'.

$> chown oracle.dba /dev/raw/raw[1-4]

6. Setup a startup file to enable the setup to survive a reboot. Place commands from steps 3 to 5 into a script (/etc/init.d/asmsetup) which will run during system startup. An example script and steps are setup is below:

#!/bin/bash
#
# chkconfig: 2345 15 99
# description: Setup files to be used as ASM disks.

# Source function library.
. /etc/init.d/functions

disklocation=/u02/asm

prog=$"ASM file disk setup"

start()
{
echo -n $"Starting $prog: "
/sbin/losetup /dev/loop1 ${disklocation}/asmdisk1
/sbin/losetup /dev/loop2 ${disklocation}/asmdisk2
/sbin/losetup /dev/loop3 ${disklocation}/asmdisk3
/sbin/losetup /dev/loop4 ${disklocation}/asmdisk4
/bin/raw /dev/raw/raw1 /dev/loop1
/bin/raw /dev/raw/raw2 /dev/loop2
/bin/raw /dev/raw/raw3 /dev/loop3
/bin/raw /dev/raw/raw4 /dev/loop4
/bin/chown oracle.dba /dev/raw/raw[1-4]
}

# See how we were called.
case "$1" in
start)
start
;;
*)
echo $"Usage: $0 {start}"
exit 1
esac

$> chkconfig --add asmsetup

This creates the following files:

/etc/rc2.d/S15asmsetup
/etc/rc3.d/S15asmsetup
/etc/rc4.d/S15asmsetup
/etc/rc5.d/S15asmsetup

/etc/rc0.d/K99asmsetup
/etc/rc1.d/K99asmsetup
/etc/rc6.d/K99asmsetup

chkconfig refers the " # chkconfig: 2345 15 99" from asmsetup. This signifies that the service has start run level set to 2, 3, 4 and 5. Stop run level set to 0, 1 and 6 (the stop does nothing in this case). And the start priority should be 15 and stop priority be 99.

ASM now has disks which can be used. I'll write a follow-up on using these disks for an ASM installation. I'll be doing both version 11.1 and 11.2 for ASM but not sure which I'll write about yet (maybe both, maybe one). 11.2 is definately more interesting since I've done 11.1 already so that has the edge right now.

How to Change the DBID and/or DB_NAME of an Oracle Database

How to change the DBID (and DB_NAME)

Step 1. The database should be backed up.

Step 2. Ensure to perform clean shut down of a database and startup in mount stage (but not open).

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 3. Invoke the DBNEWID utility (nid) specifying the new DBNAME (if also changing the DB_NAME) from the command line using a user with SYSDBA privilege:

 

Note: To change the database ID without changing the database name, in Step 3 do not specify the optional database name (DBNAME).

 

 

$ nid TARGET=SYS/XXXX@test DBNAME=test_db

DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.

DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

 

.
. 

Connected to database PROD (DBID=86997811)  

Connected to server version 10.1.0  

Control Files in database:  

/oracle/TEST_DB/data/cf1.f  

/oracle/TEST_DB/data/cf2.f  

The following datafiles are offline clean:  

/oracle/TEST_DB/data/tbs_61.f (23)  

/oracle/TEST_DB/data/tbs_62.f (24)  

/oracle/TEST_DB/data/temp3.f (3) 

These files must be writable by this utility.  

The following datafiles are read-only:  

/oracle/TEST_DB/data/tbs_51.f (15)  

/oracle/TEST_DB/data/tbs_52.f (16)  

/oracle/TEST_DB/data/tbs_53.f (22) 

These files must be writable by this utility.  

Changing database ID from 86997811 to 1250654267 

Changing database name from PROD to TEST_DB  

Control File /oracle/TEST_DB/data/cf1.f - modified  

Control File /oracle/TEST_DB/data/cf2.f - modified  

Datafile /oracle/TEST_DB/data/tbs_01.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_ax1.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_02.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_11.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_12.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/temp1.f - dbid changed, wrote new name  

Control File /oracle/TEST_DB/data/cf1.f - dbid changed, wrote new name  

Control File /oracle/TEST_DB/data/cf2.f - dbid changed, wrote new name  

Instance shut down  Database name changed to TEST_DB. 

Modify parameter file and generate a new password file before restarting. 

Database ID for database TEST_DB changed to 1250654267. 

All previous backups and archived redo logs for this database are unusable. 

Database has been shutdown, open database with RESETLOGS option. 

Successfully changed database name and ID. 

DBNEWID - Completed successfully. 

Note: If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown in the following sample output. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.

 

Step 4. Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET DB_NAME=test_db SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE;

Step 5. Create a new password file

On Unix:

$ orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdtest_db.ora password=xxxxxxx entries=10

Step 6. Open the database in RESETLOGS mode and resume normal use. For example:

SQL> STARTUP MOUNT;

 

SQL> ALTER DATABASE OPEN RESETLOGS;

Note: To change only the DBNAME, use the SETNAME parameter in Step 3 which tells the DBNEWID utility to only alter the database name and not the database ID, it is then not necessary to use the RESETLOGS option when opening the database.

 

 

Step 7. Backup the whole database again.

How to Create Non-Sparse TEMP files

Temp Files

Temporary data files in Oracle are a special type of data file. Oracle will use temporary files to store the intermdiate results of a large sort operation, and hash operations, as well as to store global temporary table data, or result data when there is insufficient memory to hold it all in RAM. Temp files also never have redo generated for them, though they can generate undo. Temporary data files never need to have backups done and doing so is a waste of time since upon recovery they need to be recreated.

Once of the nuances with temp files is that if the OS permits, the temporary files will be created sparse, i.e. they will not actually consume disk storage until they need to. This is mostly the case from my experience on UNIX/Linux systems and can be seen by comparing the output of df anddu or ls for the file system on which the temp files were created.

This results in temp files being created almost instantneously since no storage is allocated, however, since you can create files larger than the space you actually have available, you will quickly run into errors such as 'no more space' when that space is then being requested.

Creating non-sparse TEMP files

There are a few ways to create non-sparse files, I'm just showing the way I've used and know work.

1. Create the OS file:

$> dd if=/dev/zero of=/u02/oradata/ORCL/temp1_01.dbf bs=1024k count=1024

2. Create the TEMP tablespace and/or temp file:

SQL> CREATE TEMPORARY TABLESPACE temp1 

TEMPFILE '/u02/oradata/ORCL/temp1_01.dbf' REUSE;

Using Oracle SPFILEs

Using Server Parameter Files (SPFILEs)

SPFILEs remove the proliferation of parameter files, as well as the ability to maintain such files outside the database using text editors. The default naming convention is

spfile$ORACLE_SID.ora - UNIX/Linux environment
spfile%ORACLE_SID%.ora - Windows environment

I recommended to maintain the default location, i.e. $ORACLE_HOME/dbs, as doing otherwise defeats the simplicity SPFILEs represent. Also, moving the file elsewhere involves you telling Oracle this new location and otherwise keeping your own internal audit of that location, leading to the similar problems caused by using parameter files.

SPFILEs and Oracle RAC

When using Oracle RAC, all instances share the same SPFILE. This single SPFILE contains all the parameters for all instances with instance-specific settings identified by the SID.

*.cluster_database_instance=2
*.db_name='ORCL'
ORCL1.undo_tablespace='UNDO_ORCL1'
ORCL2.undo_tablespace='UNDO_ORCL2'

In the above example, the *.db_name='ORCL' indicates all instances using this SPFILE will be mounting a database named 'ORCL'. The ORCL1.undo_tablespace='UNDO_ORCL1' indicates the instance named ORCL1 will use that specific undo tablespace and so on.

Setting & Unsettting Values in SPFILEs

The command below shows the format of the command used to manipulate settings using an SPFILE.

ALTER SYSTEM SET parameter=value ;

Portions in <> are optional, and the presence of the pipe symbol indicates "one of the list".

The various sections are pretty straight forward so I will no go into most of them here.

• The DEFERRED option which specifies the system change is to take place for subsequent sessions only (not currently established sessions). This is important since by default changes will take effect immediately, but some parameters cannot be changed 'immediately' - they can only be changed for newly established sessions. The error received for such commands is 'ORA-02096: specified initialization parameter is not modifiable with this option'.

• The SID='sid|*' is mostly useful in a clustered environment; SID='*' is the default. This allows you to specify a parameter setting uniquely for any given instance in the cluster. However, if you need to 'unset' a parameter such that it does not show in the SPFILE and is therefore defaulted, you will need to make use of SID='sid|*' component. For example, to 'unset' the SORT_AREA_SIZE parameter:

ALTER SYSTEM RESET sort_area_size SCOPE=spfile SID='*';

The general format to 'unset' parameters is

ALTER SYSTEM RESET parameter SID='sid|*';

SPFILEs Copies

You can create backup copies of your SPFILE as either a one-time parameter file
CREATE pfile FROM spfile;

Using an OS copy command, or if on a UNIX/Linux platform you can extract your settings using:

strings spfile$ORACLE_SID.ora

On Windows you can just open in WordPad and copy and paste the text which will be formatted as clear text into another file. You can also obtain all the non-default parameters in the alert.log file since this is displayed following each instance start and build your pfile from those settings.

Installing and Configuring Enterprise Manager Client Interface (emcli)

Notes:

• Enteprise Manager Client Interface (emcli) needs to be installed on each host from which its usage is required.
• Environment was EM Grid Control 10.2.0.5
  

Installation

1. Obtain the EM CLI Client kit (emclikit.jar). The emclikit.jar file is physically located in the $OMS_HOME/sysman/jlib directory of the 10.2 Grid Control OMS home, it can also be downloaded from http(s)://host:port/em/console/emcli/download
2. Set your JAVA_HOME environment variable and ensure that it is part of your PATH. You must be running Java 1.4.1 or greater.
3. Install the EM CLI Client. You can install the client in any directory either on the same machine as the EM CLI Management Services or on any machine in your network (download the emclikit.jar to that machine). Run the followingcommand:

java -jar emclikit.jar client -install_dir=

Setup
1. Tell emcli how to find em repository with the following command

$> emcli setup -url=http://:4889/em -username=sysman

emcli will ask you for your password.

2. Tell emcli how find your sudo program (all on 1 line)

$> emcli create_privilege_delegation_setting -setting_name=sudo_setting -setting_type=SUDO -settings="SETTINGS:/usr/local/bin/sudo –S –u %RUNAS% %command%"

3. Apply this setting to your specific hosts (all on 1 line)

$> emcli apply_privilege_delegation_setting -setting_name=sudo_setting -target_type=host -target_names="host1;host2;host3;"

Setting Credentials for the Job System to work with Enterprise Manager

Windows systems require that you set the correct credentials for the Jobs system to work properly in Enterprise Manager. By default, the Management Agent service is installed as a LocalSystem user. When submitting jobs, such as stopping or starting the database, the user submitting the job must have the Log on as a batch job privilege enabled.

Perform the following steps to establish that privilege for any
operating system user who needs to submit an Enterprise Manager job.

1. Start the Local Security Policy tool:
   
   • Windows 2000: From the Start menu, select Control Panel, Administrative Tools, then Local Security Policy.
     
   • Windows 2003: From the Start menu, select Administrative Tools, then Local Security Policy.
     
   • Windows XP: From the Start menu, select Control Panel, Administrative Tools, then Local Security Policy.
     
   
   
2. Under the Security Settings list, expand the list to Local Policies.
   
3. Under Local Policies, double-click User Rights Assignment.
   
4. Under Policy, search for the Log on as a batch job policy.
   

   If the Management Agent service is installed as any other user (that is, not LocalSystem), then, in addition to granting the Log on as a batch job privilege, you must grant the "Windows service" user the following three privileges:

   
   
   • Act as part of the operating system
     
   • Adjust memory quotas for a process (This setting is named Increase memory quotas

     on Windows 2000.)

   • Replace a process level token
     
   
   
5. With each policy, perform the following steps:
   
   1. Double-click the policy name.
      
   2. In the Properties dialog box, click Add User or Group.
      
   3. In the Select Users or Groups dialog box, enter the name of the user (for example, jsmith, administrator, and so on.)
      
   4. Click Check Names to check that you have entered the name correctly.
      
   5. Click OK.
      
   
   
6. Click OK to exit the Properties dialog box, then exit Local Security Settings and Administrative Tools.
   
7. Restart your computer.
   

If a user exists locally and at the domain level, Windows gives the
local user precedence. To use the domain user, qualify the user name
with the domain name. For example, to use the user joe in the ACCOUNTS domain specify the user name as ACCOUNTS\joe.

Note: Validated for EM Grid Control 10.2.0.4 - 10.2.0.5

powered by performancing firefox