PATCH AND UPGRADES

UPGRADE 10G TO 11G

----------------------------------------------------------------------------------------------------------- Recently we did database upgrade from 10g to Oracle 11g.I would like share that activity with you. Pre-Requisite: You should have the Oracle database 10g, which you want to migrate. Also here we are upgrading to Oracle Database 11g - Beta 6 (11.1.0.6) Step 1) Installing Oracle 11g Home We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home. Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0 then my 11g Oracle Home is : /u01/app/oracle/oracle/product/11.1.0 Just a parallel 11.1.0 directory can be created and we can install oracle home in this location. Start the installation using the below command ./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst Screen 1 - Select Product Install select "Oracle Database 11g" Screen 2 - Select Installation Method Choose "Advanced Installation" Screen 3 - Specify Inventory directory and creditials Note: We are providing local inventory here inside the corresponding ORACLE_HOME location. Screen 4 - Select Installation Type Choose "Enterprise Edition" Screen 5 - Installation Location Oracle Base as parent directory of ORACLE HOME Screen 6 - Product Specific Pre-requisite Checks It may gives below warning, we can ignore and proceed further Screen 7 - Upgrade an Existsing Database Choose "No" Screen 8 - Select Configuration Option Choose "Install Software Only" Screen 9 - Privileged system groups Based on the group of oracle user, this value has to be set. Screen 10 - Summary Click on "Install" At the end of installation, installer will ask to run root.sh script. Do not press OK button. Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g. Step 2) Pre-Upgrade Utility In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location. [oracle]$ cd $ORACLE_HOME [oracle]$ cd rdbms/admin/ [oracle]$ pwd /u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin [oracle]$ cp utlu111i.sql /tmp The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail. Most of the time issue use to come up with time zone.... Then login to the 10g oracle database and run the above sql you copied. Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07 . ********************************************************************** Database: ********************************************************************** -> name: ORCL -> version: 10.2.0.1.0 -> compatible: 10.2.0.1.0 -> blocksize: 8192 -> platform: Linux IA (32-bit) -> timezone file: V2 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** -> SYSTEM tablespace is adequate for the upgrade. . . . . WARNING: -> Database contains schemas with objects dependent on network packages. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER SYSMAN has dependent objects. WARNING: -> EM Database Control Repository exists in the database. .... Direct downgrade of EM Database Control is not supported. Refer to the .... 11g Upgrade Guide for instructions to save the EM data prior to upgrade. . PL/SQL procedure successfully completed. The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail. Step 3) Executing the recommended steps Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home WARNING: -> Database is using an old timezone file version. .... Patch the 10.2.0.1.0 database to timezone file version 4 .... BEFORE upgrading the database. Re-run utlu111i.sql after .... patching the database to record the new timezone file version. Finding the Version of existing timezone files: SQL> select * from v$timezone_file; FILENAME VERSION ———— ———- timezlrg.dat 2 SQL> SELECT CASE COUNT(DISTINCT(tzname)) WHEN 183 then 1 WHEN 355 then 1 WHEN 347 then 1 WHEN 377 then 2 WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end WHEN 185 then 3 WHEN 386 then 3 WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end ELSE 0 end VERSION FROM v$timezone_names; VERSION ———- 2 If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files. Check the database version SQL> select banner from v$version; BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied. Always try to use the official patch The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the "correct" patch through opatch is always preferable to the manual method described in this note. If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release. For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script &timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well. Please follow the metalink note ID 396387.1 Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it. [oracle]$ unzip p5632264_10202_LINUX.zip [oracle]$ ls etc files README.txt [oracle]$ cd files/oracore/zoneinfo [oracle]$ ls readme.txt timezlrg.dat timezone.dat Backup $ORACLE_HOME/oracore/zoneinfo directory [oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup Copy the .dat files [oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo Bounce the database and check the TIMEZONE version again SQL> select * from v$timezone_file; FILENAME VERSION ———— ———- timezlrg.dat 4 SQL> SELECT CASE COUNT(DISTINCT(tzname)) WHEN 183 then 1 WHEN 355 then 1 WHEN 347 then 1 WHEN 377 then 2 WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end WHEN 185 then 3 WHEN 386 then 3 WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end ELSE 0 end VERSION FROM v$timezone_names; VERSION ———- 4 WARNING: -> Database contains stale optimizer statistics. .... Refer to the 11g Upgrade Guide for instructions to update .... statistics prior to upgrading the database. .... Component Schemas with stale statistics: .... SYS .... SYSMAN Gather Dictionary stats: Connect as sys user and gather statistics SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS'); PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN'); PL/SQL procedure successfully completed. Step 4) Run Pre-Upgrade Utility again After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don't get any critical warnings. Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home. If every thing looks fine, Shut down the database from 10g Oracle Home This time make sure you dont have the critical warnings like the one with TIMEZONE version. Step 5) Starting Upgrade Source the following variables for 11g Oracle Home [oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1 [oracle]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle]$ export ORACLE_SID=orcl [oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin connected to the database sys as sysdba sqlplus "/ as sysdba" -> will be connected to idle instance SQL> startup upgrade ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 611000320 bytes Fixed Size 1301588 bytes Variable Size 201327532 bytes Database Buffers 402653184 bytes Redo Buffers 5718016 bytes Database mounted. Database opened. SQL> SPOOL upgrade.log SQL> @catupgrd.sql Once the upgrades finishes. It will shut down the database automatically. Login again as sysdba and startup in normal mode. Check the dba_registry for the components and its status Step 6) Post-Upgrade Steps Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation. SQL> STARTUP Run utlu111s.sql to display the results of the upgrade: SQL> @?/rdbms/admin/utlu111s.sql . Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40 . Component Status Version HH:MM:SS . Oracle Server . VALID 11.1.0.6.0 00:19:02 JServer JAVA Virtual Machine . VALID 11.1.0.6.0 00:02:55 Oracle Workspace Manager . VALID 11.1.0.6.0 00:00:54 OLAP Analytic Workspace . VALID 11.1.0.6.0 00:00:26 OLAP Catalog . VALID 11.1.0.6.0 00:00:58 Oracle OLAP API . VALID 11.1.0.6.0 00:00:25 Oracle Enterprise Manager . VALID 11.1.0.6.0 00:11:00 Oracle XDK . VALID 11.1.0.6.0 00:00:53 Oracle Text . VALID 11.1.0.6.0 00:00:50 Oracle XML Database . VALID 11.1.0.6.0 00:03:52 Oracle Database Java Packages . VALID 11.1.0.6.0 00:00:21 Oracle Multimedia . VALID 11.1.0.6.0 00:04:25 Spatial . VALID 11.1.0.6.0 00:05:18 Oracle Expression Filter . VALID 11.1.0.6.0 00:00:13 Oracle Rules Manager . VALID 11.1.0.6.0 00:00:12 Gathering Statistics . 00:04:03 Total Upgrade Time: 00:55:57 PL/SQL procedure successfully completed. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode: SQL> @?/rdbms/admin/catuppst.sql Run utlrp.sql to recompile SQL> select count(*) from dba_objects 2 where status = 'INVALID'; COUNT(*) ———- 1576 SQL> @?/rdbms/admin/utlrp.sql SQL> select count(*) from dba_objects 2 where status = 'INVALID'; COUNT(*) ———- 0 This completes the upgrade.

CONVERT PHYSICAL STANBY TO SNAPSHOP

