Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 43 min 6 sec ago

RAC Attack Vagrant FAILED

Sat, 2017-03-25 17:34

If anyone has successfully configured RAC Attack using Vagrant, I really would love to know how you did it.

First, instructions are all over the place and I finally followed the one below.

https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_Automation

Does anyone know what the vagrant password is?

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant up

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave
on first boot shared disks will be created, this will take some time

collabn1 eth1 lanip  :192.168.78.51
collabn1 eth2 privip :172.16.100.51
collabn1 dns server role is master
Bringing machine 'collabn2' up with 'virtualbox' provider...
Bringing machine 'collabn1' up with 'virtualbox' provider...
==> collabn2: Importing base box 'racattack/oracle65'...
==> collabn2: Matching MAC address for NAT networking...
==> collabn2: Checking if box 'racattack/oracle65' is up to date...
==> collabn2: Setting the name of the VM: collabn2.1703251454
==> collabn2: Fixed port collision for 22 => 2222. Now on port 2200.
==> collabn2: Clearing any previously set network interfaces...
==> collabn2: Preparing network interfaces based on configuration...
    collabn2: Adapter 1: nat
    collabn2: Adapter 2: hostonly
    collabn2: Adapter 3: hostonly
==> collabn2: Forwarding ports...
    collabn2: 22 (guest) => 2200 (host) (adapter 1)
==> collabn2: Running 'pre-boot' VM customizations...
==> collabn2: Booting VM...
==> collabn2: Waiting for machine to boot. This may take a few minutes...
    collabn2: SSH address: 127.0.0.1:2200
    collabn2: SSH username: vagrant
    collabn2: SSH auth method: private key
    collabn2:
    collabn2: Vagrant insecure key detected. Vagrant will automatically replace
    collabn2: this with a newly generated keypair for better security.
    collabn2:
    collabn2: Inserting generated public key within guest...
    collabn2: Removing insecure key from the guest if it's present...
    collabn2: Key inserted! Disconnecting and reconnecting using new SSH key...
    collabn2: Warning: Authentication failure. Retrying...
..........	
    collabn2: Warning: Authentication failure. Retrying...
Timed out while waiting for the machine to boot. This means that
Vagrant was unable to communicate with the guest machine within
the configured ("config.vm.boot_timeout" value) time period.

If you look above, you should be able to see the error(s) that
Vagrant had when attempting to connect to the machine. These errors
are usually good hints as to what may be wrong.

If you're using a custom box, make sure that networking is properly
working and you're able to connect to the machine. It is a common
problem that networking isn't setup properly in these boxes.
Verify that authentication configurations are also setup properly,
as well.

If the box appears to be booting properly, you may want to increase
the timeout ("config.vm.boot_timeout") value.

E:\vagrantfile\OracleLinux\racattack12cR1>

++++++++++++++++++++++++++++

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant plugin install vagrant-rekey-ssh
Installing the 'vagrant-rekey-ssh' plugin. This can take a few minutes...
Fetching: sshkey-1.9.0.gem (100%)
Fetching: vagrant-rekey-ssh-0.1.9.gem (100%)
Installed the plugin 'vagrant-rekey-ssh (0.1.9)'!

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant box list
racattack/oracle65 (virtualbox, 16.01.01)
ubuntu/trusty64    (virtualbox, 20170313.0.7)

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant reload

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave

collabn1 eth1 lanip  :192.168.78.51
collabn1 eth2 privip :172.16.100.51
collabn1 dns server role is master
==> collabn2: Less insecure SSH key not found, generating key
==> collabn2: Less insecure SSH key generated and stored at C:/Users/falcon/.vagrant.d/less_insecure_private_key
==> collabn2: Attempting graceful shutdown of VM...
    collabn2: Guest communication could not be established! This is usually because
    collabn2: SSH is not running, the authentication information was changed,
    collabn2: or some other networking issue. Vagrant will force halt, if
    collabn2: capable.
