In Oracle Database 10g and below you could open the physical standby database for read-only activities, but only after stopping the recovery process.
In Oracle 11g, you can query the physical standby database in real time while applying the archived logs. This means standby continue to be in sync with primary but can use the standby for reporting.
Let us see the steps now..
First, cancel the managed standby recovery:
SQL> alter database recover managed standby database cancel;
Database altered.
Then, open the database as read only:
SQL> alter database open read only;
Database altered.
While the standby database is open in read-only mode, you can resume the managed recovery process.
SQL> alter database recover managed standby database disconnect;
Database altered.
Snapshot Standby
In Oracle Database 11g, physical standby database can be temporarily converted into an updateable one called Snapshot Standby Database.
In that mode, you can make changes to database. Once the test is complete, you can rollback the changes made for testing and convert the database into a standby undergoing the normal recovery. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes.
Steps:
Configure the flash recovery area, if it is not already done.
SQL> alter system set db_recovery_file_dest_size = 2G;
System altered.
SQL> alter system set db_recovery_file_dest= '+FRADG';
System altered.
Stop the recovery.
SQL> alter database recover managed standby database cancel;
Database altered.
Convert this standby database to snapshot standby using command
SQL> alter database convert to snapshot standby;
Database altered.
Now recycle the database
SQL> shutdown immediate ...
SQL> startup
ORACLE instance started.
Database is now open for read/write operations
SQL> select open_mode, database_role from v$database;
After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database by following the steps below
SQL> connect / as sysdba Connected. SQL> shutdown immediate
SQL> startup mount
... Database mounted.
SQL> alter database convert to physical standby;
Database altered.
Now shutdown, mount the database and start managed recovery.
SQL> shutdown
ORACLE instance shut down.
SQL> startup mount ORACLE instance started. ... Database mounted.
Start the managed recovery process
SQL> alter database recover managed standby database disconnect;
Now the standby database is back in managed recovery mode. When the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now.
In 10g this can be done by following steps .. DR failover test with flashback
Redo Compression
In Oracle Database 11g you can compress the redo that goes across to the standby server via SQL*Net using a parameter compression set to true. This works only for the logs shipped during the gap resolution. Here is the command you can use to enable compression.
alter system set log_archive_dest_2 = 'service=STDBYDB LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=STDBYDB compression=enable'
Oracle Database 11g New Features in RMAN for DBA interview.
Advice on recovery
To find out failure...
RMAN> list failure;
To get the advice on recovery
RMAN> advise failure;
Recovery Advisor generates a script that can be used to repair the datafile or resolve the issue. The script does all the work.
To verify what the script actually does ...
RMAN> repair failure preview;
Now execute the actual repair by issuing...
RMAN> repair failure;
Proactive Health Checks
In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, can check database blocks for physical corruption.
RMAN> validate database;
Parallel backup of the same datafile.
In 10g each datafile is backed by only one channel. In Oracle Database 11g RMAN, the multiple channels can backup one datafiles parallel by breaking the datafile into chunks known as "sections."
Optimized backup of undo tablespace.
In 10g, when the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed. In Oracle Database 11g, RMAN bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup.
Improved Block Media Recovery Performance
If flashback logs are present, RMAN will use these in preference to backups during block media recovery (BMR), which can significantly improve BMR speed.
Block Change Tracking Support for Standby Databases
Block change tracking is now supported on physical standby databases, which in turn means fast incremental backups are now possible on standby databases.
Faster Backup Compression
RMAN now supports the ZLIB binary compression algorithm as part of the Oracle Advanced Compression option. The ZLIB algorithm is optimized for CPU efficiency, but produces larger zip files than the BZIP2 algorithm available previously, which is optimized for compression.
Archived Log Deletion Policy Enhancements
The archived log deletion policy of Oracle 11g has been extended to give greater flexibility and protection in a Data Guard environment. The Oracle 10g and Oracle 11g syntax is displayed below.
# Oracle 10g Syntax. CONFIGURE ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON STANDBY | NONE}} # Oracle 11g Syntax. ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON [ALL] STANDBY |BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}[ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}]...}
The extended syntax allows for configurations where logs are eligible for deletion only after being applied to, or transferred to, one or more standby database destinations.
COMPRESSION parameter in expdp : *****************************
One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them by using parameter COMPRESSION in the expdp command line. The parameter has three options:
METDATA_ONLY - only the metadata is compressed DATA_ONLY - only the data is compressed; the metadata is left alone. ALL - both the metadata and data are compressed. NONE - this is the default; no compression is performed.
Encryption : ********************************
The dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g.
Data Masking : ******************************
when you import data from production to QA, you may want to make sure sensitive data are altered in such a way that they are not identifiable. Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.
REMAP_TABLE: ********************************* Allows you to rename tables during an import operation.
Example The following is an example of using the REMAP_TABLE parameter to rename the employees table to a new name of emps: impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps
Showing newest posts with label Interview Questions : Wait Events. Show older posts Oracle Wait Events
Before looking into wait events, let us understand various state of user process. Oracle user process is typically in one of the three states:
a. Idle wait. e.g. 'SQL*Net message from client'
b. Running code - Either on CPU or on a run queue. Oracle itself does not know if it is on-CPU or just on a run queue.
c. Waiting
i. for some resource to become available. e.g. enqueue (lock) or a latch ii. for an activity to complete that it has requested. Like an IO read request.
Oracle has a set of 'Wait Events' for activities in 'a' and 'c', and record CPU utilization for 'b'.
This is best illustrated with a simplified example of few seconds in the life of an Oracle shadow process:
State Notes... ~~~~~ ~~~~~~~~ IDLE : Waiting for 'SQL*Net message from client'. Receives a SQL*Net packet requesting 'parse/execute' of a statement
ON CPU : decodes the SQL*Net packet.
WAITING : Waits for 'latch free' to obtain the a 'library cache' latch Gets the latch.
ON CPU : Scans for the SQL statement in the shared pool, finds a match, frees latch , sets up links to the shared cursor etc.. & begins to execute.
WAITING : Waits for 'db file sequential read' as we need a block which is not in the buffer cache. Ie: Waiting for an IO to complete.
ON CPU : Block read has completed so execution can continue. Constructs a SQL*Net packet to send back to the user containing the first row of data.
WAITING : Waits on 'SQL*Net message to client' for an acknowledgement that the SQL*Net packet was reliably delivered.
IDLE : Waits on 'SQL*Net message from client' for the next thing to do.
•This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy". The two main cases where this can occur are:
1.Another session is reading the block into the buffer 2.Another session holds the buffer in an incompatible mode to our request
Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.
Typical solutions are:-
Look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimise the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.
Increase the PCTFREE for the table storage parameter. This will result in less rows per block.
Consider implementing reverse key indexes. (if range scans aren't commonly used against the segment)
In v$session_wait, the P1, P2, and P3 columns identify the file number, block number, and buffer busy reason codes, respectively.
"Read By Other Session" wait event.
When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.
Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.
When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:
SELECT p1 "file#", p2 "block#" FROM v$session_wait WHERE event = 'read by other session';
If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object.
The following query will give the name and type of the object:
SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file AND &block BETWEEN block_id AND block_id + blocks - 1
Log File Sync waits
Log file sync waits occur when sessions wait for redo data to be written to disk. Typically this is caused by slow writes or committing too frequently in the application.
db file sequential read
Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).
db file scattered read
This wait happens when a session is waiting for a multiblock IO to complete. This typically occurs during full table scans or index fast full scans. Oracle reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache.
direct path read
Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).
This style of read request is typically used for:
Sort I/Os when memory Sort areas are exhausted and temporary tablespaces are used to perform the sort Parallel Query slaves.
direct path write
This wait is seen for:
Direct load operations (eg: Create Table as Select (CTAS) may use this) Parallel DML operations Sort IO (when a sort does not fit in memory)
db file parallel write
DBW waits on "db file parallel write" when waiting for a parallel write to files and blocks to complete. The db file parallel write occurs when the process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.
From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.
The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.
We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.
Client Side Connect-Time Load Balance : ****************
The client load balancing feature enables clients to randomize connection requests among the listeners. This is done by client Tnsnames Parameter: LOAD_BALANCE. The (load_balance=yes) instructs SQLNet to progress through the list of listener addresses in the address_list section of the net service name in a random sequence. When set to OFF, instructs SQLNet to try the addresses sequentially until one succeeds.
Client Side Connect-Time failover : ***********************************
This is done by client Tnsnames Parameter: FAILOVER The (failover=on) enables clients to connect to another listener if the initial connection to the first listener fails. Without connect-time failover, Oracle Net attempts a connection with only one listener.
Server Side Listener Connection Load Balancing. ****************************
With server-side load balancing, the listener directs a connection request to the best instance currently providing the service. Init parameter remote_listener should be set. When set, each instance registers with the TNS listeners running on all nodes within the cluster.
There are two types of server-side load balancing:
Load Based — Server side load balancing redirects connections by default depending on node load. This id default.
Session Based — Session based load balancing takes into account the number of sessions connected to each node and then distributes the connections to balance the number of sessions across the different nodes.
From 10g release 2 the service can be setup to use load balancing advisory. This mean connections can be routed using SERVICE TIME and THROUGHPUT. Connection load balancing means the goal of a service can be changed, to reflect the type of connections using the service.
Transparent Application Failover (TAF) is a feature of the Oracle Call Interface (OCI) driver at client side. It enables the application to automatically reconnect to a database, if the database instance to which the connection is made fails. In this case, the active transactions roll back. Tnsnames Parameter: FAILOVER_MODE
e.g (failover_mode=(type=select)(method=basic)) Failover Mode Type can be Either SESSION or SELECT.
Session failover will have just the session to failed over to the next available node. With SELECT, the select query will be resumed. TAF can be configured with just server side service settings by using dbms_service package.
Fast Connection Failover (FCF)
Fast Connection Failover is a feature of Oracle clients that have integrated with FAN HA Events. Oracle JDBC Implicit Connection Cache, Oracle Call Interface (OCI), and Oracle Data Provider for .Net (ODP.Net) include fast connection failover.
With fast connection failover, when a down event is received, cached connections affected by the down event are immediately marked invalid and cleaned up
What are Oracle Clusterware processes for 10g on Unix and Linux:
Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.
Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user
Event manager daemon (evmd) —A background process that publishes events that crs creates.
Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.
What are Oracle database background processes specific to RAC:*****
•LMS—Global Cache Service Process
•LMD—Global Enqueue Service Daemon
•LMON—Global Enqueue Service Monitor
•LCK0—Instance Enqueue Process
To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction.
Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES).
The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.
What are Oracle Clusterware Components: ********************
Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.
Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster
How do you troubleshoot node reboot:
Please check metalink ...
Note 265769.1 Troubleshooting CRS Reboots Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.
How do you backup the OCR
There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\
To display backups : #ocrconfig -showbackup To restore a backup : #ocrconfig -restore
With Oracle RAC 10g Release 2 or later, you can also use the export command: #ocrconfig -export -s online, and use -import option to restore the contents back. With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command: # ocrconfig -manualbackup
How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name
How do I identify the voting disk location
#crsctl query css votedisk
How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform) or #ocrcheck
Is ssh required for normal Oracle RAC operation ?
"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.
What is SCAN?
Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.
Click here for more details from Oracle
What is the purpose of Private Interconnect ?
Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol. RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.
Why do we have a Virtual IP (VIP) in Oracle RAC?
Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs. When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.
What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?
This is most likely due to a fault in interconnect network. Check netstat -s if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.
How many nodes are supported in a RAC Database?
10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.
Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem?
Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.
what is the purpose of the ONS daemon? ****************
The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node. The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners.
This in order to facilitate:
a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes. b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.
This paper describes procedures to dramatically reduce downtime during the process of migrating Oracle databases residing on raw partitions, volumes or conventional file systems to Automatic Storage Management (ASM) by using Oracle Recovery Manager (RMAN) and Oracle Data Guard. This paper is equally relevant to existing Data Guard users who want to migrate to ASM, and users who do not currently use Data Guard, but who seek to minimize downtime during migration to ASM.
There are several alternative approaches to performing ASM migration. Oracle recommends using one of the following methods:
• ASM migration using Oracle Data Guard physical standby: Use this method if your requirement is to minimize downtime during the migration. It is possible to reduce total downtime to just seconds by using the best practices described in this white paper.
• ASM migration using Oracle RMAN: A simpler approach, but one that can result in downtime measured in minutes to hours, depending on the method used for migration. This RMAN procedures for migrating your database to ASM are documented in the Oracle Database Backup and Recovery Advance User’s Guide 10g Release 2, Chapter 16
ORACLE AUTOMATIC STORAGE MANAGEMENT :
Oracle Database 10g Automatic Storage Management (ASM) is an integrated volume manager and file system for Oracle database files. ASM simplifies database storage administration by automating the layout of Oracle database files, such as datafiles, control files, redo log files, and backup files. ASM brings significant key values to Oracle Database platforms at no additional cost. ASM improves manageability by simplifying storage provisioning, storage array migration, and storage consolidation. ASM provides flexible easy-to-manage interfaces including the SQL*Plus, Oracle Enterprise Manager GUIs and a UNIX-like command line interface.
ASM provides sustained best-in-class performance because of its MAA Best Practices - Minimal Downtime Migration to ASM Page 3
Maximum Availability Architecture innovative rebalancing feature that distributes data evenly across all storage resources, providing for an even distribution of I/O and optimal performance. ASM is the preferred file system and volume manager for Oracle database files because ASM:
• Simplifies and automates storage management
• Increases storage utilization, uptime, and agility
• Delivers predictable performance and availability service level agreements
ORACLE DATA GUARD :
Data Guard is a central component of an integrated Oracle Database High Availability (HA) solution set that helps organizations ensure business continuity by minimizing the various kinds of planned and unplanned downtime that can affect the business. Data Guard provides the management, monitoring, and automation software infrastructure to create, maintain, and monitor one or more standby databases, to protect enterprise data from failures, disasters, errors, and data corruptions. Going beyond traditional Disaster Recovery (DR) solutions, you can configure Data Guard to automatically fail over the production database to a standby system if the primary database fails, thus achieving a level of high availability required for mission critical applications. In addition to providing HA/DR, Data Guard standby databases also support production functions for reporting, queries, backups and testing, while in a standby database role. The procedures described in this paper provide an example of how Data Guard can also help reduce planned downtime. The following list summarizes the procedures described in this paper:
1. Create a Data Guard standby database.
2. Migrate the standby database to ASM and test until you are satisfied that the migration has been successful.
3. Execute a planned switchover, transforming the standby database into the new primary database. The switchover can be executed in seconds. This is the only downtime required for the migration.
4. If you are using Data Guard as an HA/DR solution, you can then migrate the new standby (old primary database) into ASM, resulting in all databases in your Data Guard configuration having been migrated to ASM.
ASM MIGRATION STEPS USING ORACLE DATA GUARD :
The steps described in this section assume you create a physical standby database for the sole purpose of minimizing downtime during your migration to ASM.
Maximum Availability Architecture Note: If you have an existing Data Guard configuration with a physical standby database that you can use to perform the migration to ASM, use the procedures described in Chapter 16 of the Oracle Database Backup and Recovery Advance User’s Guide 10g Release 2 to perform the migration. Of course, when following this procedure it is always recommended that you convert standby databases first, then perform a switchover to minimize the downtime required to convert your primary database.
For clarity (and because this paper assumes you will use Data Guard for a one-time migration), the instructions use SQL*Plus commands to create the standby database, enable the Data Guard configuration, and perform a Data Guard switchover. However, if you plan to use Data Guard on an ongoing basis, consider using the Data Guard Broker management interface (DGMGRL) or Enterprise Manager, to greatly simplify creation and management of your Data Guard configuration [2]. These steps have been verified on a configuration running Oracle Database 10g Release 2 (10.2).
Migration to ASM can occur on either the same or a different server or cluster. The steps differ slightly depending on if the target is on a different server or cluster. The differences are highlighted, where necessary. The high-level steps include:
• Prepare the Source database
• Prepare the Data Guard standby database
• Instantiate the Standby database in ASM
• Enable Oracle Data Guard 10g
• Move production to ASM with Data Guard switchover
• Perform post ASM migration steps
The Appendix contains a copy of the database parameter files used to migrate a database called sales for two scenarios where:
To prepare the source database for migration, run the following steps:
1. Using RMAN, create a backup of the database including a copy of the current controlfile that you will use for the standby database.
RMAN> connect target /
RMAN> backup database include current controlfile for standby;
MAA Best Practices - Minimal Downtime Migration to ASM Page 5
Maximum Availability Architecture :
2. Make the backups accessible to the target system. The path to the backup files on the source and standby system must be the same for these procedures to work.
3. Using SQL*Plus, create a copy of the source database parameter file, which you will use as a template for the standby database parameter file.
SQL> create pfile=’/tmp/pfile.ora’ from spfile;
Copy the parameter file you created to the standby system
4. Using the Oracle Network Configuration Assistant (NETCA) utility create an Oracle Net Service name on the source system that connects back to the standby database.
Prepare the Data Guard Standby Database :
The following steps assume that the ASM instance is running and the ASM disk group has already been created
[4]. To prepare the standby database, perform the following steps:
1. Edit the parameter file you copied from the source database to make the following changes.
a. Remove the current reference to the CONTROL_FILES parameter.
b. Edit or Add the DB_CREATE_FILE_DEST parameter to point to the ASM disk group for the data files.
c. Edit or Add the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters to point to the ASM disk group and define the size for the flash recovery area.
d. Optionally, if the online redo log files should be located in a specific ASM disk group other than what was specified by DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST, Edit or Add the DB_CREATE_ONLINE_LOG_DEST_n parameter to point to the ASM disk group for the redo log files.
e. Add the FAL_SERVER parameter that refers to a net service name that points to the source database.
f. Add the FAL_CLIENT parameter that refers to a net service name that points to the standby database.
The FAL_SERVER and FAL_CLIENT parameters should be prefixed with the Oracle SID of the standby database.
Note: If you create the standby database locally on the same resources, then you also need to set the following parameters.
Maximum Availability Architecture :
g. Edit the DB_UNIQUE_NAME parameter to be unique. This parameter is referenced in the file names created in the ASM Disk Group.
h. Edit or Add the LOG_ARCHIVE_CONFIG parameter to reference the two DB_UNIQUE_NAME that will be in the configuration. The LOG_ARCHIVE_CONFIG parameter will be: ‘dg_config=(,)’;
2. Create the Oracle Password file using the orapwd utility. For example: $ orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=sys password
3. Create a net service name on the standby system that connects back to the source database and a net service name on the standby system that refers to the standby database.
Instantiate the Standby Database in ASM :
RMAN provides a single command that instantiates the standby database using the information from the source database. In RMAN, you connect to the source database using the CONNECT TARGET command, and you connect to the standby database using the CONNECT AUXILIARY command. Before you can instantiate the database, you must create the server parameter file for the standby database.
1. Create the server parameter file using the parameter file you edited above:
SQL> create spfile from pfile=’/tmp/pfile.ora’;
2. Start the standby instance and instantiate the standby database:
SQL> startup force nomount
RMAN> connect target sys/@
RMAN> connect auxiliary /
RMAN> duplicate target database for standby;
At the end of the duplicate command, a physical standby database will be created on the standby system.
Note: If the standby database will be an Oracle RAC database or if you want to store the SPFILE in ASM, then you must move the SPFILE into an ASM disk group, as follows: a. Create a pfile from the spfile:
SQL> create pfile='/tmp/pfile.asm' from spfile; b. Shutdown the standby database:
Maximum Availability Architecture :
SQL> shutdown c. Start the standby database using the pfile created in step a: SQL> startup mount pfile='/tmp/pfile.asm'; d. Create the spfile in the ASM Disk Group using the db_unique_name value that you specified earlier: SQL> create spfile='+data/salesasm/spfilesalesasm' from pfile='/tmp/pfile.asm'; e. Shut down the standby database to leverage the changes: SQL> shutdown f. Create an initialization parameter file that references the spfile created above on all nodes in the cluster: $ echo "spfile='+data/salesasm/spfilesalesasm'" > ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora g. Remove the spfile created in step 1: $ rm ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora h. Start all instances of the standby database:
SQL> startup mount
Enable Oracle Data Guard 10g :
To enable Oracle Data Guard 10g, perform the following steps:
1. Configure the LOG_ARCHIVE_DEST_n parameter on the source database to transmit redo data to the standby database:
SQL> alter system set log_archive_dest_n=’service= ARCH valid_for=(online_logfiles,primary_role) db_unique_name=’ comment=’for ASM instantiation’ scope=both;
Note: If you create the standby database on the same server or cluster as the source database, then you must also set the following parameters on the source database. a. Edit or Add the LOG_ARCHIVE_CONFIG parameter to reference the two DB_UNIQUE_NAME parameters that will be in the configuration. The LOG_ARCHIVE_CONFIG parameter will be similar to the following: *.log_archive_config='dg_config=(sales,salesasm)'
Oracle Data Guard has the ability toperform synchronous and asynchronous log shipping as well asreal-time apply. To configure these options, see Chapters 5 and 6 in the Oracle Data Guard Concepts and Administration manual [1].
Maximum Availability Architecture :
2. Start Redo Apply on the standby database to apply the redo received from the source database: SQL> alter database recover managed standby database nodelay disconnect;
You can find additional information about configuring and monitoring Oracle Data Guard in the Oracle Data Guard Concepts and Administration manual and on the Maximum Availability Architecture Oracle Technology Network (OTN) Web site [3]. Use Data Guard Switchover to Move Production to ASM
Downtime occurs while you perform a Data Guard switchover to transition the standby database already using ASM to the primary production role. The switchover process requires a brief outage, typically requiring only seconds to complete.
1. If the primary database is an Oracle RAC database, then you must shut down all but one instance.
SQL> shutdown
Alternatively, you can shut down the instances of an Oracle RAC database using the Server Control (srvctl) utility.
2. On the source database, archive the current redo log:
SQL> alter system archive log current;
3. Verify that the source database is ready to perform an Oracle Data Guard switchover operation:
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
The output should show ‘TO STANDBY” or “SESSIONS ACTIVE.” If any other output displays, then it is not possible to perform a switchover at this time. Contact Oracle Support for diagnosing the problem that is preventing a switchover operation. 4. If the standby database is an Oracle RAC database, then all but one instance must be shut down:
SQL> shutdown
Alternatively, you can shut down the instances of an Oracle RAC database using the Server Control (srvctl) utility.
5. Stop all users accessing the source database.
Maximum Availability Architecture :
6. Issue the following command to initiate the Oracle Data Guard switchover operation. Any connections to the source database will be terminated, and the database transitions to a quiesced state: SQL> alter database commit to switchover to standby with session shutdown;
7. By the time step 6 completes, the last of the redo data from the source database will have been sent to the standby database, and the changes will be applied. Query the V$DATABASE view as shown in the following example, and when the query returns “TO PRIMARY” on the standby database, you can safely proceed.. The time it takes for the status to return “TO PRIMARY” depends on a number of factors including, but not limited to, the amount of redo data that still needs to be applied to the standby database. For example:
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY
8. On the standby database, complete the Oracle Data Guard switchover operation:
SQL> alter database commit to switchover to primary;
9. Open the standby database:
SQL> alter database open;
10. If the standby database is an Oracle RAC database, then start the remaining instances:
SQL> startup
Alternatively, you can shutdown the instances of an Oracle RAC database using the Server Control (srvctl) utility.
11. Instruct clients to connect to the new primary database using ASM.
Perform Post ASM Migration Steps :
This section assumes that Data Guard is used only one-time to perform the ASM migration. Once the standby database has become the primary database, perform the following steps to remove the original source database: 1. Invoke SQL*Plus and issue the following statements to reset the FAL_SERVER and FAL_CLIENT parameters on the standby system:
SQL> alter system reset fal_server sid=’’;
SQL> alter system reset fal_client sid=’’;
Maximum Availability Architecture :
Note: If you create the standby database on the same server or cluster as the source database, then the following parameters also need to be reset on the standby database:
SQL> alter system reset log_archive_config sid='' 2. Shutdown the original source database.
3. Delete the original source database using the Database Configuration Assistant (DBCA).
Example standby database parameter file for different server configuration or cluster migration
Example source database parameter file for different server configuration or cluster migration MAA Best Practices - Minimal Downtime Migration to ASM Page 12 Maximum Availability Architecture
Example source RAC database parameter file for different server configuration or cluster migration
Example standby RAC database parameter file for different server configuration or cluster migration Maximum Availability Architecture sales1.instance_number=1 sales2.instance_number=2 *.job_queue_processes=10 *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=164626432 *.processes=150 *.remote_listener='LISTENERS_SALES' *.remote_login_passwordfile='exclusive' *.sessions=170 *.sga_target=494927872 sales2.thread=2 sales1.thread=1 *.undo_management='AUTO' sales2.undo_tablespace='UNDOTBS2' sales1.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/sales/udump' Database Parameter Files for Same Server or Cluster Migration
Example source database parameter file for same server configuration or cluster migration
Example standby database parameter file for same server configuration or cluster migration MAA Best Practices - Minimal Downtime Migration to ASM Page 14
Example source Oracle RAC database parameter file for same server configuration or cluster migration MAA Best Practices - Minimal Downtime Migration to ASM Page 15
