Thinking Out Loud

June 25, 2021

The Horror Of Restore Validate For SBT_TAPE Backups

Filed under: 12.2,RMAN — mdinh @ 10:20 pm

For the database environment, there are database backups to disk and tape.

I wanted to validate tape backup by using validate preview summary device type=SBT_TAPE.

I hit a brick wall as the restore was reading backups from both DISK and SBT_TAPE.

Here is the command used:

restore database until time "TRUNC(sysdate)" validate preview summary device type=SBT_TAPE;

Oracle support came through with the following info:

Reading from Disk because of the preview command.

The command is now replaced with:

restore database   until time "TRUNC(sysdate)" validate device type=SBT_TAPE;
restore archivelog until time "TRUNC(sysdate)" validate device type=SBT_TAPE;

Another option is to use RETENTION POLICY.

RMAN> show RETENTION POLICY;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> restore archivelog from time='SYSDATE-7' validate device type=SBT_TAPE;

Verify missing archivelog backup.

--- list backup of archivelog sequence 89961 thread 2;

RMAN-06025: no backup of archived log for thread 2 with sequence 3712 and starting SCN of 11284417423066 found to restore

How to Check/Validate that RMAN Backups Are Good (Doc ID 466221.1)

--- Report of backups that are used to do the restore and recover:
restore database preview;

--- Check the backup pieces are good:
restore database validate;

Works beautifully and learned something new today.

June 5, 2021

Data Pump Compatible Version

Filed under: 12.2,18c,DataPump — mdinh @ 12:51 am

Import failed as shown below:

Import: Release 18.0.0.0.0 - Production on Fri Jun 4 13:07:19 2021
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0.0 not compatible with target version 12.1.0.2.0

Here are the compatible settings for source and target.

Source is 18.6.0.0.0 with compatible=18.0.0.0
Target is 18.6.0.0.0 with compatible=12.1.0.2

Run export with version.

expdp version=12.1.0.2

That’s all folks.

January 28, 2021

Remove Characters From String Using regexp_replace

Filed under: 12.2,regexp — mdinh @ 1:38 pm

It’s probably have been half a century since I have coded PL/SQL.

So there I was, reviewing PL/SQL code and it looks rather redundant.

Did you know there is defined variable USER for PL/SQL code and using sys_context is not necessary?

Also, regexp_replace can be used to remove all characters from string.

Here is an example:

Basically, one line of code replaces all the redundant code.

I have no idea why there is a need to extract characters.

DINH099PD@ORCLPDB1 > show user
USER is "DINH099PD"
DINH099PD@ORCLPDB1 > @dinh.sql
DINH099PD@ORCLPDB1 > DECLARE
  2    -- Remove character from l_user
  3    l_user VARCHAR2(30) := regexp_replace(USER, '[[:alpha:]]|_');
  4    l_user2 varchar2(20);
  5    l_output varchar2(100);
  6  BEGIN
  7    -- Remove character from l_user2
  8    l_user2 := sys_context('USERENV', 'CURRENT_USER');
  9    l_output := replace(replace(l_user2, 'DINH', ''),'PD', '');
 10    dbms_output.put_line (l_user);
 11    dbms_output.put_line (l_output);
 12  END;
 13  /
099
099

PL/SQL procedure successfully completed.

DINH099PD@ORCLPDB1 >

September 30, 2020

Automating Index Rebuild

Filed under: 12c,oracle — mdinh @ 11:28 pm

IMPORTANT: This is not a recommendation to rebuild indexes.

The post will outline SQL used to determine index to rebuild.

PL/SQL will be used to check table lock for the underlying index and if there is no lock, then rebuild index else skip rebuild for index.

1.Download Index Sizing and create copy index_est_proc_2.sql.org

2. Create table index_rebuild.

SQL> desc index_rebuild
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(20)
 LEAF_BLOCKS                                        NUMBER
 TARGET_SIZE                                        NUMBER

SQL>

3. Update index_est_proc_2.sql and include the following insert into table index rebuild.

if m_leaf_estimate < &m_scale_factor * r.leaf_blocks then
  dbms_output.put_line(
    to_char(sysdate,'hh24_mi_ss') || '|table|' ||
    trim(r.table_name) || '|index|' ||
    trim(r.index_name) || '|' || 'Current Leaf blocks|' || trim(to_char(r.leaf_blocks,'999,999,999')) || '|Target size|' || 
    trim(to_char(m_leaf_estimate,'999,999,999'))
  );

  -- Insert data into table index_rebuild as well as output to terminal.
  insert into index_rebuild(table_owner,table_name,index_name,leaf_blocks,target_size)
  values
  (UPPER('&m_owner'),trim(r.table_name),trim(r.index_name),r.leaf_blocks,m_leaf_estimate);
  dbms_output.new_line;
