Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 11 hours 7 min ago

Data Pump or Data Pain Part04 – The End

Sun, 2017-02-19 13:10

Please take a look at my notes below:
DataPump: Schema/DB Migration Notes


Data Pump or Data Pain Part03 – user/db object

Tue, 2017-02-14 22:48

You might be thinking, why is it necessary to specify schemas versus perform full import or why create SQL file?

$ cat impdp_full02_user.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_user.log
schemas=GGS_ADMIN,DEMO
include=USER
sqlfile=user.sql

There are 5 users imported which correspond to EXU8USR view.
If users already exists in the new database, wouldn’t create user just fail?

From demo below #schemas=GGS_ADMIN,DEMO (commented) which will
import SYS/SYSTEM/OUTLN

$ impdp parfile=impdp_full02_user.par

Import: Release 11.2.0.4.0 - Production on Tue Feb 14 19:04:05 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full02_user.par
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 4 USER objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Feb 14 19:04:07 2017 elapsed 0 00:00:01

One good reason to create SQL file is to know what changes will be made to DB ahead of time.

$ cat /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/user.sql

-- new object type path: DATABASE_EXPORT/SYS_USER/USER
-- CONNECT SYSTEM
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:A1856CFD100792EB56F0432B20D3C4AAD57A48DD2C89A94E055FE37B2DD0;8A8F025737A9097A'
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: DATABASE_EXPORT/SCHEMA/USER
 ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:0966E912AE33B8963B86F44E9731A9D0126DF07B0A8EADF85E248BBC18D5;2D594E86F93B17A1'
      TEMPORARY TABLESPACE "TEMP";
 CREATE USER "OUTLN" IDENTIFIED BY VALUES 'S:14BB7D86CDE99B2AF179EA19879DCB4A7DA651430A671FE8453ADB858B35;4A3BA55E08595C81'
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK;
 CREATE USER "GGS_ADMIN" IDENTIFIED BY VALUES 'S:2AD4199BA9BF38A158B1181515FA385823EABDD9945B84F9698037BF319A;2E16F5C363B2AFF8'
      DEFAULT TABLESPACE "GGS_DATA"
      TEMPORARY TABLESPACE "TEMP";
 CREATE USER "DEMO" IDENTIFIED BY VALUES 'S:63BE233139FAE11FA97490DC8D7CABDAED282C89A7D343CE4D45972C8087;4646116A123897CF'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

$ grep DATABASE_EXPORT /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/expdp_full.log
|grep -v SCHEMA|sort

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

SYS_USER excluded; otherwise, SYS password will be changed via ALTER USER.

$ cat impdp_full03_dbobj.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_dbobj.log
include=AUDIT
include=CONTEXT
include=DB_LINK
include=DIRECTORY
include=GRANT
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=PUBLIC_SYNONYM/SYNONYM
include=RESOURCE_COST
include=ROLE
include=TRUSTED_DB_LINK
include=SYSTEM_PROCOBJACT
sqlfile=dbobj.sql

Next, import objects from DATABASE_EXPORT.

$ impdp parfile=impdp_full03_dbobj.par

Import: Release 11.2.0.4.0 - Production on Tue Feb 14 20:34:31 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full03_dbobj.par
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 48 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
     Completed 2 DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 2 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 6 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 3 CONTEXT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 17 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 4 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 24 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 3 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
     Completed 29 AUDIT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Feb 14 20:34:33 2017 elapsed 0 00:00:02

$ egrep ‘LINK|SYNONYM|DIRECTORY’ /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/dbobj.sql

-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE DATABASE LINK "PRIVATE_DBLINK.LOCALDOMAIN"
CREATE PUBLIC DATABASE LINK "PUBLIC_DB_LINK.LOCALDOMAIN"
-- new object type path: DATABASE_EXPORT/TRUSTED_DB_LINK
-- new object type path: DATABASE_EXPORT/DIRECTORY/DIRECTORY
 CREATE DIRECTORY "DATA_PUMP_DIR" AS '/u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/';
 CREATE DIRECTORY "TEST" AS '/tmp/';
-- new object type path: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "DEMO";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "EXP_FULL_DATABASE";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "IMP_FULL_DATABASE";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "EXP_FULL_DATABASE";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "IMP_FULL_DATABASE";
GRANT WRITE ON DIRECTORY "TEST" TO "GGS_ADMIN";
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
CREATE  PUBLIC SYNONYM "OL$" FOR "SYSTEM"."OL$";
CREATE  PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS";
CREATE  PUBLIC SYNONYM "OL$NODES" FOR "SYSTEM"."OL$NODES";
CREATE  PUBLIC SYNONYM "PRODUCT_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PRODUCT_USER_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PUBLIC_HAWK" FOR "GGS_ADMIN"."OGG$Q_TAB_E_HAWK";
oracle@arrow1:HAWKA:/media/sf_working/datapump
$

++++++++++

-- CONNECT DEMO
CREATE DATABASE LINK "PRIVATE_DBLINK.LOCALDOMAIN"
   CONNECT TO CURRENT_USER
   USING 'sales';
-- CONNECT SYS
CREATE PUBLIC DATABASE LINK "PUBLIC_DB_LINK.LOCALDOMAIN"
   CONNECT TO "REMOTE_USERNAME" IDENTIFIED BY VALUES ':1'
   USING 'test';

Goldengate 12c runInstaller UNIX_GROUP_NAME | INVENTORY_LOCATION

Tue, 2017-02-14 08:20

I keep finding ways to break things because the environments are not consistent.

UNIX_GROUP_NAME
This parameter is the UNIX group name to be set for the inventory on UNIX platforms.
Note: The UNIX group name is used for first-time installations only.

Details for oraInventory

$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

Notice the group is dba while /etc/oraInst.loc has oinstall.
Probably because I specified UNIX_GROUP_NAME=dba?
Don’t remembered since this environment is really old.

$ ls -ld /u01/app/oracle/oraInventory
drwxrwx---. 6 oracle dba 4096 Feb 14 04:50 /u01/app/oracle/oraInventory

What happens when INVENTORY_LOCATION is not the same as /etc/oraInst.loc ?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA11g SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ ls -ld /u01/app/oraInventory
drwxrwx---. 5 oracle oinstall 4096 Feb 14 05:18 /u01/app/oraInventory
oracle@arrow1:HAWKA:/home/oracle
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ env|grep HOME
GG_HOME=/u01/app/12.2.0.1/ggs01
HOME=/home/oracle

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ export GG_HOME=/u01/app/12.2.0.1/ggs03

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-21-32AM.log

List of Homes on this system:

Home name= OraGI12Home1, Location= "/u01/app/12.1.0.2/grid"
Home name= OraDB12Home1, Location= "/u01/app/oracle/product/12.1.0.2/db_1"
Home name= OraDb11g_home1, Location= "/u01/app/oracle/product/11.2.0.4/db_1"
Home name= OraHome1, Location= "/u01/app/12.2.0.1/ggs01"
Home name= OraHome2, Location= "/u01/app/12.2.0.1/ggs02"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

