Thinking Out Loud

August 4, 2017

Windows Datapump Export

Filed under: 11g,oracle,Windows — mdinh @ 3:45 am
Tags:

The purpose of the script is to perform full database export keeping 3 export copies.
If export is successful, then fullexp*.dmp will be renamed with _1.dmp suffix added to filename.
If export is unsuccessful, then code will exit, skipping rename operations.

Note: there should never be .dmp file without # suffix unless export is unsuccessful.

In hindsight, directoryName should be using variable (ORACLE_SID) versus hardcode.

SET ORACLE_SID=DB01
SET directoryName=D:\DB01\export

expdp ‘/ as sysdba’ full=y directory=DATA_PUMP_DIR dumpfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp logfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.log flashback_time=SYSTIMESTAMP REUSE_DUMPFILES=YES
IF %ERRORLEVEL% NEQ 0 GOTO ERROR

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp” (DEL “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.*”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log”)

EXIT 0

:ERROR
EXIT 1

Results after 4 runs.

08/03/2017  07:53 PM     2,680,008,704 fullexp_DB01_CMWPHV1_1.dmp
08/03/2017  07:53 PM           161,707 fullexp_DB01_CMWPHV1_1.log
08/03/2017  07:46 PM     2,680,008,704 fullexp_DB01_CMWPHV1_2.dmp
08/03/2017  07:46 PM           161,707 fullexp_DB01_CMWPHV1_2.log
08/03/2017  07:37 PM     2,680,008,704 fullexp_DB01_CMWPHV1_3.dmp
08/03/2017  07:37 PM           161,707 fullexp_DB01_CMWPHV1_3.log

April 28, 2017

Bug 18411339 – Low performance V$ARCHIVE_GAP (11.2.0.4) fix 12.2.0.1

Filed under: 11g,12c,Dataguard — mdinh @ 12:31 am

Just came across bug from 11.2.0.4 not fixed until 12.2 base release. Seriously Oracle?
In the test case below, it looks to have only affected 11.2.0.4 – 64bit for AIX Version 7.1 since I recall this was not an issues for Linux.

11.2.0.4.0
select * from v$archive_gap;
Elapsed: 00:01:48.93

12.1.0.2.0
select * from v$archive_gap;
Elapsed: 00:00:06.60

Work Around