1- Confirm that you have Flashback feature enabled If that is not enabled, perform the following steps: 1- Review and set (if needed) the db_recovery parameters on both database(standby & primary) and then activate flashback On Primary SQL> show parameters db_recovery; NAME TYPE VALUE ---------------------------- ---------- ------------------------- db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> alter system set db_recovery_file_dest_size=50g scope=both; SQL> alter system set db_recovery_file_dest='+ASM_PROD_FRA' scope=both; SQL> alter system set db_flashback_retention_target=60 scope=both; SQL> shutdown immediate On Standby SQL> show parameters db_recovery; NAME TYPE VALUE ---------------------------- ---------- ------------------------- db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> alter system set db_recovery_file_dest_size=50g scope=both; SQL> Alter system set db_recovery_file_dest='+ASM_STDBY_FRA' scope=both; SQL> alter system set db_flashback_retention_target=60 scope=both; SQL> shutdown immediate; SQL> startup mount; SQL> alter database flashback on; SQL> select name,database_role,flashback_on from v$database; On Primary SQL> startup mount SQL> alter database flashback on; SQL> alter database open; SQL> select name,database_role,flashback_on from v$database; 2- Convert the Standby in Snapshot Standby After the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode SQL> shutdown immediate SQL> startup mount SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; After that you are ready to perform any DDL and DML operations on it. SQL> shutdown immediate; SQL> startup; As you can see, this process is very simple, you just type a few commands, re-start your DB and you are ready to test this amazing feature!!!

SHUTDOWN RAC

1. Shutdown Oracle Home process accessing database. 2. Shutdown RAC Database Instances on all nodes. 3. Shutdown All ASM instances from all nodes. 4. Shutdown Node applications running on nodes. 5. Shut down the Oracle Cluster ware or CRS. 1. Shutdown Oracle Home process accessing database: There could be oracle and non-oracle application which access database, So first step is to stop all the applications or DBA should inform application owner and he should stop all applications accessing Database. DBA should also stop Oracle application like Enterprise Manager Grid Control and Database Listener which access database for monitoring and database connections. [oracle@database ~]$ emctl stop dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. https://database.example.com:5500/em/console/aboutApplication Stopping Oracle Enterprise Manager 11g Database Control ... ... Stopped. [grid@node1 bin]$ srvctl stop listener -n node1 [grid@node1 bin]$ srvctl status listener -n node1 Listener LISTENER is enabled on node(s): node1 Listener LISTENER is not running on node(s): node1 2. Shutdown RAC Database Instances on all nodes: Suppose DBA has two node RAC database, So he has to stop all instances from all DB nodes. Here, I am taking anexample of two node RAC. First, I am checking on which server database is running and then stopping and verify for the same. Syntax: srvctl stop database -d {databasename} [oracle@node2 ~]$ srvctl status database -d oradb Instance oradb1 is running on node node1 Instance oradb2 is running on node node2 [oracle@node2 ~]$ srvctl stop database -d oradb [oracle@node2 ~]$ srvctl status database -d oradb Instance oradb1 is not running on node node1 Instance oradb2 is not running on node node2 We just need to execute one command from any one of the server having database and it will stop all database instances on all servers. If you have more than one database configured on Nodes, then Database Administrator has to execute this command for each database. 3. Shutdown All ASM instances from all nodes: Next DBA has to shut down an ASM instance which are used to access database, enter the following command, where node is the name of the node where the ASM instance is running Syntax: srvctl stop asm -n {node} [grid@node2 oracle]# srvctl stop asm -n node1 -f [grid@node2 oracle]# srvctl stop asm -n node2 -f [grid@node2 oracle]# srvctl status asm -n node1 ASM is not running on node1 [grid@node2 oracle]# srvctl status asm -n node2 ASM is not running on node2 Sometimes, Database administrator face some issues in stopping ASM instance, In that case use "-f" option to forcefully shutdown ASM instances. 4.ShutdownNode applications running on nodes: To stop node applicationsrunning on a node, enter the following command, where node is the name of the node where theapplications are running [grid@node2 oracle]# srvctl stop nodeapps -n node1 -f [grid@node2 oracle]# srvctl status nodeapps -n node1 VIP node1-vip is enabled VIP node1-vip is running on node: node1 Network is enabled Network is running on node: node1 GSD is disabled GSD is not running on node: node1 ONS is enabled ONS daemon is running on node: node1 Repeat same command for all nodes one by one. If you face any issue in stopping nodeapplications use "-f" as force option to stop applications. 5. Shut down the Oracle Clusterware or CRS: In this command all CRS related process will be stopped. This is the only command which needs to be executed by "root" user on all database nodes. [root@node1 bin]# crsctl check cluster -all ************************************************************** node1: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************* node2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** [root@node1 bin]#crsctl stop crs CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1' CRS-2673: Attempting to stop 'ora.crsd' on 'node1' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node1' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'node1' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node1' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'node1' CRS-2673: Attempting to stop 'ora.node2.vip' on 'node1' ------------------------------------------------- ------------------------------------------------- ------------------------------------------------- CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'node1' CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1' CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed CRS-4133: Oracle High Availability Services has been stopped. [root@node1 bin]# crsctl check cluster -all CRS-4639: Could not contact Oracle High Availability Services CRS-4000: Command Check failed, or completed with errors. DBA can see Now RAC is completely down. Now you can move ahead with your patching, maintenance activity. I hope this is very helpful ):-

CREATING A PHYSICAL STANDBY DATABASE

