Thinking Out Loud

May 18, 2020

ACFS Mystery

Filed under: 12.2,ACFS — mdinh @ 10:49 pm

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?

May 2, 2020

When To Use dgmgrl / vs dgmgrl sys@tns

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 3:41 pm

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.

 

May 1, 2020

12.1 Dataguard Multiple Standby Databases Switchover Using DGMGRL

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 2:28 am

Followup from How To Create New Standby Using Active Standby Duplication From Existing Standby

There are too many contents to post to blog.

Note: 12c = 12.1 and 12.2

Access 12c Dataguard Multiple Standby Databases Switchover Using DGMGRL for more details.

Thanks to CrossMyP4th on Twitter about Password file not syncing between primary and standby. As of 12.2, this is automatic.

What can happen when password files are not in sync: 12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby

12.1 LOG_ARCHIVE_TRACE

A useful enhancement in 12cR2 is that password files are automatically synchronized in a Data Guard environment: Auto Sync for Password Files in #Oracle 12.2 Data Guard

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)

12c: Data Guard Physical Standby – Managing password files in a RAC Physical Standby (Doc ID 1984091.1)

April 17, 2020

How To Create New Standby Using Active Standby Duplication From Existing Standby

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 8:46 pm

Basically, my insanity is from my experience as Senior Tax Advisor and investment background.

Planning and organization will simplify processes in the long run.

I will share my thoughts for implementing Data Guard having two standby databases.

Data Guard configurations will be for the following hosts: LAX, PHX, SLC

  1. ORACLE_SID and db_name will be the same value.
  2. db_unique_name and service_names will be the same value.
  3. Listener will be LISTENER.
  4. TNS entries (alias) will be created for service_names and listener.
  5. Data Guard Broker is being used with Oracle Restart

Hostname: LAX (PRIMARY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/LAX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX

dg_config=(LAX,PHX,SLC)
fal_server='PHX,SLC'

Hostname: PHX (CURRENT STANDBY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKA,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/PHX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX

dg_config=(LAX,PHX,SLC)
fal_server='LAX,SLC'

Hostname: SLC (NEW STANDBY) : OEL7

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/SLC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC

dg_config=(LAX,PHX,SLC)
fal_server='LAX,PHX'

I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!

I did so for consistency and to avoid having to make too many changes.

As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.

Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”

How does this relate to Data Guard and creating standby databases?

Always create database having db_name different from db_unique_name.

One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”

The demonstration will be using Option #2.

One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.

CONFIGURE ARCHIVELOG DELETION TO NONE

There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51

LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';

PHX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7

Apply patch as required.

SLC : Create pfile and startup nomount database

Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.

$ cat initHAWK.ora
*.db_name='HAWK'
*.db_flash_cache_size='0'
*.db_flash_cache_file='+DATA/flashfile.dat'

FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME

tnsping LAX; tnsping PHX; tnsping SLC
tnsping LISTENER_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC

FOR ALL HOSTS : Verify connect using TNS as SYSDBA

sqlplus sys@LAX as sysdba
sqlplus sys@PHX as sysdba
sqlplus sys@SLC as sysdba

PHX : Perform RMAN Active DB Duplication

$ rman checksyntax @ duplicate.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020

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

RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors

Recovery Manager complete.

$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &

PHX : Add 2nd Standby (SLC) to DG Broker Configuration

Better not to use dgmgrl / as it may be problematic.

$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@PHX
DGMGRL> add database SLC as connect identifier is SLC;
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database SLC
DGMGRL> exit

SLC : Move spfile to ASM

$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora

SQL> create pfile='/tmp/init@.ora' from spfile;
File created.

SQL> ! ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora

SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora';
File created.

SQL>

SLC : Add database to Oracle Oracle Restart

$ export ORACLE_SID=HAWK
$ export ORACLE_UNQNAME=SLC

$ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \
  -spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \
  -role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE

$ srvctl config database -d $ORACLE_UNQNAME
$ srvctl start database -d $ORACLE_UNQNAME
$ srvctl status database -d $ORACLE_UNQNAME -v

$ ps -ef|grep [p]mon

PHX/SLC : Review resource

crsctl stat res -t -w "TYPE = ora.database.type"
crsctl stat res -t -w "TYPE = ora.service.type"

LAX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

PHX/SLC: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

REFERENCES:

What’s New in Oracle Data Guard?

New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.

Data Guard Broker 12c Release 1 (12.1.0.2)

The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:

ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT

Configuring RMAN in a Data Guard Environment

CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.

  New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.

CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.

UPDATES:

The only consistency with Oracle is that it’s inconsistent.

*** 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 On Node or RAC as the Broker will use the clusterware to restart an instance.

*** How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)

*** Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
If “Duplicate from Active Database” is used , you must connect using ‘TNS alias’ to both, Target and AUXiliary Instance.
Therefore, the AUXiliary instance should have a static listener configured like:
$ rman target sys/@ auxiliary sys/@

To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount.

For this recommendation is to create a ‘Static Listener’ entry for the AUX instance.

The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it’s not registered with listener.
PMON process registers the instances with the listener when they are in MOUNT status, while instances in NOMOUNT status appear in listener as BLOCKED.

In addition, RMAN will shutdown the auxiliary instance several times during the execution.
Therefore, the connection may succeed initially, then fail with the error later in the execution.

*** Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)