select USERENV('Instance'), high.thread#, low.lsq, high.hsq
 from
  (select a.thread#, rcvsq, min(a.sequence#)-1 hsq
   from v$archived_log a,
        (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
           from v$log_history lh, v$database_incarnation di
          where lh.resetlogs_time = di.resetlogs_time
            and lh.resetlogs_change# = di.resetlogs_change#
            and di.status = 'CURRENT'
            and lh.thread# is not null
            and lh.resetlogs_change# is not null
            and lh.resetlogs_time is not null
         group by lh.thread#, lh.resetlogs_change#
        ) b
   where a.thread# = b.thread#
     and a.resetlogs_change# = b.resetlogs_change#
     and a.sequence# > rcvsq
   group by a.thread#, rcvsq) high,
 (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
   from
     (select thread#, min(sequence#)+1 lsq
      from
        v$log_history lh, x$kccfe fe, v$database_incarnation di
      where to_number(fe.fecps) <= lh.next_change# and to_number(fe.fecps) >= lh.first_change#
        and fe.fedup!=0 and bitand(fe.festa, 12) = 12
        and di.resetlogs_time = lh.resetlogs_time
        and lh.resetlogs_change# = di.resetlogs_change#
        and di.status = 'CURRENT'
      group by thread#) lh_lsq,
     (select thread#, max(sequence#)+1 lsq
      from
        v$log_history
      where (select min( to_number(fe.fecps))
             from x$kccfe fe
             where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
      >= next_change#
      group by thread#) srl_lsq
   where srl_lsq.thread# = lh_lsq.thread#(+)
  ) low
 where low.thread# = high.thread#
 and lsq < = hsq and hsq > rcvsq;

March 28, 2017

RMAN-06820: WARNING: failed to archive current log at primary database

Filed under: 11g,RMAN — mdinh @ 1:18 pm

The best solution is a simple one.

Standard Edition Oracle with Manual Standby generating error – RMAN-06820.

From RMAN-06820 ORA-17629 During Backup at Standby Site (Doc ID 1616074.1):
As of 11.2.0.4, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site.
This is achieved by forcing a log switch at the primary site.

Some options to remedy the issue which seems too complicated.
RMAN-06820 ORA-17629 ORA-12154 During Backup of a Standby Database (Doc ID 2025142.1)
OERR: RMAN-6613 “Connect identifier for DB_UNIQUE_NAME %s not configured” Reference Note (Doc ID 2050646.1)
RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1)

DEMO:

oracle@arrow2:HAWKB:/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 1 14:09:07 2016

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

connected to target database: HAWK (DBID=3187737370, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
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 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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_HAWKB.f'; # default

RMAN> CONFIGURE DB_UNIQUE_NAME 'HAWKB' CONNECT IDENTIFIER 'HAWKB';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 12/01/2016 14:09:26
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:09:53
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:09:54
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:09:55
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_ALL_d417xlg1_.bkp tag=ALL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:09:55

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:10:22
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:10:23
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_UNTIL_d417ygxb_.bkp tag=UNTIL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:10:23

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:10:26
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:26

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:32
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:32

RMAN> exit


Recovery Manager complete.
oracle@arrow2:HAWKB:/home/oracle
$

Database Backup and Recovery User’s Guide (11.2)
Backing Up Archived Redo Logs with RMAN
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV81399

Online Redo Log Switch will occur with backup contains the following clause:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM ..

The simple solutions for not switching redo at primary.

Use backup archivelog until time ‘sysdate’ instead of backup archivelog all.
Use backup until sequence instead of from sequence.
Use backup database followed with backup archivelog until time ‘sysdate’.

UPDATE based on comments:

Using RMAN with sys password did not work. This test was not done by me but a teammate and I did not wanted to waste more of client’s time on the matter.

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 24 07:43:11 2016

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

RMAN> connect target sys

target database Password: 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel default
RMAN-10008: could not create channel context
RMAN-10002: ORACLE error: ORA-15021: parameter "remote_dependencies_mode" is not valid in asm instance
RMAN-10006: error running SQL statement: alter session set remote_dependencies_mode = signature

RMAN> exit
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 24 07:43:36 2016

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

RMAN> connect target sys

target database Password: 
connected to target database: PROD (DBID=4122225506, not open)

RMAN> BACKUP AS COMPRESSED BACKUPSET filesperset 20 maxsetsize 16G tag rman_PROD_AL_TEST ARCHIVELOG FROM SEQUENCE 47341;

Starting backup at 24-NOV-16
using target database control file instead of recovery catalog
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database: 
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

allocated channel: ORA_DISK_1

February 26, 2017

dbca does hard coding

Filed under: 11g,oracle — mdinh @ 3:12 pm

Creating Additional Data Dictionary Structures

catoctk.sql: Oracle Cryptographic Toolkit package

owminst.plb: Workspace Manager

Workspace Manager is installed by default in the seed database and in all databases created by the Database Configuration Assistant (DBCA).

However, in all other Oracle databases, such as those you create with a customized procedure, you must install Workspace Manager before you can use its features.

I know what you are thinking, “What’s the BIG deal?”.  For the most part, it’s not.

However, when a person uses dbca to create a database with different ORACLE_HOME, SID from one server versus another, it becomes inefficient to search and replace all the hard coded values.

Now I know what you are thinking, “Why are ORACLE_HOME’s different to begin with and why don’t I just use dbca to create another database?”

That’s not the scope for this post :=) but I am sure you can ascertain the answers.

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ ls -alrt

total 40
drwxr-x---. 3 oracle oinstall 4096 Feb 23 20:45 ..
-rw-r-----. 1 oracle oinstall 1948 Feb 23 20:45 init.ora
-rwxr-xr-x. 1 oracle oinstall  627 Feb 23 20:45 foo.sh
-rw-r-----. 1 oracle oinstall 1143 Feb 23 20:45 CreateDB.sql
-rw-r-----. 1 oracle oinstall  346 Feb 23 20:45 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall  768 Feb 23 20:45 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  506 Feb 23 20:45 lockAccount.sql
-rwxr-xr-x. 1 oracle oinstall  656 Feb 23 20:45 foo.sql
drwxr-x---. 2 oracle oinstall 4096 Feb 23 20:45 .
-rw-r-----. 1 oracle oinstall  966 Feb 23 20:45 postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sh

#!/bin/sh

OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/foo/adump
mkdir -p /u01/app/oracle/admin/foo/dpdump
mkdir -p /u01/app/oracle/admin/foo/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/foo
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/product/11.2.0.4/db_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=foo; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: foo:/u01/app/oracle/product/11.2.0.4/db_1:Y
/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/foo/scripts/foo.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sql

set verify off
-- DEFINE sysPassword="hacker"
-- DEFINE systemPassword="hacker"
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwfoo force=y
@/u01/app/oracle/admin/foo/scripts/CreateDB.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBCatalog.sql
host /u01/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d foo -o /u01/app/oracle/product/11.2.0.4/db_1 -n foo
@/u01/app/oracle/admin/foo/scripts/lockAccount.sql
@/u01/app/oracle/admin/foo/scripts/postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat CreateDBCatalog.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/foo/scripts/CreateDBCatalog.log append
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/foo/scripts/sqlPlusHelp.log append
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off

Here’s one that I have for creating single instance DB.

Not the best as I should have labeled “pro *** Running initxml.sql ***”
with its corresponding COMP_ID or COMP_NAME versus name of the sql script.

NOTE: for production environment, you would not autoextend datafile 128M (really bad for performance).

define sysPassword="hacker"
define systemPassword="hacker"
spool crdb.log
set echo on timing on time on
host echo $ORACLE_SID
host sysresv
create spfile from pfile;
startup force nomount;
CREATE DATABASE
MAXINSTANCES 1
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 400
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE='US/Pacific'
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword"
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 513M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M
DEFAULT TABLESPACE users DATAFILE SIZE 129M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
UNDO TABLESPACE undotbs DATAFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M;
spool off
--
spool run_catalog.log
pro *** Running catalog.sql ***
@?/rdbms/admin/catalog.sql
spool off
--
spool run_catproc.log
pro *** Running catproc.sql ***
@?/rdbms/admin/catproc.sql
spool off
/* 
-- BEG Add additional components as required.
spool run_initjvm.log
pro *** Running initjvm.sql ***
@?/javavm/install/initjvm.sql
spool off
--
spool run_initxml.log
pro *** Running initxml.sql ***
@?/xdk/admin/initxml.sql
spool off
--
spool run_xmlja.log
pro *** Running initxml.sql ***
@?/xdk/admin/xmlja.sql
spool off
--
spool run_catjava.log
pro *** Running catjava.sql ***
@?/rdbms/admin/catjava.sql
spool off
--
spool run_catxdbj.log
pro *** Running catcatxdbj.sql ***
@?/rdbms/admin/catxdbj.sql
spool off
-- END Add additional components as required.
*/ 
spool crdb.log append 
exec dbms_scheduler.set_scheduler_attribute(attribute=>'default_timezone',value=>'US/Pacific'); 
connect system/"&&systemPassword" 
@?/sqlplus/admin/pupbld.sql 
exit

February 19, 2017

Data Pump or Data Pain Part04 – The End

Filed under: 11g,DataPump — mdinh @ 7:10 pm

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

February 15, 2017

Data Pump or Data Pain Part03 – user/db object

Filed under: 11g,DataPump — mdinh @ 4:48 am

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';

February 14, 2017

Data Pump or Data Pain Part02 – tablespace/user

Filed under: 11g,DataPump — mdinh @ 3:38 am

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’

February 11, 2017

Data Pump or Data Pain

Filed under: 11g,DataPump — mdinh @ 7:30 pm

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";

January 7, 2017

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

Filed under: 11g,linux,oracle — mdinh @ 6:46 am

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

Actual error example.

$ free 
              total        used        free      shared  buff/cache   available
Mem:      263760440    34016732   173314152      786468    56429556   228457668
Swap:      16777212           0    16777212

++++++++++

ARROW1:(SYS@HAWKA):PRIMARY> select round(263760440*.875) memlock from dual;

 MEMLOCK
----------
 230790385

ARROW1:(SYS@HAWKA):PRIMARY>

++++++++++

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

$ grep memlock /etc/security/limits.conf
oracle	soft    memlock 	unlimited
oracle	hard    memlock 	unlimited

*.sga_max_size=32212254720/1024 = KB

SYS@HAWKA> select round(32212254720/1024/2048)+1 from dual;

ROUND(32212254720/1024/2048)+1
------------------------------
                         15361

select 'vm.nr_hugepages='||to_char(round(32212254720/1024/2048,-2)+100) hugepages
from dual
 3 ;

HUGEPAGES
---------------------
vm.nr_hugepages=15500

ARROW1:(SYS@HAWKA):PRIMARY>

From alert.log
    Thu Feb 09 15:16:55 2017
      PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
    Thu Feb 09 15:16:55 2017
         2048K            15023           15362           15012        NONE

January 6, 2017

Check Oracle Preinstallation/Validated RPM Logs

Filed under: 11g,12c,linux,oracle — mdinh @ 12:50 am

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]#

Next Page »

Blog at WordPress.com.