==> collabn2: Forcing shutdown of VM...
==> collabn2: Checking if box 'racattack/oracle65' is up to date...
==> collabn2: Setting the name of the VM: collabn2.1703251507
==> collabn2: Clearing any previously set forwarded ports...
==> collabn2: Fixed port collision for 22 => 2222. Now on port 2200.
==> collabn2: Clearing any previously set network interfaces...
==> collabn2: Preparing network interfaces based on configuration...
    collabn2: Adapter 1: nat
    collabn2: Adapter 2: hostonly
    collabn2: Adapter 3: hostonly
==> collabn2: Forwarding ports...
    collabn2: 22 (guest) => 2200 (host) (adapter 1)
==> collabn2: Running 'pre-boot' VM customizations...
==> collabn2: Booting VM...
==> collabn2: Waiting for machine to boot. This may take a few minutes...
    collabn2: SSH address: 127.0.0.1:2200
    collabn2: SSH username: vagrant
    collabn2: SSH auth method: private key
    collabn2: Warning: Authentication failure. Retrying...
..........	
    collabn2: Warning: Authentication failure. Retrying...
Timed out while waiting for the machine to boot. This means that
Vagrant was unable to communicate with the guest machine within
the configured ("config.vm.boot_timeout" value) time period.

If you look above, you should be able to see the error(s) that
Vagrant had when attempting to connect to the machine. These errors
are usually good hints as to what may be wrong.

If you're using a custom box, make sure that networking is properly
working and you're able to connect to the machine. It is a common
problem that networking isn't setup properly in these boxes.
Verify that authentication configurations are also setup properly,
as well.

If the box appears to be booting properly, you may want to increase
the timeout ("config.vm.boot_timeout") value.

E:\vagrantfile\OracleLinux\racattack12cR1>

++++++++++++++++++++++++++++++

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant ssh

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave

collabn1 eth1 lanip  :192.168.78.51
collabn1 eth2 privip :172.16.100.51
collabn1 dns server role is master
This command requires a specific VM name to target in a multi-VM environment.

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant box list
racattack/oracle65 (virtualbox, 16.01.01)
ubuntu/trusty64    (virtualbox, 20170313.0.7)


E:\vagrantfile\OracleLinux\racattack12cR1>vagrant ssh collabn1

collabn1 eth1 lanip  :192.168.78.51
collabn1 eth2 privip :172.16.100.51
collabn1 dns server role is master
VM must be created before running this command. Run `vagrant up` first.

E:\vagrantfile\OracleLinux\racattack12cR1>vboxmanage list runningvms
"ubuntu_default_1490460698004_34177" {78574166-49a6-433f-af6a-f3d03146a5f4}
"collabn2.1703251507" {ef37e131-f44a-49f8-ae2a-7eed423c1c7e}

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant ssh collabn2

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave
vagrant@127.0.0.1's password:
vagrant@127.0.0.1's password:
vagrant@127.0.0.1's password:
Connection to 127.0.0.1 closed by remote host.
Connection to 127.0.0.1 closed.

E:\vagrantfile\OracleLinux\racattack12cR1>vagrant ssh collabn2

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave
vagrant@127.0.0.1's password:
vagrant@127.0.0.1's password:

E:\vagrantfile\OracleLinux\racattack12cR1>

++++++++++++++++++++++++++++++

E:\vagrantfile>vagrant box list
racattack/oracle65 (virtualbox, 16.01.01)
ubuntu/trusty64    (virtualbox, 20170313.0.7)

E:\vagrantfile>vagrant box remove racattack/oracle65

collabn2 eth1 lanip  :192.168.78.52
collabn2 eth2 privip :172.16.100.52
collabn2 dns server role is slave
on first boot shared disks will be created, this will take some time
Removing box 'racattack/oracle65' (v16.01.01) with provider 'virtualbox'...

E:\vagrantfile>vagrant box list
ubuntu/trusty64 (virtualbox, 20170313.0.7)