10g Data Guard, Physical Standby Creation, step by step primary database name: white on rac2 machine standby database name: black on rac1 machine Creating a Data Guard Physical Standby environment, General Review. Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully met and executed. In this example I did use 2 hosts, that host a RAC database. All RAC preinstall requisites are then in place and no additional configuration was necessary to implement Data Guard Physical Standby manually. The Enviroment 2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 ssh is configured for user oracle on both nodes Oracle Home is on identical path on both nodes Implementation notes: Once you have your primary database up and running these are the steps to follow: 1. Enable Forced Logging 2. Create a Password File 3. Configure a Standby Redo Log 4. Enable Archiving 5. Set Primary Database Initialization Parameters Having followed these steps to implement the Physical Standby you need to follow these steps: 1. Create a Control File for the Standby Database 2. Backup the Primary Database and transfer a copy to the Standby node. 3. Prepare an Initialization Parameter File for the Standby Database 4. Configure the listener and tnsnames to support the database on both nodes 5. Set Up the Environment to Support the Standby Database on the standby node. 6. Start the Physical Standby Database 7. Verify the Physical Standby Database Is Performing Properly Step by Step Implementation of a Physical Standby Environment Primary Database Steps Primary Database General View SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 0 Current log sequence 1 SQL> select name from v$database; NAME --------- WHITE SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/white/system01.dbf /u01/app/oracle/oradata/white/undotbs01.dbf /u01/app/oracle/oradata/white/sysaux01.dbf /u01/app/oracle/oradata/white/users01.dbf SQL> show parameters unique NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string white SQL> Enable Forced Logging In order to implement Standby Database we enable 'Forced Logging'. This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged into the redo logs. SQL> ALTER DATABASE FORCE LOGGING; Database altered. Create a Password File A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby. [oracle@rac2 ~]$ cd $ORACLE_HOME/dbs [oracle@rac2 dbs]$ orapwd file=orapwwhite password=oracle force=y SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP ------------------------------ ----- ----- SYS TRUE TRUE Configure a Standby Redo Log A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs. In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL path and file name. If you are not using OMF's you then must pass the full qualified name. SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- -------------------------------------------------- 3 ONLINE /u01/app/oracle/oradata/white/redo03.log 2 ONLINE /u01/app/oracle/oradata/white/redo02.log 1 ONLINE /u01/app/oracle/oradata/white/redo01.log SQL> select bytes from v$log; BYTES ---------- 52428800 52428800 52428800 SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 2 '/u01/app/oracle/oradata/white/stby04.log' size 50m; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 2 '/u01/app/oracle/oradata/white/stby05.log' size 50m; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 2 '/u01/app/oracle/oradata/white/stby06.log' size 50m; Database altered. SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE; GROUP# TYPE MEMBER ---------- ------- -------------------------------------------------- 3 ONLINE /u01/app/oracle/oradata/white/redo03.log 2 ONLINE /u01/app/oracle/oradata/white/redo02.log 1 ONLINE /u01/app/oracle/oradata/white/redo01.log 4 STANDBY /u01/app/oracle/oradata/white/stby04.log 5 STANDBY /u01/app/oracle/oradata/white/stby05.log 6 STANDBY /u01/app/oracle/oradata/white/stby06.log 6 rows selected. Set Primary Database Initialization Parameters Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we convert it to an spfile. Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so that it can hold both roles, as Primary or Standby. SQL> CREATE PFILE FROM SPFILE; File created. (or) SQL> CREATE PFILE='/tmp/initwhite.ora' from spfile; File created. Edit the pfile to add the standby parameters, here shown highlighted: white.__db_cache_size=184549376 white.__java_pool_size=4194304 white.__large_pool_size=4194304 white.__shared_pool_size=88080384 white.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/white/adump' *.background_dump_dest='/u01/app/oracle/admin/white/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/white/control01.ctl','/u01/app/oracle/oradata/white/control02.ctl','/u01/app/oracle/oradata/white/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/white/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='white' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=whiteXDB)' *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=94371840 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=285212672 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/white/udump' db_unique_name='white' LOG_ARCHIVE_CONFIG='DG_CONFIG=(white,black)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/white/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white' LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 #Standby role parameters------------------------------------------ fal_server=black fal_client=white standby_file_management=auto db_file_name_convert='/u01/app/oracle/oradata/black/','/u01/app/oracle/oradata/white/' log_file_name_convert='/u01/app/oracle/oradata/black/','/u01/app/oracle/oradata/white/' Once the new parameter file is ready we create from it the spfile: SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initwhite.ora ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated ORA-07286: sksagdi: cannot obtain device information. Linux Error: 2: No such file or directory note: create a archive log destination(location) folder as per in parameter file and then startup the database. SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initwhite.ora ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 96470608 bytes Database Buffers 184549376 bytes Redo Buffers 2973696 bytes SQL> create spfile from pfile; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. Enable Archiving On 10g you can enable archive log mode by mounting the database and executing the archivelog command: SQL> startup mount ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 96470608 bytes Database Buffers 184549376 bytes Redo Buffers 2973696 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/white/arch/ Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 SQL> Standby Database Steps Here, i am going to create standby database using backup of the primary database datafiles,redologs, controlfile by rman. compare with user managed backup, rman is comfortable and flexible method. Create an RMAN backup which we will use later to create the standby: [oracle@rac2 ~]$ . oraenv ORACLE_SID = [oracle] ? white [oracle@rac2 ~]$ rman target=/ Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 20 18:41:51 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: WHITE (DBID=3603807872) RMAN> backup full database format '/u01/app/oracle/backup/%d_%U.bckp' plus archivelog format '/u01/app/oracle/backup/%d_%U.bckp'; Next, create a standby controlfile backup via RMAN: RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/%U'; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY; RMAN> BACKUP ARCHIVELOG ALL; In this simple example, I am backing up the primary database to disk; therefore, I must make the backupsets available to the standby host if I want to use them as the basis for my duplicate operation: [oracle@rac2 ~]$ cd /u01/app/oracle/backup [oracle@rac2 backup]$ ls -lart total 636080 drwxrwxr-x 9 oracle oinstall 4096 Jan 20 18:42 .. -rw-r----- 1 oracle oinstall 50418176 Jan 20 18:43 WHITE_01l3v1uv_1_1.bckp -rw-r----- 1 oracle oinstall 531472384 Jan 20 18:54 WHITE_02l3v203_1_1.bckp -rw-r----- 1 oracle oinstall 7143424 Jan 20 18:54 WHITE_03l3v2jf_1_1.bckp -rw-r----- 1 oracle oinstall 1346560 Jan 20 18:54 WHITE_04l3v2jv_1_1.bckp -rw-r----- 1 oracle oinstall 7110656 Jan 20 19:19 05l3v41r_1_1 drwxr-xr-x 2 oracle oinstall 4096 Jan 20 19:20 . -rw-r----- 1 oracle oinstall 53174272 Jan 20 19:21 06l3v448_1_1 [oracle@rac2 backup]$ scp * oracle@rac1:/u01/app/oracle/backup/ 05l3v41r_1_1 100% 6944KB 6.8MB/s 00:00 06l3v448_1_1 100% 51MB 16.9MB/s 00:03 WHITE_01l3v1uv_1_1.bckp 100% 48MB 2.7MB/s 00:18 WHITE_02l3v203_1_1.bckp 100% 507MB 1.5MB/s 05:47 WHITE_03l3v2jf_1_1.bckp 100% 6976KB 996.6KB/s 00:07 WHITE_04l3v2jv_1_1.bckp 100% 1315KB 1.3MB/s 00:01 NOTE: The primary and standby database location for backup folder must be same. for eg: /u01/app/oracle/backup folder On the standby node create the required directories to get the datafiles mkdir -p /u01/app/oracle/oradata/black mkdir -p /u01/app/oracle/oradata/black/arch mkdir -p /u01/app/oracle/admin/black mkdir -p /u01/app/oracle/admin/black/adump mkdir -p /u01/app/oracle/admin/black/bdump mkdir -p /u01/app/oracle/admin/black/udump mkdir -p /u01/app/oracle/flash_recovery_area/WHITE mkdir -p /u01/app/oracle/flash_recovery_area/WHITE/onlinelog Prepare an Initialization Parameter File for the Standby Database Copy from the primary pfile to the standby destination [oracle@rac2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/ [oracle@rac2 dbs]$ scp initwhite.ora oracle@rac1:/tmp/initblack.ora initwhite.ora 100% 1704 1.7KB/s 00:00 Copy and edit the primary init.ora to set it up for the standby role,as here shown highlighted: black.__db_cache_size=188743680 black.__java_pool_size=4194304 black.__large_pool_size=4194304 black.__shared_pool_size=83886080 black.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/black/adump' *.background_dump_dest='/u01/app/oracle/admin/black/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/black/control01.ctl','/u01/app/oracle/oradata/black/control02.ctl','/u01/app/oracle/oradata/black/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/black/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_file_name_convert='/u01/app/oracle/oradata/white/','/u01/app/oracle/oradata/black/' *.db_name='white' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.db_unique_name='black' *.dispatchers='(PROTOCOL=TCP) (SERVICE=blackXDB)' *.fal_client='black' *.fal_server='white' *.job_queue_processes=10 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(white,black)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/black/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=black' *.LOG_ARCHIVE_DEST_2='SERVICE=white LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=white' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' *.LOG_ARCHIVE_MAX_PROCESSES=30 *.log_file_name_convert='/u01/app/oracle/oradata/white/','/u01/app/oracle/oradata/black/' *.open_cursors=300 *.pga_aggregate_target=94371840 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=285212672 *.standby_file_management='auto' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/black/udump' Configure the listener and tnsnames to support the database on both nodes Configure listener.ora on both servers to hold entries for both databases #on RAC2 Machine LISTENER_VMRACTEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)) ) ) SID_LIST_LISTENER_VMRACTEST = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = white) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = white) ) ) #on rac1 machine LISTENER_VMRACTEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)) ) ) SID_LIST_LISTENER_VMRACTEST = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = black) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = black) ) ) Configure tnsnames.ora on both servers to hold entries for both databases #on rac2 machine LISTENER_VMRACTEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)) ) ) WHITE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = white) ) ) BLACK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = black) ) ) #on rac1 machine LISTENER_VMRACTEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)) ) ) WHITE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = white) ) ) BLACK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = black) ) ) Start the listener and check tnsping on both nodes to both services #on machine rac1 [oracle@rac1 tmp]$ lsnrctl stop LISTENER_VMRACTEST LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 20-JAN-2010 23:59:41 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1521))) The command completed successfully [oracle@rac1 tmp]$ lsnrctl start LISTENER_VMRACTEST LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:00:00 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_vmractest.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.localdomain)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_VMRACTEST Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 21-JAN-2010 00:00:00 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_vmractest.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.localdomain)(PORT=1521))) Services Summary... Service "black" has 1 instance(s). Instance "black", status UNKNOWN, has 1 handler(s) for this service... Service "black_DGMGRL" has 1 instance(s). Instance "black", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 tmp]$ tnsping black TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:00:21 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = black))) OK (10 msec) [oracle@rac1 tmp]$ tnsping white TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:00:29 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = white))) OK (10 msec) #on rac2 machine [oracle@rac2 dbs]$ lsnrctl stop LISTENER_VMRACTEST LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:22:48 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1 521))) The command completed successfully [oracle@rac2 dbs]$ lsnrctl start LISTENER_VMRACTEST LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:23:08 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/liste ner.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener _vmractest.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.localdomain)(PORT=1 521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1 521))) STATUS of the LISTENER ------------------------ Alias LISTENER_VMRACTEST Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 21-JAN-2010 00:23:08 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/list ener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listen er_vmractest.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.localdomain)(PORT=1521))) Services Summary... Service "white" has 1 instance(s). Instance "white", status UNKNOWN, has 1 handler(s) for this service... Service "white_DGMGRL" has 1 instance(s). Instance "white", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 dbs]$ tnsping white TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:23 :14 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.loc aldomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = whi te))) OK (0 msec) [oracle@rac2 dbs]$ tnsping black TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2010 00:23 :18 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.loc aldomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bla ck))) OK (10 msec) Set Up the Environment to Support the Standby Database on the standby node. Create a passwordfile for the standby: [oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwblack password=oracle note: sys password must be identical for both primary and standby database Append an entry to oratab: [oracle@rac1 ~]$ echo "black:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab Startup nomount the Standby database Nomount the standby instance in preparation for the duplicate operation: Startup nomount the Standby database and generate an spfile [oracle@rac1 ~]$ . oraenv ORACLE_SID = [whiteowl] ? black [oracle@rac1 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 21 00:38:03 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/tmp/initblack.ora' ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes SQL> create spfile from pfile='/tmp/initblack.ora'; File created. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes Create the standby database using rman: [oracle@rac1 ~]$ . oraenv ORACLE_SID = [oracle] ? black [oracle@rac1 ~]$ rman target=sys/oracle@white auxiliary=/ Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 21 00:43:11 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: WHITE (DBID=3603807872) connected to auxiliary database: WHITE (not mounted) RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; Start the redo apply: SQL> alter database recover managed standby database disconnect from session; Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied. On the Primary: SQL> alter system switch logfile; SQL> alter system archive log current; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/white/arch/ Oldest online log sequence 8 Next log sequence to archive 10 Current log sequence 10 On the Standby: SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/black/arch/ Oldest online log sequence 8 Next log sequence to archive 0 Current log sequence 10 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG 2 ORDER BY SEQUENCE#; Stop the managed recovery process on the standby: SQL> alter database recover managed standby database cancel;

STARTUP RAC DATABASE

In Starting Real Application Clusters Database, We will also follow a set of sequence. The order will be just opposite to Shutting down the Real Application Clusters Database. 1. Start Oracle Clusterware or CRS. 2. Start Node applications running on nodes. 3. Start All ASM instances from all nodes. 4. Start RAC Database Instances on all nodes. 5. Start Oracle Home process accessing database. 1. Start Oracle Clusterware or CRS: CRS starts automatically when you start or restart Server, but Here DBA has manually shutdown the CRS So, he has to start it manually. This is the only command which needs to be executed by "root" user. Database Administrator should execute this command on all nodes. [root@node1 bin]# crsctl start crs CRS-4123: Oracle High Availability Services has been started [root@node2 bin]# crsctl check cluster -all ************************************************************** node1: CRS-4535: Cannot communicate with Cluster Ready Services CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** node2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** Here, DBA can see "CRS-4639: Could not contact Oracle High Availability Services" or "CRS-4535: Cannot communicate with Cluster Ready Services" messages. Wait 5 minutes and then again check with "crsctl check cluster -all" command. This time Database administrator will get "CRS-4537: Cluster Ready Services is online". If still same issue DBA can start ora.crsd process to resolve this issue. Below is the command [root@node1 bin]# crsctl start res ora.crsd -init [root@node1 bin]# crsctl check cluster -all ************************************************************** node1: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** node2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** Now, Cluster is up and running. DBA can also use "ps -ef | grepd.bin" to check cluster status. 2. Start Node applications running on nodes: To start node applications running on a node, enter the following command, where node is the name of the node where the applications are running [grid@node1 bin]$ srvctl start nodeapps -n node1 [grid@node1 bin]$ srvctl status nodeapps -n node1 VIP node1-vip is enabled VIP node1-vip is running on node: node1 Network is enabled Network is running on node: node1 GSD is disabled GSD is not running on node: node1 ONS is enabled ONS daemon is running on node: node1 DBA has to execute this command for each node to start Real Application Clusters Cluster database. 3. Start All ASM instances from all nodes: Next DBA has to start all ASM instances which are used to access database, enter the following command, where node is the name of the node where the ASM instance has to start. [grid@node1 bin]$ srvctl start asm -n node1 [grid@node1 bin]$ srvctl status asm -n node1 ASM is running on node1 DBA has to start ASM instance on all database nodes. 4. Start RAC Database Instances on all nodes: Now, We will start database instances on database nodes to access data. [grid@node1 bin]$ srvctl start database -d oradb [grid@node1 bin]$ srvctl status database -d oradb Instance oradb1 is running on node node1 Instance oradb2 is running on node node2 Now database is up and running on both DB nodes. 5. Start Oracle Home process accessing database: let's start listener and OEM to access database and allow users to connect to the database. [grid@node1 bin]$ srvctl start listener -n node1 [grid@node1 bin]$ srvctl status listener -n node1 Listener LISTENER is enabled on node(s): node1 Listener LISTENER is running on node(s): node1 [oracle@database ~]$ emctl start dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. https://database.example.com:5500/em/console/aboutApplication Starting Oracle Enterprise Manager 11g Database Control .......................... . started. ------------------------------------------------------------------ Logs are generated in directory /etc/oracle/oracle/database.example.com_orcl/sysman/log

patch overview

It all started in January 2005 with Critical Patch Updates (CPU). Then Patch Set Updates (PSU) were added as cumulative patches that included priority fixes as well as security fixes. As of the October 2012 Critical Patch Update, Oracle has changed the terminology to better differentiate between patch types. This terminology will be used for the Oracle Database, Enterprise Manager, Fusion Middleware, and WebLogic. Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter. Think of the CPU as the overarching quarterly release and not as a single patch. Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes. The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2). Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version. Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name. For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version. Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes. STEPS TO APPLY A PATCH Patching Oracle 12.1.0.2 with the April 2018 Bundle Patch For Oracle Database 12.1.0.2 I downloaded the Database Proactive Bundle Patch April 2018: Patch 27486326 This patch bundle contains also Clusterware and Client patches - but the database patch is actually 27338029. According to the REAME.html I execute the conflict check - but in my case using the 12.2.0.1.12 OPatch previously installed: STEPS TO APPLY A PATCH 1.) Download Patch 27486326(p27486326_121020_Linux-x86-64.zip) from oracle.com *** Done **** 2.) mkdir /u02/patch 3.) cp p27486326_121020_Linux-x86-64.zip to /u02/patch 4.) unzip p27486326_121020_Linux-x86-64.zip 5.) lsnrctl stop 6.) shutdown immediate 7.) Do these checks /u01/app/oracle/product/12.1.0.2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u02/patch/p27486326_121020_Linux-x86-64/27486326/27338029 /u01/app/oracle/product/12.1.0.2/OPatch/opatch prereq CheckSystemSpace --phBaseDir /u02/patch/p27486326_121020_Linux-x86-64/27486326/27338029 8.) Apply the patch $ cd /u02/patch/p27486326_121020_Linux-x86-64/27486326/27338029 $ /u01/app/oracle/product/12.2.0.1/OPatch/opatch apply 9.) startup the database and listener 10.) Update the database catalog /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose

