Thinking Out Loud

August 23, 2020

tablespace_segment_advisor

Filed under: oracle,performance — mdinh @ 2:41 am

I have been working on tasks for weekly tablespace segment advisor to shrink all segments residing in tablespace.

There are many blogs out there with the same info; however, it was not to the requirements and this is a combinations after research.

Here is a demo for 19c; however, the preparations have been tested in 11.2.

=======================================================
### SQL Scripts
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ ls -l
total 11
-rwxrwxrwx 1 vagrant vagrant 1048 Aug 23 04:07 10-advise.sql
-rwxrwxrwx 1 vagrant vagrant  257 Aug 23 03:31 20-benefit.sql
-rwxrwxrwx 1 vagrant vagrant  475 Aug 23 04:05 30-space_save.sql
-rwxrwxrwx 1 vagrant vagrant  722 Aug 23 03:36 40-recommendations.sql
-rwxrwxrwx 1 vagrant vagrant  431 Aug 23 04:09 99-delete.sql
-rwxrwxrwx 1 vagrant vagrant  141 Aug 23 04:03 set_global_var.sql
-rwxrwxrwx 1 vagrant vagrant  259 Aug 23 04:00 test.sql

=======================================================
### Create test case.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ test.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:10:55 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> drop table big_table purge;
drop table big_table purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.01
SQL> create table big_table (id number, name char(200)) tablespace USERS;

Table created.

Elapsed: 00:00:00.02
SQL> insert into big_table select rownum,'a' from dual connect by rownum<900000; 899999 rows created. Elapsed: 00:00:12.65 SQL> commit;

Commit complete.

Elapsed: 00:00:01.86
SQL> delete from big_table where mod(id,10)<>0;

810000 rows deleted.

Elapsed: 00:00:37.72
SQL> commit;

Commit complete.

Elapsed: 00:00:00.08
SQL> exit

=======================================================
### Run advise.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 10-advise.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:13:40 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> DECLARE
  2    l_object_id   NUMBER;
  3    l_object_type VARCHAR2(32767) := 'TABLESPACE';
  4    l_attr1       VARCHAR2(32767) := '&v_tablespace';
  5    l_task_name   VARCHAR2(32767) := '&v_task_name';
  6  BEGIN
  7  DBMS_ADVISOR.create_task (
  8    advisor_name => 'Segment Advisor',
  9    task_name    => l_task_name
 10  );
 11
 12  DBMS_ADVISOR.create_object (
 13    task_name   => l_task_name,
 14    object_type => l_object_type,
 15    attr1       => l_attr1,
 16    attr2       => NULL,
 17    attr3       => NULL,
 18    attr4       => 'null',
 19    attr5       => NULL,
 20    object_id   => l_object_id
 21  );
 22
 23  DBMS_ADVISOR.set_task_parameter (
 24    task_name => l_task_name,
 25    parameter => 'RECOMMEND_ALL',
 26    value     => 'TRUE');
 27
 28  DBMS_ADVISOR.execute_task (
 29    task_name => l_task_name
 30  );
 31
 32  END;
 33  /
old   4:   l_attr1       VARCHAR2(32767) := '&v_tablespace';
new   4:   l_attr1       VARCHAR2(32767) := 'USERS';
old   5:   l_task_name   VARCHAR2(32767) := '&v_task_name';
new   5:   l_task_name   VARCHAR2(32767) := 'SEGMENT_ADVISOR_TBS_USERS';
SQL> set feedback on echo on head on
SQL> select task_name, advisor_name
  2  from DBA_ADVISOR_TASKS
  3  where advisor_name='Segment Advisor'
  4  ;

TASK_NAME                      ADVISOR_NAME
------------------------------ ---------------------------------------
SEGMENT_ADVISOR_TBS_USERS      Segment Advisor

1 row selected.

SQL> exit

=======================================================
### Review benefit.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 20-benefit.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:14:49 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='&v_task_name' order by 1;
old   1: SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='&v_task_name' order by 1
new   1: SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='SEGMENT_ADVISOR_TBS_USERS' order by 1

BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table SYS.BIG_TABLE and perform shrink, estimated savings is 180447064 bytes.
SQL> exit

=======================================================
### Review space saving.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 30-space_save.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:15:48 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col segment_name for a30
SQL> SELECT
  2  segment_name,
  3  round(allocated_space/1024/1024,1) alloc_mb,
  4  round(used_space/1024/1024,1) used_mb,
  5  round(reclaimable_space/1024/1024) reclaim_mb,
  6  round(reclaimable_space/allocated_space*100,0) pctsave
  7  FROM TABLE(dbms_space.asa_recommendations())
  8  where tablespace_name='&v_tablespace'
  9  order by pctsave desc
 10  ;

SEGMENT_NAME                     ALLOC_MB    USED_MB RECLAIM_MB    PCTSAVE
------------------------------ ---------- ---------- ---------- ----------
BIG_TABLE                             216       43.9        172         80
SQL> exit

=======================================================
### Create recommendations.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 40-recommendations.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:16:24 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

***********************************************************************
***   nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1 &         ***
***********************************************************************
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle@db-fs-1:hawk:/sf_working/segment_advisor

=======================================================
### Review SQL script.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ cat run.sql
set echo on timing on

alter table "SYS"."BIG_TABLE" enable row movement;
alter table "SYS"."BIG_TABLE" shrink space COMPACT;
alter table "SYS"."BIG_TABLE" shrink space;

exit

=======================================================
### Run SQL script using nohup.
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1 &
[1] 27417
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$
[1]+  Done                    nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$

=======================================================
### Review results.
=======================================================

Live Run for comparison.
/home/oracle/segment_advisor$ grep ORA- run.out | sort | uniq -c
   6 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
   2 ORA-10631: SHRINK clause should not be specified for this object
  12 ORA-10636: ROW MOVEMENT is not enabled

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ grep ORA- run.out | sort | uniq -c

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ grep Elapsed run.out | sort | uniq -c
      1 Elapsed: 00:00:00.02
      1 Elapsed: 00:00:02.91
      1 Elapsed: 00:00:06.84

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ cat run.out
nohup: ignoring input

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:18:07 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL> alter table "SYS"."BIG_TABLE" enable row movement;

Table altered.

Elapsed: 00:00:00.02
SQL> alter table "SYS"."BIG_TABLE" shrink space COMPACT;

Table altered.

Elapsed: 00:00:06.84
SQL> alter table "SYS"."BIG_TABLE" shrink space;

Table altered.

Elapsed: 00:00:02.91
SQL>
SQL> exit

=======================================================
### Delete Advisor Task
=======================================================

oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 99-delete.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:20:04 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> DECLARE
  2    l_object_id      NUMBER;
  3    l_object_type VARCHAR2(32767) := 'TABLESPACE';
  4    l_attr1       VARCHAR2(32767) := 'v_tablespace';
  5    l_task_name      VARCHAR2(32767) := '&v_task_name';
  6  BEGIN
  7  DBMS_ADVISOR.delete_task (
  8    task_name => l_task_name
  9  );
 10  END;
 11  /