E:\vagrantfile>


Vagrant was unable to mount VirtualBox shared folders – Solved

Sat, 2017-03-25 12:14

My starting point of installing Vagrant on Windows:
https://www.sitepoint.com/getting-started-vagrant-windows/
http://tech.osteel.me/posts/2015/01/25/how-to-use-vagrant-on-windows.html

If you are attempting to use Vagrant with VirtualBox, you may encountered issues with mounting shared folders.

Update VirtualBox to 5.1.18r114002

E:\Vagrant\sitepoint>vagrant version

Installed Version: 1.9.3
Latest Version: 1.9.3

You're running an up-to-date version of Vagrant!

E:\Vagrant\sitepoint>vagrant init ubuntu/trusty64

A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

E:\Vagrant\sitepoint>vagrant up

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'ubuntu/trusty64' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'ubuntu/trusty64'
    default: URL: https://atlas.hashicorp.com/ubuntu/trusty64
==> default: Adding box 'ubuntu/trusty64' (v20170313.0.7) for provider: virtualbox
    default: Downloading: https://atlas.hashicorp.com/ubuntu/boxes/trusty64/versions/20170313.0.7/providers/virtualbox.box
==> default: Successfully added box 'ubuntu/trusty64' (v20170313.0.7) for 'virtualbox'!
..........
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: The guest additions on this VM do not match the installed version of
    default: VirtualBox! In most cases this is fine, but in rare cases it can
    default: prevent things such as shared folders from working properly. If you see
    default: shared folder errors, please make sure the guest additions within the
    default: virtual machine match the version of VirtualBox you have installed on
    default: your host and reload your VM.
    default:
    default: Guest Additions Version: 4.3.36
    default: VirtualBox Version: 5.1
==> default: Mounting shared folders...
    default: /vagrant => E:/Vagrant/sitepoint
Vagrant was unable to mount VirtualBox shared folders. This is usually
because the filesystem "vboxsf" is not available. This filesystem is
made available via the VirtualBox Guest Additions and kernel module.
Please verify that these guest additions are properly installed in the
guest. This is not a bug in Vagrant and is usually caused by a faulty
Vagrant box. For context, the command attempted was:

mount -t vboxsf -o uid=1000,gid=1000 vagrant /vagrant

The error output from the command was:

: No such file or directory

E:\Vagrant\sitepoint>vboxmanage –version

5.1.16r113841

E:\Vagrant\sitepoint>vboxmanage list runningvms

"sitepoint_default_1490457189188_6052" {b176bb95-09ff-44d8-863f-def329afb003}

E:\Vagrant\sitepoint>vboxmanage showvminfo b176bb95-09ff-44d8-863f-def329afb003

Name:            sitepoint_default_1490457189188_6052
Groups:          /
Guest OS:        Ubuntu (64-bit)

Shared folders:
Name: 'vagrant', Host path: '\\?\E:\Vagrant\sitepoint' (machine mapping), writable

E:\Vagrant\sitepoint>vagrant plugin install vagrant-vbguest

Installing the 'vagrant-vbguest' plugin. This can take a few minutes...
Fetching: micromachine-2.0.0.gem (100%)
Fetching: vagrant-vbguest-0.13.0.gem (100%)
Installed the plugin 'vagrant-vbguest (0.13.0)'!

E:\Vagrant\sitepoint>vboxmanage controlvm sitepoint_default_1490457189188_6052 poweroff

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

E:\Vagrant\sitepoint>vboxmanage unregistervm sitepoint_default_1490457189188_6052 –delete

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Update VirtualBox and Try Again

This time, create new directory: E:\Vagrant\ubuntu

E:\Vagrant\ubuntu>vboxmanage –version

5.1.18r114002

E:\Vagrant\ubuntu>vagrant init ubuntu/trusty64

A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

E:\Vagrant\ubuntu>vagrant up

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'ubuntu/trusty64' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'ubuntu/trusty64'
    default: URL: https://atlas.hashicorp.com/ubuntu/trusty64