How to move OCR and Voting disk to new storage device

Moving OCR ========== You must be logged in as the root user, because root owns the OCR files. Also an ocrmirror must be in place before trying to replace the OCR device. Make sure there is a recent backup of the OCR file before making any changes: ocrconfig -showbackup If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate an export of the online OCR file: In 10.2 # ocrconfig -export -s online In 11g # ocrconfig -manualbackup The new OCR disk must be owned by root, must be in the oinstall group, and must have permissions set to 640. Provide at least 100 MB disk space for the OCR. On one node as root run: # ocrconfig -replace ocr # ocrconfig -replace ocrmirror Now run ocrcheck to verify if the OCR is pointing to the new file Moving Voting Disk ================== Note: crsctl votedisk commands must be run as root Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes before making any modification to the voting disk. Determine the current voting disk location using: crsctl query css votedisk Take a backup of all voting disk: dd if=voting_disk_name of=backup_file_name To move a Voting Disk, provide the full path including file name: crsctl delete css votedisk -force crsctl add css votedisk -force After modifying the voting disk, start the Oracle Clusterware stack on all nodes # crsctl start crs Verify the voting disk location using crsctl query css votedisk

STEPS TO PATCH 11G

Please follow the below steps to learn how to apply critical patch update in Oracle database. Always make sure you have appropriate Opatch version in place to apply CPU/PSU patches. Latest can be downloaded from Metalik patch number #6880880. Connect as sysdba and run the script as below. [oracle@oem12c OPatch]$ ./opatch lsinv Hope it would help someone in understanding how to apply CPU patches in oracle database. I have used Oracle database 11.2.0.1.0 and CPU2011 patch to write this paper and have restricted/removed some outputs(messages/standard outputs) to make the paper not lengthy. Solution: Pre-requisites: Download Oracle CPU2011 patch - p12419278_112010_LINUX.zip from metalink with patch number - 12419278 and unzip it somewhere at your database server. In my case I have inflated inside /u01/app directory. [oracle@oem12c app]$ pwd /u01/app [oracle@oem12c app]$ ls -ltr total 53192 drwxrwxr-x 31 oracle oinstall 4096 Jul 8 2011 12419278 -- unzipped CPU patch bundle -rwxr-xr-x 1 oragrid oinstall 4633877 Oct 15 15:59 p12419278_112010_LINUX.zip -- downloaded CPU patch zip file List of any previous interim one off patches if already applied any: [oracle@oem12c OPatch]$ ./opatch lsinv Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m Invoking OPatch 11.1.0.6.6 Oracle Interim Patch Installer version 11.1.0.6.6 Copyright (c) 2009, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.6 OUI version : 11.2.0.1.0 OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_12-38-45PM.log Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-26_12-38-45PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.1.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. Note : - We can see no any interim patches applied to this ORACLE_HOME. Here we go and Apply CPU2011 patch now. ================================ Just navigate inside the unzipped patch directory and find the list of patches which will be applied. [oracle@oem12c app]$ cd 12419278 [oracle@oem12c 12419278]$ pwd /u01/app/12419278 Below are the list of interim patches inside this CPU patch bundle which will be applied to this ORACLE_HOME. [oracle@oem12c 12419278]$ pwd /u01/app/12419278 [oracle@oem12c 12419278]$ ls -ltr total 168 drwxr-xr-x 4 oracle oinstall 4096 Mar 15 2010 9454038 drwxr-xr-x 4 oracle oinstall 4096 Mar 24 2010 9454037 drwxr-xr-x 4 oracle oinstall 4096 May 31 2010 9676419 drwxr-xr-x 4 oracle oinstall 4096 May 31 2010 9676420 drwxr-xr-x 4 oracle oinstall 4096 Sep 14 2010 9971778 drwxr-xr-x 4 oracle oinstall 4096 Sep 14 2010 9971779 drwxr-xr-x 4 oracle oinstall 4096 Sep 14 2010 9971780 drwxr-xr-x 4 oracle oinstall 4096 Nov 23 2010 10323077 drwxr-xr-x 4 oracle oinstall 4096 Nov 23 2010 10323079 drwxr-xr-x 4 oracle oinstall 4096 Nov 23 2010 10323081 drwxr-xr-x 4 oracle oinstall 4096 Nov 23 2010 10323082 drwxr-xr-x 4 oracle oinstall 4096 Feb 23 2011 11794164 drwxr-xr-x 4 oracle oinstall 4096 Feb 23 2011 11794167 drwxr-xr-x 4 oracle oinstall 4096 Mar 25 2011 11794163 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534742 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534743 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534745 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534746 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534747 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534748 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534749 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534750 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534752 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534753 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534754 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534755 drwxr-xr-x 4 oracle oinstall 4096 May 18 2011 12534756 drwxr-xr-x 5 oracle oinstall 4096 May 19 2011 12419278 drwxr-xr-x 4 oracle oinstall 4096 Jul 8 2011 12534751 -rw-r--r-- 1 oracle oinstall 21 Jul 8 2011 README.txt -rwxr-xr-x 1 oracle oinstall 2872 Jul 8 2011 patchmd.xml -rw-rw-r-- 1 oracle oinstall 42530 Jul 18 2011 README.html Note : - you can see there are many interim patches since 2010, yes Oracle critical patches are cumulative which includes all previous CPU patches released for that database version so far this is why it is recommended to apply latest CPU/PSU patches to an Oracle database. Now let's invoke opatch from ORACLE_HOME where we are going to apply these N number of CPU patches included in CPU2011 CPU patch bundle. [oracle@oem12c 12419278]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch napply Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m Invoking OPatch 11.1.0.6.6 Oracle Interim Patch Installer version 11.1.0.6.6 Copyright (c) 2009, Oracle Corporation. All rights reserved. UTIL session Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.6 OUI version : 11.2.0.1.0 OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_12-56-58PM.log Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt Invoking utility "napply" Checking conflict among patches... Checking if Oracle Home has components required by patches... Checking conflicts against Oracle Home... OPatch continues with these patches: 10323077 10323079 10323081 10323082 11794163 11794164 11794167 12419278 12534742 12534743 12534745 12534746 12534747 12534748 12534749 12534750 12534751 12534752 12534753 12534754 12534755 12534756 9454037 9454038 9676419 9676420 9971778 9971779 9971780 Do you want to proceed? [y|n] -- here press y to agree on applying all patches inside this patch bundle. y User Responded with: Y Running prerequisite checks... Patch 12534751: Optional component(s) missing : [ oracle.client, 11.2.0.1.0 ] OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1') Is the local system ready for patching? [y|n] -- Here press y if your all oracle services are down which are running from the ORACLE_HOME in order to proceed ahead for patching activity. y User Responded with: Y Backing up files affected by the patch 'NApply' for restore. This might take a while... Execution of 'sh /u01/app/12419278/12419278/custom/scripts/pre -apply 12419278 ': Return Code = 0 Applying patch 10323077... ApplySession applying interim patch '10323077' to OH '/u01/app/oracle/product/11.2.0/dbhome_1' Backing up files affected by the patch '10323077' for rollback. This might take a while... Patching component oracle.rdbms.dv.oc4j, 11.2.0.1.0... Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/BasePageHandler.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/admin/AdminPageHandler.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/security/LoginPageHandler.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/util/ViewUtil.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_home/_about.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_delete.class" Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_errorPage.class" . . . . . . . . . . . Applying patch 9971779... ApplySession applying interim patch '9971779' to OH '/u01/app/oracle/product/11.2.0/dbhome_1' Backing up files affected by the patch '9971779' for rollback. This might take a while... Patching component oracle.javavm.server, 11.2.0.1.0... Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/jvm_exp.sql" Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/initjvm.sql" Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/jvmursc.sql" ApplySession adding interim patch '9971779' to inventory Verifying the update... Inventory check OK: Patch ID 9971779 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 9971779 are present in Oracle Home. Applying patch 9971780... ApplySession applying interim patch '9971780' to OH '/u01/app/oracle/product/11.2.0/dbhome_1' Backing up files affected by the patch '9971780' for rollback. This might take a while... Patching component oracle.rdbms.dbscripts, 11.2.0.1.0... Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmscdcu.sql" Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcu.plb" Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcp.plb" Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcs.plb" Patching component oracle.rdbms, 11.2.0.1.0... Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a" with "lib/libserver11.a/kkzl.o" ApplySession adding interim patch '9971780' to inventory Verifying the update... Inventory check OK: Patch ID 9971780 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 9971780 are present in Oracle Home. Running make for target ioracle Running make for target client_sharedlib Running make for target client_sharedlib Running make for target itnslsnr The local system has been patched and can be restarted. UtilSession: N-Apply done. OPatch succeeded. Post OPatch steps: ============= As in the previous step, all patches have been applied to the ORACLE_HOME so we can start Oracle services now and run the following catcpu.sql script from the ORACLE_HOME where we applied the patch. [oracle@oem12c CPUJul2011]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/cpu/CPUJul2011 SQL>@/u01/app/oracle/product/11.2.0/dbhome_1/cpu/CPUJul2011/catcpu.sql After running above CPU bundle script, run utlrp.sql script to compile database objects gone in INVALID state during patching activity. SQL>$ORACLE_HOME/rdbms/admin/utlrp.sql Let's verify the number of patches applied to this ORACLE_HOME =================================================== Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m Invoking OPatch 11.1.0.6.6 Oracle Interim Patch Installer version 11.1.0.6.6 Copyright (c) 2009, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.6 OUI version : 11.2.0.1.0 OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_16-18-51PM.log Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-26_16-18-51PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.1.0 There are 1 products installed in this Oracle Home. Interim patches (29) : Patch 9971780 : applied on Mon Oct 26 13:15:17 IST 2015 Unique Patch ID: 13710306 Created on 14 Sep 2010, 06:04:59 hrs PST8PDT Bugs fixed: 9971780 Patch 9971779 : applied on Mon Oct 26 13:15:10 IST 2015 Unique Patch ID: 13710306 Created on 14 Sep 2010, 06:04:14 hrs PST8PDT Bugs fixed: 9971779, 9454036 Patch 9971778 : applied on Mon Oct 26 13:15:07 IST 2015 Unique Patch ID: 13710306 Created on 14 Sep 2010, 06:02:16 hrs PST8PDT Bugs fixed: 9971778 Patch 9676420 : applied on Mon Oct 26 13:15:05 IST 2015 Unique Patch ID: 13710306 Created on 31 May 2010, 02:36:09 hrs PST8PDT Bugs fixed: 9676420 Patch 9676419 : applied on Mon Oct 26 13:15:03 IST 2015 Unique Patch ID: 13710306 Created on 31 May 2010, 02:07:11 hrs PST8PDT Bugs fixed: 9676419 Patch 9454038 : applied on Mon Oct 26 13:15:00 IST 2015 Unique Patch ID: 13710306 Created on 15 Mar 2010, 08:56:17 hrs PST8PDT Bugs fixed: 9454038 Patch 9454037 : applied on Mon Oct 26 13:14:58 IST 2015 Unique Patch ID: 13710306 Created on 24 Mar 2010, 06:44:08 hrs PST8PDT Bugs fixed: 9454037 Patch 12534756 : applied on Mon Oct 26 13:14:55 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:30 hrs PST8PDT Bugs fixed: 12534756, 11794165 Patch 12534755 : applied on Mon Oct 26 13:14:51 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:28 hrs PST8PDT Bugs fixed: 12534755, 8702535, 8991997 Patch 12534754 : applied on Mon Oct 26 13:14:35 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:27 hrs PST8PDT Bugs fixed: 12534754 Patch 12534753 : applied on Mon Oct 26 13:14:29 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:25 hrs PST8PDT Bugs fixed: 12534753 Patch 12534752 : applied on Mon Oct 26 13:13:58 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:24 hrs PST8PDT Bugs fixed: 12534752 Patch 12534751 : applied on Mon Oct 26 13:13:55 IST 2015 Unique Patch ID: 13710306 Created on 8 Jul 2011, 02:03:42 hrs PST8PDT Bugs fixed: 12534751 Patch 12534750 : applied on Mon Oct 26 13:13:53 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:21 hrs PST8PDT Bugs fixed: 12534750 Patch 12534749 : applied on Mon Oct 26 13:13:49 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:20 hrs PST8PDT Bugs fixed: 12534749 Patch 12534748 : applied on Mon Oct 26 13:13:36 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:18 hrs PST8PDT Bugs fixed: 12534748 Patch 12534747 : applied on Mon Oct 26 13:12:01 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:16 hrs PST8PDT Bugs fixed: 12534747 Patch 12534746 : applied on Mon Oct 26 13:07:56 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:13 hrs PST8PDT Bugs fixed: 12534746 Patch 12534745 : applied on Mon Oct 26 13:07:46 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:11 hrs PST8PDT Bugs fixed: 12534745 Patch 12534743 : applied on Mon Oct 26 13:07:32 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:08 hrs PST8PDT Bugs fixed: 12534743 Patch 12534742 : applied on Mon Oct 26 13:04:44 IST 2015 Unique Patch ID: 13710306 Created on 18 May 2011, 06:13:07 hrs PST8PDT Bugs fixed: 12534742 Patch 12419278 : applied on Mon Oct 26 13:04:18 IST 2015 Unique Patch ID: 13710306 Created on 19 May 2011, 06:17:30 hrs PST8PDT Bugs fixed: 9655013, 12419278, 9952260, 9369797, 11724991, 10249532 Patch 11794167 : applied on Mon Oct 26 13:04:14 IST 2015 Unique Patch ID: 13710306 Created on 23 Feb 2011, 04:04:04 hrs PST8PDT Bugs fixed: 11794167 Patch 11794164 : applied on Mon Oct 26 13:04:05 IST 2015 Unique Patch ID: 13710306 Created on 23 Feb 2011, 00:52:19 hrs PST8PDT Bugs fixed: 11794164 Patch 11794163 : applied on Mon Oct 26 13:04:02 IST 2015 Unique Patch ID: 13710306 Created on 25 Mar 2011, 02:54:11 hrs PST8PDT Bugs fixed: 10323080, 11794163 Patch 10323082 : applied on Mon Oct 26 13:03:43 IST 2015 Unique Patch ID: 13710306 Created on 15 Dec 2010, 05:59:50 hrs PST8PDT Bugs fixed: 10323082 Patch 10323081 : applied on Mon Oct 26 13:03:22 IST 2015 Unique Patch ID: 13710306 Created on 15 Dec 2010, 05:59:55 hrs PST8PDT Bugs fixed: 10323081 Patch 10323079 : applied on Mon Oct 26 13:03:18 IST 2015 Unique Patch ID: 13710306 Created on 15 Dec 2010, 06:00:22 hrs PST8PDT Bugs fixed: 10323079 Patch 10323077 : applied on Mon Oct 26 13:03:05 IST 2015 Unique Patch ID: 13710306 Created on 15 Dec 2010, 06:00:48 hrs PST8PDT Bugs fixed: 10323077 -------------------------------------------------------------------------------- OPatch succeeded. Now we are done with CPU patching activity.