old   5:   l_task_name   VARCHAR2(32767) := '&v_task_name';
new   5:   l_task_name   VARCHAR2(32767) := 'SEGMENT_ADVISOR_TBS_USERS';

PL/SQL procedure successfully completed.

SQL> select task_name, advisor_name
  2  from DBA_ADVISOR_TASKS
  3  where advisor_name='Segment Advisor'
  4  ;

no rows selected

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$

SQL Scripts for tablespace_segment_advisor

August 19, 2020

Minimal Downtime Grid Infrastructure Out of Place Patching

Filed under: 19c,Grid Infrastructure,Patching — mdinh @ 1:43 am

Looks like OOP is back again; however, I have not had the opportunity to test.

When you test, it’s important to test rollback as well.

GRID Out Of Place (OOP) Rollback Disaster

Steps for Minimal Downtime Grid Infrastructure Out of Place ( OOP ) Patching using gridSetup.sh (Doc ID 2662762.1)
May 13, 2020
Oracle Database – Enterprise Edition – Version 19.6.0.0.0 and later
This is applicable only for cluster environment,not the SIHA(Oracle Restart)

In general, the following steps are involved:

1) Installing and Patching the Grid infrastructure (software only)

/u01/app/19.7.0.0/grid/gridSetup.sh -ApplyRU 3089972
Chose the option “Install Software only” and select all the nodes.

2) Switching the Grid Infrastructure Home

Run the gridSetup.sh from the target home

/u01/app/19.7.0.0/grid/gridSetup.sh -SwitchGridhome

It will launch the GUI (you could run it in silent as well with a response file)

During this phase, you can select the automated root.sh option if you wanted. Otherwise it will prompt the root.sh.

August 15, 2020

Is creategoldimage really required?

Filed under: 19c,creategoldimage,upgrade — mdinh @ 10:28 pm

Typically, creategoldimage is used to create image for install or upgrade; however, creategoldimage is too BUGGY.

$GRID_HOME/gridSetup.sh -creategoldimage -exclFiles $ORACLE_HOME/log,$ORACLE_HOME/.patch_storage -destinationlocation /u01/app/oracle/goldimage -silent

In the discussion with LDC, he was thinking of using tar and I did not know if tar will work.

Here’s the proof of concept that it works.

Upgrade_Oracle_Restart_from_12.2_to_19.8.pdf

[FATAL] [INS-32700] The gold image creation failed for Grid 19.8

Filed under: 19c,creategoldimage,Grid Infrastructure — mdinh @ 6:27 pm

If you came here looking for solution, my apologies as I don’t have one.

If you came here looking for comfort, then I am here as you are not alone.

Honestly, it might just be easier to applyRU vs creategoldimage.
gridSetup.sh -applyRU $PATCH_DIR/31305339


Applied Patch 31305339 - GI Release Update 19.8.0.0.200714 for RAC environment.

==================================================

[FATAL] [INS-32700] The gold image creation failed for Grid 19.8

[root@ol7-19-lax1 ~]# crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [761455134].
[root@ol7-19-lax1 ~]#

[root@ol7-19-lax2 ~]# crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [761455134].
[root@ol7-19-lax2 ~]#

==================================================