end if;

4. Create plsql_rebuild_idx.sql

set timing on time on serveroutput on size unlimited trimsp on tab off lines 200
col TABLE_OWNER for a30
col TABLE_NAME for a30
col INDEX_NAME for a35
col USERNAME for a10
col MACHINE for a10
col MODULE for a30
-- Display current user session info.
select s.username as Username,
       s.machine as Machine,
       s.module as Module,
       s.sid as SessionID,
       p.pid as ProcessID,
       p.spid as "UNIX ProcessID"
from
v$session s, v$process p
where s.sid = sys_context ('userenv','sid')
and s.PADDR = p.ADDR
;
set echo on
-- Rebuild indexes with LEAF_BLOCKS < 16000000 and edit as required.
select * from index_rebuild where LEAF_BLOCKS < 16000000;
exit
lock table index_rebuild in EXCLUSIVE mode WAIT 120;
DECLARE
  l_sql varchar2(1000);
  l_ct  number;
BEGIN
FOR d in (
  select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
)
LOOP
  select count(*) into l_ct
  from v$locked_object a, v$session b, dba_objects c
  where b.sid = a.session_id
  and a.object_id = c.object_id
  and c.object_type='TABLE'
  and c.owner=d.TABLE_OWNER
  and c.object_name=d.TABLE_NAME
  and d.LEAF_BLOCKS < 16000000;
  IF l_ct = 0 THEN
    dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
    l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
    dbms_output.put_line (l_sql);
    execute immediate l_sql;
    delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
  END IF;
END LOOP;
END;
/
delete from index_rebuild;
commit;
exit

5. Run plsql_rebuild_idx.sql using nohup

nohup sqlplus "/ as sysdba" @ plsql_rebuild_idx.sql > plsql_rebuild_idx.log 2>&1 &

6. Review

$ cat plsql_rebuild_idx.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 24 14:13:00 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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

14:13:00 SQL> select * from index_rebuild;

TABLE_OWNER                    TABLE_NAME                     INDEX_NAME           LEAF_BLOCKS TARGET_SIZE
------------------------------ ------------------------------ -------------------- ----------- -----------
XXXX                           YYYYYYYYYY1                    ZZZZZZZZZZZ_M            9721430     4328586
XXXX                           YYYYYYYYYY2                    ZZZZZZZZZZZ_MP          15865953     5848673

Elapsed: 00:00:00.00
14:13:00 SQL> lock table index_rebuild in EXCLUSIVE mode WAIT 120;

Table(s) Locked.

Elapsed: 00:00:00.00
14:13:00 SQL> DECLARE
14:13:00   2    l_sql varchar2(1000);
14:13:00   3    l_ct  number;
14:13:00   4  BEGIN
14:13:00   5  FOR d in (
14:13:00   6    select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
14:13:00   7  )
14:13:00   8  LOOP
14:13:00   9    select count(*) into l_ct
14:13:00  10    from v$locked_object a, v$session b, dba_objects c
14:13:00  11    where b.sid = a.session_id
14:13:00  12    and a.object_id = c.object_id
14:13:00  13    and c.object_type='TABLE'
14:13:00  14    and c.owner=d.TABLE_OWNER
14:13:00  15    and c.object_name=d.TABLE_NAME;
14:13:00  16    IF l_ct = 0 THEN
14:13:00  17      dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
14:13:00  18      l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
14:13:00  19      dbms_output.put_line (l_sql);
14:13:00  20      execute immediate l_sql;
14:13:00  21      delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
14:13:00  22    END IF;
14:13:00  23  END LOOP;
14:13:00  24  END;
14:13:00  25  /
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY1.ZZZZZZZZZZZ_M=0
alter index XXXX.ZZZZZZZZZZZ_M rebuild online parallel 4
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY2.ZZZZZZZZZZZ_MP=0
alter index XXXX.ZZZZZZZZZZZ_MP rebuild online parallel 4

PL/SQL procedure successfully completed.

Elapsed: 04:00:23.08
18:13:23 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
18:13:23 SQL> exit

7. Run index_est_proc_2.sql.org (screen output only) or index_est_proc_2.sql (screen output and insert into index_rebuild table) to determine if any more indexes are listed for rebuild.

Note: The first rebuild contained a few dozen of indexes for rebuild but was not automated.

Later, there were only 2 indexes for rebuild as shown above from real production environment before minor improvements, e.g. — Display current user session info.

Q.E.D.

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>
Next Page »

Create a free website or blog at WordPress.com.