KADDACHI Hafedh Databases Blog

jeudi 9 août 2012

Manual Upgrade of a 10gr2 Database To a 11gr2 Database

Introduction
The goal of this article is to give an example of an manual upgrade from 10gr2 database to 11gr2 database.
In my example, the 10gr2 database is BDTEST.

A. Check list to do Source Database

1 Clean up duplicate objects in the SYS and SYSTEM schemas
Some objects created under SYS schema during the installation of the DB, can be created also under SYSTEM schema with manaul launch scripts (for example for replication).
Exception for :
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY

1-1 To find duplicate objects, run as SYS:
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';

1-2 to drop duplicate objects, run as SYSTEM:
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool duplicate_objects.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and object_name not in ('AQ$_SCHEDULES', 'AQ$_SCHEDULES_PRIMARY', 'DBMS_REPCAT_AUTH', 'DBMS_REPCAT_AUTH' )
and owner = 'SYSTEM';
spool off
exit

2. Purge Recylce bin
purge the recycle bin beforethe upgrade.
SQL> purge DBA_RECYCLEBIN;

3. Cluster
If CLUSTER_DATABASE is set to TRUE, change it to FALSE before upgrading the database.

4. Pre-Upgrade Tool
To prepare your 10gr2 database for upgrade, you must first download the scirpt utility utlu112i_sql. Refer to My Oracle Support Document 884522.1: How to Download and Run Oracle's Database Pre-Upgrade Utility for details on downloading and running the Pre-Upgrade Utility. This SQL script checks the following:
1. Database configuration: Determines if the logfiles and datafiles are sized adequately for the upgrade
2. Initialization parameters: Reports which initialization parameters need changing, replacing or removing before the upgrade
3. Components: Which installed components will be upgraded
4. Miscellaneous Warnings: Any other situations requiring attention before the upgrade
5. Required tablespace: Ensure that the SYSAUX tablespace is created in the current database BEFORE the upgrade is carried out.
6. Timezone file version: Reports which file version is used and when/how to upgrade the timezone version.

Failure to run the pre-upgrade tool (utlu112i.sql) will result in the following error while running the catupgrd.sql script :

SQL> SELECT 
TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 
WHERE tz_version != (SELECT version from v$timezone_file);
SELECT 
TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 
1:
ORA-01722: invalid number

From source server (10gr2) as sys:

SQL> spool /u03/upgrade/utlu112i_1.log
SQL> @/u03/upgrade/utlu112i_1.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2012 11:25:35
Script Version: 11.2.0.1.0 Build: 004
**********************************************************************
Database:
**********************************************************************
--> name: BDTEST
--> version: 10.2.0.5.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1331 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 34 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 721 MB
--> TBS_ADMIN_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER BEMIS has 33 INVALID objects.
.... USER PUBLIC has 1 INVALID objects.
.... USER ADMINSTRM has 2 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> Sync standby database prior to upgrade.
WARNING: --> Your recycle bin contains 2359 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off

Now, on the 10gr2 database BDTEST, perform the recomendations from utlu112i_1.log.

5. Compile invalid objects :

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

6. Gather stats for tables that lacks statistics :

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

B. Upgrade

Do a cold backup pf BDTEST (10gr2) and move all the files to the 11gr2 server.
[oracle@**** ~]$ export ORACLE_SID=BDTEST
on the 11gr2 serveur :