[oracle@ol7-19-lax1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
31335188;TOMCAT RELEASE UPDATE 19.0.0.0.0 (31335188)
31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
31304218;ACFS RELEASE UPDATE 19.8.0.0.0 (31304218)
31281355;Database Release Update : 19.8.0.0.200714 (31281355)

OPatch succeeded.
[oracle@ol7-19-lax1 ~]$

[oracle@ol7-19-lax2 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
31335188;TOMCAT RELEASE UPDATE 19.0.0.0.0 (31335188)
31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
31304218;ACFS RELEASE UPDATE 19.8.0.0.0 (31304218)
31281355;Database Release Update : 19.8.0.0.200714 (31281355)

OPatch succeeded.
[oracle@ol7-19-lax2 ~]$

==================================================

[oracle@ol7-19-lax1 ~]$ . oraenv <<< +ASM1
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-19-lax1 ~]$ $ORACLE_HOME/gridSetup.sh -creategoldimage -exclFiles $ORACLE_HOME/log,$ORACLE_HOME/.patch_storage -destinationlocation /u01/app/oracle/goldimage -silent
Launching Oracle Grid Infrastructure Setup Wizard...

[FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-30-53PM for more details.
Setup failed.
[oracle@ol7-19-lax1 ~]$

==================================================

[oracle@ol7-19-lax1 ~]$ cd /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-30-53PM
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$ ls -l
total 1116
-rw-r--r--. 1 oracle oinstall 0 Aug 15 17:30 gridSetupActions2020-08-15_05-30-53PM.err
-rw-r--r--. 1 oracle oinstall 1052145 Aug 15 17:31 gridSetupActions2020-08-15_05-30-53PM.log
-rw-r--r--. 1 oracle oinstall 77842 Aug 15 17:31 gridSetupActions2020-08-15_05-30-53PM.out
-rw-r--r--. 1 oracle oinstall 129 Aug 15 17:30 installerPatchActions_2020-08-15_05-30-53PM.log
-rw-r--r--. 1 oracle oinstall 2158 Aug 15 17:31 time2020-08-15_05-30-53PM.log
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$

[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$ grep INS-32700 *
gridSetupActions2020-08-15_05-30-53PM.log:SEVERE: [Aug 15, 2020 5:31:09 PM] [FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-30-53PM for more details.
gridSetupActions2020-08-15_05-30-53PM.out:[FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-30-53PM for more details.
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$

[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$ grep -C20 INS-32700 gridSetupActions2020-08-15_05-30-53PM.log
INFO: [Aug 15, 2020 5:31:01 PM] File lib/clntsh.map marked to be zero out.
WARNING: [Aug 15, 2020 5:31:02 PM] Could not create symlink: /tmp/GridSetupActions2020-08-15_05-30-53PM/tempHome_1597512661914/log/procwatcher/prw.sh.
Refer associated stacktrace #oracle.install.ivw.common.driver.job.CreateGoldImageJob:7142
INFO: [Aug 15, 2020 5:31:02 PM] Executing [/u01/app/19.0.0/grid/OPatch/opatch, lspatches]
INFO: [Aug 15, 2020 5:31:02 PM] Starting Output Reader Threads for process /u01/app/19.0.0/grid/OPatch/opatch
INFO: [Aug 15, 2020 5:31:09 PM] 31335188;TOMCAT RELEASE UPDATE 19.0.0.0.0 (31335188)
INFO: [Aug 15, 2020 5:31:09 PM] 31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
INFO: [Aug 15, 2020 5:31:09 PM] 31304218;ACFS RELEASE UPDATE 19.8.0.0.0 (31304218)
INFO: [Aug 15, 2020 5:31:09 PM] 31281355;Database Release Update : 19.8.0.0.200714 (31281355)
INFO: [Aug 15, 2020 5:31:09 PM] The process /u01/app/19.0.0/grid/OPatch/opatch exited with code 0
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for output processor threads to exit.
INFO: [Aug 15, 2020 5:31:09 PM]
INFO: [Aug 15, 2020 5:31:09 PM] OPatch succeeded.
INFO: [Aug 15, 2020 5:31:09 PM] Output processor threads exited.
INFO: [Aug 15, 2020 5:31:09 PM] Executing [/u01/app/19.0.0/grid/bin/zip, -q, -r, /u01/app/oracle/goldimage/grid_home_2020-08-15_05-30-53PM.zip, .]
INFO: [Aug 15, 2020 5:31:09 PM] Starting Output Reader Threads for process /u01/app/19.0.0/grid/bin/zip
INFO: [Aug 15, 2020 5:31:09 PM] The process /u01/app/19.0.0/grid/bin/zip exited with code 0
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for output processor threads to exit.
INFO: [Aug 15, 2020 5:31:09 PM] Output processor threads exited.
INFO: [Aug 15, 2020 5:31:09 PM] Removing the goldimage file, as there was a failure.
SEVERE: [Aug 15, 2020 5:31:09 PM] [FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-30-53PM for more details.
INFO: [Aug 15, 2020 5:31:09 PM] Advice is ABORT
INFO: [Aug 15, 2020 5:31:09 PM] Adding ExitStatus FAILURE to the exit status set
INFO: [Aug 15, 2020 5:31:09 PM] Adding ExitStatus FAILURE to the exit status set
INFO: [Aug 15, 2020 5:31:09 PM] All forked task are completed at state setup
INFO: [Aug 15, 2020 5:31:09 PM] Completed background operations
INFO: [Aug 15, 2020 5:31:09 PM] Validating state
INFO: [Aug 15, 2020 5:31:09 PM] Completed validating state
INFO: [Aug 15, 2020 5:31:09 PM] Verifying route success
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for completion of background operations
INFO: [Aug 15, 2020 5:31:09 PM] Completed background operations
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for completion of background operations
INFO: [Aug 15, 2020 5:31:09 PM] Completed background operations
INFO: [Aug 15, 2020 5:31:09 PM] Executing action at state finish
INFO: [Aug 15, 2020 5:31:09 PM] FinishAction Actions.execute called
INFO: [Aug 15, 2020 5:31:09 PM] Completed executing action at state
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for completion of background operations
INFO: [Aug 15, 2020 5:31:09 PM] Completed background operations
INFO: [Aug 15, 2020 5:31:09 PM] Waiting for completion of background operations
INFO: [Aug 15, 2020 5:31:09 PM] Completed background operations
INFO: [Aug 15, 2020 5:31:09 PM] Moved to state
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$

==================================================

DOES NOT APPLY

Bug 29220079 - Error INS-32700 Creating a GI Gold Image (Doc ID 29220079.8)

The fix for 29220079 is first included in
20.1.0
19.3.0.0.190416 (Apr 2019) Database Release Update (DB RU)

[INS-32700] The gold image creation failed. Check the install log

The trace file shows an error creating a directory (or a file):

WARNING: [Jan 16, 2019 12:23:13 AM] Could not create directory:
/tmp/GridSetupActions2019-01-16_00-22-41AM/tempHome_1547626992976/lib.

==================================================

WARNING: [Aug 15, 2020 5:30:57 PM] Command to get the files from '/u01/app/19.0.0/grid' not owned by 'oracle' failed.
WARNING: [Aug 15, 2020 5:30:59 PM] Following files are not readable: [/u01/app/19.0.0/grid/log/procwatcher/prw.sh, /u01/app/19.0.0/grid/log/procwatcher/PRW_SYS_ol7-19-lax1, /u01/app/19.0.0/grid/log/procwatcher/prwinit.ora, /u01/app/19.0.0/grid/crf/admin/run/crfmond, /u01/app/19.0.0/grid/crf/admin/run/crflogd]
WARNING: [Aug 15, 2020 5:31:02 PM] Could not create symlink: /tmp/GridSetupActions2020-08-15_05-30-53PM/tempHome_1597512661914/log/procwatcher/prw.sh.
WARNING: [Aug 15, 2020 5:31:09 PM] Validation disabled for the state finish

==================================================

[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$ ls -l /u01/app/19.0.0/grid
total 164
drwxrwxr-x. 3 root oinstall 22 Mar 4 01:03 acfs
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfsccm
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfsccreg
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfscm
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfsiob
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfsrd
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 acfsrm
drwxr-xr-x. 2 oracle oinstall 102 Jul 11 03:49 addnode
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:50 advmccb
drwxr-xr-x. 10 oracle oinstall 4096 Apr 17 2019 assistants
drwxrwxrwt. 6 root oinstall 52 Jul 11 03:52 auth
drwxr-xr-x. 2 root oinstall 12288 Aug 15 15:20 bin
drwxrwxr-x. 4 oracle oinstall 42 Jul 11 03:52 cdata
drwxr-x---. 3 oracle oinstall 18 Mar 4 00:50 cdp
drwxrwxr-x. 8 oracle oinstall 4096 Aug 15 14:47 cfgtoollogs
drwxr-x---. 4 oracle oinstall 31 Mar 4 00:50 cha
drwxr-xr-x. 3 oracle oinstall 19 Mar 4 00:30 client
drwxr-xr-x. 4 oracle oinstall 87 Jul 11 03:49 clone
drwxr-x---. 3 root oinstall 19 Jul 11 03:52 crf
drwxr-xr-x. 14 root oinstall 4096 Jul 11 03:52 crs
drwx--x--x. 5 oracle oinstall 41 Mar 4 00:50 css
drwxr-xr-x. 3 root oinstall 18 Jul 11 03:52 ctss
drwxr-xr-x. 3 oracle oinstall 19 Aug 15 15:08 ctx
drwxrwxr-x. 7 oracle oinstall 71 Apr 17 2019 cv
drwxr-xr-x. 3 oracle oinstall 19 Apr 17 2019 dbjava
drwxr-xr-x. 2 oracle oinstall 79 Aug 15 15:17 dbs
drwxr-xr-x. 5 oracle oinstall 4096 Jul 11 03:49 deinstall
drwxr-xr-x. 3 oracle oinstall 20 Apr 17 2019 demo
drwxr-xr-x. 3 oracle oinstall 20 Apr 17 2019 diagnostics
drwxr-xr-x. 13 oracle oinstall 4096 Apr 17 2019 dmu
-rw-r--r--. 1 oracle oinstall 852 Aug 18 2015 env.ora
drwxr-x---. 7 oracle oinstall 65 Jul 11 03:52 evm
drwxr-x---. 3 oracle oinstall 18 Jul 11 03:52 gipc
drwxr-x---. 3 oracle oinstall 18 Jul 11 03:52 gnsd
drwxr-x---. 8 oracle oinstall 4096 Jul 11 04:02 gpnp
-rwxr-x---. 1 oracle oinstall 3294 Mar 8 2017 gridSetup.sh
drwxr-xr-x. 4 oracle oinstall 32 Apr 17 2019 has
drwxr-xr-x. 3 oracle oinstall 19 Apr 17 2019 hs
drwxrwx---. 10 oracle oinstall 4096 Jul 11 03:52 install
drwxr-xr-x. 2 oracle oinstall 29 Apr 17 2019 instantclient
drwxr-x---. 14 oracle oinstall 4096 Jul 11 03:49 inventory
drwxr-xr-x. 8 oracle oinstall 82 Mar 4 02:18 javavm
drwxr-xr-x. 3 oracle oinstall 35 Apr 17 2019 jdbc
drwxr-xr-x. 6 root oinstall 4096 Aug 15 15:10 jdk
drwxr-xr-x. 2 oracle oinstall 8192 Aug 15 15:14 jlib
drwxr-xr-x. 10 oracle oinstall 4096 Apr 17 2019 ldap
drwxr-xr-x. 4 root oinstall 12288 Aug 15 15:15 lib
drwxrwxr-x. 6 oracle oinstall 67 Jul 11 04:07 log
drwxr-xr-x. 5 oracle oinstall 42 Apr 17 2019 md
drwxr-x---. 3 oracle oinstall 18 Jul 11 03:52 mdns
drwxr-xr-x. 10 oracle oinstall 4096 Mar 4 02:18 network
drwxr-xr-x. 5 oracle oinstall 46 Apr 17 2019 nls
drwxr-x---. 3 oracle oinstall 18 Jul 11 03:52 ohasd
drwxr-xr-x. 3 oracle oinstall 19 Aug 15 15:08 olap
drwxr-xr-x. 3 root oinstall 18 Jul 11 03:52 ologgerd
drwxr-x---. 14 oracle oinstall 4096 Aug 15 13:50 OPatch
drwxrwxr-x. 3 oracle oinstall 16 Aug 15 14:39 opatchautocfg
drwxr-xr-x. 8 oracle oinstall 77 Apr 17 2019 opmn
drwxr-xr-x. 4 oracle oinstall 34 Apr 17 2019 oracore
drwxr-xr-x. 3 oracle oinstall 18 Mar 4 00:26 oradiag_oracle
-rw-r-----. 1 oracle oinstall 56 Jul 11 03:52 oraInst.loc
drwxr-xr-x. 6 oracle oinstall 52 Apr 17 2019 ord
drwxr-xr-x. 4 oracle oinstall 66 Apr 17 2019 ords
drwxr-xr-x. 3 oracle oinstall 19 Apr 17 2019 oss
drwxr-xr-x. 3 root oinstall 18 Jul 11 03:52 osysmond
drwxr-xr-x. 8 oracle oinstall 4096 Jul 11 03:49 oui
drwxr-xr-x. 4 oracle oinstall 33 Apr 17 2019 owm
drwxr-xr-x. 5 root oinstall 39 Apr 17 2019 perl
drwxr-xr-x. 6 oracle oinstall 78 Apr 17 2019 plsql
drwxr-xr-x. 5 oracle oinstall 42 Apr 17 2019 precomp
drwxr-xr-x. 2 oracle oinstall 26 Apr 17 2019 QOpatch
drwxr-xr-x. 5 oracle oinstall 42 Apr 17 2019 qos
drwxr-xr-x. 6 oracle oinstall 68 Jul 11 03:52 racg
drwxr-xr-x. 15 oracle oinstall 4096 Mar 4 02:18 rdbms
drwxr-xr-x. 3 oracle oinstall 21 Apr 17 2019 relnotes
drwxr-xr-x. 7 oracle oinstall 102 Apr 17 2019 rhp
-rwxr-xr-x. 1 root oinstall 405 Jul 11 03:49 root.sh
-rwx------. 1 oracle oinstall 490 Apr 17 2019 root.sh.old
-rw-r-----. 1 oracle oinstall 10 Apr 17 2019 root.sh.old.1
-rwx------. 1 oracle oinstall 405 Apr 18 2019 root.sh.old.2
-rw-r-----. 1 oracle oinstall 10 Apr 17 2019 root.sh.old.3
-rwxr-xr-x. 1 oracle oinstall 415 Mar 4 00:40 root.sh.old.4
-rw-r-----. 1 oracle oinstall 10 Apr 17 2019 root.sh.old.5
-rwxr-xr-x. 1 root oinstall 414 Jul 11 03:49 rootupgrade.sh
-rwxr-x---. 1 oracle oinstall 628 Sep 3 2015 runcluvfy.sh
drwxr-xr-x. 5 oracle oinstall 4096 Apr 17 2019 sdk
drwxr-xr-x. 3 oracle oinstall 18 Apr 17 2019 slax
drwxr-xr-x. 6 oracle oinstall 4096 Aug 15 15:08 sqlpatch
drwxr-xr-x. 6 oracle oinstall 53 Jul 11 03:48 sqlplus
drwxr-xr-x. 8 oracle oinstall 77 Jul 11 03:52 srvm
drwxr-x---. 5 root oinstall 63 Mar 4 00:30 suptools
drwxr-xr-x. 4 oracle oinstall 29 Apr 17 2019 tomcat
drwxr-xr-x. 3 oracle oinstall 35 Apr 17 2019 ucp
drwxr-xr-x. 7 oracle oinstall 71 Apr 17 2019 usm
drwxr-xr-x. 2 oracle oinstall 33 Apr 17 2019 utl
-rw-r-----. 1 oracle oinstall 500 Feb 6 2013 welcome.html
drwxr-xr-x. 3 oracle oinstall 18 Apr 17 2019 wlm
drwxr-xr-x. 3 oracle oinstall 19 Apr 17 2019 wwg
drwxr-xr-x. 5 oracle oinstall 4096 Aug 15 15:19 xag
drwxr-x---. 6 oracle oinstall 58 Apr 17 2019 xdk
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-30-53PM]$

==================================================

[oracle@ol7-19-lax2 ~]$ . oraenv <<< +ASM2
ORACLE_SID = [hawk2] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-19-lax2 ~]$ $ORACLE_HOME/gridSetup.sh -creategoldimage -exclFiles $ORACLE_HOME/log,$ORACLE_HOME/.patch_storage -destinationlocation /u01/app/oracle/goldimage -silent
Launching Oracle Grid Infrastructure Setup Wizard...

[FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-49-37PM for more details.
Setup failed.
[oracle@ol7-19-lax2 ~]$

==================================================

[oracle@ol7-19-lax1 logs]$ $ORACLE_HOME/gridSetup.sh -creategoldimage -exclFiles $ORACLE_HOME/log,$ORACLE_HOME/.patch_storage -destinationlocation /u01/app/oracle/goldimage -silent -debug
Launching Oracle Grid Infrastructure Setup Wizard...

[FATAL] [INS-32700] The gold image creation failed. Check the install log /u01/app/oraInventory/logs/GridSetupActions2020-08-15_05-58-28PM for more details.
Setup failed.

[oracle@ol7-19-lax1 logs]$ ls -alrt
total 204
drwxrwx---. 2 oracle oinstall 4096 Jul 11 03:52 GridSetupActions2020-07-11_03-46-06AM
-rw-r-----. 1 oracle oinstall 0 Jul 11 04:09 oraInstall2020-07-11_04-08-44AM.err
-rw-r-----. 1 oracle oinstall 117 Jul 11 04:09 oraInstall2020-07-11_04-08-44AM.out.ol7-19-lax2
-rw-r-----. 1 oracle oinstall 0 Jul 11 04:09 oraInstall2020-07-11_04-08-44AM.err.ol7-19-lax2
-rw-r-----. 1 oracle oinstall 41420 Jul 11 04:09 UpdateNodeList2020-07-11_04-08-44AM.log
-rw-r-----. 1 oracle oinstall 32660 Jul 11 04:09 installActions2020-07-11_04-08-44AM.log
-rw-r-----. 1 oracle oinstall 151 Jul 11 04:11 oraInstall2020-07-11_04-08-44AM.out
drwxrwx---. 3 oracle oinstall 4096 Jul 11 04:11 GridSetupActions2020-07-11_04-08-44AM
-rw-r-----. 1 oracle oinstall 94 Jul 11 04:18 time2020-07-11_04-16-02AM.log
-rw-r-----. 1 oracle oinstall 0 Jul 11 04:18 oraInstall2020-07-11_04-16-02AM.out
-rw-r-----. 1 oracle oinstall 0 Jul 11 04:18 oraInstall2020-07-11_04-16-02AM.err
-rw-r-----. 1 oracle oinstall 17442 Jul 11 04:18 installActions2020-07-11_04-16-02AM.log
drwxrwx---. 2 oracle oinstall 4096 Jul 11 04:24 InstallActions2020-07-11_04-16-02AM
-rw-r-----. 1 oracle oinstall 24443 Aug 15 15:03 OPatch2020-08-15_02-57-03PM.log
-rw-r-----. 1 oracle oinstall 35327 Aug 15 15:15 OPatch2020-08-15_03-03-52PM.log
drwxrwx---. 6 oracle oinstall 4096 Aug 15 17:58 .
-rw-r-----. 1 oracle oinstall 14673 Aug 15 17:58 installActions2020-08-15_05-58-28PM.log
drwxrwx---. 5 oracle oinstall 92 Aug 15 17:58 ..
drwxr-xr-x. 2 oracle oinstall 4096 Aug 15 17:58 GridSetupActions2020-08-15_05-58-28PM
[oracle@ol7-19-lax1 logs]$

[oracle@ol7-19-lax1 logs]$ cd GridSetupActions2020-08-15_05-58-28PM
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-58-28PM]$ ls -l
total 1256
-rw-r--r--. 1 oracle oinstall 1853 Aug 15 17:58 gridSetupActions2020-08-15_05-58-28PM.err
-rw-r--r--. 1 oracle oinstall 1179847 Aug 15 17:58 gridSetupActions2020-08-15_05-58-28PM.log
-rw-r--r--. 1 oracle oinstall 87723 Aug 15 17:58 gridSetupActions2020-08-15_05-58-28PM.out
-rw-r--r--. 1 oracle oinstall 129 Aug 15 17:58 installerPatchActions_2020-08-15_05-58-28PM.log
-rw-r--r--. 1 oracle oinstall 2158 Aug 15 17:58 time2020-08-15_05-58-28PM.log
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-58-28PM]$ cat gridSetupActions2020-08-15_05-58-28PM.err
---# Begin Stacktrace #---------------------------
ID: oracle.install.ivw.common.driver.job.CreateGoldImageJob:7962
java.nio.file.NoSuchFileException: /tmp/GridSetupActions2020-08-15_05-58-28PM/tempHome_1597514317225/log/procwatcher/prw.sh
at sun.nio.fs.UnixException.translateToIOException(UnixException.java:86)
at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:102)
at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:107)
at sun.nio.fs.UnixFileSystemProvider.createSymbolicLink(UnixFileSystemProvider.java:457)
at java.nio.file.Files.createSymbolicLink(Files.java:1043)
at oracle.install.ivw.common.driver.job.CreateGoldImageJob.createTemporaryHome(CreateGoldImageJob.java:844)
at oracle.install.ivw.common.driver.job.CreateGoldImageJob.createGoldImage(CreateGoldImageJob.java:1016)
at oracle.install.ivw.common.driver.job.CreateGoldImageJob.call(CreateGoldImageJob.java:327)
at oracle.install.ivw.common.driver.job.CreateGoldImageJob.call(CreateGoldImageJob.java:2463)
at oracle.install.ivw.common.driver.job.CreateGoldImageJob.call(CreateGoldImageJob.java:121)
at oracle.install.ivw.crs.driver.CRSImageSetupDriver.setup(CRSImageSetupDriver.java:403)
at oracle.install.commons.base.interview.common.action.SetupAction$1.call(SetupAction.java:62)
at oracle.install.commons.base.interview.common.action.SetupAction$1.call(SetupAction.java:58)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

---# End Stacktrace #-----------------------------
[oracle@ol7-19-lax1 GridSetupActions2020-08-15_05-58-28PM]$

August 7, 2020

grid19c_upgrade_has.rsp

Filed under: 19c,response_file — mdinh @ 2:16 am

This response file is generated from GUI.

The values can be 1,2,4,8,16,32, or 64 MB, depending on the specific disk group compatibility level.
Larger AU sizes typically provide performance advantages for data warehouse applications that use large sequential reads.

Which Is The Grow Up Factor When We Pass From AUsize Of 1 To AUsize Of 4? (Doc ID 1961116.1)

gridSetup.sh -silent -applyRU $PATCH_DIR/31305339 -responseFile ~/grid19c_upgrade_has.rsp -ignorePrereqFailure

--- grid19c_upgrade_has.rsp ---
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
*** INVENTORY_LOCATION=<FILL IN PATH LOCATION>
*** ORACLE_BASE=<FILL IN PATH LOCATION>
oracle.install.option=UPGRADE
oracle.install.crs.config.scanType=LOCAL_SCAN
oracle.install.crs.config.ClusterConfiguration=STANDALONE
oracle.install.crs.config.configureAsExtendedCluster=false
oracle.install.crs.config.gpnp.configureGNS=false
oracle.install.crs.config.autoConfigureClusterNodeVIP=false
oracle.install.crs.config.gpnp.gnsOption=CREATE_NEW_GNS
oracle.install.crs.configureGIMR=false
oracle.install.asm.configureGIMRDataDG=false
oracle.install.crs.config.useIPMI=false
oracle.install.asm.diskGroup.AUSize=1
oracle.install.asm.gimrDG.AUSize=1
oracle.install.asm.configureAFD=false
oracle.install.crs.configureRHPS=false
oracle.install.crs.config.ignoreDownNodes=false
oracle.install.config.managementOption=NONE
oracle.install.config.omsPort=0
oracle.install.crs.rootconfig.executeRootScript=false

August 3, 2020

Last Time You Tested Data Guard Was?

Filed under: 19c,Dataguard — mdinh @ 12:05 am

Data Guard is like a spare tire. Don’t wait to find there is no air when you need it.

[oracle@ol7-112-dg1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Aug 2 16:57:28 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@hawk
Password:
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 52 seconds ago)

DGMGRL> switchover to hawk_stby
Performing switchover NOW, please wait...
Operation requires a connection to database "hawk_stby"
Connecting ...
Connected to "hawk_stby"
Connected as SYSDBA.
New primary database "hawk_stby" is opening...
Operation requires start up of instance "hawk" on database "hawk"
Starting instance "hawk"...
Connected to an idle instance.
ORACLE instance started.
Connected to "hawk"
Database mounted.
Connected to "hawk"
Switchover succeeded, new primary is "hawk_stby"
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
    hawk      - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

DGMGRL> exit
[oracle@ol7-112-dg1 ~]$

==================================================

[oracle@ol7-112-dg1 upgrade19c]$ sqlplus / as sysdba @ status

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 2 16:59:45 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Session altered.

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2  ;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- -------------------- ------------------
HAWK      hawk                           MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE  YES

SQL> ;
  1  select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2*
SQL> select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6  ;

PID                           INST   THREAD# CLIENT_P PROCESS   STATUS       SEQUENCE#    BLOCK# DELAY_MINS
------------------------ --------- --------- -------- --------- ------------ --------- --------- ----------
3118                             1         1 LGWR     RFS       RECEIVING          169      3237          0
3151                             1         1 N/A      MRP0      APPLYING_LOG       169      3237          0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg1 upgrade19c]$

==================================================

[oracle@ol7-112-dg1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Aug 2 17:00:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@hawk_stby
Password:
Connected to "hawk_stby"
Connected as SYSDBA.
DGMGRL> switchover to hawk
Performing switchover NOW, please wait...
Operation requires a connection to database "hawk"
Connecting ...
Connected to "hawk"
Connected as SYSDBA.
New primary database "hawk" is opening...
Operation requires start up of instance "hawk" on database "hawk_stby"
Starting instance "hawk"...
Connected to an idle instance.
ORACLE instance started.
Connected to "hawk_stby"
Database mounted.
Connected to "hawk_stby"
Switchover succeeded, new primary is "hawk"
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

DGMGRL> exit
[oracle@ol7-112-dg1 ~]$

==================================================

[oracle@ol7-112-dg1 upgrade19c]$ sqlplus / as sysdba @ status

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 2 17:02:06 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Session altered.

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2  ;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- -------------------- ------------------
HAWK      hawk                           READ WRITE           PRIMARY          MAXIMUM PERFORMANCE  YES

SQL> ;
  1  select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2*
SQL> select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6  ;

PID                           INST   THREAD# CLIENT_P PROCESS   STATUS       SEQUENCE#    BLOCK# DELAY_MINS
------------------------ --------- --------- -------- --------- ------------ --------- --------- ----------
3328                             1         1 LNS      LNS       WRITING            172      3252          0

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg1 upgrade19c]$

August 1, 2020

Rolling Upgrades Using Physical Standby and physru_v3.sh

Filed under: 19c,Dataguard,upgrade — mdinh @ 4:12 pm

I am not going to reiterate what is already out there; however, what is out there covers 12.1.0.2.0 vs 19.3.0.0.0.

Here is the syntax and what seems to be ambiguous is target version.

$./physru.sh &lt;sysdba user&gt; &lt;primary TNS alias&gt; &lt;physical standby TNS alias&gt; &lt;primary db unique name&gt; &lt;physical standby db unique name&gt; &lt;target version&gt;

NOTE: This is NOT all the steps for upgrade but only applicable steps when running physru.sh.

First physru execution completed successfully.

### First physru execution:
Verifies that Data Guard Broker is disabled and FRA is configured.
Creates a guaranteed restore point
Converts the existing Physical Standby to a Logical Standby

oracle@ol7-112-dg2:hawk:/sf_working/upgrade19c
$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.3.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Jul 30 20:06:30 2020 [0-1] Identifying rdbms software version
Jul 30 20:06:31 2020 [0-1] database hawk is at version 11.2.0.4.0
Jul 30 20:06:31 2020 [0-1] database hawk_stby is at version 11.2.0.4.0
Jul 30 20:06:31 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Jul 30 20:06:31 2020 [0-1] verifying backup location at hawk and hawk_stby
Jul 30 20:06:31 2020 [0-1] verifying available flashback restore points
Jul 30 20:06:31 2020 [0-1] verifying DG Broker is disabled
Jul 30 20:06:31 2020 [0-1] looking up prior execution history
Jul 30 20:06:31 2020 [0-1] purging script execution state from database hawk
Jul 30 20:06:31 2020 [0-1] purging script execution state from database hawk_stby
Jul 30 20:06:31 2020 [0-1] starting new execution of script

### Stage 1: Backup user environment in case rolling upgrade is aborted
Jul 30 20:06:31 2020 [1-1] database hawk location for backup controlfile is /u01/app/oracle/fast_recovery_area
Jul 30 20:06:32 2020 [1-1] database hawk_stby location for backup controlfile is /u01/app/oracle/fast_recovery_area
Jul 30 20:06:32 2020 [1-1] creating restore point PRU_0000_0003 on database hawk_stby
Jul 30 20:06:32 2020 [1-1] backing up current control file on hawk_stby
Jul 30 20:06:32 2020 [1-1] created backup control file /u01/app/oracle/fast_recovery_area/PRU_0003_hawk_stby_f.f
Jul 30 20:06:32 2020 [1-1] creating restore point PRU_0000_0003 on database hawk
Jul 30 20:06:32 2020 [1-1] backing up current control file on hawk
Jul 30 20:06:32 2020 [1-1] created backup control file /u01/app/oracle/fast_recovery_area/PRU_0003_hawk_f.f

NOTE: Restore point PRU_0000_0001 and backup control file PRU_0003_hawk_stby_f.f
can be used to restore hawk_stby 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
Jul 30 20:06:32 2020 [2-1] verifying RAC is disabled at hawk_stby
Jul 30 20:06:32 2020 [2-1] verifying database roles
Jul 30 20:06:32 2020 [2-1] verifying physical standby is mounted
Jul 30 20:06:32 2020 [2-1] verifying database protection mode
Jul 30 20:06:32 2020 [2-1] verifying transient logical standby datatype support
Jul 30 20:06:33 2020 [2-2] starting media recovery on hawk_stby
Jul 30 20:06:39 2020 [2-2] confirming media recovery is running
Jul 30 20:06:39 2020 [2-2] waiting for apply lag to fall under 30 seconds
Jul 30 20:06:52 2020 [2-2] apply lag measured at 13 seconds
Jul 30 20:06:52 2020 [2-2] stopping media recovery on hawk_stby
Jul 30 20:06:53 2020 [2-2] executing dbms_logstdby.build on database hawk
Jul 30 20:06:59 2020 [2-2] converting physical standby into transient logical standby
Jul 30 20:07:03 2020 [2-3] opening database hawk_stby
Jul 30 20:07:05 2020 [2-4] configuring transient logical standby parameters for rolling upgrade
Jul 30 20:07:05 2020 [2-4] starting logical standby on database hawk_stby
Jul 30 20:07:10 2020 [2-4] enabling log archive destination to database hawk_stby
Jul 30 20:07:11 2020 [2-4] waiting until logminer dictionary has fully loaded
Jul 30 20:07:51 2020 [2-4] dictionary load 03% complete
Jul 30 20:08:01 2020 [2-4] dictionary load 62% complete
Jul 30 20:08:11 2020 [2-4] dictionary load is complete
Jul 30 20:08:11 2020 [2-4] waiting for apply lag to fall under 30 seconds
Jul 30 20:08:15 2020 [2-4] apply lag measured at 3 seconds

NOTE: Database hawk_stby 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 hawk_stby 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 hawk, set the cluster_database parameter to TRUE.
eg: SQL&gt; alter system set cluster_database=true scope=spfile;

2) Shutdown instance hawk.
eg: SQL&gt; shutdown abort;

3) Startup and open all instances for database hawk_stby.
eg: srvctl start database -d hawk_stby