==> default: Adding box 'ubuntu/trusty64' (v20170313.0.7) for provider: virtualbox
    default: Downloading: https://atlas.hashicorp.com/ubuntu/boxes/trusty64/versions/20170313.0.7/providers/virtualbox.box
    default: Progress: 100% (Rate: 1519k/s, Estimated time remaining: --:--:--)
==> default: Successfully added box 'ubuntu/trusty64' (v20170313.0.7) for 'virtualbox'!
..........
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: The guest additions on this VM do not match the installed version of
    default: VirtualBox! In most cases this is fine, but in rare cases it can
    default: prevent things such as shared folders from working properly. If you see
    default: shared folder errors, please make sure the guest additions within the
    default: virtual machine match the version of VirtualBox you have installed on
    default: your host and reload your VM.
    default:
    default: Guest Additions Version: 4.3.36
    default: VirtualBox Version: 5.1
==> default: Mounting shared folders...
    default: /vagrant => E:/Vagrant/ubuntu

E:\Vagrant\ubuntu>
Config Public and Private Keys

E:\Vagrant\ubuntu>vagrant ssh-config

Host default
  HostName 127.0.0.1
  User vagrant
  Port 2222
  UserKnownHostsFile /dev/null
  StrictHostKeyChecking no
  PasswordAuthentication no
  IdentityFile E:/Vagrant/ubuntu/.vagrant/machines/default/virtualbox/private_key
  IdentitiesOnly yes
  LogLevel FATAL

Connect to server: with passwordless auto login using PuTTY