[oracle@**** ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 9 10:31:25 2012

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

Connected to an idle instance.
SQL> startup UPGRADE;
SQL> spool catupgrd.log
SQL> @?/rdbms/admin/catupgrd.sql
......

SQL>

SQL>@?/rdbms/admin/utlrp.sql

SQL>shutdown immediate;

SQL>startup

Execute the script utlu112i.sql that provides a summury of the upgrade :
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2012 14:27:06

**********************************************************************

Database:

**********************************************************************
--> name: BDTEST

--> version: 11.2.0.1.0

--> compatible: 11.2.0.0.0

--> blocksize: 8192

--> timezone file: V4

Database already upgraded; to rerun upgrade use rdbms/admin/catupgrd.sql.

PL/SQL procedure successfully completed.

SQL>

Main important changes
According to the oracle note Oracle 11gR2 Upgrade Companion [ID 785351.1], the main changes
in behavior between 10g and 11g are :

New Background Processes (Oracle Database 10g to 11g Change)

The following are the new database background process that you could be
running depending upon the features being used.

DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.
DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.
EMNC (event monitor coordinator) is the background server process used for database event management and notifications.
FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.
FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.
GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment
GEN0 (general task execution process) performs required tasks including SQL and DML
GMON maintains disk membership in ASM disk groups.
MARK marks ASM allocation units as stale following a missed write to an offline disk.
SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.
VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority
VKRM (virtual scheduler for resource manager process) manages the CPU scheduling for all managed Oracle processes. The process schedules managed processes in accordance with an active resource plan.

Initialization Parameters (Oracle Database 10g to Oracle Database 11g Change)

1. OPTIMIZER_USE_INVISIBLE_INDEXES
OPTIMIZER_USE_INVISIBLE_INDEXES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of invisible indexes. The default
value for OPTIMIZER_USE_INVISIBLE_INDEXES is FALSE.

2. OPTIMIZER_USE_PENDING_STATISTICS
OPTIMIZER_USE_PENDING_STATISTICS was introduced in Oracle Database 11g
Release 1. This parameter specifies whether or not the optimizer uses pending
statistics when compiling SQL statements. The default value for
OPTIMIZER_USE_PENDING_STATISTICS is FALSE.

3. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES was introduced in Oracle Database
11g Release 1. This parameter enables or disables the automatic recognition of
repeatable SQL statements, as well as the generation of SQL plan baselines for such
statements. The default value for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is
FALSE.

4. OPTIMIZER_USE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of SQL plan baselines stored in
SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for
the SQL statement being compiled. If one is found in SQL Management Base, then the
optimizer will cost each of the baseline plans and pick one with the lowest cost. The
default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE.

Automatic Optimizer Statistics Collection

In Oracle Database 11g Automatic optimizer statistics collection runs as part of the automated
maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined
maintenance windows. The AutoTask statistics collection replaces the Oracle Database 10g
GATHER_STATS_JOB. Automatic optimizer statistics collection is enabled by default. If for some

reason automatic optimizer statistics collection needs to be disabled, you can disable it using the

DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
To re-enable automatic optimizer statistics collection, you can enable it using the ENABLE
procedure in the DBMS_AUTO_TASK_ADMIN package
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

for complete information, please refer to the [ID 785351.1] note.

mardi 7 août 2012

How to Execute Process Flow from SQL*Plus

1. Introduction :
Process flow allows us to execute mappings, tranformations ...
Using Sql*Plus, you can execute the process flows, and you can find belw the scripts to use for 9i, 10g and 11g.

2. Scripts
2.1 9i
First create the Run_Owb_Job function as :

CREATE OR REPLACE FUNCTION RUN_OWB_OWNER_9204."RUN_OWB_JOB" ( p_repos_owner in varchar2 default null
, p_location_name in varchar2 default null
, p_task_type in varchar2 default null
, p_task_name in varchar2 default null
, p_system_params in varchar2 default '","'
, p_custom_params in varchar2 default '","'
, p_oem_friendly in number default 0
) return number
is
l_oem_style boolean := case (p_oem_friendly)
when 0 then false
else true
end ;
l_audit_execution_id number; -- Audit Execution Id
l_audit_result number := wb_rt_api_exec.RESULT_FAILURE; -- Result Code
l_audit_result_disp varchar2(64) := 'FAILURE'; -- Result Display Code
l_task_type_name varchar2(64); -- Task Type Name
l_task_type varchar2(64); -- Task Type
l_task_name varchar2(64); -- Task Name
l_location_name varchar2(64); -- Location Name
l_return number ;
procedure override_input_parameter
(
p_audit_execution_id in number,
p_parameter_name in varchar2,
p_value in varchar2,
p_parameter_kind in number
)
is
l_parameter_kind varchar2(64);
begin
if p_parameter_kind = wb_rt_api_exec.PARAMETER_KIND_SYSTEM
then
l_parameter_kind := 'SYSTEM';
else
l_parameter_kind := 'CUSTOM';
end if;
dbms_output.put_line('| ' || p_parameter_name || '%' || l_parameter_kind || '=' || '''' || p_value || '''');
wb_rt_api_exec.override_input_parameter
(
p_audit_execution_id,
p_parameter_name,
p_value,
p_parameter_kind
);
end;
procedure override_input_parameters
(
p_audit_execution_id in number,
p_parameters varchar2,
p_parameter_kind in number
)
is
l_anchor_offset number := 1;
l_start_offset number := 1;
l_equals_offset number;
l_comma_offset number;
l_value_offset number;
l_esc_offset number;
l_esc_count number;
l_esc_char varchar2(4);
l_parameter_name varchar2(4000);
l_parameter_value varchar2(4000);
function strip_escape
(
p_escapedString varchar2
)
return varchar2
is
l_strippedString varchar2(4000);
l_a_char varchar2(4);
l_b_char varchar2(4);
l_strip_offset number := 1;
begin
loop
exit when p_escapedString is null or l_strip_offset > length(p_escapedString);
l_a_char := SUBSTR(p_escapedString, l_strip_offset, 1);
if l_strip_offset = length(p_escapedString)
then
l_strippedString := l_strippedString || l_a_char;
exit;
else
if l_a_char = '\'
then
l_b_char := SUBSTR(p_escapedString, l_strip_offset + 1, 1);
if l_b_char = '\' or l_b_char = ','
then
l_strippedString := l_strippedString || l_b_char;
l_strip_offset := l_strip_offset + 1;
end if;
else
l_strippedString := l_strippedString || l_a_char;
end if;
end if;
l_strip_offset := l_strip_offset + 1;
end loop;
return l_strippedString;
end;
begin
loop
l_equals_offset := INSTR(p_parameters, '=', l_start_offset);
exit when l_equals_offset = 0;
l_start_offset := l_equals_offset + 1;
loop
l_comma_offset := INSTR(p_parameters, ',', l_start_offset);
if l_comma_offset = 0
then
l_comma_offset := length(p_parameters) + 1;
exit;
else
l_esc_count := 0;
l_esc_offset := l_comma_offset - 1;
loop
l_esc_char := SUBSTR(p_parameters, l_esc_offset, 1);
exit when l_esc_char != '\';
l_esc_count := l_esc_count + 1;
l_esc_offset := l_esc_offset - 1;
end loop;

if MOD(l_esc_count, 2) != 0
then
l_start_offset := l_comma_offset + 1;
else
exit;
end if;
end if;
end loop;
l_parameter_name := LTRIM(RTRIM(SUBSTR(p_parameters, l_anchor_offset, l_equals_offset - l_anchor_offset)));
l_parameter_value := strip_escape(SUBSTR(p_parameters, l_equals_offset + 1, l_comma_offset - (l_equals_offset + 1)));
-- Override Input Parameter
override_input_parameter(p_audit_execution_id, l_parameter_name, l_parameter_value, p_parameter_kind);
exit when l_comma_offset >= length(p_parameters)-1;
l_start_offset := l_comma_offset + 1;
l_anchor_offset := l_start_offset;
end loop;
end;
procedure override_custom_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_CUSTOM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
procedure override_system_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_SYSTEM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
begin
execute immediate ('alter session set current_schema = ' || p_repos_owner) ;
--
-- Initialize Return Code
--
l_return := wb_rt_api_exec.RESULT_FAILURE;
--
-- Import Parameters
--
dbms_output.put_line('Stage 1: Decoding Parameters');
l_task_type_name := p_task_type ;
if UPPER(l_task_type_name) = 'PLSQL'
then
l_task_type := 'PLSQL';
elsif UPPER(l_task_type_name) = 'SQL_LOADER'
then
l_task_type := 'SQLLoader';
elsif UPPER(l_task_type_name) = 'PROCESS'
then
l_task_type := 'ProcessFlow';
else
l_task_type := l_task_type_name;
end if;
l_task_name := p_task_name ;
l_location_name := p_location_name ;
dbms_output.put_line('| location_name=' || l_location_name);
dbms_output.put_line('| task_type=' || l_task_type);
dbms_output.put_line('| task_name=' || l_task_name);
--
-- Decode Parameters
--
begin
--
-- Prepare Execution
--
dbms_output.put_line('Stage 2: Opening Task ');
dbms_output.put_line('Stage 2:' || l_task_type);
dbms_output.put_line('Stage 2:' || l_task_name);
dbms_output.put_line('Stage 2:' || l_location_name);
l_audit_execution_id := wb_rt_api_exec.open(l_task_type, l_task_name, l_location_name);
dbms_output.put_line('| l_audit_execution_id=' || to_char(l_audit_execution_id));
commit;
--
-- Override Parameters
--
dbms_output.put_line('Stage 3: Overriding Parameters');
override_system_input_params(l_audit_execution_id, p_system_params);
override_custom_input_params(l_audit_execution_id, p_custom_params);
--
-- Execute
--
dbms_output.put_line('Stage 4: Executing Task');
l_audit_result := wb_rt_api_exec.execute(l_audit_execution_id);
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_audit_result_disp := 'SUCCESS';
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_audit_result_disp := 'WARNING';
elsif l_audit_result = wb_rt_api_exec.RESULT_FAILURE
then
l_audit_result_disp := 'FAILURE';
else
l_audit_result_disp := 'UNKNOWN';
end if;
dbms_output.put_line('| l_audit_result=' || to_char(l_audit_result) || ' (' || l_audit_result_disp || ')');
-- Finish Execution
dbms_output.put_line('Stage 5: Closing Task');
wb_rt_api_exec.close(l_audit_execution_id);
commit;
dbms_output.put_line('Stage 6: Processing Result');
if l_oem_style
then
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_return := 0;
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_return := 0;
else
l_return := l_audit_result;
end if;
else
l_return := l_audit_result;
end if;
dbms_output.put_line('| exit=' || to_char(l_return));
exception
when no_data_found
then
raise_application_error(-20001, 'Task not found - Please check the Task Type, Name and Location are correct.');
end;
return l_return ;
end;
/

Second, create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION "FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('RUN_OWB_OWNER_9204','MAIN_DEPLOY','PROCESS','WF_MAIN');
RETURN 0;
END FUN_LAUNCH_OWB;
/

2.2 10g

First create the Run_Owb_Job function as :

CREATE OR REPLACE function REP_OWB_OWNER.run_owb_job
( p_repos_owner in varchar2 default null
, p_location_name in varchar2 default null
, p_task_type in varchar2 default null
, p_task_name in varchar2 default null
, p_system_params in varchar2 default '","'
, p_custom_params in varchar2 default '","'
, p_oem_friendly in number default 0
) return number
is
l_oem_style boolean := case (p_oem_friendly)
when 0 then false
else true
end ;
l_audit_execution_id number; -- Audit Execution Id
l_audit_result number := wb_rt_api_exec.RESULT_FAILURE; -- Result Code
l_audit_result_disp varchar2(64) := 'FAILURE'; -- Result Display Code
l_task_type_name varchar2(64); -- Task Type Name
l_task_type varchar2(64); -- Task Type
l_task_name varchar2(64); -- Task Name
l_location_name varchar2(64); -- Location Name
l_return number ;
procedure override_input_parameter
(
p_audit_execution_id in number,
p_parameter_name in varchar2,
p_value in varchar2,
p_parameter_kind in number
)
is
l_parameter_kind varchar2(64);
begin
if p_parameter_kind = wb_rt_api_exec.PARAMETER_KIND_SYSTEM
then
l_parameter_kind := 'SYSTEM';
else
l_parameter_kind := 'CUSTOM';
end if;
dbms_output.put_line('| ' || p_parameter_name || '%' || l_parameter_kind || '=' || '''' || p_value || '''');
wb_rt_api_exec.override_input_parameter
(
p_audit_execution_id,
p_parameter_name,
p_value,
p_parameter_kind
);
end;
procedure override_input_parameters
(
p_audit_execution_id in number,
p_parameters varchar2,
p_parameter_kind in number
)
is
l_anchor_offset number := 1;
l_start_offset number := 1;
l_equals_offset number;
l_comma_offset number;
l_value_offset number;
l_esc_offset number;
l_esc_count number;
l_esc_char varchar2(4);
l_parameter_name varchar2(4000);
l_parameter_value varchar2(4000);
function strip_escape
(
p_escapedString varchar2
)
return varchar2
is
l_strippedString varchar2(4000);
l_a_char varchar2(4);
l_b_char varchar2(4);
l_strip_offset number := 1;
begin
loop
exit when p_escapedString is null or l_strip_offset > length(p_escapedString);
l_a_char := SUBSTR(p_escapedString, l_strip_offset, 1);
if l_strip_offset = length(p_escapedString)
then
l_strippedString := l_strippedString || l_a_char;
exit;
else
if l_a_char = '\'
then
l_b_char := SUBSTR(p_escapedString, l_strip_offset + 1, 1);
if l_b_char = '\' or l_b_char = ','
then
l_strippedString := l_strippedString || l_b_char;
l_strip_offset := l_strip_offset + 1;
end if;
else
l_strippedString := l_strippedString || l_a_char;
end if;
end if;
l_strip_offset := l_strip_offset + 1;
end loop;

return l_strippedString;
end;
begin
loop
l_equals_offset := INSTR(p_parameters, '=', l_start_offset);
exit when l_equals_offset = 0;
l_start_offset := l_equals_offset + 1;
loop
l_comma_offset := INSTR(p_parameters, ',', l_start_offset);
if l_comma_offset = 0
then
l_comma_offset := length(p_parameters) + 1;
exit;
else
l_esc_count := 0;
l_esc_offset := l_comma_offset - 1;
loop
l_esc_char := SUBSTR(p_parameters, l_esc_offset, 1);
exit when l_esc_char != '\';
l_esc_count := l_esc_count + 1;
l_esc_offset := l_esc_offset - 1;
end loop;
if MOD(l_esc_count, 2) != 0
then
l_start_offset := l_comma_offset + 1;
else
exit;
end if;
end if;
end loop;
l_parameter_name := LTRIM(RTRIM(SUBSTR(p_parameters, l_anchor_offset, l_equals_offset - l_anchor_offset)));
l_parameter_value := strip_escape(SUBSTR(p_parameters, l_equals_offset + 1, l_comma_offset - (l_equals_offset + 1)));
-- Override Input Parameter
override_input_parameter(p_audit_execution_id, l_parameter_name, l_parameter_value, p_parameter_kind);
exit when l_comma_offset >= length(p_parameters)-1;
l_start_offset := l_comma_offset + 1;
l_anchor_offset := l_start_offset;
end loop;
end;
procedure override_custom_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_CUSTOM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
procedure override_system_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_SYSTEM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
begin
execute immediate ('alter session set current_schema = ' || p_repos_owner) ;
--
-- Initialize Return Code
--
l_return := wb_rt_api_exec.RESULT_FAILURE;
--
-- Import Parameters
--
dbms_output.put_line('Stage 1: Decoding Parameters');
l_task_type_name := p_task_type ;
if UPPER(l_task_type_name) = 'PLSQL'
then
l_task_type := 'PLSQL';
elsif UPPER(l_task_type_name) = 'SQL_LOADER'
then
l_task_type := 'SQLLoader';
elsif UPPER(l_task_type_name) = 'PROCESS'
then
l_task_type := 'ProcessFlow';
else
l_task_type := l_task_type_name;
end if;
l_task_name := p_task_name ;
l_location_name := p_location_name ;
dbms_output.put_line('| location_name=' || l_location_name);
dbms_output.put_line('| task_type=' || l_task_type);
dbms_output.put_line('| task_name=' || l_task_name);
--
-- Decode Parameters
--
begin
--
-- Prepare Execution
--
dbms_output.put_line('Stage 2: Opening Task ');
dbms_output.put_line('Stage 2:' || l_task_type);
dbms_output.put_line('Stage 2:' || l_task_name);
dbms_output.put_line('Stage 2:' || l_location_name);
l_audit_execution_id := wb_rt_api_exec.open(l_task_type, l_task_name, l_location_name);
dbms_output.put_line('| l_audit_execution_id=' || to_char(l_audit_execution_id));
commit;
--
-- Override Parameters
--
dbms_output.put_line('Stage 3: Overriding Parameters');
override_system_input_params(l_audit_execution_id, p_system_params);
override_custom_input_params(l_audit_execution_id, p_custom_params);
--
-- Execute
--
dbms_output.put_line('Stage 4: Executing Task');
l_audit_result := wb_rt_api_exec.execute(l_audit_execution_id);
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_audit_result_disp := 'SUCCESS';
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_audit_result_disp := 'WARNING';
elsif l_audit_result = wb_rt_api_exec.RESULT_FAILURE
then
l_audit_result_disp := 'FAILURE';
else
l_audit_result_disp := 'UNKNOWN';
end if;
dbms_output.put_line('| l_audit_result=' || to_char(l_audit_result) || ' (' || l_audit_result_disp || ')');
-- Finish Execution
dbms_output.put_line('Stage 5: Closing Task');
wb_rt_api_exec.close(l_audit_execution_id);
commit;
dbms_output.put_line('Stage 6: Processing Result');
if l_oem_style
then
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_return := 0;
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_return := 0;
else
l_return := l_audit_result;
end if;
else
l_return := l_audit_result;
end if;
dbms_output.put_line('| exit=' || to_char(l_return));
exception
when no_data_found
then
raise_application_error(-20001, 'Task not found - Please check the Task Type, Name and Location are correct.');
end;
return l_return ;
end;
/

second create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION REP_OWB_OWNER."FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('REP_OWB_OWNER','OWF_LOCATION','PROCESS','FI_MAIN');
RETURN 0;
END FUN_LAUNCH_OWB;
/

2.3 11g
create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION OWBSYS."FUN_LAUNCH_OWB" RETURN
NUMBER AS
res number;
BEGIN

OWBSYS.wb_rt_script_util.set_workspace('REP_OWB_OWNER.WORKSPACE_OWNER');
res := OWBSYS.wb_rt_script_util.run_task(
'LOCATION_WORKFLOW_11GR2',
'PROCESSFLOW',
'WF_MAIN',
NULL,
NULL,
0,
0);
RETURN 0;

END FUN_LAUNCH_OWB;
/

lundi 23 avril 2012

10g Physical Standby Data Guard configuration

I. Introduction

According to oracle note Oracle D ata Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

A standby database can be either a physical standby database or a logical standby database:

Physical standby database
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.
A physical standby database can be used for business purposes other than disaster recovery on a limited basis.
Logical standby database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.
A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

Figure 1-1 Typical Data Guard Configuration

Complete article : (http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm)

For my example, the primary database is ORALNDO (host=hots1), and the standby is NASHVILL (host=hots2).
The OS is linux x86_64 and the oracle version is 10.2.0.5.

II. Configuration

1. Ensure that ORLANDO is in archivelog mode, otherwise convert it.

remote_login_passwordfile =exclusive.
Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

2. Create Standby Redo Logs (optional)
on primary db ORALNDO :
SQL> select group#, thread#, bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 2
2 1 52428800 2
3 1 52428800 2

create standby redo log : (# of online redo log file groups on primary + 1) * maximum # of threads
for oralndo : threads = 1 because no RAC
The number of standby redo logs required for the physical standby database in this example is (3 + 1) * 1 = 4 at 50MB each.

From the primary database, connect as SYS and run the following to create four standby redo log file groups:
SQL> alter database add standby logfile thread 1 group 4 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 50m;
Database altered.
sql > select group#, type, member from v$logfile order by group#, member;

....

3. Init param for primary db : ORLANDO

##############################################################################

## Primary Role Parameters ##

DB_UNIQUE_NAME=ORLANDO

SERVICE_NAMES=ORLANDO

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'

compatible=10.2.0.5.0

control_files=("/u02/oracle/oradata/ORLANDO/controlfile/control01.ctl", "/u03/oracle/oradata/ORLANDO/controlfile/control02.ctl",

"/u04/oracle/oradata/ORLANDO/controlfile/control03.ctl")

LOG_ARCHIVE_DEST_1=

'LOCATION=/u05/oracle/oradata/ORLANDO/archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=ORLANDO'

LOG_ARCHIVE_DEST_2=

'SERVICE=NASHVILL

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=NASHVILL'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=DEFER

## Standby Role Parameters ##

DB_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'

LOG_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='ORLANDO'

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=1247805440

###########################################

# Processes and Sessions

###########################################

processes=150

###########################################

# File Configuration

###########################################

db_recovery_file_dest=/u05/oracle/flash_recovery_area

db_recovery_file_dest_size=2147483648

###########################################

# SGA Memory

###########################################

sga_target=1610612736

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/u01/app/oracle/admin/ORLANDO/bdump

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Database Identification

###########################################

db_name=ORLANDO

###########################################

# Diagnostics and Statistics

###########################################

user_dump_dest=/u01/app/oracle/admin/ORLANDO/udump

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=ORLANDOXDB)"

###########################################

# Security and Auditing

###########################################

audit_file_dest=/u01/app/oracle/admin/ORLANDO/adump

###########################################

# Database Identification

###########################################

db_domain=""

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Cache and I/O

###########################################

db_block_size=8192

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_tablespace=UNDOTBS1

###########################################

# Diagnostics and Statistics

###########################################

core_dump_dest=/u01/app/oracle/admin/ORLANDO/cdump

###########################################

# Security and Auditing

###########################################

remote_login_passwordfile=EXCLUSIVE

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

###########################################

# Cache and I/O

###########################################

db_file_multiblock_read_count=16

4. Rman backup of primary db: orlando
RMAN > backup database plus archivelog;

5. Create a Standby Controlfile
RMAN> backup device type disk format '/u02/oracle/oradata/ORLANDO/standby_controlfile/%U' current controlfile for standby;

6. create dirtectories on the standby server (host2)
mkdir -p /u01/app/oracle/admin/NASHVILL/adump
mkdir -p /u01/app/oracle/admin/NASHVILL/udump
mkdir -p /u01/app/oracle/admin/NASHVILL/dpdump
mkdir -p /u01/app/oracle/admin/NASHVILL/bdump
mkdir -p /u01/app/oracle/admin/NASHVILL/pfile
mkdir -p /u01/app/oracle/admin/NASHVILL/scripts
mkdir -p /u02/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u03/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u04/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u02/oracle/oradata/NASHVILL/data_system
mkdir -p /u02/oracle/oradata/ORLANDO/data
mkdir -p /u02/oracle/oradata/NASHVILL/data
mkdir -p /u03/oracle/oradata/NASHVILL/data
mkdir -p /u04/oracle/oradata/NASHVILL/data
mkdir -p /u02/oracle/oradata/NASHVILL/redo
mkdir -p /u03/oracle/oradata/NASHVILL/redo
mkdir -p /u04/oracle/oradata/NASHVILL/redo
mkdir -p /u05/oracle/oradata/NASHVILL/archive
mkdir -p /u02/oracle/oradata/NASHVILL/standby_controlfile

mkdir -p /u02/oracle/oradata/ORLANDO/ ----> to copy standby_controlfile see 13.

7. copy orlando.pfile to standby

From primary host (host1) to stdb host (host2)
a. scp ORLANDO_init.ora oracle@host2:/u01/app/oracle/admin/NASHVILL/pfile
b. on the stdb host : mv ORLANDO_init.ora NASHVILL_init.ora

8.update NASHVILL_init.ora

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
## Primary Role Parameters ##
DB_NAME=ORLANDO
INSTANCE_NAME=NASHVILL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'
compatible=10.2.0.5.0
control_files=("/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl", "/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl",
"/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl")
DB_UNIQUE_NAME=NASHVILL
LOG_ARCHIVE_DEST_1=
'LOCATION=/u05/oracle/oradata/NASHVILL/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=NASHVILL'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
SERVICE_NAMES=NASHVILL

## Standby Role Parameters ##
DB_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
LOG_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='NASHVILL'
standby_file_management='auto'
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1247805440
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# File Configuration
###########################################
db_recovery_file_dest=/u05/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# SGA Memory
###########################################
sga_target=1610612736
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/NASHVILL/bdump
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Diagnostics and Statistics
###########################################
user_dump_dest=/u01/app/oracle/admin/NASHVILL/udump
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=NASHVILLXDB)"
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/NASHVILL/adump
###########################################
# Database Identification
###########################################
db_domain=""
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
core_dump_dest=/u01/app/oracle/admin/NASHVILL/cdump
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
###########################################
# Cache and I/O
###########################################
db_file_multiblock_read_count=16

9. tnsnames

host2 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL)
)
)