*** Bug 11715084 : ACTIVE DUPLICATE SHOULD WORK WHEN CONNECTED TO STANDBY AS SOURCE DB
Updated : 25-Feb-2020. Fixed in Product Version 12.1

*** Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)

Q.E.D.

April 5, 2020

DATAGUARD sqlnet.ora NAMES.DEFAULT_DOMAIN

Filed under: 12c,Dataguard — mdinh @ 5:20 am

If you just want the solution, then read Database Startup Fails With ORA-00119 (Doc ID 471767.1)

From standby database, startup mount resulted in the following errors:

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'

When setting local_listener, my preference is:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

However, some implementations will use the following:

alter system set local_listener=LISTENER scope=spfile sid='*';

There are pros and cons to both.

When using LISTENER with tnsnames, modifications can be performed from tnsnames.ora without having to modify database parameters.

However, it’s not forgiving when there are misconfiguration.

Demo 1:
Modify local_listener and restart DB.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
remote_listener                      string
SQL>

Demo 2:
Modify local_listener and restart DB failed. Modify sqlnet.ora (delete NAMES.DEFAULT_DOMAIN=world).

### This is a bad omen and no changes should be made to DB until tnsping is resolved.
[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:32:05

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

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

### Restart DB FAILED
SQL> alter system set local_listener=LISTENER scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'
SQL>

That’s not good!

Check sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
[oracle@ol7-121-dg2 ~]$

Did you see the problem? Not sure why NAMES.DEFAULT_DOMAIN=world was set and remove solved the issue.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

Demo 3:
Rollback sqlnet.ora (add NAMES.DEFAULT_DOMAIN=world), modify tnsnames.ora (LISTENER.world), and restart DB.

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)

NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:54:52

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

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
LISTENER.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))

hawka.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

hawkb.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 05:04:18

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

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
OK (0 msec)

[oracle@ol7-121-dg2 ~]$

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

As demonstrated, having more options is not always good as it can lead to more likelihood for errors. Chose your evil wisely.

April 4, 2020

DATAGUARD Using DBCA Silent Mode Is Not Setting DB_UNIQUE_NAME

Filed under: 12c,Dataguard — mdinh @ 11:30 pm

Unfortunately, db_name=db_unique_name which is not ideal for Data Guard environment.

Even without Data Guard, my preference is to have db_unique_name different from db_name.

Typically, I like to append letter versus number to db_unique_name to avoid confusion with RAC instance.

