Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 8 hours 3 min ago

grid19c_upgrade_has.rsp

Thu, 2020-08-06 21:16

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

Last Time You Tested Data Guard Was?

Sun, 2020-08-02 19:05

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]$
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5f275c4c98454', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Rolling Upgrades Using Physical Standby and physru_v3.sh

Sat, 2020-08-01 11:12

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 <sysdba user> <primary TNS alias> <physical standby TNS alias> <primary db unique name> <physical standby db unique name> <target version> 

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> alter system set cluster_database=true scope=spfile;

          2) Shutdown instance hawk.
          eg: SQL> 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

I Will Buy You Lunch To Solve dbua -silent [FATAL] [DBT-20061]

Fri, 2020-07-31 14:22

Currently upgrading 11.2 to 19.3 database using dbua silent.

Run preupgrade.jar and no issues detected.

Run preupgrade_fixups.sql

Run dbua -silent error resulted with [FATAL] [DBT-20061]

Per Doc ID 2246770.1, ignore the error which may be hazardous.

Actually, I have already figured our the root cause; however, the method I used may not always be available for all environments.

Will post solution in a week.

HINT: probable cause is in the output which did not occur for first run.

$ env|grep HOME
OLD_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
NEW_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

$ $OLD_HOME/jdk/bin/java -jar $NEW_HOME/rdbms/admin/preupgrade.jar TERMINAL TEXT FILE
==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-07-31T18:50:38
oracle@ol7-112-dg2:hawk:/home/oracle
$
	
*******************************************************

$ sqlplus / as sysdba @/u01/app/oracle/cfgtoollogs/hawk_stby/preupgrade/preupgrade_fixups.sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 31 18:52:21 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 7
Generated on:            2020-07-31 18:50:36

For Source Database:     HAWK
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  min_recovery_area_size    NO          Manual fixup required.
    2.  parameter_min_val         NO          Manual fixup recommended.
    3.  em_present                NO          Manual fixup recommended.
    4.  amd_exists                NO          Manual fixup recommended.
    5.  apex_manual_upgrade       NO          Manual fixup recommended.
    6.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    7.  exf_rul_exists            NO          Informational only.
                                              Further action is optional.
    8.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