Using username "vagrant".
Authenticating with public key "imported-openssh-key"
Welcome to Ubuntu 14.04.5 LTS (GNU/Linux 3.13.0-113-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

  System information as of Sat Mar 25 17:23:22 UTC 2017

  System load:  0.0               Processes:           74
  Usage of /:   3.6% of 39.34GB   Users logged in:     0
  Memory usage: 25%               IP address for eth0: 10.0.2.15
  Swap usage:   0%

  Graph this data and manage this system at:
    https://landscape.canonical.com/

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

0 packages can be updated.
0 updates are security updates.

New release '16.04.2 LTS' available.
Run 'do-release-upgrade' to upgrade to it.


Last login: Sat Mar 25 17:23:22 2017 from 10.0.2.2
vagrant@vagrant-ubuntu-trusty-64:~$

E:\Vagrant\ubuntu>vagrant ssh

Welcome to Ubuntu 14.04.5 LTS (GNU/Linux 3.13.0-113-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

  System information as of Sat Mar 25 17:36:01 UTC 2017

  System load:  0.0               Processes:           77
  Usage of /:   3.6% of 39.34GB   Users logged in:     1
  Memory usage: 25%               IP address for eth0: 10.0.2.15
  Swap usage:   0%

  Graph this data and manage this system at:
    https://landscape.canonical.com/

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

0 packages can be updated.
0 updates are security updates.

New release '16.04.2 LTS' available.
Run 'do-release-upgrade' to upgrade to it.


Last login: Sat Mar 25 17:36:01 2017 from 10.0.2.2
vagrant@vagrant-ubuntu-trusty-64:~$

PURGEOLDEXTRACTS Not Purging Trail Files

Wed, 2017-03-22 20:16

The best part of playing poker is misreading the hand and won.

Well, today this applied to help troubleshoot a Goldengate issue.
Granted it might not be the root cause but it did solve the issue for the time being.

Given:

1. Goldengate version 10.4

2. mgr process started with uid vs username

$ id -a
uid=19208(ggsuser) gid=1601(dba) groups=1601(dba)

$ ps -ef|grep ./mgr
19208    18576     1  0 16:05 ?        00:00:00 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. mgr.prm contains PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2

5. ggserr.log - trail files not being purged
2017-03-22 16:09:35  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 16:09:35  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 (MINKEEPFILES option not used.).
2017-03-22 16:09:35  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

6. send mgr, getpurgeoldextracts
GGSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...
PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              24        0        0        0   Y
OK	
No extract trails

GGSCI> 

Comparison:

1. Goldengate version 12.2

2. mgr process started with username vs uid
ggsuser  73929     1  0 Mar21 ?        00:00:46 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. send mgr, getpurgeoldextracts
GSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              48        0        1        0   Y
OK	
Extract Trails
Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit
/ggs/dirdat/aa                              45

GGSCI>

Research:

1. Did not apply
Goldengate Manager Not Purging Trail Files (Doc ID 1460097.1)

2. Did not apply
Trail Files Not Automatically Purged by 11.1 When PurgeOldExtracts for MGR Has Been Configured (Doc ID 1943702.1)

3. Helped resolve the problem by misreading.
PURGEOLDEXTRACTS Not Purging Trail Files (Doc ID 967934.1)

The use of a UNIX symbolic link to a process can prevent MANAGER from being able to find the appropriate trail files that need to be purged.

There was not symbolic link to a process, that's just insane and who would do such a thing!

Initially read this incorrectly as symbolic link and check dirdat (symbolic link) which lead to the solution.

Solution:

Replace PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
With    PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1

2017-03-22 19:49:10  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 19:49:10  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1 (MINKEEPFILES option not used.).
2017-03-22 19:49:10  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000150, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 150.
2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000151, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 151.
.........
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000183, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 183.
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000184, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 184.

adrci purging

Mon, 2017-03-20 17:31

I did not know this.

Is there a way to control Auto_Purge Frequency done by the MMON ? (Doc ID 1446242.1)

The automatic purge cycle is designed as follows.
(1) The first actual purge action will be 2 days after instance startup time
(2) The next automatic purge actions following this first purge is done once every 7 days

If you like to purge more often, then it will need to be done manually.

The blog below was every helpful for creating adrci scripts.
https://grepora.com/2016/08/03/adrci-retention-policy-and-ad-hoc-purge-script-for-all-bases/

Here is what I have created.

$ ./adrci_show_control.sh

SHOW CONTROL diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           720                  8760                 2016-11-24 19:05:55.164304 -08:00                                                 2017-02-28 19:56:23.753525 -08:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           1                    1                    2017-03-04 10:01:39.568251 -08:00        2017-03-18 07:00:21.124556 -07:00        2017-02-28 19:55:26.148874 -08:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

SHOW CONTROL diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           720                  8760                 2017-03-04 18:10:18.197875 -08:00                                                                                          1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched

$ ./adrci_set_control.sh

SET CONTROL diag/crs/arrow1/crs:
SET CONTROL diag/rdbms/hawka/HAWKA:
SET CONTROL diag/rdbms/test/test:

$ ./adrci_purge.sh

PURGE diag/crs/arrow1/crs:

ADR Home = /u01/app/oracle/diag/crs/arrow1/crs:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1344875867           2160                 2880                 2017-03-20 15:02:48.861513 -07:00                                                 2017-03-20 15:03:01.019503 -07:00        1                    2                    82                   1                    2016-11-24 19:05:55.164304 -08:00
1 rows fetched

PURGE diag/rdbms/hawka/HAWKA:

ADR Home = /u01/app/oracle/diag/rdbms/hawka/HAWKA:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1630649358           2160                 2880                 2017-03-20 15:02:48.879455 -07:00        2017-03-18 07:00:21.124556 -07:00        2017-03-20 15:03:01.348572 -07:00        1                    2                    80                   1                    2016-11-27 18:22:12.601136 -08:00
1 rows fetched

PURGE diag/rdbms/test/test:

ADR Home = /u01/app/oracle/diag/rdbms/test/test:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
2768052777           2160                 2880                 2017-03-20 15:02:48.894455 -07:00                                                 2017-03-20 15:03:01.442372 -07:00        1                    2                    80                   1                    2017-03-04 18:10:18.197875 -08:00
1 rows fetched


$ cat adrci_show_control.sh
for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
echo "SHOW CONTROL ${f}:";
adrci exec="set home $f; show control;" ;
done

$ cat adrci_set_control.sh
for f in $( adrci exec=”show homes” | grep -v “ADR Homes:” );
do
echo “set control ${f}:”;
adrci exec=”set home $f; set control \(SHORTP_POLICY=2160, LONGP_POLICY=2880\);” ;
done

$ cat adrci_purge.sh
for f in $( adrci exec=”show homes” | grep -v “ADR Homes:” );
do
echo “purge ${f}:”;
adrci exec=”set home $f; show control; purge” ;
done


ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases

Mon, 2017-03-20 17:31

|GREP ORA

As you know, since 11g we have a Automatic Diagnostic Repository (ADR). To better manage it, we also have a Command-line Interface, called ADRCI.
ADR contains all diagnostic information for database (logs, traces, incidents, problems, etc).

adr1

ADR Structure

View original post 349 more words


Quick Check for Corrupt Block (file #, block #)

Sun, 2017-03-19 13:21

$ egrep -n '^Corrupt block relative dba| ^Reread of blocknum' alert.log| tail -50

124974:Corrupt block relative dba: 0x04c7d3be (file 12, block 312452)
124983:Reread of blocknum=312452, file=+DATA/db/datafile/db_data03.329.456465464. found valid data

124974|124983 are line number from alert.log

On reread found valid data, there is no need to proceed further since block has been validated. Typically, this occurs when there is a fractured block.

If you have doubts:
RMAN> backup validate check logical datafile 12;
SQL> select * from v$database_block_corruption;

Find corrupted segment.

select owner, segment_name, segment_type, partition_name
from dba_extents
where file_id = 12
and 312452 between block_id and block_id + blocks – 1
and rownum = 1
;

OWNER
------------------------------
SEGMENT_NAME
-----------------------------
SEGMENT_TYPE	   
------------------------------
PARTITION_NAME
------------------------------
MDINH
T1
TABLE PARTITION    
T1_P8

Deceptive Numbers

Fri, 2017-03-10 15:09

My portfolio increased $10,000 today. Original investment is $1,000,000.

I made 50% on my investment today. Original investment is $200.

There is more to than meets the eye.


Using Exception Table

Tue, 2017-03-07 18:44

It has been quite a while since I have used Exception table and just a quick demo to share.

oracle@arrow1:HAWKA:/media/sf_working/sql
$ sqlplus demo/demo @exception_demo.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 16:36:33 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:(DEMO@HAWKA):PRIMARY> col OWNER for a20
ARROW1:(DEMO@HAWKA):PRIMARY> col TABLE_NAME for a20
ARROW1:(DEMO@HAWKA):PRIMARY> col R_CONSTRAINT_NAME for a20
ARROW1:(DEMO@HAWKA):PRIMARY> drop table f purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> drop table p purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> drop table exceptions purge;

Table dropped.

ARROW1:(DEMO@HAWKA):PRIMARY> set echo off
Running @?/rdbms/admin/utlexcpt.sql

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY> desc exceptions
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ROW_ID                           ROWID
 OWNER                            VARCHAR2(30)
 TABLE_NAME                       VARCHAR2(30)
 CONSTRAINT                       VARCHAR2(30)

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> create table p
  2  ( id number not null,
  3    CONSTRAINT p_pk PRIMARY KEY (id)
  4  );

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> create table f
  2  ( id number not null,
  3    CONSTRAINT  f_fk FOREIGN KEY (id) REFERENCES  p(id)
  4  );

Table created.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> alter table f DISABLE CONSTRAINT f_fk
  2  ;

Table altered.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> insert into p values (1);

1 row created.

ARROW1:(DEMO@HAWKA):PRIMARY> insert into f values (2);

1 row created.

ARROW1:(DEMO@HAWKA):PRIMARY> commit;

Commit complete.

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> alter table f ENABLE CONSTRAINT f_fk
  2  EXCEPTIONS INTO exceptions
  3  ;
alter table f ENABLE CONSTRAINT f_fk
                                *
ERROR at line 1:
ORA-02298: cannot validate (DEMO.F_FK) - parent keys not found


ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select owner,table_name,constraint_type,R_CONSTRAINT_NAME
  2  from dba_constraints
  3  where owner in ('DEMO')
  4  and table_name in ('P','F')
  5  order by owner, table_name,R_CONSTRAINT_NAME asc,constraint_type desc
  6  ;

OWNER                TABLE_NAME           C R_CONSTRAINT_NAME
-------------------- -------------------- - --------------------
DEMO                 F                    R P_PK
DEMO                 F                    C
DEMO                 P                    P
DEMO                 P                    C

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select owner,table_name,constraint_type,R_CONSTRAINT_NAME
  2  from dba_constraints
  3  where owner in ('DEMO')
  4  and constraint_name in ('P_PK')
  5  order by owner, table_name,R_CONSTRAINT_NAME asc,constraint_type desc
  6  ;

OWNER                TABLE_NAME           C R_CONSTRAINT_NAME
-------------------- -------------------- - --------------------
DEMO                 P                    P

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select * from exceptions
  2  ;

ROW_ID             OWNER                TABLE_NAME           CONSTRAINT
------------------ -------------------- -------------------- ------------------------------
AAAEv1AAFAAAAerAAA DEMO                 F                    F_FK

ARROW1:(DEMO@HAWKA):PRIMARY>
ARROW1:(DEMO@HAWKA):PRIMARY> select f.*
  2  from f, exceptions
  3  where exceptions.constraint='F_FK'
  4  and f.rowid=exceptions.row_id
  5  ;

        ID
----------
         2

ARROW1:(DEMO@HAWKA):PRIMARY>

Goldengate 12c OGG-01117 core dumped

Tue, 2017-02-28 17:47

This probably applies to other versions as well.

Don’t you love it when there’s a simple solution?

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> create subdirs

Creating subdirectories under current directory /oracle/12.2.0/ggs01

Parameter files                /oracle/12.2.0/ggs01/dirprm: created
Report files                   /oracle/12.2.0/ggs01/dirrpt: created
Checkpoint files               /oracle/12.2.0/ggs01/dirchk: created
Process status files           /oracle/12.2.0/ggs01/dirpcs: created
SQL script files               /oracle/12.2.0/ggs01/dirsql: created
Database definitions files     /oracle/12.2.0/ggs01/dirdef: created
Extract data files             /oracle/12.2.0/ggs01/dirdat: created
Temporary files                /oracle/12.2.0/ggs01/dirtmp: created
Credential store files         /oracle/12.2.0/ggs01/dircrd: created
Masterkey wallet files         /oracle/12.2.0/ggs01/dirwlt: created
Dump files                     /oracle/12.2.0/ggs01/dirdmp: created


GGSCI (test1) 2> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (test1) 3> add credentialstore

Credential store created in ./dircrd/.

GGSCI (test1) 4> alter credentialstore add user ggs_user alias ggs_user
Password:

Credential store in ./dircrd/ altered.

GGSCI (test1) 5> dblogin useridalias ggs_user

Source Context :
  SourceModule            : [ggapp.util.pcs]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150330/oggcore/OpenSys/src/gglib/ggapp/pcsutl.c]
  SourceFunction          : [AbendHandler]
  SourceLine              : [1036]
  ThreadBacktrace         : [22] elements
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f746dc0066b]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f746dbfa734]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(_MSG_ERR_SIGNAL_RECEIVED(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition)+0x3a) [0x7f746dbde41d]]
                          : [ggsci() [0x52a977]]
                          : [/lib64/libpthread.so.0(+0xf850) [0x7f7469f72850]]
                          : [/lib64/libpthread.so.0(pthread_mutex_lock+0x4) [0x7f7469f6c444]]
                          : [ggsci(ggs::gglib::MultiThreading::Mutex::Lock()+0x9) [0x556bb9]]
                          : [ggsci(CContextItem::operator char const*() const+0x1d) [0x576b03]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::initConnectionLogon(ggs::gglib::ggdbapi::DBAuthParams const&)+0x3d0) [0x5d13b0]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::openDataSource(ggs::gglib::ggdbapi::DBAuthParams const&)+0x28) [0x5d1578]]
                          : [ggsci() [0x57c20c]]
                          : [ggsci(gl_db_login(char const*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)11>&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12>&, short, char (&) [2048])+0x61) [0x57c7a1]]
                          : [ggsci() [0x6437a6]]
                          : [ggsci(GGSCIDB_get_command(char const*, char const*, char const*, short, short, char (&) [2048])+0x1a0) [0x63ef00]]
                          : [ggsci(do_cmd(char*, unsigned long, char*, unsigned long)+0xe05) [0x6632a5]]
                          : [ggsci() [0x66746b]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x5550c0]]
                          : [ggsci(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x555e0d]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x555ef1]]
                          : [ggsci(main+0x3b) [0x667a7b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7f7465cdfc36]]
                          : [ggsci() [0x4f5b89]]