Another option is using airport code; however, this will be inaccurate when database is migrated to new location.

If I can help it, then I would like implementation to be robust.

To resolve the issue and have db_unique_name different from db_name,
add -initparams db_unique_name=${NODE1_DB_UNIQUE_NAME} to dbca.

Example:

dbca -silent -createDatabase                                                 \
  -responseFile NO_VALUE                                                     \
  -templateName General_Purpose.dbc                                          \
  -sid ${ORACLE_SID}                                                         \
  -gdbname ${ORACLE_SID}                                                     \
  -characterSet AL32UTF8                                                     \
  -sysPassword ${SYS_PASSWORD}                                               \
  -systemPassword ${SYS_PASSWORD}                                            \
  -createAsContainerDatabase false                                           \
  -databaseType MULTIPURPOSE                                                 \
  -automaticMemoryManagement false                                           \
  -totalMemory 2048                                                          \
  -storageType FS                                                            \
  -datafileDestination "${DATA_DIR}"                                         \
  -redoLogFileSize 50                                                        \
  -emConfiguration NONE                                                      \
  -sampleSchema false                                                        \
  -initparams db_unique_name=${NODE1_DB_UNIQUE_NAME}                         \
  -ignorePreReqs

set lines 100
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a55

SQL> set lines 100
SQL> column NAME_COL_PLUS_SHOW_PARAM format a30
SQL> column VALUE_COL_PLUS_SHOW_PARAM format a55

SQL> show parameter db%name

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
db_file_name_convert           string
db_name                        string      hawk
db_unique_name                 string      hawka
pdb_file_name_convert          string

SQL> show parameter create%dest

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
db_create_file_dest            string      /u01/oradata
db_create_online_log_dest_1    string      /u01/oradata
db_create_online_log_dest_2    string
db_create_online_log_dest_3    string
db_create_online_log_dest_4    string
db_create_online_log_dest_5    string

SQL> show parameter dump_dest

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
background_dump_dest           string      /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log
core_dump_dest                 string      /u01/app/oracle/diag/rdbms/hawka/hawk/cdump
user_dump_dest                 string      /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log
SQL>

March 14, 2020

ORA-14758: Last partition in the range section cannot be dropped

Filed under: 12.2,partitioning — mdinh @ 7:27 pm

Quick and dirty post.

Works for 12.2.0.1 and failed with ORA-14758 below 12.2.0.1

00:51:45 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P661        YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10 rows selected.

00:51:49 DINH @ HAWK:HAWK:&gt;alter table test drop partition D1;

Table altered.

00:52:00 DINH @ HAWK:HAWK:&gt;alter table test drop partition D2;

Table altered.

00:52:08 DINH @ HAWK:HAWK:&gt;alter table test drop partition D3;

Table altered.

00:52:20 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

00:52:23 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P662;

Table altered.

00:52:46 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P663;

Table altered.

00:52:58 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P664;

Table altered.

00:53:09 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:53:12 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P665;

Table altered.

00:53:43 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P666;

Table altered.

00:53:53 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:04 DINH @ HAWK:HAWK:&gt;alter table test drop partition SYS_P667;

Table altered.

00:54:22 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:28 DINH @ HAWK:HAWK:&gt;insert into test values(to_date('01.01.2009', 'dd.mm.yyyy'), 1);

1 row created.

00:54:36 DINH @ HAWK:HAWK:&gt;commit;

Commit complete.

00:54:41 DINH @ HAWK:HAWK:&gt;@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P668        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:44 DINH @ HAWK:HAWK:&gt;exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

18:59:19 DB-FS-1:(MDINH@hawk):PRIMARY&gt; @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- -------------------------------------------------------------------------------------
MDINH        TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P68         YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P69         YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P70         YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P71         YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P72         YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P73         YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P74         YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

10 rows selected.

18:59:40 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D1;

Table altered.

18:59:50 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D2;