DBA ARCHITECTURE QUESTIONS

Tell me about yourself? Your role as a DBA? Your Day to Day activities? What is difference between oracle SID and Oracle service name? Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID. What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation? Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process. What are bind variables? With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on. What is the difference between data block/extent/segment? A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object. What is the difference between PGA and UGA? When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA). What is SGA? Define structure of shared pool component of SGA? The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer. Shared pool portion contain three major area: Library cache (parse SQL statement, cursor information and execution plan), data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,data buffer cache for parallel execution message and control structure. What is the difference between SMON and PMON processes? SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default. PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default. What is a system change number (SCN)? SCN is a value that is incremented whenever a dirty read occurs. SCN is incremented whenever a deadlock occurs. SCN is a value that keeps track of explicit locks. SCN is a value that is incremented whenever database changes are made. What is the main purpose of 'CHECKPOINT' in oracle database? How do you automatically force the oracle to perform a checkpoint? A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery. The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database. LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks. What happens when we fire SQL statement in Oracle? First it will check the syntax and semantics in library cache, after that it will create execution plan. If already data is in buffer cache it will directly return to the client. If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client. What is the use of large pool, which case you need to set the large pool? You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool. What does database do during the mounting process? While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only. What are logfile states? "CURRENT" state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current. If a redo group containing redo's of a dirty buffer that redo group is said to be 'ACTIVE' state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media). And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten. One more state 'UNUSED' initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state. What is log switch? The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch. ALTER SYSTEM SWITCH LOGFILE; How to check Oracle database version? SQL> Select * from v$version; Explain Oracle Architecture? Oracle Instance: a means to access an Oracle database,always opens one and only one database and consists of memory structures and background process. Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database. Oracle database: a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log) Instance memory Structures: System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. SGA Memory structures: Includes Shared Pool, Database Buffer Cache, Redo Log Buffer among others. Shared Pool : Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache. Library Cache: Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements. Data Dictionary Cache : Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access. Database Buffer Cache: Stores copies of data blocks that have been retrieved from the datafiles. Everything done here. Redo Log Buffer : Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes. User process: Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server. Server process: Connects to the Oracle Instance and is Started when a user establishes a session. fulfills calls generated and returns results. Each server process has its own nonshared PGA when the process is started. Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information. In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. Program Global Area (PGA): Memory area used by a single Oracle server process. Allocated when the server process is started, deallocated when the process is terminated and used by only one process. Used to process SQL statements and to hold logon and other session information. Background processes: Started when an Oracle Instance is started. Background Processes Maintains and enforces relationships between physical and memory structures There are two types of database processes: 1. Mandatory background processes 2. Optional background processes Mandatory background processes: - DBWn, PMON, CKPT, LGWR, SMON Optional background processes: - ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn DBWn writes when: • Checkpoint occurs • Dirty buffers reach threshold • There are no free buffers • Timeout occurs • RAC ping request is made • Tablespace OFFLINE • Tablespace READ ONLY • Table DROP or TRUNCATE • Tablespace BEGIN BACKUP Log Writer (LGWR) writes: • At commit • When 1/3rd full • When there is 1 MB of redo • Every 3 seconds • Before DBWn writes System Monitor (SMON) Responsibilities: • Instance recovery - Rolls forward changes in redo logs - Opens database for user access - Rolls back uncommitted transactions • Coalesces free space • Deallocates temporary segments. Process Monitor (PMON) Cleans up after failed processes by: • Rolling back the transaction • Releasing locks • Releasing other resources • Restarting dead dispatchers Checkpoint (CKPT) Responsible for: • Signaling DBWn at checkpoints • Updating datafile headers with checkpoint information • Updating control files with checkpoint information Archiver (ARCn) • Optional background process • Automatically archives online redo logs when ARCHIVELOG mode is set • Preserves the record of all changes made to the database Why do you run orainstRoot and ROOT.SH once you finalize the Installation? orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba. Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin. orainstRoot.sh [root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory to 770. Changing groupname of /u01/app/oraInventory to dba. The execution of the script is complete root.sh [root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh Running Oracle 11g root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. Finished product-specific root actions. For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server. It creates the additional directories and sets appropriate ownership and permissions on files for root user. Oracle Database 11g New Feature for DBAs? 1) Automatic Diagnostic Repository [ADR] 2) Database Replay 3) Automatic Memory Tuning 4) Case sensitive password 5) Virtual columns and indexes 6) Interval Partition and System Partition 7) The Result Cache 8) ADDM RAC Enhancements 9) SQL Plan Management and SQL Plan Baselines 10) SQL Access Advisor & Partition Advisor 11) SQL Query Repair Advisor 12) SQL Performance Analyzer (SPA) New 13) DBMS_STATS Enhancements 14) The Result Cache 15) Total Recall (Flashback Data Archive) Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently What is the Difference Between Local Inventory and Global Inventory? What is oraInventory ? oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory. There are basically two kind of inventories, One is Local Inventory (also called as Oracle Home Inventory) and other is Global Inventory (also called as Central Inventory). What is Global Inventory ? Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories) You will see entry like HOME NAME="ORA10g_HOME" LOC="/u01/oracle/10.2.0/db" TYPE="O" IDX="1?/ What is Local Inventory ? Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only. What is Oracle Home Inventory? Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location: $ORACLE_HOME/inventory It contains the following files and folders: · Components File · Home Properties File · Other Folders Can I have multiple Global Inventory on a machine ? Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well. What to do if my Global Inventory is corrupted ? No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option -attachHome ./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc ORACLE_HOME="Oracle_Home_Location" ORACLE_HOME_NAME="Oracle_Home_Name" CLUSTER_NODES="{}" What is RESULT Cache? 11G Backgroung Processes? The following process are added in 11g as new background processes. 1 dbrm DB resource manager 2 dia0 Diagnosability process 3 fbda Flashback data archiver process 4 vktm Virtual Timekeeper 5 w000 Space Management Co-ordination process 6 smc0 Space Manager process NOTE : The above six are mandatory processes. But 11g has 56 new processes added which can be queried using If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ? Background processes are started automatically when the instance is started. Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated. If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted. Any issues related to backgroud processes should be monitored and analyzed from the trace files generated and the alert log. What is SGA_TARGET and SGA_MAX_SIZE ? SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don't use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow. SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE. SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE. SGA_MAX_SIZE sets the maximum value for sga_target. SGA_TAGET is 10G feature used to change the sga size dynamically .it specifies the total amount of SGA memory available to an instance. this feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size are affected. SGA_MAX_SIZE & SGA_TARGET SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic. The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value. I.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can't resize the SGA_TARGET value to more than 4GB. It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET Do not dynamically set or unset the SGA_TARGET parameter. This should be set only at startup. SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing. SGA_TARGET provides the following: § Single parameter for total SGA size § Automatically sizes SGA components § Memory is transferred to where most needed § Uses workload information § Uses internal advisory predictions § STATISTICS_LEVEL must be set to TYPICAL § SGA_TARGET is dynamic § Can be increased till SGA_MAX_SIZE § Can be reduced till some component reaches minimum size § Change in value of SGA_TARGET affects only automatically sized components If I keep SGA_TARGET =0 then what will happen ? Disable automatic SGA tuning by setting sga_target=0 Disable ASMM by setting SGA_TARGET=0 SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing. Default value 0 (SGA auto tuning is disabled) What happens when you run ALTER DATABASE OPEN RESETLOGS ? The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN. The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files. In what scenarios open resetlogs required ? An ALTER DATABASE OPEN RESETLOGS statement is required, 1.after incomplete recovery (Point in Time Recovery) or 2.recovery with a backup control file. 3. recovery with a control file recreated with the reset logs option Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation.. Difference between RESETLOGS and NORESETLOGS ? http://oracleappstechnology.blogspot.in/2008/05/difference-between-resetlogs-and.html After recover database operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS NORESETLOGS: The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started. RESETLOGS: CAUTION: Never use RESETLOGS unless necessary. Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!! Before using the RESETLOGS option take an offline backup of the database. What is SCN (System Change Number) ? The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs. Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file. What is Database Incarnation ? Database incarnation is effectively a new "version" of the database that happens when you reset the online redo logs using "alter database open resetlogs;". Database incarnation falls into following category Current, Parent, Ancestor and Sibling i) Current Incarnation : The database incarnation in which the database is currently generating redo. ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation. iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation. iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other. How to view Database Incarnation history of Database ? Using SQL> select * from v$database_incarnation; Using RMAN>LIST INCARNATION; However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation. •For example my current database INCARNATION is 3 and now I have used FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use, RMAN> RESET DATABASE TO INCARNATION 2; RMAN> RECOVER DATABASE TO SCN 3000; ORACLE - BACKUP AND RECOVERY How would you decide your backup strategy and timing for backup? In fact backup strategy is purely depends upon your organization business need. If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup. If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be taken in-frequently or weekly or monthly. In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection. What is difference between Restoring and Recovery of database? Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state. What is the difference between complete and incomplete recovery? An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup. What is the benefit of running the DB in archivelog mode over no archivelog mode? When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode. If an oracle database is crashed? How would you recover that transaction which is not in backup? If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup. What is the difference between HOTBACKUP and RMAN backup? For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode. Can we use Same target database as Catalog database? No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state. Incremental backup levels: Level 0 - full backup that can be used for subsequent incrementals RMAN> backup incremental level 0 database; Differential Level 1-only the blocks that have changed since the last backup (whether it is level 0 or level 1) RMAN> backup incremental level 1 differential database; Cumulative Level 1 - all changes since the last level 0 incremental backup RMAN> backup incremental level 1 cumulative database; A full backup cannot be used for a cumulative level 1 backup. A cumulative level 1 backup must be done on top of an incremental level 0 backup. Why RMAN incremental backup fails even though full backup exists? If you have taken the RMAN full backup using the command 'Backup database', where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the 'full backup without level 0' can not be considered as a parent backup from which you can take level 1 backup. Can we perform RMAN level 1 backup without level 0? If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version). If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup. If the compatibility is greater than 10.0.0, RMAN copies all block changes since the file was created, and stores the results as level 1 backup. How to put Manual/User managed backup in RMAN? In case of recovery catalog, you can put by using catalog command: RMAN> CATALOG START WITH '/oracle/backup.ctl'; How to check RMAN version in oracle? If you want to check RMAN catalog version then use the below query from SQL*plus SQL> Select * from rcver; What happens actually in case of instance Recovery? While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started. Instance recovery occurs in 2 steps: Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery. Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