oracle@ol7-112-dg2:hawk:/sf_working/upgrade19c

Second physru execution FAILED.

### Second physru execution to switchover (APPLICATION BROWNOUT):
Executes a switchover making the upgraded standby database the primary database.
Executes a flashback of the original primary database to the guaranteed restore point from step 1 and shuts it down.

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.3.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 01:55:56 2020 [0-1] Identifying rdbms software version
Aug 01 01:55:56 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 01:55:57 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 01:56:00 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 01:56:02 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 01:56:03 2020 [0-1] verifying available flashback restore points
Aug 01 01:56:04 2020 [0-1] verifying DG Broker is disabled
Aug 01 01:56:05 2020 [0-1] looking up prior execution history
Aug 01 01:56:08 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 01:56:08 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 01:56:09 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 01:56:09 2020 [3-1] ERROR: hawk_stby is not at version 19.3.0.0.0
[oracle@ol7-112-dg2 upgrade19c]$

Second physru execution SUCCEEDED as 19.0.0.0.0 (base release) is used vs 19.3.0.0.0 (actual release).

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 02:48:40 2020 [0-1] Identifying rdbms software version
Aug 01 02:48:40 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 02:48:41 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 02:48:45 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 02:48:47 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 02:48:48 2020 [0-1] verifying available flashback restore points
Aug 01 02:48:49 2020 [0-1] verifying DG Broker is disabled
Aug 01 02:48:50 2020 [0-1] looking up prior execution history
Aug 01 02:48:53 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 02:48:53 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 02:48:54 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 02:48:54 2020 [3-1] database hawk_stby is at version 19.0.0.0.0