Table altered.

18:59:58 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


19:00:06 DB-FS-1:(MDINH@hawk):PRIMARY&gt; 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 sql]$

SOLUTION:

I have been discussing this with LDC and agree the better solution is to TRUNCATE the partition.

How To Avoid ORA-14758 in Interval Partitioned Table (Doc ID 1526571.1)
(iv) Temporarily disable the interval partition.

Here is one example of Temporarily disable the interval partition.
This has side effects as shown which has worked until one day it stopped working.
Test cases are simple and real world may not be as such.

12:44:18 DB-FS-1:(MDINH@hawk):PRIMARY&gt; @p

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         D1              NO                             TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D2              NO                             TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D3              NO                             TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P101        YES                            TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        YES                            TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        YES                            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        YES                            TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        YES                            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        YES                            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        YES                            TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

10 rows selected.

12:44:20 DB-FS-1:(MDINH@hawk):PRIMARY&gt; drop alter table test drop partition D1;
drop alter table test drop partition D1
     *
ERROR at line 1:
ORA-00950: invalid DROP option


12:44:48 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D1;

Table altered.

12:45:45 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D2;

Table altered.

12:45:57 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


12:46:07 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table test truncate partition D3;

Table truncated.

12:46:31 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table TEST set interval ();

Table altered.

12:50:23 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table TEST drop partition D3;

Table altered.

12:50:44 DB-FS-1:(MDINH@hawk):PRIMARY&gt; alter table TEST set interval (numtoyminterval(1,'YEAR'));

Table altered.

12:50:53 DB-FS-1:(MDINH@hawk):PRIMARY&gt; @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         SYS_P101        NO                             TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        NO                             TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        NO                             TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        NO                             TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        NO                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        NO                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        NO                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

7 rows selected.

12:50:58 DB-FS-1:(MDINH@hawk):PRIMARY&gt; insert into test values(to_date('01.01.2010', 'dd.mm.yyyy'), 1);

1 row created.

12:52:15 DB-FS-1:(MDINH@hawk):PRIMARY&gt; commit;

Commit complete.

12:54:36 DB-FS-1:(MDINH@hawk):PRIMARY&gt; @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME           POS INT HIGH_VALUE
------------ ------------ --------------- ------------ --- ----------------------------------------------------------
MDINH        TEST         SYS_P101                   1 NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102                   2 NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103                   3 NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104                   4 NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105                   5 NO  TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106                   6 NO  TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107                   7 NO  TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P108                   8 YES TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

8 rows selected.

12:54:40 DB-FS-1:(MDINH@hawk):PRIMARY&gt;

February 19, 2020

Simplify RMAN Restore With Meaningful Tag

Filed under: 12.2,RMAN — mdinh @ 9:44 pm

Here is a simple demo for how to restore RMAN backup in case of failed migration using RMAN tag.