2017-02-28 20:52:47  ERROR   OGG-01117  Received signal: Segmentation violation (11).

2017-02-28 20:52:47  ERROR   OGG-01668  PROCESS ABENDING.
Segmentation fault (core dumped)

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

Opatch Apply Instructions: Ensure that you set the ORACLE_HOME environment variable to the Oracle GoldenGate home.

I had just completed patching Goldengate and forgot to set ORACLE_HOME back to DB_HOME.

oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/12.2.0/ggs01
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ source ~/.orcl
The Oracle base remains unchanged with value /opt/oracle
oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> dblogin useridalias ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

Goldengate 12c PREPARECSN BUG

Mon, 2017-02-27 20:56

It looks like I have encountered BUG which applies to any platform but Oracle only has patch for Solaris Sparc.

Anyone working for Oracle able to assist here please?

ADD SCHEMATRANDATA Throws OGG-01780 Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command. (Doc ID 2188988.1)

Oracle GoldenGate – Version 12.2.0.1.0 and later
Information in this document applies to any platform.

Patch 24601324: Patch FOR MLR 24590215: Solaris Sparc: Oracle12c: OGG 12.2.0.1.160517

Only 3 options accepted after ADD SCHEMATRANDATA causing the issue.

The current patch will change the code to accept 5 options after ADD SCHEMATRANDATA