When INVENTORY_LOCATION is different from /etc/oraInst.loc, use -invPtrLoc.

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oraInventory/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-44-55AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_05-44-55AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 11g                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

Just out of curiosity, what’s the difference between ORA11g|ORA12c?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA12c SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=dba INVENTORY_LOCATION=/u01/app/oraInventory2
Starting Oracle Universal Installer...

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ cat /u01/app/oraInventory2/oraInst.loc
inventory_loc=/u01/app/oraInventory2
inst_group=dba

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ls -ld /u01/app/oraInventory2/
drwxrwx---. 5 oracle dba 4096 Feb 14 06:08 /u01/app/oraInventory2/

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory2/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory2
   from           : /u01/app/oraInventory2/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_06-09-42AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_06-09-42AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 12c                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

Data Pump or Data Pain Part02 – tablespace/user

Mon, 2017-02-13 21:38

This is still all WIP and if you stay with me, I will provide all the sequences for export and import.

Too much info put in one post.

EXP-10 Usernames Which Cannot Be Exported (Doc ID 217135.1)
Did you know from $ORACLE_HOME/rdbms/admin/catexp.sql there is view EXU8USR providing which schemas will not be in full export?

This looks rather similar to column oracle_maintained from dba_users for 12c database.

Schema bold red will not be exported. Trust but verify.

oracle@arrow1:HAWKA:/media/sf_working/datapump
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 13 18:28:51 2017

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


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

ARROW1:(SYS@HAWKA):PRIMARY> set lines 120 tab off trimsp on pages 1000
ARROW1:(SYS@HAWKA):PRIMARY> col name for a55
ARROW1:(SYS@HAWKA):PRIMARY> select username from dba_users order by 1;

USERNAME
------------------------------
APPQOSSYS
DBSNMP
DEMO
DIP
GGS_ADMIN
ORACLE_OCM
OUTLN
SYS
SYSTEM

9 rows selected.

ARROW1:(SYS@HAWKA):PRIMARY> select name from exu8usr order by 1;

NAME
-------------------------------------------------------
DEMO
GGS_ADMIN
OUTLN
SYS
SYSTEM

ARROW1:(SYS@HAWKA):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
oracle@arrow1:HAWKA:/media/sf_working/datapump
$

First step is to pre-create tablespaces.

$ cat impdp_full01_tbs.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_tbs.log
include=TABLESPACE
sqlfile=tbs.sql

$ impdp parfile=impdp_full01_tbs.par

Import: Release 11.2.0.4.0 - Production on Mon Feb 13 18:46:56 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full01_tbs.par
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Mon Feb 13 18:46:57 2017 elapsed 0 00:00:01

$ cat /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/tbs.sql

See how the filename is hard coded even when OMF is being used.

-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE
  SIZE 268435456
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 8192M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 1610612736
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 8192M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 135266304
  AUTOEXTEND ON NEXT 134217728 MAXSIZE 8193M,
  SIZE 16777216,
  SIZE 10485760
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

   ALTER DATABASE DATAFILE
  '/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf' RESIZE 16785408;

CREATE TABLESPACE "GGS_DATA" DATAFILE
  SIZE 269484032
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 16385M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Basically datafile 5 was created with 16777216 and resized to 16785408.
Why did datapump not use the current size vs original size?
I know what you are probably thinking, why not just create SQL script to do the work.
True but it’s like buying a Mercedes-Benz and having to roll down the windows by hand. (Dating myself).

ARROW1:(SYS@HAWKA):PRIMARY> select file#,name,bytes from v$datafile where name like '%user%';

     FILE# NAME                                                         BYTES
---------- ------------------------------------------------------- ----------
         4 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf        135266304
         5 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf         16785408
         7 /oradata/HAWKA/datafile/o1_mf_users_db4vw289_.dbf         10485760

ARROW1:(SYS@HAWKA):PRIMARY>

SYSTEM and SYSAUX tablespaces are not exported while UNDO and TEMP are.
Hopefully, the following was performed to get all the details from DB creation.
alter database backup controlfile to trace as ‘/tmp/cf_@.sql’ reuse resetlogs;
select property_name,property_value from DATABASE_PROPERTIES;

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 1 seconds

+++++++++
     
ARROW1:(SYS@HAWKA):PRIMARY> select name from v$tablespace order by 1;

NAME
-------------------------------------------------------
GGS_DATA
SYSAUX
SYSTEM
TEMP
UNDOTBS
USERS

6 rows selected.

ARROW1:(SYS@HAWKA):PRIMARY>     

Last but not least, did you know you can create database in achivelog mode to begin with versus having to enable ARCHIVELOG mode after the fact?

Take a look at my post below.
OTN Appreciation Day : Create Database Using SQL | Thinking Out Loud Blog

ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE=’US/Mountain’


Goldengate 12c Find log sequence#, rba# for integrated extract (Doc ID 2006932.1)

Sun, 2017-02-12 20:50

When using integrated extract, info command does not show Seqno, RBA, e.g. Seqno 3292, RBA 79236752.
Command send status will show Seqno, RBA; is not usable when process is stopped.
For Oracle GoldenGate – Version 12.1.2.1.2 and later, there is now debug option to retrieve Seqno, RBA.

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

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