List backup from File System:

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/*MIGRATION*
-rw-r----- 1 oracle oinstall  12886016 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
-rw-r----- 1 oracle oinstall   1073152 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
-rw-r----- 1 oracle oinstall 112263168 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
-rw-r----- 1 oracle oinstall 212926464 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
[oracle@db-fs-1 ~]$

List backup from RMAN:

[oracle@db-fs-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Feb 19 04:21:17 2020

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

connected to target database: HAWK (DBID=3291419015)

RMAN> list backup summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of database summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION

RMAN> list backup of archivelog all summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION

RMAN> list backup of controlfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of spfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION

RMAN> list backupset 42,49,50,52;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
42      Incr 0  203.05M    DISK        00:00:29     2020-FEB-18 21:57:17
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:56:48
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  1    0  Incr 1428959    2020-FEB-18 21:56:48              NO    /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
49      3.00K      DISK        00:00:00     2020-FEB-18 21:57:32
        BP Key: 49   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:32

  List of Archived Logs in backup set 49
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    3       1429002    2020-FEB-18 21:57:26 1429040    2020-FEB-18 21:57:32

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
50      Full    96.00K     DISK        00:00:00     2020-FEB-18 21:57:34
        BP Key: 50   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:33
  SPFILE Included: Modification time: 2020-FEB-18 21:51:45
  SPFILE db_unique_name: HAWK

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
52      Full    1.05M      DISK        00:00:01     2020-FEB-18 21:57:36
        BP Key: 52   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:35
  Control File Included: Ckp SCN: 1429047      Ckp time: 2020-FEB-18 21:57:35

RMAN>

You are probably wondering why BS 49 with Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 contains archivelog?

RMAN backup script:

[oracle@db-fs-1 ~]$ cat /u01/backup/backup_keep.rman
spool log to /u01/backup/rman_keep_backup_migration.log
connect target;
set echo on
show all;
run {
allocate channel c1 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c2 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c3 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
backup as compressed backupset incremental level 0 filesperset 1 check logical database
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
backup as compressed backupset archivelog from time 'trunc(sysdate)'
filesperset 8
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
run {
allocate channel c1 device type disk format '/u01/backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
backup as compressed backupset current controlfile
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION';
report schema;
exit
[oracle@db-fs-1 ~]$

RMAN backup log snippet:

allocated channel: c1
channel c1: SID=57 device type=DISK

Starting backup at 2020-FEB-18 21:57:30

backup will be obsolete on date 2020-AUG-18 21:57:30
archived logs required to recover from this backup will be backed up
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2020-FEB-18 21:57:31
channel c1: finished piece 1 at 2020-FEB-18 21:57:32
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
current log archived

backup will be obsolete on date 2020-AUG-18 21:57:32
archived logs required to recover from this backup will be backed up
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set

******* input archived log thread=1 sequence=3 RECID=30 STAMP=1032731852 *******

channel c1: starting piece 1 at 2020-FEB-18 21:57:32
channel c1: finished piece 1 at 2020-FEB-18 21:57:33
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01

Restore backup from RMAN:

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     805306368 bytes

Fixed Size                     8625856 bytes
Variable Size                314573120 bytes
Database Buffers             478150656 bytes
Redo Buffers                   3956736 bytes

RMAN> restore controlfile from '/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54';

Starting restore at 2020-FEB-19 03:41:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/fra/HAWK/controlfile/o1_mf_h4r8xqh6_.ctl
Finished restore at 2020-FEB-19 03:41:38

RMAN> alter database mount;

RMAN> catalog start with '/u01/backup' noprompt;

RMAN> restore database preview summary from tag='MIGRATION';

Starting restore at 2020-FEB-19 03:43:05
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
recovery will be done up to SCN 1428959
Media recovery start SCN is 1428959
Recovery must be done beyond SCN 1428964 to clear datafile fuzziness
Finished restore at 2020-FEB-19 03:43:05

RMAN> restore database from tag='MIGRATION';
RMAN> recover database until scn 1428965;

Starting recover at 2020-FEB-19 03:44:45
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/19/2020 03:44:45
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HAWK     3291419015       PARENT  1          2017-JAN-26 13:52:29
2       2       HAWK     3291419015       PARENT  1408558    2020-FEB-18 18:49:45
3       3       HAWK     3291419015       PARENT  1424305    2020-FEB-18 20:02:49
4       4       HAWK     3291419015       PARENT  1425161    2020-FEB-18 20:19:50
5       5       HAWK     3291419015       PARENT  1425162    2020-FEB-18 20:33:05
6       6       HAWK     3291419015       PARENT  1426203    2020-FEB-18 21:13:15
7       7       HAWK     3291419015       CURRENT 1428966    2020-FEB-18 22:05:54

RMAN> recover database until scn 1428967;

Starting recover at 2020-FEB-19 03:47:41
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
channel ORA_DISK_1: piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 tag=MIGRATION
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc thread=1 sequence=1
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4rx8c8b_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc RECID=32 STAMP=1032752861
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-FEB-19 03:47:42

RMAN> alter database open resetlogs;

Statement processed

RMAN> report schema;

Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf
3    470      SYSAUX               NO      /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4s86of7_.dbf
4    70       UNDOTBS1             YES     /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4s86kbl_.dbf
7    5        USERS                NO      /u02/oradata/HAWK/datafile/o1_mf_users_h4s86ncz_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/HAWK/datafile/o1_mf_temp_h4s8jc3n_.tmp

RMAN> delete force noprompt backup tag='MIGRATION';

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
39      39      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
40      40      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
41      41      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
42      42      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
43      43      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
44      44      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
45      45      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
46      46      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
47      47      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
48      48      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
49      49      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
50      50      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
52      52      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44 RECID=39 STAMP=1032731809
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45 RECID=40 STAMP=1032731816
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43 RECID=41 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42 RECID=42 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46 RECID=43 STAMP=1032731841
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47 RECID=44 STAMP=1032731843
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48 RECID=45 STAMP=1032731845
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50 RECID=46 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 RECID=47 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 RECID=48 STAMP=1032731851
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 RECID=49 STAMP=1032731852
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53 RECID=50 STAMP=1032731854
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54 RECID=52 STAMP=1032752561
Deleted 13 objects


RMAN> exit


Recovery Manager complete.

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/
total 28
drwxrwxr-x 6 oracle oinstall  4096 Feb 18 19:11 ..
-rw-r--r-- 1 oracle oinstall  1104 Feb 18 20:40 backup_keep.rman
-rw-r--r-- 1 oracle oinstall 12346 Feb 18 21:57 rman_keep_backup_migration.log
drwxr-xr-x 2 oracle oinstall  4096 Feb 19 04:28 .
[oracle@db-fs-1 ~]$

Just a crazy idea.
Keep the same backup tag for all backups until the next level 0.

Backup TAG for daily level 0 backup:

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b%d)"
2020Feb19
[oracle@db-fs-1 ~]$

Backup TAG for weekly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)_WK$(date +%U)"
2020Feb_WK07
[oracle@db-fs-1 ~]$

Backup TAG for monthly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)"
2020Feb
[oracle@db-fs-1 ~]$

Tag: ARCH for archivelog backup and may not be useful.
LV=A means archivelog backup.

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
69      B  A  A DISK        2020-FEB-19 13:29:02 1       1       NO         ARCH
70      B  A  A DISK        2020-FEB-19 13:29:03 1       1       NO         ARCH
71      B  F  A DISK        2020-FEB-19 13:29:04 1       1       NO         TAG20200219T132904

RMAN>

In writing this post, I realized the my own backup script will need some improvements.

January 28, 2020

Use srvctl stop home When Stopping Many Database Instances

Filed under: 12c,18c,19c,srvctl — mdinh @ 6:13 pm

=============================================================================
### Stops all Oracle clusterware resources that run from the Oracle home.
=============================================================================

Usage: srvctl stop home -oraclehome  -statefile  -node  [-stopoption ] [-force]
    -oraclehome              Oracle home path
    -statefile         Specify a file path for the 'srvctl stop home' command to store the state of the resources
    -node               Node name
    -stopoption      Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
    -force                         Force stop
    -help                          Print usage
[oracle@ol7-19-rac1 ~]$

=============================================================================
### Check ALL DB status running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is running on node ol7-19-rac1. Instance status: Open.
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.

[oracle@ol7-19-rac1 ~]$ srvctl status home -o $ORACLE_HOME -s $ORACLE_HOME/statushome.txt -node ol7-19-rac1
Database cdbrac is running on node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ cat $ORACLE_HOME/statushome.txt
db-cdbrac
[oracle@ol7-19-rac1 ~]$

=============================================================================
### STOP ALL DB running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl stop home -o $ORACLE_HOME -s $ORACLE_HOME/stophome.txt -node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ cat $ORACLE_HOME/stophome.txt
db-cdbrac

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is not running on node ol7-19-rac1
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.
[oracle@ol7-19-rac1 ~]$

=============================================================================
### START ALL DB running from same ORACLE_HOME for NODE
=============================================================================

[oracle@ol7-19-rac1 ~]$ srvctl start home -o $ORACLE_HOME -s $ORACLE_HOME/stophome.txt -node ol7-19-rac1

[oracle@ol7-19-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is running on node ol7-19-rac1. Instance status: Open.
Instance cdbrac2 is running on node ol7-19-rac2. Instance status: Open.
[oracle@ol7-19-rac1 ~]$

UPDATE: Mar 11, 2020 – Remove hard coding. Status works on GI as shown.

srvctl status home -o $ORACLE_HOME -s ~/statushome.txt -node $(hostname -s)
srvctl stop home -o $ORACLE_HOME -s ~/stophome.txt -node $(hostname -s)
srvctl start home -o $ORACLE_HOME -s ~/stophome.txt -node $(hostname -s)

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

[oracle@ol7-19-rac1 ~]$ srvctl status home -o $ORACLE_HOME -s ~/statushome.txt -node $(hostname -s)
Disk Group ora.CRS.dg is running on ol7-19-rac1
Disk Group ora.DATA.dg is running on ol7-19-rac1
Disk Group ora.RECO.dg is running on ol7-19-rac1
Listener ASMNET1LSNR_ASM is running on node ol7-19-rac1
Listener LISTENER is running on node ol7-19-rac1
SCAN listener ora.LISTENER_SCAN1.lsnr is running on node ol7-19-rac1
SCAN VIP ora.scan1.vip is running on node ol7-19-rac1
ONS daemon is running on node: ol7-19-rac1
VIP ora.ol7-19-rac1.vip is running on node: ol7-19-rac1
Network is running on node: ol7-19-rac1
[oracle@ol7-19-rac1 ~]$

January 17, 2020

Data Guard Fast-Start Failover Test – Shutdown Standby Host

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 9:41 pm

Data Guard Fast-Start Failover Test – Shutdown Primary Host

Review primary host and start observer:

[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:42:54 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-DG1:(SYS@cdb1):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-dg1 sql]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  cdb1      - Primary database
    cdb1_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

DGMGRL> enable fast_start failover
Enabled.
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  cdb1      - Primary database
    cdb1_stby - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 12 seconds ago)

DGMGRL> validate database cdb1

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database cdb1_stby

  Database Role:     Physical standby database
  Primary Database:  cdb1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

DGMGRL> show database cdb1

Database - cdb1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    cdb1

  Database Error(s):
    ORA-16820: fast-start failover observer is no longer observing this database

Database Status:
ERROR

DGMGRL> show database cdb1_stby

Database - cdb1_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    cdb1

  Database Error(s):
    ORA-16820: fast-start failover observer is no longer observing this database

Database Status:
ERROR

DGMGRL> start observer
[P001 01/17 20:46:01.38] Authentication failed.
DGM-16979: Unable to log on to the primary or standby database as SYSDBA
Failed.
DGMGRL> connect sys@cdb1
Password:
Connected as SYSDBA.
DGMGRL> start observer
Observer started

Restart standby host, listener, and database:

resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
$ vagrant status
Current machine states:

default                   poweroff (virtualbox)

The VM is powered off. To restart the VM, simply run `vagrant up`

resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...

====================================================================
resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
$ vagrant status
Current machine states:

default                   running (virtualbox)

The VM is running. To stop this VM, you can run `vagrant halt` to
shut it down forcefully, or you can run `vagrant suspend` to simply
suspend the virtual machine. In either case, to restart it again,
simply run `vagrant up`.

====================================================================
resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
$ vagrant ssh
Last login: Fri Jan 17 20:11:35 2020 from 10.0.2.2
[vagrant@ol7-121-dg2 ~]$ sudo su - oracle
Last login: Fri Jan 17 20:11:44 UTC 2020 on pts/0
[oracle@ol7-121-dg2 ~]$ . oraenv <<< cdb1
ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ol7-121-dg2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JAN-2020 20:53:20

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

Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                17-JAN-2020 20:53:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1_stby_DGMGRL" has 1 instance(s).
  Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-121-dg2 ~]$ cd /sf_working/sql
[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:53:38 2020

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

Connected to an idle instance.

SYS@cdb1> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SYS@cdb1> @stby.sql

Session altered.

*** v$database ***

DB          OPEN                   DATABASE           REMOTE     SWITCHOVER      DATAGUARD  PRIMARY_DB
UNIQUE_NAME MODE                   ROLE               ARCHIVE    STATUS          BROKER     UNIQUE_NAME
----------- ---------------------- ------------------ ---------- --------------- ---------- ---------------
cdb1_stby   MOUNTED                PHYSICAL STANDBY   ENABLED    NOT ALLOWED     ENABLED    cdb1

*** gv$archive_dest ***

                                                                                              MOUNT
 THREAD#  DEST_ID DESTINATION               STATUS       TARGET           SCHEDULE PROCESS       ID
-------- -------- ------------------------- ------------ ---------------- -------- ---------- -----
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL            ACTIVE   ARCH           0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL            ACTIVE   RFS            0

*** gv$archive_dest_status ***

                               DATABASE        RECOVERY
 INST_ID  DEST_ID STATUS       MODE            MODE                    GAP_STATUS      ERROR
-------- -------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1        1 VALID        MOUNTED-STANDBY IDLE                                    NONE
       1       32 VALID        UNKNOWN         IDLE                                    NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                   26 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO              25 17-JAN-2020 20:53:53         2 41.68333
       1        1 YES             23 17-JAN-2020 20:12:12

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 24 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                                    day(2) to second(0) interval

*** gv$managed_standby ***

no rows selected

SYS@cdb1> 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-dg2 sql]$

Screen output from observer:

DGMGRL> start observer
Observer started
[W000 01/17 20:48:58.27] The primary database has requested a transition to the UNSYNC/LAGGING state.
[W000 01/17 20:48:58.28] Permission granted to the primary database to transition to UNSYNC/LAGGING state.
[W000 01/17 20:50:01.29] The primary database has been in UNSYNC/LAGGING state for 63 seconds.
[W000 01/17 20:51:04.31] The primary database has been in UNSYNC/LAGGING state for 126 seconds.
[W000 01/17 20:52:07.33] The primary database has been in UNSYNC/LAGGING state for 189 seconds.
[W000 01/17 20:53:10.36] The primary database has been in UNSYNC/LAGGING state for 252 seconds.
[W000 01/17 20:54:13.39] The primary database has been in UNSYNC/LAGGING state for 315 seconds.
[W000 01/17 20:54:16.39] The primary database returned to SYNC/NOT LAGGING state.

Validate Data Guard configuration:

[oracle@ol7-121-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  cdb1      - Primary database
    cdb1_stby - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

DGMGRL> validate database cdb1

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database cdb1_stby

  Database Role:     Physical standby database
  Primary Database:  cdb1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

DGMGRL> exit
[oracle@ol7-121-dg2 sql]$

Open database read only:

This is required because database is not register to cluster resource.

[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 21:33:07 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-DG2:(SYS@cdb1):PHYSICAL STANDBY> alter database open read only;

Database altered.

OL7-121-DG2:(SYS@cdb1):PHYSICAL STANDBY> 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-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show database cdb1_stby

Database - cdb1_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    cdb1

Database Status:
SUCCESS

DGMGRL> exit
[oracle@ol7-121-dg2 sql]$
Next Page »

Create a free website or blog at WordPress.com.