### Stage 4: Switch the transient logical standby to be the new primary
Aug 01 02:48:59 2020 [4-1] waiting for hawk_stby to catch up (this could take a while)
Aug 01 02:49:00 2020 [4-1] waiting for apply lag to fall under 30 seconds
Aug 01 02:49:12 2020 [4-1] apply lag measured at 9 seconds
Aug 01 02:49:16 2020 [4-2] using fast switchover optimizations

NOTE: A switchover is about to be performed which will incur a brief outage
of the primary database.  If you answer 'y' to the question below,
database hawk_stby will become the new primary database, and database hawk
will be converted into a standby in preparation for upgrade.  If you answer
'n' to the question below, the script will exit, leaving the databases in
their current roles.
Are you ready to proceed with the switchover? (y/n): y

Aug 01 02:49:31 2020 [4-2] continuing
Aug 01 02:49:31 2020 [4-2] switching hawk to become a logical standby
Aug 01 02:49:39 2020 [4-2] hawk is now a logical standby
Aug 01 02:49:39 2020 [4-2] waiting for standby hawk_stby to process end-of-redo from primary
Aug 01 02:49:44 2020 [4-2] switching hawk_stby to become the new primary
Aug 01 02:49:45 2020 [4-2] hawk_stby is now the new primary

### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical
Aug 01 02:49:49 2020 [5-1] shutting down database hawk
Aug 01 02:50:03 2020 [5-1] mounting database hawk
Aug 01 02:50:12 2020 [5-2] flashing back database hawk to restore point PRU_0000_0003
Aug 01 02:50:15 2020 [5-3] converting hawk into physical standby
Aug 01 02:50:17 2020 [5-4] shutting down database hawk