OL7-112-DG2:(SYS@hawk_stby:LOGICAL STANDBY>
	
*******************************************************
	
$ $NEW_HOME/bin/dbua -silent \
> -sid $ORACLE_SID \
> -oracleHome $ORACLE_HOME \
> -recompile_invalid_objects TRUE \
> -upgradeTimezone TRUE \
> -emConfiguration NONE \
> -upgrade_parallelism 2
Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM
Performing Pre-Upgrade Checks...
============================
PRE- and POST- FIXUP ACTIONS
=============================
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM/hawk/upgrade.xml
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM/hawk/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM/hawk/postupgrade_fixups.sql
[FATAL] [DBT-20061] One or more of the pre-upgrade checks on the database have resulted into error conditions that must be addressed before proceeding.
   ACTION: Refer to the pre-upgrade results location for details: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM/hawk

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

$ ls -l /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-31_06-53-41PM/hawk
total 692
-rw-r-----. 1 oracle oinstall      1 Jul 31 18:53 checksBuffer.tmp
-rw-r-----. 1 oracle oinstall  41134 Jul 31 18:53 components.properties
-rw-r-----. 1 oracle oinstall  15085 Jul 31 18:53 dbms_registry_extended.sql
drwxr-x---. 3 oracle oinstall     21 Jul 31 18:53 oracle
-rw-r-----. 1 oracle oinstall  14051 Jul 31 18:53 parameters.properties
-rw-r-----. 1 oracle oinstall   8584 Jul 31 18:53 postupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall   7884 Jul 31 18:53 preupgrade_driver.sql
-rw-r-----. 1 oracle oinstall  13082 Jul 31 18:53 preupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall 101702 Jul 31 18:53 preupgrade_messages.properties
-rw-r-----. 1 oracle oinstall 461660 Jul 31 18:53 preupgrade_package.sql
-rw-r-----. 1 oracle oinstall   5215 Jul 31 18:53 sqls.log
drwxr-x---. 3 oracle oinstall     24 Jul 31 18:53 upgrade
-rw-r-----. 1 oracle oinstall  15082 Jul 31 18:53 upgrade.xml

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

DBUA Silent : Director Service Registration Error DBT-20060 DBT-08010 (Doc ID 2246770.1)	

Case 2 :
SEVERE: [FATAL] [DBT-20061] One or more of the pre-upgrade checks on the database have resulted into error conditions that must be addressed before proceeding.

./dbua -silent -sid <SID> -oracleHome $ORACLE_HOME -J-Doracle.install.dbua.validate.all=false

The Meaning Of Preupgrade Check Name

Wed, 2020-07-29 14:32

Upgrading 11.2.4 database with GoldenGate installed (don’t step on booby trap) to 19.3.

Does anyone know what the meaning of “Preupgrade Check Name” and wouldn’t be great of Oracle does not make you dig for it?

Please comment if you know the meaning of “Preupgrade Check Name”.

I will update post a week later with the info.

Run preupgrade_fixups.sql

oracle@db-fs-1:hawk:/home/oracle
$ sqlplus / as sysdba @/u01/app/oracle/cfgtoollogs/hawk/preupgrade/preupgrade_fixups.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 29 16:55:45 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 7
Generated on:            2020-07-29 16:54:11

For Source Database:     HAWK
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  parameter_min_val         NO          Manual fixup recommended.
    2.  em_present                NO          Manual fixup recommended.
    3.  amd_exists                NO          Manual fixup recommended.
    4.  apex_manual_upgrade       NO          Manual fixup recommended.
    5.  streams_setup             NO          Manual fixup recommended.
    6.  trgowner_no_admndbtrg     YES         None.
    7.  pre_fixed_objects         YES         None.
    8.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    9.  exf_rul_exists            NO          Informational only.
                                              Further action is optional.
   10.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

16:56:18 SYS @ hawk:>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@db-fs-1:hawk:/home/oracle
$

Run postupgrade_fixups.sql


oracle@db-fs-1:hawk:/home/oracle
$ sqlplus / as sysdba @ /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-07-29_07-50-43PM/hawk/postupgrade_fixups.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 29 21:16:52 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.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 7
Generated on:            2020-07-29 19:50:53

For Source Database:     HAWK
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    9.  old_time_zones_exist      YES         None.
   10.  dir_symlinks              YES         None.
   11.  post_dictionary           YES         None.
   12.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
   13.  upg_by_std_upgrd          NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.


Session altered.

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:/home/oracle
$

Let’s search for trgowner_no_admndbtrg – no luck!

oracle@db-fs-1:hawk:/u01/app/oracle/cfgtoollogs/hawk/preupgrade
$ grep trgowner_no_admndbtrg *.*
preupgrade_fixups.sql:    --    CHECK/FIXUP name: trgowner_no_admndbtrg
preupgrade_fixups.sql:    fixup_result := dbms_preup.run_fixup('trgowner_no_admndbtrg',6) AND fixup_result;
preupgrade_messages.properties:#    bspeckha    04/05/18 - update trgowner_no_admndbtrg_check action
preupgrade_package.sql:Rem    hvieyra     01/11/18 - Bug 27344799: Add fixup for check trgowner_no_admndbtrg_check.
preupgrade_package.sql:    FUNCTION trgowner_no_admndbtrg_check    (result_txt OUT CLOB) RETURN NUMBER;
preupgrade_package.sql:    FUNCTION trgowner_no_admndbtrg_fixup    (result_txt IN OUT VARCHAR2, pSqlcode IN OUT NUMBER) RETURN number;
preupgrade_package.sql:FUNCTION trgowner_no_admndbtrg_check (result_txt OUT CLOB) RETURN NUMBER
preupgrade_package.sql:        result_txt := get_failed_check_xml('trgowner_no_admndbtrg',
preupgrade_package.sql:END trgowner_no_admndbtrg_check;
preupgrade_package.sql:FUNCTION trgowner_no_admndbtrg_fixup (
preupgrade_package.sql:END trgowner_no_admndbtrg_fixup;
oracle@db-fs-1:hawk:/u01/app/oracle/cfgtoollogs/hawk/preupgrade
$

Create 19c RAC Standby Using RMAN

Wed, 2020-07-15 18:55

See RAC_19c_rman_duplicate_standby_same_sid.log

Confirmed!

*** Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations
that are managed by Oracle Restart, RAC One Node or RAC as the Broker will use the clusterware to restart an instance.

Rename ASM DiskGroup 19c

Sat, 2020-07-11 11:17
IMPORTANT: NO DB has been created !!!

Why is rename ASM DG?
1. Test RAC RMAN duplicate for standby similar to client environment.
2. Just because.

Rename Diskgroup name by renamedg command doesn't update the OCR (Doc ID 2392499.1)	

Bug 8740909  [11GR2-LNX-090726]OLD DISK GROUP RESOURCE NOT DELETED AFTER RENAMEDG

renamedg does not update resources, nor does renamedg update any file references within the database. 
Because of this, the original disk group resource is not automatically deleted. 
This resource can be manually deleted with Server Control Utility (SRVCTL).

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

[oracle@ol7-19-phx2 ~]$ asmcmd -V
asmcmd version 19.6.0.0.0
[oracle@ol7-19-phx2 ~]$

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

[oracle@ol7-19-phx1 ~]$ crsctl stat res -t -w "TYPE = ora.diskgroup.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@ol7-19-phx1 ~]$


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

[oracle@ol7-19-phx1 ~]$ asmcmd umount RECO
[oracle@ol7-19-phx2 ~]$ asmcmd umount RECO

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

[oracle@ol7-19-phx1 ~]$ asmcmd lsdg -g
Inst_ID  State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  NORMAL  N         512             512   4096  1048576     98301    47769            32767            7501              0             Y  CRS/
      2  MOUNTED  NORMAL  N         512             512   4096  1048576     98301    47769            32767            7501              0             Y  CRS/
      1  MOUNTED  EXTERN  N         512             512   4096  1048576     32767    32663                0           32663              0             N  DATA_PHX/
      2  MOUNTED  EXTERN  N         512             512   4096  1048576     32767    32663                0           32663              0             N  DATA_PHX/

[oracle@ol7-19-phx1 ~]$ crsctl stat res -t -w "TYPE = ora.diskgroup.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.RECO.dg(ora.asmgroup)
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@ol7-19-phx1 ~]$

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

[oracle@ol7-19-phx1 ~]$ renamedg dgname=RECO newdgname=FRA_PHX verbose=true

Parameters in effect:

         Old DG name       : RECO
         New DG name          : FRA_PHX
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: dgname=RECO newdgname=FRA_PHX verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
ERROR: submiting READ on /dev/sdg failed due to kgfknm error
ERROR: -20(27041 /dev/sdg)ERROR: submiting READ on /dev/sda failed due to kgfknm error
ERROR: -20(27041 /dev/sda)ERROR: submiting READ on /dev/sdc failed due to kgfknm error
ERROR: -20(27041 /dev/sdc)ERROR: submiting READ on /dev/sdf failed due to kgfknm error
ERROR: -20(27041 /dev/sdf)ERROR: submiting READ on /dev/sde failed due to kgfknm error
ERROR: -20(27041 /dev/sde)ERROR: submiting READ on /dev/sdd failed due to kgfknm error
ERROR: -20(27041 /dev/sdd)ERROR: submiting READ on /dev/sdb failed due to kgfknm error
ERROR: -20(27041 /dev/sdb)ERROR: submiting READ on /dev/sda1 failed due to kgfknm error
ERROR: -20(27041 /dev/sda1)ERROR: submiting READ on /dev/sda2 failed due to kgfknm error
ERROR: -20(27041 /dev/sda2)ERROR: submiting READ on /dev/sdb1 failed due to kgfknm error
ERROR: -20(27041 /dev/sdb1)Identified disk UFS:/dev/sdg1 with disk number:0 and timestamp (33103213 1600008192)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
ERROR: submiting READ on /dev/sdg failed due to kgfknm error
ERROR: -20(27041 /dev/sdg)ERROR: submiting READ on /dev/sda failed due to kgfknm error
ERROR: -20(27041 /dev/sda)ERROR: submiting READ on /dev/sdc failed due to kgfknm error
ERROR: -20(27041 /dev/sdc)ERROR: submiting READ on /dev/sdf failed due to kgfknm error
ERROR: -20(27041 /dev/sdf)ERROR: submiting READ on /dev/sde failed due to kgfknm error
ERROR: -20(27041 /dev/sde)ERROR: submiting READ on /dev/sdd failed due to kgfknm error
ERROR: -20(27041 /dev/sdd)ERROR: submiting READ on /dev/sdb failed due to kgfknm error
ERROR: -20(27041 /dev/sdb)ERROR: submiting READ on /dev/sda1 failed due to kgfknm error
ERROR: -20(27041 /dev/sda1)ERROR: submiting READ on /dev/sda2 failed due to kgfknm error
ERROR: -20(27041 /dev/sda2)ERROR: submiting READ on /dev/sdb1 failed due to kgfknm error
ERROR: -20(27041 /dev/sdb1)Identified disk UFS:/dev/sdg1 with disk number:0 and timestamp (33103213 1600008192)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/sdg1
Modifying the header
Completed phase 2
[oracle@ol7-19-phx1 ~]$

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

[oracle@ol7-19-phx1 ~]$ asmcmd mount FRA_PHX
[oracle@ol7-19-phx2 ~]$ asmcmd mount FRA_PHX

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

[oracle@ol7-19-phx1 ~]$ asmcmd lsdg FRA_PHX -g
Inst_ID  State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512             512   4096  1048576     32767    32663                0           32663              0             N  FRA_PHX/
      2  MOUNTED  EXTERN  N         512             512   4096  1048576     32767    32663                0           32663              0             N  FRA_PHX/

[oracle@ol7-19-phx1 ~]$ crsctl stat res -t -w "TYPE = ora.diskgroup.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.RECO.dg(ora.asmgroup)
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@ol7-19-phx1 ~]$

### For when DG is not online:
srvctl start diskgroup -diskgroup FRA_PHX -node "node_list"

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

[oracle@ol7-19-phx1 ~]$ srvctl remove diskgroup -g RECO
[oracle@ol7-19-phx1 ~]$ crsctl stat res -t -w "TYPE = ora.diskgroup.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA_PHX.dg(ora.asmgroup)
      1        ONLINE  ONLINE       ol7-19-phx1              STABLE
      2        ONLINE  ONLINE       ol7-19-phx2              STABLE
      3        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@ol7-19-phx1 ~]$