GGSCI (arrow1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_HAWK      00:00:10      00:00:06
EXTRACT     STOPPED     P_HAWK      00:00:00      00:02:03


GGSCI (arrow1.localdomain) 2> info e*

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:08 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:06
                     SCN 0.4928929 (4928929)


GGSCI (arrow1.localdomain) 3> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:02 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:16  Seqno 3292, RBA 79236752
                     SCN 0.4928939 (4928939)


GGSCI (arrow1.localdomain) 4> send e* status

Sending STATUS request to EXTRACT E_HAWK ...


EXTRACT E_HAWK (PID 1665)
  Current status: Recovery complete: Processing data

  Current read position:
  Redo thread #: 1
  Sequence #: 3292
  RBA: 79263888
  Timestamp: 2017-02-12 18:36:47.000000
  SCN: 0.4928987 (4928987)
  Current write position:
  Sequence #: 0
  RBA: 1420
  Timestamp: 2017-02-12 18:36:56.251219
  Extract Trail: ./dirdat/aa



GGSCI (arrow1.localdomain) 5> stop e*

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


GGSCI (arrow1.localdomain) 6> send e* status

Sending STATUS request to EXTRACT E_HAWK ...

ERROR: sending message to EXTRACT E_HAWK (Connection reset by peer).


GGSCI (arrow1.localdomain) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:07      00:00:13
EXTRACT     STOPPED     P_HAWK      00:00:00      00:03:09


GGSCI (arrow1.localdomain) 8> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status STOPPED
Checkpoint Lag       00:00:07 (updated 00:00:19 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:37:07  Seqno 3292, RBA 79275152
                     SCN 0.4929013 (4929013)


GGSCI (arrow1.localdomain) 9> exit
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$

Data Pump or Data Pain

Sat, 2017-02-11 13:30

WARNING: Rants ahead.

Simple request migrate schema from one database to another, right?

Create new database perform schema export and import this only works if objects are self contained.

The following objects are missing from schema export to name a few.
DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Here’s what was done and hopefully did not missed anything. TBS was pre-created.

$ cat impdp_full_public.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_public.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM

$ cat impdp_full_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_schema.log
include=SCHEMA:"IN ('DEMO')"
DEMO

$ cat expdp_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')"
metrics=Y
reuse_dumpfiles=Y
dumpfile=schema.dmp
logfile=exp_schema.log
SCHEMAS=ggs_admin,demo

$ expdp parfile=expdp_schema.par

Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:47:22 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_schema.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
     Estimated 12 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 19 MB
Processing object type SCHEMA_EXPORT/USER
     Completed 2 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 14 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 5 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 2 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
     Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 3 PROCACT_SCHEMA objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
     Completed 2 SYNONYM objects in 0 seconds
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
     Completed 2 TYPE objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
     Completed 1 SEQUENCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
     Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 12 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
     Completed 1 FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
     Completed 1 ALTER_FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 1 INDEX objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 7 INDEX_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 5 VIEW objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 12 TABLE_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
     Completed 8 PROCDEPOBJ objects in 1 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
     Completed 11 PROCOBJ objects in 0 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 0 seconds
. . exported "DEMO"."T"                                  6.087 MB   68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK"              16.98 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C"        5.804 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G"        13.16 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H"        9.039 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I"        9.007 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L"        7.828 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S"        11.31 KB       3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T"        6.218 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED"             11.81 KB       1 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT"                      0 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY"              0 KB       0 rows
     Completed 12 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Feb 11 10:47:29 2017 elapsed 0 00:00:07

$ cat expdp_full.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time=systimestamp
metrics=Y
exclude=statistics
reuse_dumpfiles=Y
dumpfile=full.dmp
#PARALLEL=2
#DUMPFILE=full%U.dmp
logfile=expdp_full.log
FULL=Y

$ expdp parfile=expdp_full.par

Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:59:36 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
     Estimated 69 TABLE_DATA objects in 3 seconds
Total estimation using BLOCKS method: 23.31 MB
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 4 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 48 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 49 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 4 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 18 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 1 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 3 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 10 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 11 TYPE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 17 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 4 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
     Completed 7 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
     Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 72 TABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
     Completed 6 PRE_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 24 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 424 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 1 PACKAGE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 5 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 4 PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 5 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 4 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
     Completed 106 INDEX objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 89 CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 17 VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 3 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 7 COMMENT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 1 PACKAGE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 36 REF_CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
     Completed 4 POST_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
     Completed 8 PROCDEPOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 11 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Completed 4 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
     Completed 29 AUDIT objects in 1 seconds
. . exported "DEMO"."T"                                  6.087 MB   68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK"              16.98 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C"        5.804 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G"        13.16 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H"        9.039 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I"        9.007 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L"        7.828 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S"        11.31 KB       3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T"        6.218 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED"             11.81 KB       1 rows
. . exported "SYSTEM"."DEF$_LOB"                         6.664 KB       0 rows
. . exported "OUTLN"."OL$HINTS"                          13.12 KB       0 rows
. . exported "SYSTEM"."DEF$_AQCALL"                      14.73 KB       0 rows
. . exported "SYSTEM"."DEF$_AQERROR"                     14.73 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"               7.843 KB       0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"         6.25 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"            5.859 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"         10.72 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"           7.085 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"         6.257 KB       0 rows
. . exported "OUTLN"."OL$"                               10.17 KB       0 rows
. . exported "OUTLN"."OL$NODES"                          7.781 KB       0 rows
. . exported "SYSTEM"."DEF$_CALLDEST"                        7 KB       0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST"                 5.007 KB       0 rows
. . exported "SYSTEM"."DEF$_DESTINATION"                 13.50 KB       0 rows
. . exported "SYSTEM"."DEF$_ERROR"                       8.210 KB       0 rows
. . exported "SYSTEM"."DEF$_ORIGIN"                      7.390 KB       0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR"                  5.796 KB       0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"         6.218 KB       0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"             7.843 KB       0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"             6.210 KB       0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT"                 6.226 KB       0 rows
. . exported "SYSTEM"."REPCAT$_DDL"                      7.406 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION"                9.890 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS"                  7.390 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"           8.187 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED"                8.640 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"           6.601 KB       0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"              6.203 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"             5.429 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"         8.679 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY"                 9.070 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"           6.656 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"        10.69 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT"                   7.398 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG"                13.09 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                13.79 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"           7.390 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT"                10.67 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP"                  8.226 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                8.609 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION"                8.25 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.835 KB      19 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"    8.265 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"      7.835 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                7.015 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"             6.210 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    7 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"       7.046 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"           9.062 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"         6.648 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"      5.828 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"          7.867 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT"                      0 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY"              0 KB       0 rows
     Completed 69 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 2 seconds
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/full.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sat Feb 11 10:59:53 2017 elapsed 0 00:00:17
$ cat impdp_full_sql.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_syn.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM
sqlfile=impdp_full.sql

$ impdp parfile=impdp_full_sql.par

Import: Release 11.2.0.4.0 - Production on Sat Feb 11 11:09:06 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full_sql.par
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Feb 11 11:09:09 2017 elapsed 0 00:00:02

$ cat $ORACLE_HOME/rdbms/log/impdp_full.sql

-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
CREATE FUNCTION verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   db_name varchar2(40);
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
   i_char varchar2(10);
   simple_password varchar2(10);
   reverse_user varchar2(32);

BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;


   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
    END LOOP;

   -- Check if the password is same as the username reversed

   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;

   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
    END LOOP;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;

   -- Check if the password contains at least one letter, one digit
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;

   IF isdigit = FALSE THEN
      raise_application_error(-20008, 'Password must contain at least one digit, one character');
   END IF;
   -- 2. Check for the character
   <>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20009, 'Password must contain at least one \
              digit, and one character');
   END IF;


   <>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);

     differ := abs(differ);
     IF differ < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20011, 'Password should differ from the \
            old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/