hots1 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL) (UR=A) -- This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
)
)

10. listener (host2)
(SID_DESC =
(GLOBAL_DBNAME = NASHVILL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = NASHVILL)

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

from host1 server, check the communication beetween the 2 databases : tnsping NASHVILL

11. create pwdfile on host2
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwNASHVILL password=xxxx

12. startup stanby on nomount

export ORACLE_SID=NASHVILL
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/NASHVILL/pfile/NASHVILL_init.ora';

13.copy backups
Using an OS remote copy utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host :
scp -r /u05/oracle/flash_recovery_area/ORLANDO oracle@xxxx:/u05/oracle/flash_recovery_area/
scp -r /u02/oracle/oradata/ORLANDO/standby_controlfile oracle@xxxx:/u02/oracle/oradata/ORLANDO/

14.Create the Physical Standby Database

on stdby host (host2):

[oracle@host2 ~]$ export ORACLE_SID=NASHVILL

[oracle@host2 ~]$ rman target sys/xxxxl@ORLANDO auxiliary/

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 19 17:23:49 2012

connected to target database: ORLANDO (DBID=4090161544)

connected to auxiliary database: ORLANDO (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 20-MAR-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 20-MAR-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1 tag=TAG20120319T151009

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl

output filename=/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl

output filename=/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl

Finished restore at 20-MAR-12

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u02/oracle/oradata/NASHVILL/data_system/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u02/oracle/oradata/NASHVILL/data_system/system01.dbf";

set newname for datafile 2 to

"/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf";

set newname for datafile 3 to

"/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf";

set newname for datafile 4 to

"/u02/oracle/oradata/NASHVILL/data_system/users01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u02/oracle/oradata/NASHVILL/data_system/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 20-MAR-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u02/oracle/oradata/NASHVILL/data_system/system01.dbf

restoring datafile 00002 to /u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf

restoring datafile 00003 to /u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf

restoring datafile 00004 to /u02/oracle/oradata/NASHVILL/data_system/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp tag=TAG20120319T150920

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 20-MAR-12

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=6 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=7 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=8 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=9 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/users01.dbf

Finished Duplicate Db at 20-MAR-12

exit;

15. primary db
alter system set log_archive_dest_state_2=enable scope=both;

16. standby
To use Real Time Apply, run the following alternate version of the alter database command to place the standby database in managed recovery mode:

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

17.test
primary : alter system archive log current;


SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- -----------------------------------------------------------------
VALID

standby : select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME ARC APP
---------- --------- --------- --- ---
131 11-APR-12 11-APR-12 YES YES
132 11-APR-12 12-APR-12 YES YES
133 12-APR-12 16-APR-12 YES YES

III. Role transition : Implement Role Switchover Operation

1. Initialisation
Primary and stantdby databases must support the role transition.

Primary :

LOG_ARCHIVE_DEST_2=

'SERVICE=NASHVILL VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NASHVILL'

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='ORLANDO'

Standby:

LOG_ARCHIVE_DEST_2=

'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
DB_UNIQUE_NAME=NASHVILL'

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='NASHVILL'

2.tnsnames

hots2 :
ORLANDO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLANDO )
)
)

3. Redo current with primary DB : ORLANDO

SQL> alter system switch logfile;

System altered.


SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- ---------------------------------------------------------
VALID

4. Redo apply within standby DB : NASHVILL

SQL> select client_process, process, sequence#, status
2 from v$managed_standby;

CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 150 CLOSING
ARCH ARCH 151 CLOSING
N/A MRP0 152 WAIT_FOR_LOG
UNKNOWN RFS 0 IDLE

5. Perform the Switcher Operation

Primary DB:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Status "Session active" indicates to apply "WITH SESSION SHUTDOWN" clause :

SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.

6. Shtdown and restart the primary DB as a standby DB

SQL> shutdown immediate;
SQL> startup mount;

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

7. standby DB NASHVILL becomes primary :

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate;

SQL> startup ;

8.standby manages operations :

--START REDO APPLY
SQL> alter database recover managed standby database disconnect;

--START REDO APPLY USING REAL-TIME APPLY
SQL> alter database recover managed standby database using current logfile disconnect;