Q.E.D.

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5f0a4304a75c6', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

GoldenGate – Supplemental Logging Is A Mess

Tue, 2020-06-02 22:22

I was tasked to find supplemental logging details for Oracle database used with GoldenGate.

Note: this is not a pluggable database.

With ADD TRANDATA, use dba_log_groups and dba_log_group_columns.

With ADD SCHEMATRANDATA, use select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Basically, one would need to run the query with logmnr pipeline function for all the tables in the schema.

Here is one process I used.

Create info_schematrandata.prm

$ cat info_schematrandata.prm
dblogin USERID ggs, PASSWORD *
info schematrandata *

Run ggsci using info_schematrandata.prm (full path is required)

$ ggsci paramfile /home/oracle/working/dinh/info_schematrandata.prm > info_schematrandata.log

Here is example for results (actual contains 12 schemas)

$ grep -i enable info_schematrandata.log
2020-06-01 05:19:35  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT".
2020-06-01 05:19:35  INFO    OGG-01981  Schema level supplemental logging is enabled on schema "SCOTT" for all columns.

After finding the schemas, use logmnr pipeline function to find all the details.

select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

You can find demo with logmnr$always_suplog_columns at: GoldenGate 12c Features Found in 11.2.1.0.21 ???

References:

OGG: How To Log All Columns With Add Schematrandata To Get NOCOMPRESSUPDATES (Doc ID 1413142.1)

ADD SCHEMATRANDATA does not allow columns to be specified.
This enables logging of Primary Key columns only.
By default, updates are compressed.
In order to log all columns ADD TRANDATA would have to be used.
The ADD TRANDATA can be used in conjunction with ADD SCHEMATRANDATA to specify the non-primary key columns.

How to Check Supplemental Logging When ADD SCHEMATRANDATA is Enabled (Doc ID 1537837.1)

It is not listed in dba_log_groups or dba_log_group_columns.
select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Effects of ADD TRANDATA and ADD SCHEMATRANDATA on an Oracle databases’ Supplemental Logging (Doc ID 2070331.1)

Some useful commands from ggsci:

INFO TRANDATA [container.]owner.table (info trandata *) did not work
INFO SCHEMATRANDATA schema            (info schematrandata *)
LIST TABLES table                     (list tables SCOTT.*)

Note to self:

$ cat list_table.prm
dblogin USERID ggs, PASSWORD *
list tables SCOTT.*

$ ggsci paramfile /home/oracle/working/dinh/list_table.prm > list_table.log

$ grep '\.' list_table.log | egrep -iv 'found|ggsci'| grep -A 10000 "Successfully logged into database."|grep -v database > table.log

$ cat table.log
SCOTT.EMP
SCOTT.BONUS
SCOTT.DEPT

$ cat read.sh
#!/bin/bash
IFS="."
while read f1 f3
do
echo "select * from table(logmnr\$always_suplog_columns('$f1','$f3')) order by intcol;"
done < /home/oracle/working/dinh/table.log
exit

$ ./read.sh > /tmp/suplog.sql

$ head /tmp/suplog.sql
select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

$ cat suplog.sql
set numw 8 lines 200 timing off echo off pages 10000 trimsp on tab off
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a65 wrap
col owner for a20
col table_name for a20
col column_name for a30
col log_group_type for a20
col column_list for a80
col log_group_name for a30
col table_name for a30
spool Database_Supplemental_Logging_Details.log
pro ******** Database ********
SELECT
name,db_unique_name,open_mode,database_role,remote_archive,switchover_status,dataguard_broker,primary_db_unique_name
FROM v$database
;
pro ******** Database Supplemental Logging ********
SELECT
supplemental_log_data_min MIN,
supplemental_log_data_pk PK,
supplemental_log_data_ui UI,
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL"
FROM v$database
;
pro ******** Table Supplemental Logging ********
pro
pro ******** GoldenGate: ADD TRANDATA ********
SELECT
g.owner, g.table_name, g.log_group_name, g.log_group_type,
DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') always,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g, dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND g.log_group_name = c.log_group_name(+)
AND g.table_name = c.table_name(+)
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type, DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional')
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
;
pro ******** Schema Supplemental Logging ********
pro
pro ******** GoldenGate: ADD SCHEMATRANDATA ********
@/tmp/suplog.sql
exit