-- new object type path: DATABASE_EXPORT/PROFILE
 ALTER PROFILE "DEFAULT"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION "VERIFY_FUNCTION_11G"
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400 ;
-- new object type path: DATABASE_EXPORT/ROLE
 CREATE ROLE "SELECT_CATALOG_ROLE";

 REVOKE "SELECT_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "EXECUTE_CATALOG_ROLE";

 REVOKE "EXECUTE_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "DELETE_CATALOG_ROLE";

 REVOKE "DELETE_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "DBFS_ROLE";

 REVOKE "DBFS_ROLE" FROM SYS;
 CREATE ROLE "AQ_ADMINISTRATOR_ROLE";

 REVOKE "AQ_ADMINISTRATOR_ROLE" FROM SYS;
 CREATE ROLE "AQ_USER_ROLE";

 REVOKE "AQ_USER_ROLE" FROM SYS;
 CREATE ROLE "ADM_PARALLEL_EXECUTE_TASK";

 REVOKE "ADM_PARALLEL_EXECUTE_TASK" FROM SYS;
 CREATE ROLE "GATHER_SYSTEM_STATISTICS";

 REVOKE "GATHER_SYSTEM_STATISTICS" FROM SYS;
 CREATE ROLE "RECOVERY_CATALOG_OWNER";

 REVOKE "RECOVERY_CATALOG_OWNER" FROM SYS;
 CREATE ROLE "SCHEDULER_ADMIN";

 REVOKE "SCHEDULER_ADMIN" FROM SYS;
 CREATE ROLE "HS_ADMIN_SELECT_ROLE";

 REVOKE "HS_ADMIN_SELECT_ROLE" FROM SYS;
 CREATE ROLE "HS_ADMIN_EXECUTE_ROLE";

 REVOKE "HS_ADMIN_EXECUTE_ROLE" FROM SYS;
 CREATE ROLE "HS_ADMIN_ROLE";

 REVOKE "HS_ADMIN_ROLE" FROM SYS;
 CREATE ROLE "GLOBAL_AQ_USER_ROLE" IDENTIFIED GLOBALLY;
 CREATE ROLE "OEM_ADVISOR";

 REVOKE "OEM_ADVISOR" FROM SYS;
 CREATE ROLE "OEM_MONITOR";

 REVOKE "OEM_MONITOR" FROM SYS;
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
CREATE  PUBLIC SYNONYM "OL$" FOR "SYSTEM"."OL$";
CREATE  PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS";
CREATE  PUBLIC SYNONYM "OL$NODES" FOR "SYSTEM"."OL$NODES";
CREATE  PUBLIC SYNONYM "PRODUCT_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PRODUCT_USER_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PUBLIC_HAWK" FOR "GGS_ADMIN"."OGG$Q_TAB_E_HAWK";

Steps to Recreate Central Inventory in Real Applications Clusters (Doc ID 413939.1)

Wed, 2017-02-08 21:13

$ echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/db_1

$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2017-02-08_15-56-03PM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

This happened due to error during install. – oraInventory mismatch.

$ cat /etc/oraInst.loc
inst_group=oinstall
inventory_loc=/u01/app/oraInventory

$ cd /u01/software/database
$ export DISTRIB=`pwd`
$ ./runInstaller -silent -showProgress -waitforcompletion -force -ignorePrereq -responseFile $DISTRIB/response/db_install.rsp \
> oracle.install.option=INSTALL_DB_SWONLY \
> UNIX_GROUP_NAME=oinstall \
> INVENTORY_LOCATION=/u01/app/oracle/oraInventory \

Backup oraInventory for both nodes and attachHome

$ cd $ORACLE_HOME/oui/bin
$ ./runInstaller -silent -ignoreSysPrereqs -attachHome \
ORACLE_HOME="/u02/app/12.1.0/grid" ORACLE_HOME_NAME="OraGI12Home1" \
LOCAL_NODE="node01" CLUSTER_NODES="{node01,node02}" CRS=true
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome \
ORACLE_HOME="/u01/app/oracle/product/12.1.0/db_1" ORACLE_HOME_NAME="OraDB12Home1" \
LOCAL_NODE="node01" CLUSTER_NODES="{node01,node02}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.

runcluvfy.sh -pre crsinst NTP failed PRVF-07590 PRVG-01017

Wed, 2017-02-08 06:56

12c (12.1.0.2.0) RAC Oracle Linux Server release 7.3
/u01/software/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

Starting Clock synchronization checks using Network Time Protocol(NTP)...

Checking existence of NTP configuration file "/etc/ntp.conf" across nodes
  Node Name                             File exists?            
  ------------------------------------  ------------------------
  node02                                yes                     
  node01                                yes                     
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP configuration file "/etc/ntp.conf" existence check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
  Node Name                             Running?                
  ------------------------------------  ------------------------
  node02                                no                      
  node01                                yes                     
PRVF-7590 : "ntpd" is not running on node "node02"
PRVG-1017 : NTP configuration file is present on nodes "node02" on which NTP daemon or service was not running
Result: Clock synchronization check using Network Time Protocol(NTP) failed

NTP was indeed running on both nodes.
The issue is /var/run/ntpd.pid does not exist on the failed node.
NTP was started with incorrect options.

GOOD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 20:37:18 CST; 3 days ago
 Main PID: 22517 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid

# ll /var/run/ntpd.*
-rw-r--r-- 1 root root 5 Feb  3 20:37 /var/run/ntpd.pid

BAD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service           
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 18:10:23 CST; 3 days ago
 Main PID: 22403 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -g           

# ll /var/run/ntpd.*
ls: cannot access /var/run/ntpd.*: No such file or directory

SOLUTION:

Restart ntpd on failed node.

12c Database spfile Parameter alias is not created in ASM Diskgroup (Doc ID 1950769.1)

Sun, 2017-02-05 14:41

This is new as of 12.1.0.2.

$ srvctl config database -d hawk
Database unique name: hawk
Database name: hawk
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/HAWK/PARAMETERFILE/spfile.264.934897017
Password file: +DATA/hawk/orapwhawk
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: 
Database instances: hawk1,hawk2
Configured nodes: hawk01,hawk02
Database is administrator managed

Alias will need to be created manually.

SQL> ALTER DISKGROUP dg1 ADD ALIAS '+DG1/rac12c/spfilerac12c.ora'  FOR  '+dg1/rac12c/parameterfile/spfile.271.860077229'; 

WARNING:
I did not create alias and was curious why alias was not created. Now I know.

What I did: echo “SPFILE=’+DATA/HAWK/PARAMETERFILE/spfile.264.934897017” > $ORACLE_HOME/dbs/init$ORACLE_SID.ora

Setting SPFILE Parameter Values for Oracle RAC
http://docs.oracle.com/database/121/RACAD/admin.htm#RACAD815


Check Listen Ports

Wed, 2017-02-01 22:21

Just playing around.

-t: tcp
-u: udp

$ netstat -tpan

