Beiträge getaggt mit 11g New Features
11gR2 RAC Architecture Picture
From my just finished RAC course – it was an LVC without whiteboard, which forced me to do the sketches on the computer. You might find it useful. It literally takes the whole day (at least) to explain it, though. And yes: It is oversimplified and some things are missing (I paint all the time in that course) 🙂
Just for me to memorize:
ohasd starts the agents
- orarootagent -> crsd, ctssd …
- cssdagent -> cssd
- oraagent -> evmd, asm …
crsd starts the agents
- orarootagent -> node vip, SCAN vip …
- oraagent -> Local Listener, SCAN Listener, Diskgroups, Database, Services …
Addendum: The corporation kindly offered to translate my above amateurish sketch into a professional graphic which I gladly accepted. See the brushed up sketch below:
I like it – just seems to me that due to corporate identity requirements, we are a little short in colours 🙂
Auto DOP: Differences of parallel_degree_policy=auto/limited
Recently, I delivered a Seminar about Parallel Processing in 11g where I came across some interesting findings, that I’d like to share with the Oracle Community. See my introduction into the 11g New Feature Auto DOP here, if that topic is completely new for you. There are big differences in the handling of Hints resp. Table-assigned parallel degrees, depending on the setting of parallel_degree_policy.
The parameter defaults to MANUAL, which gives you the known behavior of versions before 11g. LIMITED will only assign a system computed degree of parallelism (DOP) for tables, decorated with a parallel degree of DEFAULT, while prallel_degree_policy=AUTO will consider to assign a system computed DOP to all tables. Let’s see some details:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
I did my tests on 11.2.0.1 and 11.2.0.2.
SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam@prima Connected. SQL> set pages 300 SQL> show parameter parallel NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_limit string CPU parallel_degree_policy string MANUAL parallel_execution_message_size integer 16384 parallel_force_local boolean FALSE parallel_instance_group string parallel_io_cap_enabled boolean FALSE parallel_max_servers integer 20 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_min_time_threshold string AUTO parallel_server boolean FALSE parallel_server_instances integer 1 parallel_servers_target integer 8 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 SQL> create table t as select * from dual; Table created.
That is my test case. All the red parameters have default values. The table t is of course way too small to justify a parallel operation; especially, it will not meet the parallel_min_time_threshold of estimated runtime (about 10 seconds with AUTO). The setting parallel_degree_policy=MANUAL would leave the system as dumb as in earlier versions regarding an appropriate DOP, though. It would give me any DOP I demand with Hints or Parallel Degree on the table. See how that is different with AUTO/LIMITED:
SQL> alter system set parallel_degree_policy=auto; System altered. SQL> select /*+ parallel (t,8) */ * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 0 0 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 0 0 Server Threads 0 0 Allocation Height 0 0 Allocation Width 0 0 Local Msgs Sent 0 0 Distr Msgs Sent 0 0 Local Msgs Recv'd 0 0 Distr Msgs Recv'd 0 0 11 rows selected.
Although this was a valid hint, I got no parallel operation! That is different with LIMITED:
SQL> alter system set parallel_degree_policy=limited; System altered. SQL> select /*+ parallel (t,8) */ * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 1 1 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 1 1 Server Threads 8 0 Allocation Height 8 0 Allocation Width 1 0 Local Msgs Sent 26 26 Distr Msgs Sent 0 0 Local Msgs Recv'd 26 26 Distr Msgs Recv'd 0 0 11 rows selected.
Same statement, now I got my (not sensible) DOP. There is a new hint in 11g on the statement level, though, that is also delivering my requested DOP with AUTO:
SQL> connect adam/adam@prima Connected. SQL> alter system set parallel_degree_policy=auto; System altered. SQL> select /*+ parallel (8) */ * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 1 1 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 1 1 Server Threads 8 0 Allocation Height 8 0 Allocation Width 1 0 Local Msgs Sent 26 26 Distr Msgs Sent 0 0 Local Msgs Recv'd 26 26 Distr Msgs Recv'd 0 0 11 rows selected.
That is the only way to overrule the Auto DOP with parallel_degree_policy=AUTO. Similar that is with Parallel Degree on the table:
SQL> connect adam/adam@prima Connected. SQL> alter system set parallel_degree_policy=auto; System altered. SQL> alter table t parallel; Table altered. SQL> select * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 0 0 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 0 0 Server Threads 0 0 Allocation Height 0 0 Allocation Width 0 0 Local Msgs Sent 0 0 Distr Msgs Sent 0 0 Local Msgs Recv'd 0 0 Distr Msgs Recv'd 0 0 11 rows selected. SQL> alter table t parallel 8; Table altered. SQL> select * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 0 0 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 0 0 Server Threads 0 0 Allocation Height 0 0 Allocation Width 0 0 Local Msgs Sent 0 0 Distr Msgs Sent 0 0 Local Msgs Recv'd 0 0 Distr Msgs Recv'd 0 0 11 rows selected. SQL> alter system set parallel_degree_policy=limited; System altered. SQL> alter table t parallel; Table altered. SQL> select * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 0 0 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 0 0 Server Threads 0 0 Allocation Height 0 0 Allocation Width 0 0 Local Msgs Sent 0 0 Distr Msgs Sent 0 0 Local Msgs Recv'd 0 0 Distr Msgs Recv'd 0 0 11 rows selected. SQL> alter table t parallel 8; Table altered. SQL> select * from t; D - X SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 1 1 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 1 1 Server Threads 8 0 Allocation Height 8 0 Allocation Width 1 0 Local Msgs Sent 26 26 Distr Msgs Sent 0 0 Local Msgs Recv'd 26 26 Distr Msgs Recv'd 0 0 11 rows selected.
You saw a behavior like in the below table described:
| parallel_degree_policy | parallel (t,8) | Parallel (8) | degree DEFAULT | degree 8 |
| manual | 8 | 8 | 4 | 8 |
| limited | 8 | 8 | 1 | 8 |
| auto | 1* | 8 | 1 | 1 |
The default degree with parallel_degree_policy=MANUAL is cpu_count * parallel_threads_per_cpu; 4 in my case.
Apart from the shown differences between AUTO and LIMITED, only AUTO enables the also New Features Parallel Statement Queueing and In-Memory Parallel Execution. My personal impression is that LIMITED works like we have hoped that parallel_automatic_tuning would but never did 🙂
Conclusion: parallel_degree_policy=LIMITED will give you the DOP you request and compute an appropriate DOP only with a parallel degree of DEFAULT as an attribute of the table. Use this parameter if you trust that your applications/designers know why they use a certain DOP. parallel_degree_policy=AUTO will overrule any specific DOP you gave – except the new 11g parallel (n) Hint – and consider to do things in parallel for all tables even without a Hint or Degree.
Addendum: See this nice related posting by Gwen Shapira, especially the part about the I/O calibration.
* Second Addendum: With 11.2.0.3, the hint /*+ parallel (t,8) */ determines the DOP to 8, regardless of the parallel_degree_policy setting. Everything else is the same as shown, especially the different behavior of the values AUTO and LIMITED with the parallel degree of the table t explicitly set to 8.
Rolling Upgrade with Transient Logical Standby
You want to upgrade to a new release with minimum downtime. You have a Physical Standby Database in place. That Physical Standby can be used for a Rolling Upgrade, becoming a Transient Logical Standby only during the upgrade operation. This posting shows a demonstration of that feature while upgrading from 11.2.0.1 to 11.2.0.2.
The same should be working also to upgrade from 11g to 12c – I have heard success stories about it recently at the DOAG annual conference but did not test it myself.
My setup involves a Primary and a Physical Standby with the Protection Mode Maximum Availability, both on the same server. The new script physru – available from MOS – really makes it easy:
[oracle@uhesse ~]$ ./physru sys prima physt prima physt 11.2.0.2.0 Please enter the sysdba password: ### Initialize script to either start over or resume execution Aug 10 09:22:44 2011 [0-1] Identifying rdbms software version Aug 10 09:22:45 2011 [0-1] database prima is at version 11.2.0.1.0 Aug 10 09:22:45 2011 [0-1] database physt is at version 11.2.0.1.0 Aug 10 09:22:46 2011 [0-1] verifying flashback database is enabled at prima and physt Aug 10 09:22:47 2011 [0-1] verifying available flashback restore points Aug 10 09:22:47 2011 [0-1] verifying DG Broker is disabled Aug 10 09:22:48 2011 [0-1] looking up prior execution history Aug 10 09:22:48 2011 [0-1] purging script execution state from database prima Aug 10 09:22:48 2011 [0-1] purging script execution state from database physt Aug 10 09:22:48 2011 [0-1] starting new execution of script ### Stage 1: Backup user environment in case rolling upgrade is aborted Aug 10 09:22:49 2011 [1-1] creating restore point PRU_0000_0001 on database physt Aug 10 09:22:49 2011 [1-1] backing up current control file on physt Aug 10 09:22:49 2011 [1-1] created backup control file /u01/app/oracle/product/11.2.0.1/db_1/dbs/PRU_0001_physt_f.f Aug 10 09:22:49 2011 [1-1] creating restore point PRU_0000_0001 on database prima Aug 10 09:22:50 2011 [1-1] backing up current control file on prima Aug 10 09:22:50 2011 [1-1] created backup control file /u01/app/oracle/product/11.2.0.1/db_1/dbs/PRU_0001_prima_f.f NOTE: Restore point PRU_0000_0001 and backup control file PRU_0001_physt_f.f can be used to restore physt back to its original state as a physical standby, in case the rolling upgrade operation needs to be aborted prior to the first switchover done in Stage 4. ### Stage 2: Create transient logical standby from existing physical standby Aug 10 09:22:51 2011 [2-1] verifying RAC is disabled at physt Aug 10 09:22:51 2011 [2-1] verifying database roles Aug 10 09:22:51 2011 [2-1] verifying physical standby is mounted Aug 10 09:22:52 2011 [2-1] verifying database protection mode Aug 10 09:22:52 2011 [2-1] verifying transient logical standby datatype support Aug 10 09:22:54 2011 [2-2] starting media recovery on physt Aug 10 09:23:00 2011 [2-2] confirming media recovery is running Aug 10 09:23:01 2011 [2-2] waiting for v$dataguard_stats view to initialize Aug 10 09:23:08 2011 [2-2] waiting for apply lag on physt to fall below 30 seconds Aug 10 09:23:09 2011 [2-2] apply lag is now less than 30 seconds Aug 10 09:23:09 2011 [2-2] stopping media recovery on physt Aug 10 09:23:09 2011 [2-2] executing dbms_logstdby.build on database prima Aug 10 09:23:19 2011 [2-2] converting physical standby into transient logical standby Aug 10 09:23:24 2011 [2-3] opening database physt Aug 10 09:23:29 2011 [2-4] configuring transient logical standby parameters for rolling upgrade Aug 10 09:23:31 2011 [2-4] starting logical standby on database physt Aug 10 09:23:39 2011 [2-4] waiting until logminer dictionary has fully loaded Aug 10 09:28:45 2011 [2-4] dictionary load 16% complete Aug 10 09:28:56 2011 [2-4] dictionary load 75% complete Aug 10 09:29:18 2011 [2-4] dictionary load is complete Aug 10 09:29:20 2011 [2-4] waiting for v$dataguard_stats view to initialize Aug 10 09:29:20 2011 [2-4] waiting for apply lag on physt to fall below 30 seconds Aug 10 09:29:21 2011 [2-4] apply lag is now less than 30 seconds NOTE: Database physt is now ready to be upgraded. This script has left the database open in case you want to perform any further tasks before upgrading the database. Once the upgrade is complete, the database must opened in READ WRITE mode before this script can be called to resume the rolling upgrade. NOTE: If physt was previously a RAC database that was disabled, it may be reverted back to a RAC database upon completion of the rdbms upgrade. This can be accomplished by performing the following steps: 1) On instance physt, set the cluster_database parameter to TRUE. eg: SQL> alter system set cluster_database=true scope=spfile; 2) Shutdown instance physt. eg: SQL> shutdown abort; 3) Startup and open all instances for database physt. eg: srvctl start database -d physt
That was the first step – my in the moment Logical Standby is now ready to upgrade. This can be done with dbua or manually, as usual:
[oracle@uhesse ~]$ sqlplus sys/oracle@physt as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 10 09:31:23 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA 1990933310 LOGICAL STANDBY
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Notice that the name and DBID of the Logical Standby is the same as on the Primary, which is unusual for a Logical Standby and caused by the new KEEP IDENTITY clause, introduced to make Transient Logical Standby possible. I have installed the new sources of 11.2.0.2 as an Out-of-Place-Upgrade in a separate Oracle Home and switch the environment now to it:
[oracle@uhesse ~]$ cp /u01/app/oracle/product/11.2.0.1/db_1/dbs/orapw* /u01/app/oracle/product/11.2.0.2/db_1/dbs/ [oracle@uhesse ~]$ cp /u01/app/oracle/product/11.2.0.1/db_1/dbs/spfile*.ora /u01/app/oracle/product/11.2.0.2/db_1/dbs/ [oracle@uhesse ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db_1 [oracle@uhesse ~]$ export PATH=$PATH:$ORACLE_HOME/bin [oracle@uhesse ~]$ export ORACLE_SID=physt [oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 10 09:35:21 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 668082176 bytes Fixed Size 2229480 bytes Variable Size 448793368 bytes Database Buffers 213909504 bytes Redo Buffers 3149824 bytes Database mounted. Database opened.
Next step is the Upgrade, done by catupgrd.sql – the most time-consuming part, but no downtime at all yet for my end users, because prima (my „Production Database“) is still running happily. Be aware that during this rolling upgrade, the Primary is not synchronized with the Standby (Redo Transport is interrupted), though. This is reflected on the Primary:
SQL> select protection_level,protection_mode from v$database;
PROTECTION_LEVEL PROTECTION_MODE
-------------------- --------------------
RESYNCHRONIZATION MAXIMUM AVAILABILITY
Back to my Standby that is upgraded:
SQL> @?/rdbms/admin/catupgrd [... Output suppressed ...] SQL> SQL> Rem ********************************************************************* SQL> Rem END catupgrd.sql SQL> Rem ********************************************************************* SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production [oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 10 09:52:10 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 668082176 bytes Fixed Size 2229480 bytes Variable Size 452987672 bytes Database Buffers 209715200 bytes Redo Buffers 3149824 bytes Database mounted. Database opened. SQL> @?/rdbms/admin/utlrp TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2011-08-10 09:52:53 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2011-08-10 09:55:56 DOC> The following query reports the number of objects that have compiled DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
I go back to the first terminal where I called physru and call it again with the very same command for the second time:
[oracle@uhesse ~]$ ./physru sys prima physt prima physt 11.2.0.2.0 Please enter the sysdba password: ### Initialize script to either start over or resume execution Aug 10 09:59:44 2011 [0-1] Identifying rdbms software version Aug 10 09:59:45 2011 [0-1] database prima is at version 11.2.0.1.0 Aug 10 09:59:46 2011 [0-1] database physt is at version 11.2.0.2.0 Aug 10 09:59:48 2011 [0-1] verifying flashback database is enabled at prima and physt Aug 10 09:59:48 2011 [0-1] verifying available flashback restore points Aug 10 09:59:49 2011 [0-1] verifying DG Broker is disabled Aug 10 09:59:50 2011 [0-1] looking up prior execution history Aug 10 09:59:50 2011 [0-1] last completed stage [2-4] using script version 0001 Aug 10 09:59:50 2011 [0-1] resuming execution of script ### Stage 3: Validate upgraded transient logical standby Aug 10 09:59:51 2011 [3-1] database physt is no longer in OPEN MIGRATE mode Aug 10 09:59:51 2011 [3-1] database physt is at version 11.2.0.2.0 ### Stage 4: Switch the transient logical standby to be the new primary Aug 10 09:59:53 2011 [4-1] waiting for physt to catch up (this could take a while) Aug 10 09:59:53 2011 [4-1] starting logical standby on database physt Aug 10 10:00:02 2011 [4-1] waiting for v$dataguard_stats view to initialize Aug 10 10:02:42 2011 [4-1] waiting for apply lag on physt to fall below 30 seconds Aug 10 10:03:13 2011 [4-1] apply lag is now less than 30 seconds Aug 10 10:03:14 2011 [4-2] switching prima to become a logical standby Aug 10 10:03:21 2011 [4-2] prima is now a logical standby Aug 10 10:03:21 2011 [4-3] waiting for standby physt to process end-of-redo from primary Aug 10 10:03:24 2011 [4-4] switching physt to become the new primary Aug 10 10:03:28 2011 [4-4] physt is now the new primary ### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical Aug 10 10:03:34 2011 [5-1] shutting down database prima Aug 10 10:03:48 2011 [5-1] mounting database prima Aug 10 10:03:58 2011 [5-2] flashing back database prima to restore point PRU_0000_0001 Aug 10 10:04:00 2011 [5-3] converting prima into physical standby Aug 10 10:04:02 2011 [5-4] shutting down database prima NOTE: Database prima has been shutdown, and is now ready to be started using the newer version Oracle binary. This script requires the database to be mounted (on all active instances, if RAC) before calling this script to resume the rolling upgrade.
During this call of the script we have a downtime (less than 20 seconds) in which no end-user can use the Production Database. It is already over – physt is now Primary with the new Release running. I go on and start the ex-Primary (prima) with the new Release sources:
[oracle@uhesse ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db_1 [oracle@uhesse ~]$ export PATH=$PATH:$ORACLE_HOME/bin [oracle@uhesse ~]$ export ORACLE_SID=prima [oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 10 10:06:37 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 668082176 bytes Fixed Size 2229480 bytes Variable Size 398461720 bytes Database Buffers 264241152 bytes Redo Buffers 3149824 bytes Database mounted. SQL> select name,dbid,database_role from v$database; NAME DBID DATABASE_ROLE --------- ---------- ---------------- PRIMA 1990933310 PHYSICAL STANDBY
Third call of the script will flashback the ex-Primary (prima) to the guaranteed restore point, created at the first call of the script, and recover from there to the present state of the new Primary (physt) – thereby „upgrading“ it with Redo Apply. The point is: You only need to call catupgrd.sql once during the whole operation.
[oracle@uhesse ~]$ ./physru sys prima physt prima physt 11.2.0.2.0 Please enter the sysdba password: ### Initialize script to either start over or resume execution Aug 10 10:08:02 2011 [0-1] Identifying rdbms software version Aug 10 10:08:02 2011 [0-1] database prima is at version 11.2.0.2.0 Aug 10 10:08:02 2011 [0-1] database physt is at version 11.2.0.2.0 Aug 10 10:08:04 2011 [0-1] verifying flashback database is enabled at prima and physt Aug 10 10:08:04 2011 [0-1] verifying available flashback restore points Aug 10 10:08:05 2011 [0-1] verifying DG Broker is disabled Aug 10 10:08:05 2011 [0-1] looking up prior execution history Aug 10 10:08:06 2011 [0-1] last completed stage [5-4] using script version 0001 Aug 10 10:08:06 2011 [0-1] resuming execution of script ### Stage 6: Run media recovery through upgrade redo Aug 10 10:08:08 2011 [6-1] upgrade redo region identified as scn range [245621, 510586] Aug 10 10:08:08 2011 [6-1] starting media recovery on prima Aug 10 10:08:15 2011 [6-1] confirming media recovery is running Aug 10 10:09:04 2011 [6-1] waiting for media recovery to initialize v$recovery_progress Aug 10 10:09:35 2011 [6-1] monitoring media recovery's progress Aug 10 10:09:38 2011 [6-2] last applied scn 241298 is approaching upgrade redo start scn 245621 Aug 10 10:10:14 2011 [6-3] recovery of upgrade redo at 01% - estimated complete at Aug 10 10:36:21 Aug 10 10:10:30 2011 [6-3] recovery of upgrade redo at 16% - estimated complete at Aug 10 10:15:57 Aug 10 10:10:46 2011 [6-3] recovery of upgrade redo at 25% - estimated complete at Aug 10 10:14:39 Aug 10 10:11:02 2011 [6-3] recovery of upgrade redo at 33% - estimated complete at Aug 10 10:14:16 Aug 10 10:11:18 2011 [6-3] recovery of upgrade redo at 45% - estimated complete at Aug 10 10:13:31 Aug 10 10:11:34 2011 [6-3] recovery of upgrade redo at 48% - estimated complete at Aug 10 10:13:50 Aug 10 10:11:51 2011 [6-3] recovery of upgrade redo at 56% - estimated complete at Aug 10 10:13:44 Aug 10 10:12:07 2011 [6-3] recovery of upgrade redo at 60% - estimated complete at Aug 10 10:13:51 Aug 10 10:12:24 2011 [6-3] recovery of upgrade redo at 68% - estimated complete at Aug 10 10:13:44 Aug 10 10:12:40 2011 [6-3] recovery of upgrade redo at 70% - estimated complete at Aug 10 10:14:00 Aug 10 10:12:56 2011 [6-3] recovery of upgrade redo at 74% - estimated complete at Aug 10 10:14:07 Aug 10 10:13:13 2011 [6-3] recovery of upgrade redo at 82% - estimated complete at Aug 10 10:14:00 Aug 10 10:13:29 2011 [6-3] recovery of upgrade redo at 87% - estimated complete at Aug 10 10:14:02 Aug 10 10:13:46 2011 [6-3] recovery of upgrade redo at 96% - estimated complete at Aug 10 10:13:56 Aug 10 10:14:04 2011 [6-4] media recovery has finished recovering through upgrade ### Stage 7: Switch back to the original roles prior to the rolling upgrade NOTE: At this point, you have the option to perform a switchover which will restore prima back to a primary database and physt back to a physical standby database. If you answer 'n' to the question below, prima will remain a physical standby database and physt will remain a primary database. Do you want to perform a switchover? (y/n):
It may be not necessary to switchover again, especially if the two Databases are on the same server. This would avoid another short downtime. I do it in spite:
Do you want to perform a switchover? (y/n): y
Aug 10 10:50:09 2011 [7-1] continuing
Aug 10 10:50:12 2011 [7-2] waiting for v$dataguard_stats view to initialize
Aug 10 10:50:12 2011 [7-2] waiting for apply lag on prima to fall below 30 seconds
Aug 10 10:50:13 2011 [7-2] apply lag is now less than 30 seconds
Aug 10 10:50:14 2011 [7-3] switching physt to become a physical standby
Aug 10 10:50:17 2011 [7-3] physt is now a physical standby
Aug 10 10:50:17 2011 [7-3] shutting down database physt
Aug 10 10:50:19 2011 [7-3] mounting database physt
Aug 10 10:50:35 2011 [7-4] waiting for standby prima to process end-of-redo from primary
Aug 10 10:50:37 2011 [7-5] switching prima to become the new primary
Aug 10 10:50:38 2011 [7-5] prima is now the new primary
Aug 10 10:50:38 2011 [7-5] opening database prima
Aug 10 10:50:44 2011 [7-6] starting media recovery on physt
Aug 10 10:50:50 2011 [7-6] confirming media recovery is running
### Stage 8: Statistics
script start time: 10-Aug-11 09:22:49
script finish time: 10-Aug-11 10:50:54
total script execution time: +00 01:28:05
wait time for user upgrade: +00 00:30:30
active script execution time: +00 00:57:35
transient logical creation start time: 10-Aug-11 09:22:53
transient logical creation finish time: 10-Aug-11 09:23:23
primary to logical switchover start time: 10-Aug-11 10:03:13
logical to primary switchover finish time: 10-Aug-11 10:03:30
primary services offline for: +00 00:00:17
total time former primary in physical role: +00 00:46:02
time to reach upgrade redo: +00 00:00:17
time to recover upgrade redo: +00 00:04:09
primary to physical switchover start time: 10-Aug-11 10:50:09
physical to primary switchover finish time: 10-Aug-11 10:50:43
primary services offline for: +00 00:00:34
SUCCESS: The physical rolling upgrade is complete
Some nice statistics at the end, documenting especially the very short downtime from end-user perspective. Now wasn’t that cool? 🙂
Keep in mind that this setup was very vanilla, though. There could be more effort involved if the Primary has tables that are unsupported with Logical Standby, especially.
Addendum: See this nice posting by Guenadi Jilevski that gives an example for Transient Logical Standby with RAC Primary & Standby
Pickleball spielen auf YouTube
Pickleball spielen 