SQL Versus ASMCMD

Tue, 2020-05-26 23:10

This is a set of scripts to help on Oracle ASM Monitoring

Reviewing the SQL looks rather complicated especially when trying to find information for specific Disk Group.

Use one command line to retrieve data or create complex SQL to do the same?


Size_MB  is Bytes/1e6
Alloc_MB is Space/1e6

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> @asm.sql

                                                                                                                                                              System
ASM File Name / Volume Name / Device Name                                                  Size_MB           Alloc_MB File Type          Creation Date        Created?
------------------------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061                                     0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403                                43                 50        CONTROLFILE 31-OCT-2018 18:33:22    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.257.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.258.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/current.256.990939051                               43                 50        CONTROLFILE 31-OCT-2018 04:50:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.264.996655365                            32,212             32,247           DATAFILE 04-JAN-2019 08:42:44    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.319.996656095                             1,074              1,076           DATAFILE 04-JAN-2019 08:54:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AVAIL.277.990939537                                   701                703           DATAFILE 31-OCT-2018 04:58:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.271.990939353                            32,212             32,247           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.316.992686897                               315                317           DATAFILE 20-NOV-2018 10:21:37    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_PDA.321.1007139241                             11,035             11,037           DATAFILE 01-MAY-2019 16:54:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_IE.270.990939353                            3,821              3,823           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.269.990939199                           33,521             33,555           DATAFILE 31-OCT-2018 04:53:18    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.279.990939547                            6,946              6,948           DATAFILE 31-OCT-2018 04:59:06    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_ESS.284.990939589                          105                107           DATAFILE 31-OCT-2018 04:59:49    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_OPSS.285.990939591                     126                128           DATAFILE 31-OCT-2018 04:59:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_UMS.287.990939593                      703                705           DATAFILE 31-OCT-2018 04:59:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAU.289.990939595                           63                 65           DATAFILE 31-OCT-2018 04:59:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_MDS.282.990939585                          734                736           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.290.1010082787                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:07    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.292.1010082785                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.314.1010082781                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:00    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.318.1010082769                 32,212             32,247           DATAFILE 04-JUN-2019 18:32:48    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.326.1013130689                 13,342             13,344           DATAFILE 09-JUL-2019 01:11:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_STB.288.990939595                           10                 12           DATAFILE 31-OCT-2018 04:59:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_WLS.286.990939591                           63                 65           DATAFILE 31-OCT-2018 04:59:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_DT_TBS.280.990939551                           12,885             12,887           DATAFILE 31-OCT-2018 04:59:10    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_IDX_TBS.281.990939585                           1,074              1,076           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.274.990939395                              34,360             34,361           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.324.1028374119                             13,627             13,629           DATAFILE 30-DEC-2019 11:28:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/MBSYNC_UK_MCD.291.990939597                        25,166             25,200           DATAFILE 31-OCT-2018 04:59:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.267.990939163                              34,360             34,361           DATAFILE 31-OCT-2018 04:52:42    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.278.990939539                              33,580             33,589           DATAFILE 31-OCT-2018 04:58:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.325.1014480263                              6,107              6,109           DATAFILE 24-JUL-2019 16:04:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PERFSTAT.320.1001228979                             2,961              2,963           DATAFILE 26-FEB-2019 07:09:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.260.990939059                               34,341             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.268.990939163                                9,026              9,028           DATAFILE 31-OCT-2018 04:52:43    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.275.990939395                               32,212             32,247           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSTEM.259.990939059                               11,996             11,998           DATAFILE 31-OCT-2018 04:50:58    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.261.990939059                             34,360             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.283.1041158311                             4,295              4,297           DATAFILE 23-MAY-2020 10:38:30    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.263.990939063                             34,360             34,361           DATAFILE 31-OCT-2018 04:51:03    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.323.1041061103                            34,359             34,361           DATAFILE 22-MAY-2020 07:38:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/USERS.262.990939059                                     7                  7           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMDATA.272.990939357                             27,259             27,281           DATAFILE 31-OCT-2018 04:55:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMINDX.273.990939393                              1,074              1,076           DATAFILE 31-OCT-2018 04:56:32    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397                   0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_1.293.990939821                              1,074              1,076          ONLINELOG 31-OCT-2018 05:03:41    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_10.300.990939985                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:25    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_11.301.990940017                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_12.302.990940049                             1,074              1,076          ONLINELOG 31-OCT-2018 05:07:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_13.303.990940081                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_14.304.990940083                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:02    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_2.294.990939853                              1,074              1,076          ONLINELOG 31-OCT-2018 05:04:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_21.305.990940085                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_22.306.990940115                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_23.307.990940179                             1,074              1,076          ONLINELOG 31-OCT-2018 05:09:38    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_24.308.990940241                             1,074              1,076          ONLINELOG 31-OCT-2018 05:10:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_25.309.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_26.310.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:13    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_3.295.990939915                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:15    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_4.296.990939917                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_5.297.990939919                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:19    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_6.298.990939951                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_9.299.990939953                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/FMWEURPD12_IAS_TEMP.317.991419415                  18,822             18,824           TEMPFILE 05-NOV-2018 18:16:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/NA_ARCHIVE_TEMP.322.1007140587                     10,737             10,740           TEMPFILE 01-MAY-2019 17:16:26    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/TEMP.315.991419415                                 34,359             34,361           TEMPFILE 05-NOV-2018 18:16:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/dr1ORCL_SID.dat                                                  0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/snapcf_ORCL_SID.f                                               43                 50        CONTROLFILE 31-OCT-2018 18:33:22    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/spfileORCL_SID.ora                                               0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    N
                                                                                ------------------ ------------------
                                                                                           736,613            737,115

                                                                                ------------------ ------------------