NOTE: Database hawk 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.

[oracle@ol7-112-dg2 upgrade19c]$

Third and Final physru execution completed successfully.

### Execute physru for the third and final time.
Start redo apply
Prompt whether to switch back to original configuration
Remove guaranteed restore points

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 02:48:40 2020 [0-1] Identifying rdbms software version
Aug 01 02:48:40 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 02:48:41 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 02:48:45 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 02:48:47 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 02:48:48 2020 [0-1] verifying available flashback restore points
Aug 01 02:48:49 2020 [0-1] verifying DG Broker is disabled
Aug 01 02:48:50 2020 [0-1] looking up prior execution history
Aug 01 02:48:53 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 02:48:53 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 02:48:54 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 02:48:54 2020 [3-1] database hawk_stby is at version 19.0.0.0.0

### Stage 4: Switch the transient logical standby to be the new primary
Aug 01 02:48:59 2020 [4-1] waiting for hawk_stby to catch up (this could take a while)
Aug 01 02:49:00 2020 [4-1] waiting for apply lag to fall under 30 seconds
Aug 01 02:49:12 2020 [4-1] apply lag measured at 9 seconds
Aug 01 02:49:16 2020 [4-2] using fast switchover optimizations

NOTE: A switchover is about to be performed which will incur a brief outage
of the primary database.  If you answer 'y' to the question below,
database hawk_stby will become the new primary database, and database hawk
will be converted into a standby in preparation for upgrade.  If you answer
'n' to the question below, the script will exit, leaving the databases in
their current roles.
Are you ready to proceed with the switchover? (y/n): y