CONVERT SNAPSHO DG TO STANDBY DG

The Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started. Note: A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database. Connect to the Stanbdy DB, shut it down and mount it: SQL> shutdown immediate; SQL> startup mount; Convert the DB. SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Shutdown and mount it again: SQL> shutdown immediate; SQL> startup mount; Finally, enable recovery mode: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Steps to recover the primary database's datafile using a copy of a standby database's datafile.

This procedure will work for all file systems including raw or ASM. Through this example we will be using datafile 12. 1) On standby database, copy datafile from ASM to a file system: RMAN> backup as copy datafile 12 format '/tmp/df12.dbf'; 2) transfer the datafile copy from the standby to the primary host using scp. On primary database 3) Place the datafile to recover offline. SQL> alter database datafile 12 offline; 4) catalog this datafile copy: RMAN> catalog datafilecopy '/tmp/df12.dbf'; 5) Confirm that datafile exists: RMAN> list copy of datafile 12; 6) Restore the datafile: RMAN> restore datafile 12; 7) Recover the datafile: RMAN> recover datafile 12; 8) Place the datafile online: SQL> alter database datafile 12 online; Steps to recover the standby database's datafile using a copy of a primary database's datafile. 1) Backup the primary database's datafile. RMAN> backup as copy datafile 12 format '/tmp/df12.dbf'; 2) transfer the file to the standby site using an operating system utility such as scp. 3) catalog the datafile copy on the standby site. RMAN> catalog datafilecopy '/tmp/df12.dbf'; 4) stop redo apply on the physical standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 5) on the standby site restore the datafile copy. RMAN> restore datafile 12; 6) restart redo apply on the physical standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;