DEMO:
WAIT
Wait for any in-flight transactions and prepare table instantiation.

LOCK
Put a lock on the table (to prepare for table instantiation).

NOWAIT
Default behavior, preparing for instantiation is done immediately.

NONE
No instantiation preparation occurs.

++++++++++++++++++++++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
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> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 2> DELETE SCHEMATRANDATA demo

2017-02-27 18:45:16  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:45:16  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 3> ADD SCHEMATRANDATA demo PREPARECSN WAIT

2017-02-27 18:45:57  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 4> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:00  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:00  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 5> ADD SCHEMATRANDATA demo PREPARECSN NOWAIT

2017-02-27 18:46:10  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 6> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:18  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:18  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 7> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN NOWAIT

2017-02-27 18:46:29  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 8> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN WAIT

2017-02-27 18:46:38  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 9> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN

2017-02-27 18:46:46  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 10> info SCHEMATRANDATA demo

2017-02-27 18:46:56  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEMO.

2017-02-27 18:46:56  INFO    OGG-01981  Schema level supplemental logging is enabled on schema DEMO for all columns.

2017-02-27 18:46:56  INFO    OGG-10462  Schema DEMO have 2 prepared tables for instantiation.

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


dbca does hard coding

Sun, 2017-02-26 09:12

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

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


Pages