Aug 01 02:49:31 2020 [4-2] continuing
Aug 01 02:49:31 2020 [4-2] switching hawk to become a logical standby
Aug 01 02:49:39 2020 [4-2] hawk is now a logical standby
Aug 01 02:49:39 2020 [4-2] waiting for standby hawk_stby to process end-of-redo from primary
Aug 01 02:49:44 2020 [4-2] switching hawk_stby to become the new primary
Aug 01 02:49:45 2020 [4-2] hawk_stby is now the new primary

### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical
Aug 01 02:49:49 2020 [5-1] shutting down database hawk
Aug 01 02:50:03 2020 [5-1] mounting database hawk
Aug 01 02:50:12 2020 [5-2] flashing back database hawk to restore point PRU_0000_0003
Aug 01 02:50:15 2020 [5-3] converting hawk into physical standby
Aug 01 02:50:17 2020 [5-4] shutting down database hawk

NOTE: Database hawk 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.

[oracle@ol7-112-dg2 upgrade19c]$

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 03:26:16 2020 [0-1] Identifying rdbms software version
Aug 01 03:26:17 2020 [0-1] database hawk is at version 19.0.0.0.0
Aug 01 03:26:18 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 03:26:26 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 03:26:29 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 03:26:31 2020 [0-1] verifying available flashback restore points
Aug 01 03:26:34 2020 [0-1] verifying DG Broker is disabled
Aug 01 03:26:36 2020 [0-1] looking up prior execution history
Aug 01 03:26:39 2020 [0-1] last completed stage [5-4] using script version 0003
Aug 01 03:26:39 2020 [0-1] resuming execution of script

