Rename Table During DataPump Import

Uncategorized
| May 19, 2020

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

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

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

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

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

Import: Release 18.0. (more...)

ACFS Mystery

Uncategorized
| May 18, 2020

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  (more...)

RMAN Restore From Tape

Uncategorized
| May 13, 2020

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

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

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

Archived logs resides in FRA and was not deleted which resulted in complete recovery without having to (more...)

Testing RMAN Restore Validate From Tape

Uncategorized
| May 12, 2020

Short and simple method to validate backup.

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

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

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

RMAN> set echo on
2> connect target;
3> show all;
4> list backup summary;
5> report schema;
6> run {
7> allocate channel ch1 type 'SBT_TAPE' parms 'SBT_LIBRARY=/opt/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/opt/idpa_ddbea.config)';
8> allocate channel ch2 type  (more...)

nohup vs screen

Uncategorized
| May 10, 2020

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

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

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

Why not trap for error and (more...)

Notes on SQL Profiles vs SQL Plan Baselines

Uncategorized
| May 6, 2020

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

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

Researh on SQL Profiles and SQL Plan Baselines

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

What is the difference between SQL Profiles and SQL Plan Baselines?
(more...)

glogin.sql for Data Guard Environment

Uncategorized
| May 2, 2020

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

Update $ORACLE_HOME/sqlplus/admin/glogin.sql

column NAME_COL_PLUS_SHOW_PARAM format a40 wrap
column VALUE_COL_PLUS_SHOW_PARAM format a80 wrap
set lines 200 echo on trimsp on tab off pages 10000 serverout on size 1000000 feedback off verify off term off echo off arraysize 5000
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@'||SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')||':'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr  (more...)

When To Use dgmgrl / vs dgmgrl sys@tns

Uncategorized
| May 2, 2020

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 (more...)

12.1 Dataguard Multiple Standby Databases Switchover Using DGMGRL

Uncategorized
| Apr 30, 2020

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. (more...)

AutoUpgrade What I Did Not Know

Uncategorized
| Apr 29, 2020

Deploy and Upgrade Postupgrade Checks and Fixes

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

In Deploy mode, AutoUpgrade also confirms that the (more...)