Grand Total:                                                                               736,613            737,115

71 rows selected.

SQL>

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

$ asmcmd lsdg -g ORCL_SID_DATA
Inst_ID  State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/
      2  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/

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

$ asmcmd ls -lsh ORCL_SID_DATA/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/:
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/:
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/:
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  ORCL_SID_PHX1RAC.312.990949397

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/:
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953

+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/:
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/:
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  N            4K        3          12K           1M  dr1ORCL_SID.dat => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  N           16K     2635        41.2M          48M  snapcf_ORCL_SID.f => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  N           512       17         8.5K           1M  spfileORCL_SID.ora => +ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061

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

$ asmcmd ls -lsh ORCL_SID_DATA/*/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  MBEURINP_SPMBRAC.312.990949397
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ecded202cec1', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Rename Table During DataPump Import

Tue, 2020-05-19 18:07

At source perform export for tables=SCOTT.SALES, SCOTT.ORDERS

$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR tables=SCOTT.SALES,SCOTT.ORDERS  dumpfile=SCOTT.dmp logfile=SCOTT.log

Preference is to use parameter file vs having to take into consideration and dealing with slash.

Import will rename table SALES to SALES_BACKUP and ORDERS to ORDERS_BACKUP for SCOTT schema.

$ cat impdp_table.par
userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=SCOTT.dmp
remap_table=SCOTT.SALES:SALES_BACKUP
remap_table=SCOTT.ORDERS:ORDERS_BACKUP
table_exists_action=SKIP
$ impdp parfile=impdp_table.par

Import: Release 18.0.0.0.0 - Production on Tue May 19 11:37:51 2020
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_table.par

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."ORDERS_BACKUP"  11.02 GB 84493879 rows
. . imported "SCOTT"."SALES_BACKUP"   44.60 MB  366568 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX2" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX1" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_INDX1" already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type CONSTRAINT:"SCOTT"."SALES_PK" already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_FULL_01" completed with 7 error(s) at Tue May 19 11:39:43 2020 elapsed 0 00:01:50

If the intention is to TRUNCATE or MERGE SCOTT.SALES AND SCOTT.ORDERS using BACKUP, then errors can be ignored.

If not, then failed constraints and indexes will need to be created accordingly.

Oracle Data Pump Import – REMAP_TABLE

 

ACFS Mystery

Mon, 2020-05-18 17:49

From ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

ACFS and AFD 12.2.0.1 Supported Platforms
Vendor          :  RedHat Linux 
Version         : 7 
Update / Kernel : Update 6 3.10.0-957 and later / 3.10.0 Red Hat kernels X86_64	
ACFS Bug or PSU	: 12.2.0.1.191015 (Base Bug 29963428)

Where: “Base” (at the “Bug or PSU” column) stands for the "12.2.0.1 Grid Infrastructure" release

Check current environment:

$ $ORACLE_HOME/OPatch/opatch lspatches
26928563;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:170711) (26928563)
26925644;OCW RELEASE UPDATE 12.2.0.1.0(ID:171003) (26925644)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)
26710464;Database Release Update : 12.2.0.1.171017 (26710464)
26247490;ACFS Interim patch for 26247490

OPatch succeeded.

$ $ORACLE_HOME/OPatch/opatch lsinventory|grep 29963428
$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed|grep 29963428

$ uname -r
3.10.0-1062.12.1.el7.x86_64

ACFS should not be supported since 29963428 has not been applied, but  it is.


# rpm -q kernel --last
kernel-3.10.0-1062.12.1.el7.x86_64            Mon 11 May 2020 14:39:40
kernel-3.10.0-957.5.1.el7.x86_64              Thu 31 Oct 2019 16:26:21
kernel-3.10.0-862.2.3.el7.x86_64              Mon 28 May 2018 11:27:22

# lsmod | grep oracle
oracleacfs 4626640 2
oracleadvm 776776 8
oracleoks 662840 2 oracleacfs,oracleadvm

# modinfo oracleoks
filename: /lib/modules/3.10.0-1062.12.1.el7.x86_64/weak-updates/usm/oracleoks.ko
author: Oracle Corporation
license: Proprietary
rhelversion: 7.4
srcversion: C5110F596402987AF02F894
depends:
vermagic: 3.10.0-693.el7.x86_64 SMP mod_unload modversions
signer: Oracle Linux RHCK Module Signing Key
sig_key: DD:99:5B:15:5C:19:B3:A7:C3:EF:77:07:B9:69:E2:5F:96:39:66:6E
sig_hashalgo: sha256

# acfsdriverstate version
ACFS-9325: Driver OS kernel version = 3.10.0-693.el7.x86_64(x86_64).
ACFS-9326: Driver Oracle version = RELEASE.
ACFS-9212: Driver build version = 12.2.0.1 (ACFSRU)..

# acfsdriverstate installed
ACFS-9203: true

# acfsdriverstate supported
ACFS-9200: Supported

# acfsroot version_check
ACFS-9316: Valid ADVM/ACFS distribution media detected at: '/a01/app/grid/12.2.0/usm/install/Oracle/EL7/x86_64/3.10.0-693/3.10.0-693-x86_64/bin'

# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.2.0.1.0]

# acfsutil registry
Mount Object:
  Device: /dev/asm/shared-57
  Mount Point: /shared
  Disk Group: shared
  Volume: shared
  Options: none
  Nodes: all
  Accelerator Volumes:

# acfsutil info fs
/shared
    ACFS Version: 12.2.0.1.0
    on-disk version:       46.0
    compatible.advm:       12.2.0.0.0
    ACFS compatibility:    12.2.0.0.0
    flags:        MountPoint,Available
    mount time:   Thu May 14 23:16:59 2020
    mount sequence number: 0
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   987842478080  ( 920.00 GB )
    total free:   849604771840  ( 791.26 GB )
    file entry table allocation: 42336256
    primary volume: /dev/asm/shared-57
        label:
        state:                 Available
        major, minor:          252, 29185
        logical sector size:   512
        size:                  987842478080  ( 920.00 GB )
        free:                  849604771840  ( 791.26 GB )
        metadata read I/O count:         80140
        metadata write I/O count:        269
        total metadata bytes read:       328335360  ( 313.12 MB )
        total metadata bytes written:    1187840  (   1.13 MB )
        ADVM diskgroup:        shared
        ADVM resize increment: 536870912
        ADVM redundancy:       unprotected
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

$ crsctl stat res -t -w "TYPE = ora.volume.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.advm
               ONLINE  ONLINE       xxxxxxx-01               STABLE
               ONLINE  ONLINE       xxxxxxx-02               STABLE
--------------------------------------------------------------------------------

$ crsctl stat res -t -w "TYPE = ora.acfs.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.acfs
               ONLINE  ONLINE       lp-ora1-rh               mounted on /shared,STABLE
               ONLINE  ONLINE       lp-ora2-rh               mounted on /shared,STABLE
--------------------------------------------------------------------------------

Do you see what’s wrong and why ACFS is supported?

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ec3117d25de0', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

RMAN Restore From Tape

Wed, 2020-05-13 22:56

In my previous post, Testing RMAN Restore Validate From Tape , now I will perform actual restore.

There is requirement to test backup to the new tape library before implementing in production by restoring the database from tape backup.

Database resides on file system. Instead of drop database, shutdown abort, delete data files and control files from disk.

Archived logs resides in FRA and was not deleted which resulted in complete recovery without having to use set until time.

set until time "to_date('2020-05-13 12:10:00','YYYY-MM-DD HH24:MI:SS')";

This is a condense output for restore.

$ grep -i dbid rman_archbackup_HAWKEYE_arch_Wed_202005131210.log
connected to target database: HAWKEYE (DBID=2937483440)

$ rman @ restore.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 15:37:09 2020

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

RMAN> set echo on
2> connect target;
3> run {
4> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
5> allocate channel ch2 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
6> allocate channel ch3 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
7> set dbid 2937483440;
8> restore controlfile from autobackup;
9> alter database mount;
10> restore database;
11> recover database;
12> alter database open resetlogs;
13> release channel ch1;
14> release channel ch2;
15> release channel ch3;
16> }
17> exit
echo set on

connected to target database: HAWKEYE (not mounted)

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=209 device type=SBT_TAPE
channel ch1: Database Application Agent Oracle v19.4.0.0

allocated channel: ch2
channel ch2: SID=217 device type=SBT_TAPE
channel ch2: Database Application Agent Oracle v19.4.0.0

allocated channel: ch3
channel ch3: SID=225 device type=SBT_TAPE
channel ch3: Database Application Agent Oracle v19.4.0.0

executing command: SET DBID

Starting restore at 2020-05-13 15:37:11
channel ch1: looking for AUTOBACKUP on day: 20200513
channel ch1: AUTOBACKUP found: c-2937483440-20200513-01
channel ch2: looking for AUTOBACKUP on day: 20200513
channel ch2: skipped, AUTOBACKUP already found
channel ch3: looking for AUTOBACKUP on day: 20200513
channel ch3: skipped, AUTOBACKUP already found
channel ch1: restoring control file from AUTOBACKUP c-2937483440-20200513-01
channel ch1: control file restore from AUTOBACKUP complete
output file name=/oradata/HAWKEYE/controlfile/control_01.ctl
output file name=/orafra/HAWKEYE/HAWKEYE/controlfile/control_02.ctl
Finished restore at 2020-05-13 15:37:21

database mounted

Starting restore at 2020-05-13 15:37:26

Starting implicit crosscheck backup at 2020-05-13 15:37:26
Finished implicit crosscheck backup at 2020-05-13 15:37:27

Starting implicit crosscheck copy at 2020-05-13 15:37:27
Finished implicit crosscheck copy at 2020-05-13 15:37:27

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece 8juvv2b1_1_1
channel ch1: piece handle=8juvv2b1_1_1 tag=LEVEL0
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:17:55
Finished restore at 2020-05-13 15:55:24

Starting recover at 2020-05-13 15:55:24

channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: reading from backup piece a6v01jp8_1_1
channel ch1: piece handle=a6v01jp8_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25

starting media recovery

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece a7v01k17_1_1

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece a9v023is_1_1

channel ch3: starting archived log restore to default destination
channel ch3: restoring archived log
channel ch3: reading from backup piece aav023it_1_1

channel ch1: piece handle=a7v01k17_1_1 tag=LEVEL1
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
channel ch1: reading from backup piece abv023iu_1_1

channel ch2: piece handle=a9v023is_1_1 tag=ARCH
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:02

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
channel ch2: reading from backup piece acv023iv_1_1

channel ch3: piece handle=aav023it_1_1 tag=ARCH
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:00:02

media recovery complete, elapsed time: 00:00:05

Finished recover at 2020-05-13 15:56:56

database opened

released channel: ch1

released channel: ch2

released channel: ch3

Recovery Manager complete.
$
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 13 16:00:32 2020

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

connected to target database: HAWKEYE (DBID=2937483440)</pre>
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HAWKEYE 2937483440 PARENT 6278186746 2017-05-22 10:50:52
2 2 HAWKEYE 2937483440 PARENT 6278189261 2017-05-22 11:05:05
3 3 HAWKEYE 2937483440 CURRENT 6747789335 2020-05-13 15:56:56

RMAN> exit

Recovery Manager complete.
$

Testing RMAN Restore Validate From Tape

Tue, 2020-05-12 08:17

Short and simple method to validate backup.

oracle@hawk:/home/oracle$ rman checksyntax @ restore_validate_idpa_ddbea.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Tue May 12 07:24:57 2020

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

RMAN> set echo on
2> connect target;
3> show all;
4> list backup summary;
5> report schema;
6> run {
7> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
8> allocate channel ch2 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
9> allocate channel ch3 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
10> crosscheck backup;
11> restore validate spfile;
12> restore validate controlfile;
13> restore database preview;
14> restore validate database;
15> recover database preview;
16> }
17> exit
The cmdfile has no syntax errors

Recovery Manager complete.
oracle@hawk:/home/oracle$
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ebad88652a48', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

nohup vs screen

Sun, 2020-05-10 15:12

While I have played a little with screen , my preference is nohup since the output from screen is so ugly.

With that being said, it would be nice for application designs to be resumable, e.g. Shocking opatchauto resume works after auto-logout

There were discussions about running many SQLs where each SQL is run manually, check for error before running the next SQL; hence, screen was used.

Why not trap for error and exit vs manually checking?

[oracle@ol7-121-dg3 ~]$ cat error.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE
set echo on
select sysdate from dual;
select * from notable;
select database_role from v$database;
exit

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba @ error.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:48:46 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2020-05-10 17:48:46
OL7-121-DG3:(SYS@hawkc:PRIMARY> select * from notable;
select * from notable
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

Another reason for using screen is to run stored procedure.

But this can be performed using nohup too as demonstrated.

[oracle@ol7-121-dg3 ~]$ export filename=test
[oracle@ol7-121-dg3 ~]$ cat > $filename.sql < set serverout on echo on
> exec dbms_output.put_line('test');
> exit
> EOF

[oracle@ol7-121-dg3 ~]$ ls -l $filename.sql
-rw-r--r--. 1 oracle oinstall 65 May 10 17:30 test.sql
[oracle@ol7-121-dg3 ~]$ cat $filename.sql
set serverout on echo on
exec dbms_output.put_line('test');
exit

[oracle@ol7-121-dg3 ~]$ nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1 &
[1] 8422

[oracle@ol7-121-dg3 ~]$
[1]+  Done                    nohup sqlplus "/ as sysdba" @ $filename.sql > $filename.log 2>&1

[oracle@ol7-121-dg3 ~]$ ls -l $filename.*
-rw-r--r--. 1 oracle oinstall 616 May 10 17:30 test.log
-rw-r--r--. 1 oracle oinstall  65 May 10 17:30 test.sql

[oracle@ol7-121-dg3 ~]$ cat $filename.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 10 17:30:53 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> exec dbms_output.put_line('test');
test
OL7-121-DG3:(SYS@hawkc:PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

It’s possible I will adopt screen at one point; however, my preference is still nohup.

Notes on SQL Profiles vs SQL Plan Baselines

Wed, 2020-05-06 17:48

It’s possible to have both SQL Profiles and SQL Plan Baselines.

SQL PROFILES    : Adjust Optimizer cardinality estimations
SPM BASELINES : Direct SQL to follow specific execution plan

Researh on SQL Profiles and SQL Plan Baselines

What is the difference between SQL Profiles and SQL Plan Baselines? (June 17, 2017)
Nice table comparision between SQL Profiles and Plan Baselines
https://sateeshv-dbainfo.blogspot.com/2017/06/what-is-difference-between-sql-profiles.html

What is the difference between SQL Profiles and SQL Plan Baselines?
https://blogs.oracle.com/optimizer/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines

Using SQL Plan Management to Control SQL Execution Plans
https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
https://blogs.oracle.com/optimizer/sql-plan-management-part-1-of-4-creating-sql-plan-baselines

SQL Plan Management (Part 2 of 4) SPM Aware Optimizer
https://blogs.oracle.com/optimizer/sql-plan-management-part-2-of-4-spm-aware-optimizer

SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines
https://blogs.oracle.com/optimizer/sql-plan-management-part-3-of-4:-evolving-sql-plan-baselines

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features (February 2, 2009)
https://blogs.oracle.com/optimizer/sql-plan-management-part-4-of-4:-user-interfaces-and-other-features

New SQL Plan Management White Paper (January 30, 2017)
https://blogs.oracle.com/optimizer/new-sql-plan-management-white-paper

Upgrade to Oracle Database 12c and Avoid Query Regression (December 3, 2015)
https://blogs.oracle.com/optimizer/upgrade-to-oracle-database-12c-and-avoid-query-regression

Repairing SQL Performance Regression with SQL Plan Management (October 15, 2019)
https://blogs.oracle.com/optimizer/repairing-sql-performance-regression-with-sql-plan-management

glogin.sql for Data Guard Environment

Sat, 2020-05-02 17:19

I got tired for seeing for db_name vs db_unique_name for sql prompt connecting with sqlplus for Data Guard environment.

Update $ORACLE_HOME/sqlplus/admin/glogin.sql

column NAME_COL_PLUS_SHOW_PARAM format a40 wrap
column VALUE_COL_PLUS_SHOW_PARAM format a80 wrap
set lines 200 echo on trimsp on tab off pages 10000 serverout on size 1000000 feedback off verify off term off echo off arraysize 5000
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@'||SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')||':'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr clear
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on

Test:

[oracle@ol7-121-dg3 ~]$ . oraenv <<< hawk
ORACLE_SID = [hawk] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-121-dg3 ~]$ sysresv|tail -1
Oracle Instance alive for sid "hawk"

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 2 22:16:36 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5eadf1f2e56f7', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

When To Use dgmgrl / vs dgmgrl sys@tns

Sat, 2020-05-02 10:41

There’s been discussion on twitter about using dgmgrl / vs dgmgrl sys@tns.

For the most part, I typically use dgmgrl sys@tns only for switch over and fail over.

Update: Should use dgmgrl sys@tns when there is an update to the database role, i.e. switch over, fail over, convert

Even Oracle’s documentation (Doc ID 278641.1) uses dgmgrl /

================================================================================
ORA-1031 Insufficient Privileges During Switchover via DGMGRL (Doc ID 740327.1)
================================================================================

CAUSE
Customer connected to database using o/s authentication from dgmgrl that resulted in ORA-01031
DGMGRL> connect /

SOLUTION
When we perform switchover using DGMGRL that requires database restarts, one must connect to dgmgrl by explicitly specifying a valid SYSDBA username/password and not using the OS Authentication.
DG Broker cannot complete a switchover using ‘connect /’ since it doesn’t have the credentials to restart the standby after shutting it down.

DGMGRL> connect sys/<password>
DGMGRL> connect sys/<pwd>@<connect string>

================================================================================
DGMGRL>CONVERT TO PHYSICAL STANDBY Fails With ORA-01031 (Doc ID 2398886.1)
================================================================================

CAUSE
The connection to the database through dgmgrl utility was not using the TNS_ALIAS
dgmgrl /

SOLUTION
Connect to the primary database through dgmgrl in order to have the convert command successfully executed:
dgmgrl sys@<primary tns_alias>

================================================================================
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)
================================================================================

1. Disable REDO Transport on Primary
1.1 Disable log shipping using DGMGRL.

If DG broker in place it is mandatory to disable log shipping via DG broker.
DGMGRL> connect /

Still do not know when dgmgrl sys@tns is absolutely necessary.

dgmgrl / works fine for monitoring, editing configuration, and modifying transport/apply.

In summary, use what makes you comfortable.

 

AutoUpgrade What I Did Not Know

Wed, 2020-04-29 12:51

Deploy and Upgrade Postupgrade Checks and Fixes

After an upgrade completes with either Deploy or Upgrade modes, AutoUpgrade performs postupgrade checks.
It provides a process where you can enable your custom scripts to be run on each of the upgraded databases,
in accordance with the configuration instructions you provide in the AutoUpgrade configuration file,
and also can run automatic postupgrade fixups as part of the postupgrade process.

In Deploy mode, AutoUpgrade also confirms that the upgrade has succeeded,
and moves database files such as sqlnet.ora, tnsname.ora, and listener.ora
from the source home to the target home.

After these actions are complete, the upgraded Oracle Database release is started in the new Oracle home.

Reference: About Oracle Database AutoUpgrade

Environments:

Source: Database Apr 2019 Release Update : 12.2.0.1.190416 (29314339)
Target: Database Release Update : 19.3.0.0.190416 (29517242)

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version
build.hash 6010a62
build.version 19.8.1
build.date 2020/04/01 16:18:24
build.max_target_version 19
build.type production

Why is moving sqlnet.ora, tnsnames.ora, and listener.ora from the source home to the target home a problem?

There were multiple databases running from the same home and created application issues.

Ironically, I just recently had discussion about using TNS_ADMIN to store sqlnet.ora, tnsname.ora, and listener.ora in central locations vs ORACLE_HOME.

Now curious if this will cause AutoUgrade to fail?

Overview of Local Naming Parameters

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory.
Oracle Net will check the other directories for the configuration file.

For example, the order checking the tnsnames.ora file is as follows:

The directory specified by the TNS_ADMIN environment variable.
If the file is not found in the directory specified, then it is assumed that the file does not exist.
If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

Reference: 6.1 Overview of Local Naming Parameters

Thanks to a super smart team member (who wants to be anonymous) for sharing.

Playing With lsof

Wed, 2020-04-29 07:53

lsof – list open files

This is a continuation of How To Check ORACLE_HOME Is Being Used

Check for count of open files:

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
550

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
56
[root@db-fs-1 ~]#

Shutdown GoldenGate and Kill pmon

[root@db-fs-1 ~]# . oraenv <<< hawk
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@db-fs-1 ~]# cd /u01/app/ggs/
[root@db-fs-1 ggs]# ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06


GGSCI (db-fs-1) 2> stop *

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (db-fs-1) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:00:05


GGSCI (db-fs-1) 4> stop mgr !

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT1        00:00:00      00:00:17


GGSCI (db-fs-1) 6> exit

[root@db-fs-1 ggs]# ps -ef|grep [p]mon
oracle   17864     1  0 Apr28 ?        00:00:08 ora_pmon_hawk
[root@db-fs-1 ggs]# kill -9 17864

Check for count of open files:

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
15

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs|wc -l
6
[root@db-fs-1 ggs]#

Check for open files:
cwd – some process is in directory /u01/app/ggs
DUH! That would be me.

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
bash    29106 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29913 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29914 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
COMMAND PID   USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
tnslsnr 876 oracle  txt    REG  249,2   972792  918631 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr
tnslsnr 876 oracle  mem    REG  249,2   241443  923131 /u01/app/oracle/11.2.0.4/db1/lib/libclsra11.so
tnslsnr 876 oracle  mem    REG  249,2 17647384  923129 /u01/app/oracle/11.2.0.4/db1/lib/libhasgen11.so
tnslsnr 876 oracle  mem    REG  249,2  3354840  923133 /u01/app/oracle/11.2.0.4/db1/lib/libocrb11.so
tnslsnr 876 oracle  mem    REG  249,2  1612720  923132 /u01/app/oracle/11.2.0.4/db1/lib/libocr11.so
tnslsnr 876 oracle  mem    REG  249,2   156012  923134 /u01/app/oracle/11.2.0.4/db1/lib/libocrutl11.so
tnslsnr 876 oracle  mem    REG  249,2    12787  919774 /u01/app/oracle/11.2.0.4/db1/lib/libskgxn2.so
tnslsnr 876 oracle  mem    REG  249,2    59021  920648 /u01/app/oracle/11.2.0.4/db1/lib/libnque11.so
tnslsnr 876 oracle  mem    REG  249,2  7996693  923239 /u01/app/oracle/11.2.0.4/db1/lib/libnnz11.so
tnslsnr 876 oracle  mem    REG  249,2 53775272  920645 /u01/app/oracle/11.2.0.4/db1/lib/libclntsh.so.11.1
tnslsnr 876 oracle  mem    REG  249,2   148728  919817 /u01/app/oracle/11.2.0.4/db1/lib/libons.so
tnslsnr 876 oracle    3r   REG  249,2    52224 1057224 /u01/app/oracle/11.2.0.4/db1/rdbms/mesg/diaus.msb
tnslsnr 876 oracle    5r   REG  249,2    12288  920670 /u01/app/oracle/11.2.0.4/db1/network/mesg/nlus.msb
tnslsnr 876 oracle    7r   REG  249,2    47104  919729 /u01/app/oracle/11.2.0.4/db1/network/mesg/tnsus.msb
[root@db-fs-1 ggs]#

Kill tns, change directory, and check for open files:

[root@db-fs-1 ggs]# ps -ef|grep tns
root        22     2  0 Apr28 ?        00:00:00 [netns]
oracle     876     1  0 Apr28 ?        00:00:03 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr LISTENER -inherit
root     29944 29106  0 14:36 pts/0    00:00:00 grep --color=auto tns

[root@db-fs-1 ggs]# kill -9 876

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
[root@db-fs-1 ggs]# cd

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
0

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
0
[root@db-fs-1 ~]#

Pages