### Stage 6: Run media recovery through upgrade redo
Aug 01 03:26:47 2020 [6-1] upgrade redo region identified as scn range [995261, 2453907]
Aug 01 03:26:47 2020 [6-1] enabling log archive destination to database hawk
Aug 01 03:26:51 2020 [6-1] starting media recovery on hawk
Aug 01 03:26:57 2020 [6-1] confirming media recovery is running
Aug 01 03:26:59 2020 [6-1] waiting for media recovery to initialize v$recovery_progress
Aug 01 03:27:20 2020 [6-1] monitoring media recovery's progress
Aug 01 03:27:32 2020 [6-3] recovery of upgrade redo at 07% - estimated complete at Aug 01 03:31:24
Aug 01 03:27:57 2020 [6-3] recovery of upgrade redo at 26% - estimated complete at Aug 01 03:30:06
Aug 01 03:28:21 2020 [6-3] recovery of upgrade redo at 42% - estimated complete at Aug 01 03:30:00
Aug 01 03:28:45 2020 [6-3] recovery of upgrade redo at 52% - estimated complete at Aug 01 03:30:10
Aug 01 03:29:10 2020 [6-3] recovery of upgrade redo at 61% - estimated complete at Aug 01 03:30:25
Aug 01 03:29:36 2020 [6-3] recovery of upgrade redo at 73% - estimated complete at Aug 01 03:30:27
Aug 01 03:30:00 2020 [6-3] recovery of upgrade redo at 82% - estimated complete at Aug 01 03:30:35
Aug 01 03:30:24 2020 [6-3] recovery of upgrade redo at 90% - estimated complete at Aug 01 03:30:42
Aug 01 03:30:51 2020 [6-3] recovery of upgrade redo at 96% - estimated complete at Aug 01 03:30:55
Aug 01 03:31:12 2020 [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 hawk back to a primary database and
hawk_stby back to a physical standby database.  If you answer 'n'
to the question below, hawk will remain a physical standby
database and hawk_stby will remain a primary database.

Do you want to perform a switchover? (y/n): y

Aug 01 03:31:26 2020 [7-1] continuing
Aug 01 03:31:36 2020 [7-2] waiting for apply lag to fall under 30 seconds
Aug 01 03:31:44 2020 [7-2] apply lag measured at 5 seconds
Aug 01 03:31:48 2020 [7-3] switching hawk_stby to become a physical standby
Aug 01 03:31:55 2020 [7-3] hawk_stby is now a physical standby
Aug 01 03:31:55 2020 [7-3] shutting down database hawk_stby
Aug 01 03:31:57 2020 [7-3] mounting database hawk_stby
Aug 01 03:32:08 2020 [7-3] starting media recovery on hawk_stby
Aug 01 03:32:15 2020 [7-3] confirming media recovery is running
Aug 01 03:32:16 2020 [7-3] waiting for standby hawk to process end-of-redo from primary
Aug 01 03:32:21 2020 [7-3] switching hawk to become the new primary
Aug 01 03:32:23 2020 [7-3] hawk is now the new primary
Aug 01 03:32:23 2020 [7-3] opening database hawk

### Stage 8: Statistics
script start time:                                           31-Jul-20 23:54:44
script finish time:                                          01-Aug-20 03:32:36
total script execution time:                                       +00 03:37:52
wait time for user upgrade:                                        +00 02:52:39
active script execution time:                                      +00 00:45:13
transient logical creation start time:                       31-Jul-20 23:54:46
transient logical creation finish time:                      31-Jul-20 23:55:14
primary to logical switchover start time:                    01-Aug-20 02:49:14
logical to primary switchover finish time:                   01-Aug-20 02:49:47
primary services offline for:                                      +00 00:00:33
total time former primary in physical role:                        +00 00:40:57
time to reach upgrade redo:
time to recover upgrade redo:                                      +00 00:03:44
primary to physical switchover start time:                   01-Aug-20 03:31:25
physical to primary switchover finish time:                  01-Aug-20 03:32:34
primary services offline for:                                      +00 00:01:09

SUCCESS: The physical rolling upgrade is complete

[oracle@ol7-112-dg2 upgrade19c]$

References:

Oracle11g Data Guard: Database Rolling Upgrade Shell Script (Doc ID 949322.1)

Data Guard physru_v3.sh Script Errors ORA-01403 ORA-06512 (Doc ID 2570572.1)

Oracle Database Rolling Upgrades

High_Level_Action_Plan

================================================================================
1. Convert physical standby to transient logical standby by running PHYSRU_v3.sh
================================================================================
Step100-Disable_DG_Broker_GoldenGate_Configuration.txt
Step110-Check_Primary_Standby_Configuration.txt
Step120-Convert_PhysicalStandby_To_TransientLogicalStandby_using_physru_v3.sh.txt
Verifies that Data Guard Broker is disabled and FRA is configured.
Creates a guaranteed restore point
Converts the existing Physical Standby to a Logical Standby

================================================================================
2. Upgrade the transient logical standby using DBUA
================================================================================
Step200-Upgrade_TransientLogicalStandby_using_dbua.txt

================================================================================
3. Convert transient logical standby to Primary by running PHYSRU_v3.sh
================================================================================
Step300-Convert_TransientLogicalStandby_To_Primary_using_physru_v3.sh.txt
Executes a switchover making the upgraded standby database the primary database.
Executes a flashback of the original primary database to the guaranteed restore point and shuts it down.

ORIGINAL_PRIMARY is down and ORIGINAL_STANDBY is now NEW_PRIMARY.

================================================================================
4. Configure 11.2 ORIGINAL_PRIMARY for 19c
================================================================================
Step400-Configure_11g_ORIGINAL_PRIMARY_for_19c.txt

================================================================================
5. Upgrade 11.2 ORIGINAL_PRIMARY to 19c by running PHYSRU_v3.sh
================================================================================
Step500-Upgrade19c_ORIGINAL_PRIMARY_using_physru_v3.sh.txt
Start redo apply
Prompt whether to switch back to original configuration
Remove guaranteed restore points

https://drive.google.com/file/d/1bZs6E3x13Bsxb4P_Qrc8Wc21OfctPgdp/view?usp=sharing

Blog at WordPress.com.