(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      -
tcp        0      0 127.0.0.1:6010              0.0.0.0:*                   LISTEN      -
tcp        0      0 127.0.0.1:2016              0.0.0.0:*                   LISTEN      12572/oraagent.bin
tcp        0      0 0.0.0.0:61194               0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      -
tcp        0     52 192.168.146.11:22           192.168.146.1:64706         ESTABLISHED -
tcp        0      0 ::1:25                      :::*                        LISTEN      -
tcp        0      0 ::1:6010                    :::*                        LISTEN      -
tcp        0      0 :::7901                     :::*                        LISTEN      31565/./mgr
tcp        0      0 ::1:2016                    :::*                        LISTEN      12572/oraagent.bin
tcp        0      0 :::42731                    :::*                        LISTEN      -
tcp        0      0 :::1551                     :::*                        LISTEN      12591/tnslsnr
tcp        0      0 :::111                      :::*                        LISTEN      -
tcp        0      0 :::22                       :::*                        LISTEN      -

$ lsof -i :1551

COMMAND   PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
tnslsnr 12591 oracle    8u  IPv6  31451      0t0  TCP *:hecmtl-db (LISTEN)

$ lsof -i :7901

COMMAND   PID   USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mgr     31565 oracle   11u  IPv6 1448686      0t0  TCP *:tnos-sp (LISTEN)

$ netstat -tpal

(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 localhost:smtp              *:*                         LISTEN      -
tcp        0      0 localhost:x11-ssh-offset    *:*                         LISTEN      -
tcp        0      0 localhost:bootserver        *:*                         LISTEN      12572/oraagent.bin
tcp        0      0 *:61194                     *:*                         LISTEN      -
tcp        0      0 *:sunrpc                    *:*                         LISTEN      -
tcp        0      0 *:ssh                       *:*                         LISTEN      -
tcp        0    852 arrow1.localdomain:ssh      192.168.146.1:64706         ESTABLISHED -
tcp        0      0 localhost:smtp              *:*                         LISTEN      -
tcp        0      0 localhost:x11-ssh-offset    *:*                         LISTEN      -
tcp        0      0 *:tnos-sp                   *:*                         LISTEN      31565/./mgr
tcp        0      0 localhost:bootserver        *:*                         LISTEN      12572/oraagent.bin
tcp        0      0 *:42731                     *:*                         LISTEN      -
tcp        0      0 *:hecmtl-db                 *:*                         LISTEN      12591/tnslsnr
tcp        0      0 *:sunrpc                    *:*                         LISTEN      -
tcp        0      0 *:ssh                       *:*                         LISTEN      -

Goldengate Importance of Commenting

Fri, 2017-01-27 20:35

I was curious to determine effect for modifying ./dirdat/aa to dirdat/aa for Goldengate parameter.

While it may look to be the same, it is not treated the same.

$ ll dirdat/*
-rw-r-----. 1 oracle oinstall    3719 Jan 27 14:15 dirdat/aa000000000
-rw-r-----. 1 oracle oinstall 8086335 Jan 27 18:16 dirdat/aa000000001

$ ll ./dirdat/*
-rw-r-----. 1 oracle oinstall    3719 Jan 27 14:15 ./dirdat/aa000000000
-rw-r-----. 1 oracle oinstall 8086335 Jan 27 18:16 ./dirdat/aa000000001

Here is what happens when parameter is modified from ./dirdat/aa to dirdat/aa

$ grep EXTTRAIL ggserr.log
2017-01-27 10:45:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500.
2017-01-27 10:45:16  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): ADD EXTRACT p_hawk  EXTTRAILSOURCE ./dirdat/aa.
2017-01-27 18:18:26  ERROR   OGG-01044  Oracle GoldenGate Capture for Oracle, e_hawk.prm:  The trail 'dirdat/aa' is not assigned to extract 'E_HAWK'. 
Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL dirdat/aa, EXTRACT E_HAWK".

How do you find how trail file as added?

GGSCI (arrow1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     E_HAWK      00:00:04      00:12:25
EXTRACT     STOPPED     P_HAWK      00:00:00      07:44:04


GGSCI (arrow1.localdomain) 2> info e*

EXTRACT    E_HAWK    Last Started 2017-01-27 18:18   Status ABENDED
Checkpoint Lag       00:00:04 (updated 00:12:27 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-01-27 18:16:52
                     SCN 0.3369629 (3369629)


GGSCI (arrow1.localdomain) 3> info e* showch

EXTRACT    E_HAWK    Last Started 2017-01-27 18:18   Status ABENDED
Checkpoint Lag       00:00:04 (updated 00:12:41 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-01-27 18:16:52
                     SCN 0.3369629 (3369629)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):
    Timestamp: 2017-01-27 10:45:14.000000
    SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2017-01-27 18:16:52.000000
    SCN: 0.3369628 (3369628)

  Current Checkpoint (position of last record read in the data source):
    Timestamp: 2017-01-27 18:16:52.000000
    SCN: 0.3369629 (3369629)

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 1
    RBA: 8086335
    Timestamp: 2017-01-27 18:16:56.302658
    Extract Trail: ./dirdat/aa
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

Header:
  Version = 2
  Record Source = A
  Type = 13
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2017-01-27 18:18:26
  Last Update Time = 2017-01-27 18:16:56
  Stop Status = A
  Last Result = 520



GGSCI (arrow1.localdomain) 4>

Alternatively, make it easier by commenting parameter files.

$ head dirprm/e_hawk.prm
EXTRACT e_hawk
-- CHECKPARAMS
-- ADD EXTRACT e_hawk, INTEGRATED TRANLOG, BEGIN NOW
-- ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500
USERIDALIAS ggs_user
EXTTRAIL ./dirdat/aa
INCLUDE dirprm/global_ggenv.inc

Goldengate Network Troubleshooting

Fri, 2017-01-20 08:50

We encounter the following error:

GGS ERROR 150  Oracle GoldenGate Capture for Oracle, pump.prm:  TCP/IP error 9 (Bad file descriptor).

Note: server collectors at target will not be started and ports not opened until pump at source is started.
Using nc to test port.

$ nc -v -z -w 3 target.local 7960; echo $?
nc: connect to target.local port 7960 (tcp) failed: Connection refused
1

$ nc -v -z -w 3 target.local 7970; echo $?
Connection to target.local 7970 port [tcp/*] succeeded!
0

Not preferred and does not mean it’s wrong – Entry starting at 16.

DYNAMICPORTLIST 7960-7980 (20)

In hindsight, I should have started a few more pump extracts
to determine if Entry values cycle back to 0 and
if port assignment will start at 7960 or will fail.

This is what happens when dozen of pump extracts are stopped and started in groups while manager is RUNNING.

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 20
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
  16   7976     0       6260   2017/01/19 12:06:12  Server
  17   7977     0       6261   2017/01/19 12:06:12  Server
  18   7978     0       6262   2017/01/19 12:06:12  Server
  19   7979     0       6263   2017/01/19 12:06:12  Server

Houston, we have a problem.
Look at Error column.
Anything other than 0 is not good.
Look at the date assigned.
These look to be orphaned processes and manager still thinks port is assigned.

GGSCI> SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 18
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7841    98      31663   2016/12/30 08:03:18  Server
   1   7842    98      31664   2016/12/30 08:03:18  Server
   2   7843    98                
   3   7844    98                
   4   7845    98                
   5   7846    98       1243   2016/12/30 08:14:01  Server
   6   7847    98       4543   2016/12/30 08:34:28  Server
   7   7848    98       4815   2016/12/30 08:35:55  Server
   8   7849    98       5094   2016/12/30 08:37:07  Server
   9   7850    98       5151   2016/12/30 08:37:20  Server
  10   7851    98       5152   2016/12/30 08:37:25  Server
  11   7852    98      26856   2017/01/17 21:57:38  Server
  12   7853    98      32133   2017/01/17 22:30:35  Server
  13   7854    98      16390   2017/01/06 03:56:56  Server
  14   7855    98      32220   2017/01/17 22:30:41  Server
 
  15   7856     0       4774   2017/01/17 22:57:40  Server
  16   7857     0       4777   2017/01/17 22:57:52  Server
  17   7858     0       4779   2017/01/17 22:57:59  Server
  
  18   7859    98      26854   2017/01/17 21:57:38  Server
  19   7860    98      26855   2017/01/17 21:57:38  Server

This is what I like.
Notice the timestamp for Assigned are all the same.
This is because pump from source was started using wildcard, i.e. start p*
Oracle support does not recommend this and YMMV.

GGSCI> !
SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 14
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7960     0       7744   2017/01/19 12:15:13  Server
   1   7961     0       7745   2017/01/19 12:15:13  Server
   2   7962     0       7746   2017/01/19 12:15:13  Server
   3   7963     0       7747   2017/01/19 12:15:13  Server
   4   7964     0       7748   2017/01/19 12:15:13  Server
   5   7965     0       7749   2017/01/19 12:15:13  Server
   6   7966     0       7750   2017/01/19 12:15:13  Server
   7   7967     0       7751   2017/01/19 12:15:13  Server
   8   7968     0       7752   2017/01/19 12:15:13  Server
   9   7969     0       7753   2017/01/19 12:15:13  Server
  10   7970     0       7754   2017/01/19 12:15:13  Server
  11   7971     0       7755   2017/01/19 12:15:13  Server
  12   7972     0       7756   2017/01/19 12:15:13  Server
  13   7973     0       7757   2017/01/19 12:15:13  Server

GGSCI> sh ps -ef|grep ./server

512       7744  7742  0 12:15 ?        00:00:00 ./server -p 7960 -k -l /ggs1040/ggserr.log
512       7745  7742  0 12:15 ?        00:00:00 ./server -p 7961 -k -l /ggs1040/ggserr.log
512       7746  7742  0 12:15 ?        00:00:00 ./server -p 7962 -k -l /ggs1040/ggserr.log
512       7747  7742  0 12:15 ?        00:00:00 ./server -p 7963 -k -l /ggs1040/ggserr.log
512       7748  7742  0 12:15 ?        00:00:00 ./server -p 7964 -k -l /ggs1040/ggserr.log
512       7749  7742  0 12:15 ?        00:00:00 ./server -p 7965 -k -l /ggs1040/ggserr.log
512       7750  7742  0 12:15 ?        00:00:00 ./server -p 7966 -k -l /ggs1040/ggserr.log
512       7751  7742  0 12:15 ?        00:00:00 ./server -p 7967 -k -l /ggs1040/ggserr.log
512       7752  7742  0 12:15 ?        00:00:00 ./server -p 7968 -k -l /ggs1040/ggserr.log
512       7753  7742  0 12:15 ?        00:00:00 ./server -p 7969 -k -l /ggs1040/ggserr.log
512       7754  7742  0 12:15 ?        00:00:00 ./server -p 7970 -k -l /ggs1040/ggserr.log
512       7755  7742  0 12:15 ?        00:00:00 ./server -p 7971 -k -l /ggs1040/ggserr.log
512       7756  7742  0 12:15 ?        00:00:00 ./server -p 7972 -k -l /ggs1040/ggserr.log
512       7757  7742  0 12:15 ?        00:00:00 ./server -p 7973 -k -l /ggs1040/ggserr.log
512       7759  7741  0 12:16 pts/1    00:00:00 sh -c ps -ef|grep ./server
512       7761  7759  0 12:16 pts/1    00:00:00 grep ./server

GGSCI>

Good followup reading.
OGG GGS Error 150: No Dynamic Ports Available Orphan Ports Server Collector (Doc ID 965356.1)

A SERVER process is an "orphan" if netstat or lsof shows only a "listening" port, with no "ESTABLISHED" connections.

Not Another Post Configuring HugePages for Oracle on Linux (x86-64)

Sat, 2017-01-07 00:46

USE_LARGE_PAGES (TRUE/FALSE/ONLY)

Test case is only for one database instance on server.

DB is using memory_target.

SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 0

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1G
memory_target                        big integer 1G
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 0
sga_target                           big integer 0

SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit

Gather memory configuration.

$ grep Huge /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

++++++++++

$ grep PageTables /proc/meminfo
PageTables:        24428 kB

++++++++++

$ free
             total       used       free     shared    buffers     cached
Mem:       1534604    1484476      50128       1512      20352     335916
-/+ buffers/cache:    1128208     406396
Swap:      4194300          0    4194300

Calculate memlock.

SQL> select round(1534604*.875) from dual;

ROUND(1534604*.875)
-------------------
            1342779

Edit /etc/security/limits.conf to add memlock as shown below.
Logout, login, check ulimit -l

$ grep memlock /etc/security/limits.conf
#        - memlock - max locked-in-memory address space (KB)
oracle   soft   memlock    1342779
oracle   hard   memlock    1342779

++++++++++

$ ulimit -l
134217728

Run hugepages_settings.sh ERROR due to memory target being used.

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

***********
** ERROR **
***********
Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured

Remove memory target configuration from DB.
Note reset was used as shown:
alter system reset memory_target scope=spfile sid=’*’;
alter system reset memory_max_target scope=spfile sid=’*’;

oracle@arrow1:HAWKA:/home/oracle
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 6 21:33:31 2017

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


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

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileHAWKA.ora
SQL> alter system reset memory_target scope=spfile sid='*';

System altered.

SQL> alter system reset memory_max_target scope=spfile sid='*';

System altered.

SQL> alter system set sga_max_size=768M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=768M scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=256M scope=spfile sid='*';

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
Database mounted.
Database opened.
SQL> @show.sql
SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 768M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 256M
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 256M
sga_target                           big integer 768M
SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
oracle@arrow1:HAWKA:/home/oracle
$

Run hugepages_settings.sh – Recommended setting: vm.nr_hugepages = 388

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 388

Manually calculate vm.nr_hugepages in KB using [sga_max_size(768M) * 1024 * Hugepagesize(2048 kB)]

SQL> select round(768*1024/2048)+1 from dual;

ROUND(768*1024/2048)+1
----------------------
                   385

SQL>

From alert log – vm.nr_hugepages=385

Fri Jan 06 21:34:33 2017
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 128 GB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total System Global Area size is 770 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by at least 385 
     (page size 2048 KB, total size 770 MB) system wide to get 
     100% of the System Global Area allocated with large pages
********************************************************************

Configure Dynamic vm.nr_hugepages=385

[root@arrow1 ~]# sysctl -w vm.nr_hugepages=385
vm.nr_hugepages = 385
[root@arrow1 ~]# grep Huge /proc/meminfo
HugePages_Total:     353
HugePages_Free:      353
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Configure Static vm.nr_hugepages=385 and restart server – Oracle recommended.

[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
[root@arrow1 ~]# vi /etc/sysctl.conf
[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
vm.nr_hugepages=385
[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6
[root@arrow1 ~]#

HugePages on Oracle Linux 64-bit (Doc ID 361468.1)
Modified:Mar 7, 2016

Step 6: Stop all the database instances and reboot the server
(Although settings could have been done dynamically they would not be effective to the extent we require before a reboot.
The best practice is to do a persistent system configuration and perform a reboot to complete the configuration as presented through the steps above)

ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
cat /boot/grub/grub.conf


Check Oracle Preinstallation/Validated RPM Logs

Thu, 2017-01-05 18:50

Quick and dirty note to self.

Grid Infrastructure Installation Guide
https://docs.oracle.com/cd/E11882_01/install.112/e41961/prelinux.htm#CWLIN2928

Check the RPM log file to review the system configuration changes. For example:

Oracle Linux 6:
/var/log/oracle-rdbms-server-11gR2-preinstall/results/orakernel.log

Oracle Linux 5 and Oracle Linux 4:
/var/log/oracle-validated/results/orakernel.log

Here are the results for 12c with Oracle Linux 6
[root@arrow1 ~]# cd /var/log/oracle-rdbms-server-12cR1-preinstall/
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# ls
backup results
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# ll *
backup:
total 8
drwx——. 2 root root 4096 Apr 26 2014 Apr-26-2014-08-37-17
drwx——. 2 root root 4096 Jun 24 2015 Jun-24-2015-17-20-47

results:
total 4
-rw-r–r–. 1 root root 3322 Jun 24 2015 orakernel.log
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# cat /etc/system-release
Oracle Linux Server release 6.6
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]#

Here are the results for 12c with Oracle Linux 7
[root@owl ~]# cd /var/log/oracle-rdbms-server-12cR1-preinstall/
[root@owl oracle-rdbms-server-12cR1-preinstall]# ls
backup results
[root@owl oracle-rdbms-server-12cR1-preinstall]# ll *
backup:
total 0
drwx——. 2 root root 91 Dec 13 00:03 Dec-13-2016-00-03-21

results:
total 4
-rw-r–r–. 1 root root 3584 Dec 13 00:03 orakernel.log
[root@owl oracle-rdbms-server-12cR1-preinstall]# cat /etc/system-release
Oracle Linux Server release 7.3
[root@owl oracle-rdbms-server-12cR1-preinstall]#


Cloning 12c SE2 Oracle Home for Windows 2012 R2

Sun, 2016-12-18 00:13

Process is pretty much similar to *nix environment with a few exceptions.

It was harder that it should be since I wanted to perform task using CLI vs GUI.

This does not cover zip and unzip of OH and I cannot believe how junky Winzip has become since I have typically been using 7-Zip.

Cloning 12c SE2 Oracle Home for Windows 2012 R2

 

 


RMAN MAXSETSIZE, MAXPIECESIZE, FILESPERSET Unveil

Thu, 2016-12-15 20:14

First, any backup implementation should have recovery point and time objective.

Next, there’s no right or wrong, just what fits the requirements.

FILESPERSET controls maximum number of files for each backupset.
MAXPIECESIZE controls maximum size of backuppiece.
MAXSETSIZE controls maximum size of backupset.

Typically, if MAXSETSIZE is set, it should be equal to or greater than maximum size of all data files.
In general, it is not recommended to set MAXSETSIZE.

Backupset can contains 1 or more backuppiece; hence, why set a limitation on maxsetsize when it may be better to set limitation for maxpiecsize?

Justification for filesperset=1 is faster recovery and filesperset>1 is to reduce management of backup, e.g. crosscheck.

Consideration, backup versus restore frequencies.

Here’s an analogy, you are at bank withdrawing $2000 and the teller ask, how do you want the bill?
Would you like 20 $100 bills or multiple small bills 1,5,10,20 or combination of there of?

Same concept applies to backupset and filesperset.

Note: FILESPERSET and MAXOPENFILES affect multiplexing, number of buffers, size of each buffer and is not the scope of this blog post.

From the DEMO, there is only 1 backuppiece for each backupset and depending on maxsetsize, the number of backupsets created is different.

MAX DATAFILE SIZE IS 513M

ARROW1:(SYS@HAWKA):PRIMARY> select max(bytes)/1024/1024 from v$datafile;

MAX(BYTES)/1024/1024
--------------------
                 513

ARROW1:(SYS@HAWKA):PRIMARY> select name, bytes/1024/1024 from v$datafile;

NAME                                                    BYTES/1024/1024
------------------------------------------------------- ---------------
/oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf                  513
/oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf                  257
/oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf                 256
/oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf                   129
/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf                    16

ARROW1:(SYS@HAWKA):PRIMARY>

Notice the distribution and size of datafiles in backupsets.

CONFIGURE MAXSETSIZE TO UNLIMITED

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
4 BACKUPSETS CREATED FOR LEVEL0 (2 datafiles, 1 controlfile, 1 spfile)
4 BACKUPSETS CREATED FOR LEVEL1
8 BACKUPSETS TOTAL SIZE 413680k
---------------------------------------------------------------------------
Datafiles backupset 1 (181.24M)
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf (257M)
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf(256M)
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  (129M)

NOTICE: sum of datafiles > 513M since maxsetsize is unlimited and backuppiece < 513M
Datafiles backupset 2 (190.24M)
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf (513M)
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf  ( 16M)

CONFIGURE MAXSETSIZE TO 513 M

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
5 BACKUPSETS CREATED FOR LEVEL0 (3 datafiles, 1 controlfile, 1 spfile)
5 BACKUPSETS CREATED FOR LEVEL1
10 BACKUPSETS TOTAL SIZE 413808k
---------------------------------------------------------------------------
NOTICE: backupset is only 3.59M and no other datafiles can be considered since it would exceed maxsetsize of 513M
Datafiles backupset 1 (3.59M) 
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf (256M)
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf   (129M)

Before backup, RMAN does not know datafile usage and it would be too time consumsing to check. Only datafile size is checked.
RMAN performed NULL compression since backupset is 189M and datafile is 513M.

NOTICE: sum of datafiles = 513M, maxsetsize is 513M, backuppiece < 513M
Datafiles backupset 2 (189.23M)
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf  (513M)

Datafiles backupset 3 (178.68M)
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf  (257M)
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf   ( 16M)

All the details.

CONFIGURE MAXSETSIZE TO UNLIMITED

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_MAX';
RMAN> list backup summary tag L0_MAX;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
213     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
214     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
215     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX
216     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX

RMAN> list backup tag L0_MAX;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
213     Incr 0  181.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 243   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp
  List of Datafiles in backup set 213
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf 
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
214     Incr 0  190.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 244   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
  List of Datafiles in backup set 214
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
215     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 245   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
216     Incr 0  16.08M     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 246   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
  Control File Included: Ckp SCN: 1706149      Ckp time: 2016-DEC-14 19:38:45

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_MAX';
RMAN> list backup summary tag L1_MAX;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
217     B  1  A DISK        2016-DEC-14 19:39:14 1       1       NO         L1_MAX
218     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
219     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
220     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX

RMAN> list backup tag L1_MAX;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
217     Incr 1  40.00K     DISK        00:00:02     2016-DEC-14 19:39:14
        BP Key: 247   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
  List of Datafiles in backup set 217
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
218     Incr 1  48.00K     DISK        00:00:03     2016-DEC-14 19:39:15
        BP Key: 248   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
  List of Datafiles in backup set 218
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  3    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
219     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 249   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
220     Incr 1  16.08M     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 250   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
  Control File Included: Ckp SCN: 1706215      Ckp time: 2016-DEC-14 19:39:15

RMAN> exit

oracle@arrow1:HAWKA:/home/oracle
$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
48      /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
56      /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
185604  /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp 
194820  /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
413680  total
oracle@arrow1:HAWKA:/home/oracle
$

MAX DATAFILE SIZE IS 513M AND CONFIGURE MAXSETSIZE TO 513 M

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO 513 M;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_513';
RMAN> list backup summary tag L0_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
221     B  0  A DISK        2016-DEC-14 19:42:36 1       1       NO         L0_513
222     B  0  A DISK        2016-DEC-14 19:42:37 1       1       NO         L0_513
223     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
224     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
225     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513

RMAN> list backup tag L0_513;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
221     Incr 0  3.59M      DISK        00:00:00     2016-DEC-14 19:42:36
        BP Key: 251   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
  List of Datafiles in backup set 221
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
222     Incr 0  189.23M    DISK        00:00:01     2016-DEC-14 19:42:37
        BP Key: 252   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
  List of Datafiles in backup set 222
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
224     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:42:38
        BP Key: 254   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
225     Incr 0  16.08M     DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 255   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
  Control File Included: Ckp SCN: 1706522      Ckp time: 2016-DEC-14 19:42:37

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_513';
RMAN> list backup summary tag L1_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
226     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
227     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
228     B  1  A DISK        2016-DEC-14 19:44:22 1       1       NO         L1_513
229     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513
230     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513

RMAN> list backup tag L1_513;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
226     Incr 1  56.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 256   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
  List of Datafiles in backup set 226
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
227     Incr 1  32.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 257   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
  List of Datafiles in backup set 227
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706667    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
228     Incr 1  96.00K     DISK        00:00:00     2016-DEC-14 19:44:22
        BP Key: 258   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
  List of Datafiles in backup set 228
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
229     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:44:23
        BP Key: 259   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
230     Incr 1  16.08M     DISK        00:00:01     2016-DEC-14 19:44:23
        BP Key: 260   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
  Control File Included: Ckp SCN: 1706671      Ckp time: 2016-DEC-14 19:44:22

RMAN> list backuppiece '/oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
253     223     1   1   AVAILABLE   DISK        /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp

RMAN> list backupset 223;

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

RMAN>

$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
40      /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
64      /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
104     /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
3688    /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
182976  /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
193784  /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
413808  total

Linux locate/print block device attributes ASMLib

Tue, 2016-12-13 08:38

Just learned about this specifically to identify whether disk is being used by ASMLib

blkid – command-line utility to locate/print block device attributes

Oracle Linux Server release 6.4
[root@rac02:/root]
# ll /etc/*release*
-rw-r--r--. 1 root root 32 Feb 22  2013 /etc/oracle-release
-rw-r--r--. 1 root root 55 Feb 22  2013 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Nov 29  2014 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Feb 22  2013 /etc/system-release-cpe

[root@rac02:/root]
# cat /etc/system-release
Oracle Linux Server release 6.4

[root@rac02:/root]
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

[root@rac02:/root]
# /sbin/blkid |sort
/dev/mapper/vg01-lv_root: UUID="97968448-9997-42a0-a106-c438a47345b8" TYPE="ext4"
/dev/mapper/vg01-lv_swap: UUID="8fe8b719-a367-4448-9ae2-76b376ad91d5" TYPE="swap"
/dev/sda1: UUID="33162370-b7a1-45ae-9c8c-966b8bd720e3" TYPE="ext4"
/dev/sda2: UUID="qHuGcf-Ntnr-hoLR-qtEb-cdgz-2sph-jgaKDK" TYPE="LVM2_member"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdc1: LABEL="DISK2" TYPE="oracleasm"
/dev/sdd1: LABEL="DISK3" TYPE="oracleasm"
/dev/sde1: LABEL="DISK4" TYPE="oracleasm"
/dev/sdf1: LABEL="DISK5" TYPE="oracleasm"
/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK8" TYPE="oracleasm"
Oracle Linux Server release 6.6
[root@arrow1 ~]# ll /etc/*release*
-rw-r--r--. 1 root root 32 Oct 15  2014 /etc/oracle-release
-rw-r--r--. 1 root root 55 Oct 15  2014 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Jun 24  2015 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Oct 15  2014 /etc/system-release-cpe

[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6

[root@arrow1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)

[root@arrow1 ~]# /sbin/blkid
/dev/sda1: UUID="7c42cea0-f23d-4fec-ad0f-b1bf4b50b17e" TYPE="ext4"
/dev/sda2: UUID="ZVWdTj-8EQN-F3ac-3Tev-xTsb-ssL8-7euz5X" TYPE="LVM2_member"
/dev/sdb1: UUID="KUEy1I-X5i2-CuSm-krTA-R5K5-i4je-Ek56ZK" TYPE="LVM2_member"
/dev/mapper/vg01-LogVol01: UUID="8e2c236c-dd87-4be6-9eaf-f72f32f0dcc6" TYPE="ext4"
/dev/mapper/vg01-LogVol00: UUID="919bab13-82b6-425b-95c8-87975cb0bf20" TYPE="swap"
Oracle Linux Server release 7.3
[root@owl ~]# ll /etc/*release*
-rw-r--r--. 1 root root  32 Nov  7 22:07 /etc/oracle-release
-rw-r--r--. 1 root root 398 Nov  7 22:07 /etc/os-release --- (New as of OEL7?)
-rw-r--r--. 1 root root  52 Nov  7 22:07 /etc/redhat-release
lrwxrwxrwx. 1 root root  14 Dec 12 23:31 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root  31 Nov  7 22:07 /etc/system-release-cpe

[root@owl ~]# cat /etc/system-release
Oracle Linux Server release 7.3

[root@owl ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

[root@owl ~]# cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.3"
ID="ol"
VERSION_ID="7.3"
PRETTY_NAME="Oracle Linux Server 7.3"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:3:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.3
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.3

[root@owl ~]# /sbin/blkid
/dev/sr0: UUID="2016-11-21-16-51-51-00" LABEL="VBOXADDITIONS_5.1.10_112026" TYPE="iso9660"
/dev/sda1: UUID="65bf9c73-fb56-49c3-b55e-85de8f318892" TYPE="xfs"
/dev/sda2: UUID="p4rqW2-uzvr-6DpX-nof0-7tgf-Yfvn-n8ehaB" TYPE="LVM2_member"
/dev/mapper/vg01-root: UUID="de9c65d9-60cc-45b8-b9fa-7459ef3f3850" TYPE="xfs"
/dev/mapper/vg01-swap: UUID="1f85c253-261e-4021-9468-651de69b8e7a" TYPE="swap"
[root@owl ~]#

GoldenGate Capture using Active DataGuard

Thu, 2016-12-08 21:38

How to Configure Extract on Standalone Active Data Guard System if Primary is RAC Multipe Nodes (Doc ID 1962336.1)

Configuring Classic Capture in Oracle Active Data Guard Only Mode
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/classic_capture.htm#GIORA997


dgmgrl from OS command line

Thu, 2016-12-08 21:32

Quick and dirty post from what I just learned.

Who said you can’t teach old dogs new tricks!

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

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

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.11 MByte/s
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$

Pages