ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [APEX_PUBLIC_USER] after noncdb_to_pdb.sql

I found a bug last week after the creation of a PDB from NON-CDB in an empty 12.2 CDB with the noncdb_to_pdb.sql script :

	[oracle@server ~]$ sqlplus / as sysdba
	SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 27 13:40:56 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	SYS@CDB> CREATE PLUGGABLE DATABASE PDB USING '/tmp/ORCL.xml' NOCOPY TEMPFILE REUSE;
	Pluggable database created.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	4 PDB MOUNTED
	SYS@CDB> col pdb_name for a15
	SYS@CDB> select pdb_name, status from dba_pdbs where pdb_name = 'PDB';
	PDB_NAME STATUS
	--------------- ----------
	PDB NEW
	SYS@CDB> alter session set container=PDB;
	Session altered.
	SYS@CDB> @?/rdbms/admin/noncdb_to_pdb.sql
	...

view raw kziaVrfyAcctStatInRootCbk.0 hosted with ❤ by GitHub

After a successful conversion from NON-CDB to PDB, here is the error when trying to connect in the PDB as APEX_PUBLIC_USER :

	[oracle@server ~]$ sqlplus APEX_PUBLIC_USER/*****@127.0.0.1:1521/PDB
	SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 28 08:34:05 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	ERROR:
	ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user],
	[APEX_PUBLIC_USER], [], [], [], [], [], [], [], [], [], []

view raw kziaVrfyAcctStatInRootCbk.1 hosted with ❤ by GitHub

	2017-04-28T08:34:05.210271-04:00
	Errors in file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_4284.trc (incident=14692) (PDBNAME=CDB$ROOT):
	ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [APEX_PUBLIC_USER], [], [], [], [], [], [], [], [], [], []
	Incident details in: /u01/app/oracle/diag/rdbms/cdb/CDB/incident/incdir_14692/CDB_ora_4284_i14692.trc
	Use ADRCI or Support Workbench to package the incident.
	See Note 411.1 at My Oracle Support for error and packaging details.
	2017-04-28T08:34:06.400212-04:00
	Dumping diagnostic data in directory=[cdmp_20170428085203], requested by (instance=1, osid=4284), summary=[incident=14692].

view raw kziaVrfyAcctStatInRootCbk.2 hosted with ❤ by GitHub

In the PDB, I looked at the APEX users more closely and found that APEX_PUBLIC_USER and APEX_050000 were defined as common users. Even if they are not available in the root container, see the result of the CDB_USERS view below :

	SYS@CDB> alter session set container = CDB$ROOT;
	Session altered.
	SYS@CDB> show con_name
	CON_NAME
	------------------------------
	CDB$ROOT
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	4 PDB READ WRITE NO
	SYS@CDB> col username for a30
	SYS@CDB> select username, common, con_id from cdb_users where username like 'APEX%';
	USERNAME COMMON CON_ID
	------------------------------ ------ ----------
	APEX_LISTENER NO 4
	APEX_PUBLIC_USER YES 4
	APEX_REST_PUBLIC_USER NO 4
	APEX_050000 YES 4

view raw kziaVrfyAcctStatInRootCbk.3 hosted with ❤ by GitHub

So to make a long story short, the noncdb_to_pdb.sql did not "convert" the APEX_PUBLIC_USER and APEX_050000 schemas as local users but left them as common users. A service request was raised and until an official fix is available, here is a workaround that Oracle Support gave to bypass the issue :

	[oracle@server ~]$ sqlplus / as sysdba
	SQL*Plus: Release 12.2.0.1.0 Production on Wed May 3 12:44:46 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	SYS@CDB> alter session set container=PDB;
	Session altered.
	SYS@CDB> create table backup_user$ as select * from user$;
	Table created.
	SYS@CDB> select spare1 from user$ where name='APEX_PUBLIC_USER';
	SPARE1
	----------
	384
	SYS@CDB> select username, common from cdb_users where username='APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER YES
	SYS@CDB> update user$ set spare1=0 where name='APEX_PUBLIC_USER';
	1 row updated.
	SYS@CDB> select username, common from cdb_users where username='APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER NO
	SYS@CDB> commit;
	Commit complete.
	SYS@CDB> shutdown immediate;
	Pluggable Database closed.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	4 PDB MOUNTED
	SYS@CDB> startup
	Pluggable Database opened.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	4 PDB READ WRITE NO
	SYS@CDB> select username, common from cdb_users where username = 'APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER NO

view raw kziaVrfyAcctStatInRootCbk.4 hosted with ❤ by GitHub

After this UNSUPPORTED modification to the data dictionary of the PDB, we were able to connect as APEX_PUBLIC_USER as it should be (without ORA-00600) :

	[oracle@server ~]$ sqlplus APEX_PUBLIC_USER/*****@127.0.0.1:1521/PDB
	SQL*Plus: Release 12.2.0.1.0 Production on Wed May 3 12:54:35 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Last Successful login time: Thu Apr 27 2017 09:25:18 -04:00
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	APEX_PUBLIC_USER@127.0.0.1:1521/PDB> show con_id
	CON_ID
	------------------------------
	4

view raw kziaVrfyAcctStatInRootCbk.5 hosted with ❤ by GitHub

Oracle will release a patch to correct the problem to the noncdb_to_pdb.sql script. The script will convert the common APEX users to local users only if they are not already available on the root container (default 12.2):

Bug 25979661 : ORA-00600: [KZIAVRFYACCTSTATINROOTCBK: !USER] [APEX_PUBLIC_USER]
https://support.oracle.com/epmos/faces/BugDisplay?id=25979661

Once again the workaround provided here is NOT SUPPORTED by Oracle and was used only to diagnose the problem.

Repartitioning a root persistent disk on Linux Red Hat 7 (Google Cloud)

You can take this procedure from the Google Cloud Platform documentation: Repartitioning a root persistent disk

Or you can take this shell script ... https://raw.githubusercontent.com/DarkAngelStrike/GoogleCloud/master/fdisk.sh

Here is an example on a new Red Hat 7 instance ...

	root@cloudshell:~$ gcloud compute instances create rh7 --image rhel-7 --zone us-central1-a --boot-disk-size 50GB --verbosity error
	Created [https://www.googleapis.com/compute/v1/projects/xxxxxx/zones/us-central1-a/instances/rh7].
	NAME ZONE MACHINE_TYPE PREEMPTIBLE INTERNAL_IP EXTERNAL_IP STATUS
	rh7 us-central1-a n1-standard-1 xx.xxx.x.x xxx.xxx.xxx.xx RUNNING
	root@cloudshell:~$ gcloud compute config-ssh
	You should now be able to use ssh/scp with your instances.
	For example, try running:
	$ ssh rh7
	root@cloudshell:~$ ssh rh7
	Warning: Permanently added 'xxx.xxx.xxx.xx' (ECDSA) to the list of known hosts.
	[darkphoenixstrike@rh7 ~]$ sudo su -
	[root@rh7 ~]#
	[root@rh7 ~]# cat /etc/redhat-release
	Red Hat Enterprise Linux Server release 7.2 (Maipo)
	[root@rh7 ~]# lsblk -o NAME,UUID,MOUNTPOINT,FSTYPE
	NAME UUID MOUNTPOINT FSTYPE
	sda
	└─sda1 690b5506-9327-4014-a0cf-80ba632779c5 / xfs
	[root@rh7 ~]# lsblk
	NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
	sda 8:0 0 50G 0 disk
	└─sda1 8:1 0 10G 0 part /
	[root@rh7 ~]# df -h /
	Filesystem Size Used Avail Use% Mounted on
	/dev/sda1 10G 1.1G 9.0G 11% /
	[root@rh7 ~]# curl -o fdisk.sh https://raw.githubusercontent.com/DarkAngelStrike/GoogleCloud/master/fdisk.sh
	% Total % Received % Xferd Average Speed Time Time Time Current
	Dload Upload Total Spent Left Speed
	100 6156 100 6156 0 0 27687 0 --:--:-- --:--:-- --:--:-- 27729
	[root@rh7 ~]# chmod u+x fdisk.sh
	[root@rh7 ~]#
	[root@rh7 ~]# ./fdisk.sh
	The device presents a logical sector size that is smaller than
	the physical sector size. Aligning to a physical sector (or optimal
	I/O) size boundary is recommended, or performance may be impacted.
	Welcome to fdisk (util-linux 2.23.2).
	Changes will remain in memory only, until you decide to write them.
	Be careful before using the write command.
	Command (m for help): Selected partition 1
	Partition 1 is deleted
	Command (m for help): Partition type:
	p primary (0 primary, 0 extended, 4 free)
	e extended
	Select (default p): Partition number (1-4, default 1): First sector (2048-104857599, default 2048): Using default value 2048
	Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599): Using default value 104857599
	Partition 1 of type Linux and of size 50 GiB is set
	Command (m for help): The partition table has been altered!
	Calling ioctl() to re-read partition table.
	WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
	The kernel still uses the old table. The new table will be used at
	the next reboot or after you run partprobe(8) or kpartx(8)
	Syncing disks.
	Connection to xxx.xxx.xxx.xx closed by remote host.
	Connection to xxx.xxx.xxx.xx closed.
	root@cloudshell:~$ ssh rh7
	Warning: Permanently added 'xxx.xxx.xxx.xx' (ECDSA) to the list of known hosts.
	[darkphoenixstrike@rh7 ~]$ sudo su -
	[root@rh7 ~]# df -h /
	Filesystem Size Used Avail Use% Mounted on
	/dev/sda1 10G 1.1G 9.0G 11% /
	[root@rh7 ~]# lsblk
	NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
	sda 8:0 0 50G 0 disk
	└─sda1 8:1 0 50G 0 part /
	[root@rh7 ~]# ./fdisk.sh
	meta-data=/dev/sda1 isize=256 agcount=4, agsize=655232 blks
	= sectsz=512 attr=2, projid32bit=1
	= crc=0 finobt=0
	data = bsize=4096 blocks=2620928, imaxpct=25
	= sunit=0 swidth=0 blks
	naming =version 2 bsize=4096 ascii-ci=0 ftype=0
	log =internal bsize=4096 blocks=2560, version=2
	= sectsz=512 sunit=0 blks, lazy-count=1
	realtime =none extsz=4096 blocks=0, rtextents=0
	data blocks changed from 2620928 to 13106944
	[root@rh7 ~]# df -h /
	Filesystem Size Used Avail Use% Mounted on
	/dev/sda1 50G 1.1G 49G 3% /

view raw gistfile1.txt hosted with ❤ by GitHub

Modifying firewall rule of Google Cloud VM with command line tools.

Here's how to modify and add port 1522 to an already defined firewall rule named "allow-tns" of a Google Cloud VM.

	root@sysdba:~$ gcloud compute firewall-rules list
	NAME NETWORK SRC_RANGES RULES SRC_TAGS TARGET_TAGS
	allow-agent default 0.0.0.0/0 tcp:3872
	allow-bipub default 0.0.0.0/0 tcp:9803
	allow-oms default 0.0.0.0/0 tcp:7803
	allow-ons default 0.0.0.0/0 tcp:2016
	allow-tns default 0.0.0.0/0 tcp:1521
	default-allow-http default 0.0.0.0/0 tcp:80 http-server
	default-allow-https default 0.0.0.0/0 tcp:443 https-server
	default-allow-icmp default 0.0.0.0/0 icmp
	default-allow-internal default 10.240.0.0/16 tcp:0-65535,udp:0-65535,icmp
	default-allow-rdp default 0.0.0.0/0 tcp:3389
	default-allow-ssh default 0.0.0.0/0 tcp:22
	root@sysdba:~$ nmap -A em13c -p 1521-1522
	Starting Nmap 6.47 ( http://nmap.org ) at 2016-01-20 10:25 EST
	Nmap scan report for em13c (xxx.xxx.xx.xx)
	Host is up (0.037s latency).
	PORT STATE SERVICE VERSION
	1521/tcp open oracle-tns Oracle TNS Listener
	1522/tcp filtered rna-lm
	Service detection performed. Please report any incorrect results at http://nmap.org/submit/ .
	Nmap done: 1 IP address (1 host up) scanned in 7.55 seconds
	root@sysdba:~$ gcloud compute firewall-rules update allow-tns --allow tcp:1521-1522
	Updated [https://www.googleapis.com/compute/v1/projects/sysdba/global/firewalls/allow-tns].
	root@sysdba:~$ gcloud compute firewall-rules list
	NAME NETWORK SRC_RANGES RULES SRC_TAGS TARGET_TAGS
	allow-agent default 0.0.0.0/0 tcp:3872
	allow-bipub default 0.0.0.0/0 tcp:9803
	allow-oms default 0.0.0.0/0 tcp:7803
	allow-ons default 0.0.0.0/0 tcp:2016
	allow-tns default 0.0.0.0/0 tcp:1521-1522
	default-allow-http default 0.0.0.0/0 tcp:80 http-server
	default-allow-https default 0.0.0.0/0 tcp:443 https-server
	default-allow-icmp default 0.0.0.0/0 icmp
	default-allow-internal default 10.240.0.0/16 tcp:0-65535,udp:0-65535,icmp
	default-allow-rdp default 0.0.0.0/0 tcp:3389
	default-allow-ssh default 0.0.0.0/0 tcp:22
	root@sysdba:~$ nmap -A em13c -p 1521-1522
	Starting Nmap 6.47 ( http://nmap.org ) at 2016-01-20 10:24 EST
	Nmap scan report for em13c (xxx.xxx.xx.xx)
	Host is up (0.037s latency).
	PORT STATE SERVICE VERSION
	1521/tcp open oracle-tns Oracle TNS Listener
	1522/tcp open oracle-tns Oracle TNS Listener
	Service detection performed. Please report any incorrect results at http://nmap.org/submit/ .
	Nmap done: 1 IP address (1 host up) scanned in 6.35 seconds

view raw gistfile1.txt hosted with ❤ by GitHub

And the links to gcloud commands ...

gcloud compute firewall-rules list
gcloud compute firewall-rules update

Adding Oracle ASM disks to Google Cloud VM with command line tools.

This is what the web portal looks like and if you haven't tried the Google Cloud Platform yet you really should do so.

Google Cloud Platform

Here is some links to the docs of the gcloud commands used in this post ...

gcloud compute instances describe
gcloud compute instances list
gcloud compute disks describe
gcloud compute disks create
gcloud compute instances attach-disk
gcloud compute instances detach-disk

	[root@em13c ~]# cat /etc/redhat-release
	Red Hat Enterprise Linux Server release 7.2 (Maipo)
	[root@em13c ~]# gcloud components update
	Your current Cloud SDK version is: 0.9.85
	You will be upgraded to version: 92.0.0
	┌────────────────────────────────────────────────────────┐
	│ These components will be updated. │
	├─────────────────────────────────┬────────────┬─────────┤
	│ Name │ Version │ Size │
	├─────────────────────────────────┼────────────┼─────────┤
	│ BigQuery Command Line Tool │ 2.0.18 │ < 1 MiB │
	│ Cloud SDK Core Libraries │ 2016.01.12 │ 3.1 MiB │
	│ Cloud Storage Command Line Tool │ 4.16 │ 2.6 MiB │
	│ Default set of gcloud commands │ │ │
	└─────────────────────────────────┴────────────┴─────────┘
	┌────────────────────────────────────────────────────────────────────────────┐
	│ These components will be installed. │
	├─────────────────────────────────────────────────────┬────────────┬─────────┤
	│ Name │ Version │ Size │
	├─────────────────────────────────────────────────────┼────────────┼─────────┤
	│ BigQuery Command Line Tool (Platform Specific) │ 2.0.18 │ < 1 MiB │
	│ Cloud SDK Core Libraries (Platform Specific) │ 2015.11.24 │ < 1 MiB │
	│ Cloud Storage Command Line Tool (Platform Specific) │ 4.15 │ < 1 MiB │
	└─────────────────────────────────────────────────────┴────────────┴─────────┘
	For the latest full release notes, please visit:
	https://cloud.google.com/sdk/release_notes
	Do you want to continue (Y/n)? y
	╔════════════════════════════════════════════════════════════╗
	╠═ Creating update staging area ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Uninstalling: BigQuery Command Line Tool ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Uninstalling: Cloud SDK Core Libraries ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Uninstalling: Cloud Storage Command Line Tool ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Uninstalling: Default set of gcloud commands ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: BigQuery Command Line Tool ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: BigQuery Command Line Tool (Platform Spec... ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: Cloud SDK Core Libraries ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: Cloud SDK Core Libraries (Platform Specific) ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: Cloud Storage Command Line Tool ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: Cloud Storage Command Line Tool (Platform... ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Installing: Default set of gcloud commands ═╣
	╠════════════════════════════════════════════════════════════╣
	╠═ Creating backup and activating new installation ═╣
	╚════════════════════════════════════════════════════════════╝
	Start a new shell for the changes to take effect.
	Update done!
	[root@em13c ~]# gcloud compute disks list
	NAME ZONE SIZE_GB TYPE STATUS
	data us-east1-b 100 pd-standard READY
	em13c us-east1-b 10 pd-standard READY
	fra us-east1-b 50 pd-standard READY
	ocrv1 us-east1-b 10 pd-standard READY
	ocrv2 us-east1-b 10 pd-standard READY
	ocrv3 us-east1-b 10 pd-standard READY
	ora us-east1-b 75 pd-standard READY
	[root@em13c ~]# gcloud compute disks create --size="50GB" --zone=us-east1-b asm-data1
	WARNING: You have selected a disk size of under [200GB]. This may result in poor I/O performance. For more information, see: https://developers.google.com/compute/docs/disks/persi
	stent-disks#pdperformance.
	Created [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/disks/asm-data1].
	NAME ZONE SIZE_GB TYPE STATUS
	asm-data1 us-east1-b 50 pd-standard READY
	[root@em13c ~]# gcloud compute disks list
	NAME ZONE SIZE_GB TYPE STATUS
	asm-data1 us-east1-b 50 pd-standard READY
	data us-east1-b 100 pd-standard READY
	em13c us-east1-b 10 pd-standard READY
	fra us-east1-b 50 pd-standard READY
	ocrv1 us-east1-b 10 pd-standard READY
	ocrv2 us-east1-b 10 pd-standard READY
	ocrv3 us-east1-b 10 pd-standard READY
	ora us-east1-b 75 pd-standard READY
	[root@em13c ~]# gcloud compute disks describe asm-data1 --zone us-east1-b
	creationTimestamp: '2016-01-19T08:38:13.404-08:00'
	id: '4213006549044680858'
	kind: compute#disk
	name: asm-data1
	selfLink: https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/disks/asm-data1
	sizeGb: '50'
	status: READY
	type: https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/diskTypes/pd-standard
	zone: https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b
	[root@em13c ~]# gcloud compute disks create --size="75GB" --zone=us-east1-b asm-fra1
	WARNING: You have selected a disk size of under [200GB]. This may result in poor I/O performance. For more information, see: https://developers.google.com/compute/docs/disks/persistent-disks#pdperformance.
	Created [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/disks/asm-fra1].
	NAME ZONE SIZE_GB TYPE STATUS
	asm-fra1 us-east1-b 75 pd-standard READY
	[root@em13c ~]# gcloud compute disks list
	NAME ZONE SIZE_GB TYPE STATUS
	asm-data1 us-east1-b 50 pd-standard READY
	asm-fra1 us-east1-b 75 pd-standard READY
	data us-east1-b 100 pd-standard READY
	em13c us-east1-b 10 pd-standard READY
	fra us-east1-b 50 pd-standard READY
	ocrv1 us-east1-b 10 pd-standard READY
	ocrv2 us-east1-b 10 pd-standard READY
	ocrv3 us-east1-b 10 pd-standard READY
	ora us-east1-b 75 pd-standard READY
	[root@em13c ~]# gcloud compute instances describe em13c --zone us-east1-b | grep deviceName
	deviceName: em13c
	deviceName: ora
	deviceName: data
	deviceName: fra
	deviceName: ocrv1
	deviceName: ocrv2
	deviceName: ocrv3
	[root@em13c ~]# gcloud compute instances attach-disk em13c --disk asm-data1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances attach-disk em13c --disk asm-fra1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances describe em13c --zone us-east1-b | grep deviceName
	deviceName: em13c
	deviceName: ora
	deviceName: data
	deviceName: fra
	deviceName: ocrv1
	deviceName: ocrv2
	deviceName: ocrv3
	deviceName: persistent-disk-7
	deviceName: persistent-disk-8
	[root@em13c ~]# Detach and Attach with the right deviceName.
	[root@em13c ~]# gcloud compute instances detach-disk em13c --disk asm-data1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances detach-disk em13c --disk asm-fra1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances attach-disk em13c --disk asm-data1 --device-name asm-data1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances attach-disk em13c --disk asm-fra1 --device-name asm-fra1 --zone us-east1-b
	Updated [https://www.googleapis.com/compute/v1/projects/***********/zones/us-east1-b/instances/em13c].
	[root@em13c ~]# gcloud compute instances describe em13c --zone us-east1-b | grep deviceName
	deviceName: em13c
	deviceName: ora
	deviceName: data
	deviceName: fra
	deviceName: ocrv1
	deviceName: ocrv2
	deviceName: ocrv3
	deviceName: asm-data1
	deviceName: asm-fra1
	[root@em13c ~]# lsblk
	NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
	sda 8:0 0 10G 0 disk
	└─sda1 8:1 0 10G 0 part /
	sdb 8:16 0 75G 0 disk
	└─sdb1 8:17 0 75G 0 part /u01
	sdc 8:32 0 100G 0 disk
	└─sdc1 8:33 0 100G 0 part /u02
	sdd 8:48 0 50G 0 disk
	└─sdd1 8:49 0 50G 0 part /u03
	sde 8:64 0 10G 0 disk
	└─sde1 8:65 0 10G 0 part
	sdf 8:80 0 10G 0 disk
	└─sdf1 8:81 0 10G 0 part
	sdg 8:96 0 10G 0 disk
	└─sdg1 8:97 0 10G 0 part
	sdh 8:112 0 50G 0 disk
	sdi 8:128 0 75G 0 disk
	[root@em13c ~]# ls -ltra /dev/sd[h-i]
	brw-rw----. 1 root disk 8, 112 Jan 19 17:03 /dev/sdh
	brw-rw----. 1 root disk 8, 128 Jan 19 17:04 /dev/sdi
	[root@em13c ~]# fdisk /dev/sdh
	Welcome to fdisk (util-linux 2.23.2).
	Changes will remain in memory only, until you decide to write them.
	Be careful before using the write command.
	Device does not contain a recognized partition table
	Building a new DOS disklabel with disk identifier 0x2b77232c.
	The device presents a logical sector size that is smaller than
	the physical sector size. Aligning to a physical sector (or optimal
	I/O) size boundary is recommended, or performance may be impacted.
	Command (m for help): p
	Disk /dev/sdh: 53.7 GB, 53687091200 bytes, 104857600 sectors
	Units = sectors of 1 * 512 = 512 bytes
	Sector size (logical/physical): 512 bytes / 4096 bytes
	I/O size (minimum/optimal): 4096 bytes / 4096 bytes
	Disk label type: dos
	Disk identifier: 0x2b77232c
	Device Boot Start End Blocks Id System
	Command (m for help):
	Command (m for help): n
	Partition type:
	p primary (0 primary, 0 extended, 4 free)
	e extended
	Select (default p): p
	Partition number (1-4, default 1):
	First sector (2048-104857599, default 2048):
	Using default value 2048
	Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599):
	Using default value 104857599
	Partition 1 of type Linux and of size 50 GiB is set
	Command (m for help): w
	The partition table has been altered!
	Calling ioctl() to re-read partition table.
	Syncing disks.
	[root@em13c ~]# fdisk /dev/sdi
	Welcome to fdisk (util-linux 2.23.2).
	Changes will remain in memory only, until you decide to write them.
	Be careful before using the write command.
	Device does not contain a recognized partition table
	Building a new DOS disklabel with disk identifier 0x692b04ee.
	The device presents a logical sector size that is smaller than
	the physical sector size. Aligning to a physical sector (or optimal
	I/O) size boundary is recommended, or performance may be impacted.
	Command (m for help): n
	Partition type:
	p primary (0 primary, 0 extended, 4 free)
	e extended
	Select (default p): p
	Partition number (1-4, default 1):
	First sector (2048-157286399, default 2048):
	Using default value 2048
	Last sector, +sectors or +size{K,M,G} (2048-157286399, default 157286399):
	Using default value 157286399
	Partition 1 of type Linux and of size 75 GiB is set
	Command (m for help): w
	The partition table has been altered!
	Calling ioctl() to re-read partition table.
	Syncing disks.
	[root@em13c ~]# ls -l /dev/sd[h-i]*
	brw-rw----. 1 root disk 8, 112 Jan 19 17:57 /dev/sdh
	brw-rw----. 1 root disk 8, 113 Jan 19 17:57 /dev/sdh1
	brw-rw----. 1 root disk 8, 128 Jan 19 17:57 /dev/sdi
	brw-rw----. 1 root disk 8, 129 Jan 19 17:57 /dev/sdi1
	[root@em13c ~]# oracleasm listdisks
	OCRV1
	OCRV2
	OCRV3
	[root@em13c ~]# oracleasm createdisk DATA1 /dev/sdh1
	Writing disk header: done
	Instantiating disk: done
	[root@em13c ~]# oracleasm createdisk FRA1 /dev/sdi1
	Writing disk header: done
	Instantiating disk: done
	[root@em13c ~]# oracleasm listdisks
	DATA1
	FRA1
	OCRV1
	OCRV2
	OCRV3
	[grid@em13c bin]$ ./kfod disks=all
	--------------------------------------------------------------------------------
	Disk Size Path User Group
	================================================================================
	1: 51199 Mb ORCL:DATA1
	2: 76799 Mb ORCL:FRA1
	3: 10239 Mb ORCL:OCRV1
	4: 10239 Mb ORCL:OCRV2
	5: 10239 Mb ORCL:OCRV3
	--------------------------------------------------------------------------------
	ORACLE_SID ORACLE_HOME
	================================================================================
	+ASM /u01/app/grid/product/12.1.0/grid
	[grid@em13c ~]$ sqlplus / as sysasm
	SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 19 18:20:39 2016
	Copyright (c) 1982, 2014, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Automatic Storage Management option
	SYS@+ASM> col name for a20
	SYS@+ASM> col compatibility for a20
	SYS@+ASM> col database_compatibility for a20
	SYS@+ASM> select group_number, name, compatibility, database_compatibility
	2 from v$asm_diskgroup;
	GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILI
	------------ -------------------- -------------------- --------------------
	1 OCRV 12.1.0.0.0 10.1.0.0.0
	SYS@+ASM> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY
	2 DISK 'ORCL:DATA1'
	3 ATTRIBUTE 'au_size'='4M',
	4 'compatible.asm' = '12.1.0.0.0',
	5 'compatible.rdbms' = '12.1.0.0.0';
	Diskgroup created.
	SYS@+ASM> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY
	2 DISK 'ORCL:FRA1'
	3 ATTRIBUTE 'au_size'='4M',
	4 'compatible.asm' = '12.1.0.0.0',
	5 'compatible.rdbms' = '12.1.0.0.0';
	Diskgroup created.
	SYS@+ASM> select group_number, name, compatibility, database_compatibility
	2 from v$asm_diskgroup
	3 order by group_number;
	GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILI
	------------ -------------------- -------------------- --------------------
	1 OCRV 12.1.0.0.0 10.1.0.0.0
	2 DATA 12.1.0.0.0 12.1.0.0.0
	3 FRA 12.1.0.0.0 12.1.0.0.0
	SYS@+ASM> @asm_diskgroups.sql
	Disk Group Sector Block Allocation
	Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Free Size (MB) Pct. Used Comp Compdb
	------------------------- ------- ------- ------------ ----------- ------ --------------- -------------- -------------- --------- ------------ ------------
	DATA 4,096 4,096 4,194,304 MOUNTED EXTERN 51,196 84 51,112 .16 12.1.0.0.0 12.1.0.0.0
	FRA 4,096 4,096 4,194,304 MOUNTED EXTERN 76,796 84 76,712 .11 12.1.0.0.0 12.1.0.0.0
	OCRV 4,096 4,096 4,194,304 MOUNTED HIGH 30,708 288 30,420 .94 12.1.0.0.0 10.1.0.0.0
	--------------- -------------- --------------
	Grand Total: 158,700 456 158,244
	SYS@+ASM> @asm_disks.sql
	Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used
	------------------------- -------------------- -------------------- -------------------- -------------- -------------- ---------
	DATA ORCL:DATA1 DATA1 DATA1 51,196 84 .16
	************************* -------------- --------------
	51,196 84
	FRA ORCL:FRA1 FRA1 FRA1 76,796 84 .11
	************************* -------------- --------------
	76,796 84
	OCRV ORCL:OCRV1 OCRV1 OCRV1 10,236 96 .94
	ORCL:OCRV2 OCRV2 OCRV2 10,236 96 .94
	ORCL:OCRV3 OCRV3 OCRV3 10,236 96 .94
	************************* -------------- --------------
	30,708 288
	-------------- --------------
	Grand Total: 158,700 456

view raw gistfile1.txt hosted with ❤ by GitHub

dbaascli - CLI for different tools to be used with Oracle Cloud DB

	[oracle@ATLAS:/home/oracle]$ rpm -qa|grep -i dbaastools
	dbaastools-1.0-1+15.3.6.0.0_150928.2115.x86_64
	[oracle@ATLAS:/home/oracle]$ which dbaascli
	/usr/bin/dbaascli
	[oracle@ATLAS:/home/oracle]$ ls -ltra /usr/bin/dbaascli
	lrwxrwxrwx 1 root root 33 Sep 29 06:43 /usr/bin/dbaascli -> /var/opt/oracle/dbaascli/dbaascli
	[oracle@ATLAS:/var/opt/oracle/dbaascli]$ ll
	total 40
	-r-xr-xr-x 1 oracle oinstall 20701 Sep 29 04:55 dbaascli
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 Documentation
	drwxr-xr-x 12 oracle oinstall 4096 Sep 29 06:43 modules
	drwxr-xr-x 3 oracle oinstall 4096 Sep 29 06:43 modulesperllib
	drwxr-xr-x 9 oracle oinstall 4096 Sep 29 06:43 perllib
	[oracle@ATLAS:/var/opt/oracle/dbaascli]$ ll modules/
	total 52
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 database
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 dataguard
	-r-xr-xr-x 1 oracle oinstall 1882 Sep 29 04:55 dbpatchm.pm
	drwxr-xr-x 4 oracle oinstall 4096 Nov 10 19:13 dv
	drwxr-xr-x 3 oracle oinstall 4096 Sep 29 06:43 examples
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 glassfish
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 listener
	-r-xr-xr-x 1 oracle oinstall 2268 Sep 29 04:55 mrec.pm
	drwxr-xr-x 5 oracle oinstall 4096 Sep 29 06:43 netsec
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 ore
	-r-xr-xr-x 1 oracle oinstall 2978 Sep 29 04:55 orec.pm
	drwxr-xr-x 3 oracle oinstall 4096 Sep 29 06:43 tde
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 tfa
	[oracle@ATLAS:/var/opt/oracle/dbaascli]$ ll Documentation/
	total 16
	-r-xr-xr-x 1 oracle oinstall 714 Sep 29 04:55 CliDocumentation.txt
	-r-xr-xr-x 1 oracle oinstall 7835 Sep 29 04:55 Module_Design_Guide.txt
	-r-xr-xr-x 1 oracle oinstall 830 Sep 29 04:55 template.pm
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/home/oracle]$ dbaascli help
	DBAAS CLI version 1.0.0
	Help for dbaascli
	dbaascli is a command line interface for different tools to be used with Oracle Cloud DB.
	This command line interface supports logging, command history and autocomplete.
	Logging is done on a session level. Each time dbaascli is run the last log is overwritten.
	To access command history use the up/down arrow keys as you would normally do in a terminal.
	Autocomplete uses the Tab key. Autocomplete will complete to the longest common string in case there is not an exact match.
	To obtain help on a command use the help keyword at the begining or end.
	Autocomplete is supported if the first word is the "help" keyword.
	Special commands are available.
	The list of special commands is:
	-H
	-HELP
	? - Get dbaascli help
	CLEAR - Clear the screen
	HELP - Get dbaascli help
	HIST - Print command line history
	HISTORY - Print command line history
	LIST - List available commands
	Q - Exit Command Line Interface
	QUIT - Exit Command Line Interface
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/home/oracle]$ dbaascli list
	DBAAS CLI version 1.0.0
	Very Long Text, press q to quit
	Available commands:
	database bounce
	database start
	database status
	database stop
	dataguard failover
	dataguard status
	dataguard switchover
	dbpatchm
	dv off
	dv off cdb
	dv off pdb
	dv on
	dv on cdb
	dv on pdb
	glassfish start
	glassfish status
	glassfish stop
	listener bounce
	listener start
	listener status
	listener stop
	mrec
	netsec config
	netsec config encryption
	netsec config integrity
	netsec deconfig
	netsec deconfig encryption
	netsec deconfig integrity
	netsec status
	netsec status encryption
	netsec status integrity
	ore setup
	orec
	tde config
	tde rotate
	tde rotate masterkey
	tde status
	tfa install
	tfa uninstall
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/home/oracle]$ dbaascli database help
	DBAAS CLI version 1.0.0
	Executing command database help
	Valid Subcommands:
	bounce
	stop
	status
	start
	[oracle@ATLAS:/home/oracle]$ dbaascli dataguard help
	DBAAS CLI version 1.0.0
	Executing command dataguard help
	Valid Subcommands:
	switchover
	status
	failover
	[oracle@ATLAS:/home/oracle]$ dbaascli listener help
	DBAAS CLI version 1.0.0
	Executing command listener help
	Valid Subcommands:
	bounce
	stop
	status
	start
	[oracle@ATLAS:/home/oracle]$ # Database Vault
	[oracle@ATLAS:/home/oracle]$ dbaascli dv help
	DBAAS CLI version 1.0.0
	Executing command dv help
	Valid Subcommands:
	on
	off
	[oracle@ATLAS:/home/oracle]$ dbaascli dbpatchm help
	DBAAS CLI version 1.0.0
	Executing command dbpatchm help
	Command: dbpatchm
	Help for DB patching
	Parameters:
	--run Format: Not defined Required: YES
	dbpatchm --run -prereq
	dbpatchm --run -setup
	dbpatchm --run -config
	dbpatchm --run -apply
	dbpatchm --run -clonedb
	dbpatchm --run -cleanup
	dbpatchm --run -reset
	dbpatchm --run -list_patches
	dbpatchm --run -rollback
	[oracle@ATLAS:/home/oracle]$ # Media Recovery
	[oracle@ATLAS:/home/oracle]$ dbaascli mrec help
	DBAAS CLI version 1.0.0
	Executing command mrec help
	Command: mrec
	Help for MREC tool.
	Parameters:
	--args Format: Not defined Required: NO
	Usage:
	mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name>
	mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name> -sid <oracle_sid>
	Where:
	-help = Print this help.
	-oss_cfgfile = full path to OSS config file.
	-old_hostname = Name of the host to recover.
	-sid = ORACLE SID of the DB instance to recover. Default is orcl
	Examples:
	mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01
	mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01 -sid orcl
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules]$ dbaascli list | grep database
	database bounce
	database start
	database status
	database stop
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules/database]$ ls -l
	total 16
	-r-xr-xr-x 1 oracle oinstall 2377 Sep 29 04:55 bounce.pm
	-r-xr-xr-x 1 oracle oinstall 3634 Sep 29 04:55 start.pm
	-r-xr-xr-x 1 oracle oinstall 3437 Sep 29 04:55 status.pm
	-r-xr-xr-x 1 oracle oinstall 3007 Sep 29 04:55 stop.pm
	[oracle@ATLAS:/home/oracle]$ dbaascli database status
	DBAAS CLI version 1.0.0
	Executing command database status
	Database Status:
	Database is open
	Database name: ATLAS
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	PL/SQL Release 12.1.0.2.0 - Production
	CORE 12.1.0.2.0 Production
	TNS for Linux: Version 12.1.0.2.0 - Production
	NLSRTL Version 12.1.0.2.0 - Production
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules]$ dbaascli list | grep listener
	listener bounce
	listener start
	listener status
	listener stop
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules/listener]$ ls -l
	total 16
	-r-xr-xr-x 1 oracle oinstall 1255 Sep 29 04:55 bounce.pm
	-r-xr-xr-x 1 oracle oinstall 1337 Sep 29 04:55 start.pm
	-r-xr-xr-x 1 oracle oinstall 1336 Sep 29 04:55 status.pm
	-r-xr-xr-x 1 oracle oinstall 1331 Sep 29 04:55 stop.pm
	[oracle@ATLAS:/home/oracle]$ dbaascli listener status
	DBAAS CLI version 1.0.0
	Executing command listener status
	Starting listener
	LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-NOV-2015 19:02:44
	Copyright (c) 1991, 2014, Oracle. All rights reserved.
	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX.compute-xxxxxxxx.oraclecloud.internal)(PORT=1521)))
	TNS-12541: TNS:no listener
	TNS-12560: TNS:protocol adapter error
	TNS-00511: No listener
	Linux Error: 111: Connection refused
	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
	TNS-12541: TNS:no listener
	TNS-12560: TNS:protocol adapter error
	TNS-00511: No listener
	Linux Error: 2: No such file or directory
	[oracle@ATLAS:/home/oracle]$ dbaascli listener start
	DBAAS CLI version 1.0.0
	Executing command listener start
	Starting listener
	LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-NOV-2015 19:03:02
	Copyright (c) 1991, 2014, Oracle. All rights reserved.
	Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
	TNSLSNR for Linux: Version 12.1.0.2.0 - Production
	System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
	Log messages written to /u01/app/oracle/diag/tnslsnr/ATLAS/listener/alert/log.xml
	Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXX.compute-xxxxxxxx.oraclecloud.internal)(PORT=1521)))
	Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX.compute-xxxxxxxx.oraclecloud.internal)(PORT=1521)))
	STATUS of the LISTENER
	------------------------
	Alias LISTENER
	Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
	Start Date 10-NOV-2015 19:03:02
	Uptime 0 days 0 hr. 0 min. 0 sec
	Trace Level off
	Security ON: Local OS Authentication
	SNMP OFF
	Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
	Listener Log File /u01/app/oracle/diag/tnslsnr/ATLAS/listener/alert/log.xml
	Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXX.compute-xxxxxxxx.oraclecloud.internal)(PORT=1521)))
	(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
	The listener supports no services
	The command completed successfully
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules]$ dbaascli list | grep netsec
	netsec config
	netsec config encryption
	netsec config integrity
	netsec deconfig
	netsec deconfig encryption
	netsec deconfig integrity
	netsec status
	netsec status encryption
	netsec status integrity
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules/netsec]$ ls -lR
	.:
	total 24
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 config
	-r-xr-xr-x 1 oracle oinstall 3847 Sep 29 04:55 config.pm
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 deconfig
	-r-xr-xr-x 1 oracle oinstall 1751 Sep 29 04:55 deconfig.pm
	drwxr-xr-x 2 oracle oinstall 4096 Sep 29 06:43 status
	-r-xr-xr-x 1 oracle oinstall 1596 Sep 29 04:55 status.pm
	./config:
	total 8
	-r-xr-xr-x 1 oracle oinstall 2390 Sep 29 04:55 encryption.pm
	-r-xr-xr-x 1 oracle oinstall 2433 Sep 29 04:55 integrity.pm
	./deconfig:
	total 8
	-r-xr-xr-x 1 oracle oinstall 2330 Sep 29 04:55 encryption.pm
	-r-xr-xr-x 1 oracle oinstall 2316 Sep 29 04:55 integrity.pm
	./status:
	total 8
	-r-xr-xr-x 1 oracle oinstall 1677 Sep 29 04:55 encryption.pm
	-r-xr-xr-x 1 oracle oinstall 1657 Sep 29 04:55 integrity.pm
	[oracle@ATLAS:/var/opt/oracle/dbaascli/modules/database]$ dbaascli netsec status
	DBAAS CLI version 1.0.0
	Executing command netsec status
	Displaying configuration for network encryption:
	Server encryption type = required
	Server encryption methods = AES256,AES192,AES128
	Client encryption status is Disabled
	Displaying configuration for network integrity:
	Server integrity checksum_level = required
	Server integrity methods = SHA1
	Client integrity status is Disabled

view raw gistfile1.txt hosted with ❤ by GitHub

Enable EPEL Repository - Oracle Database Cloud Service

	[oracle@ATLAS:/home/oracle]$ cat /etc/redhat-release
	Red Hat Enterprise Linux Server release 6.4 (Santiago)
	[oracle@ATLAS:/home/oracle]$ uname -a
	Linux ATLAS 2.6.39-400.109.1.el6uek.x86_64 #1 SMP Tue Jun 4 23:21:51 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# http://www.tecmint.com/how-to-enable-epel-repository-for-rhel-centos-6-5/
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[root@ATLAS:/root]# wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
	--2015-11-10 17:21:54-- http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
	Resolving download.fedoraproject.org... 209.132.181.15, 209.132.181.16, 66.35.62.162, ...
	Connecting to download.fedoraproject.org|209.132.181.15|:80... connected.
	HTTP request sent, awaiting response... 302 Found
	Location: http://mirror.sfo12.us.leaseweb.net/epel/6/x86_64/epel-release-6-8.noarch.rpm [following]
	--2015-11-10 17:21:55-- http://mirror.sfo12.us.leaseweb.net/epel/6/x86_64/epel-release-6-8.noarch.rpm
	Resolving mirror.sfo12.us.leaseweb.net... 209.58.135.187
	Connecting to mirror.sfo12.us.leaseweb.net|209.58.135.187|:80... connected.
	HTTP request sent, awaiting response... 200 OK
	Length: 14540 (14K) [application/x-redhat-package-manager]
	Saving to: epel-release-6-8.noarch.rpm
	100%[=================================================================================================================================>] 14,540 --.-K/s in 0.08s
	2015-11-10 17:21:55 (189 KB/s) - epel-release-6-8.noarch.rpm
	[root@ATLAS:/root]# rpm -ivh epel-release-6-8.noarch.rpm
	warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
	Preparing... ########################################### [100%]
	1:epel-release ########################################### [100%]
	[root@ATLAS:/etc]# yum repolist
	Error: Cannot retrieve metalink for repository: epel. Please verify its path and try again
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# https://community.hpcloud.com/article/centos-63-instance-giving-cannot-retrieve-metalink-repository-epel-error
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[root@ATLAS:/etc/yum.repos.d]# cat epel.repo
	[epel]
	name=Extra Packages for Enterprise Linux 6 - $basearch
	#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
	mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
	failovermethod=priority
	enabled=1
	gpgcheck=1
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	[epel-debuginfo]
	name=Extra Packages for Enterprise Linux 6 - $basearch - Debug
	#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug
	mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch
	failovermethod=priority
	enabled=0
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	gpgcheck=1
	[epel-source]
	name=Extra Packages for Enterprise Linux 6 - $basearch - Source
	#baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS
	mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch
	failovermethod=priority
	enabled=0
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	gpgcheck=1
	[root@ATLAS:/etc/yum.repos.d]# sed -i "s/mirrorlist=https/mirrorlist=http/" /etc/yum.repos.d/epel.repo
	[root@ATLAS:/etc/yum.repos.d]# cat epel.repo
	[epel]
	name=Extra Packages for Enterprise Linux 6 - $basearch
	#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
	mirrorlist=http://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
	failovermethod=priority
	enabled=1
	gpgcheck=1
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	[epel-debuginfo]
	name=Extra Packages for Enterprise Linux 6 - $basearch - Debug
	#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug
	mirrorlist=http://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch
	failovermethod=priority
	enabled=0
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	gpgcheck=1
	[epel-source]
	name=Extra Packages for Enterprise Linux 6 - $basearch - Source
	#baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS
	mirrorlist=http://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch
	failovermethod=priority
	enabled=0
	gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	gpgcheck=1
	[root@ATLAS:/etc/yum.repos.d]# yum repolist
	epel/metalink | 12 kB 00:00
	epel | 4.3 kB 00:00
	epel/primary_db | 5.7 MB 00:01
	repo id repo name status
	epel Extra Packages for Enterprise Linux 6 - x86_64 11,750
	public_ol6_latest Oracle Linux 6Server Latest (x86_64) 32,425
	public_ol6_u4_base Oracle Linux 6Server Update 4 installation media copy (x86_64) 8,396
	repolist: 52,571
	[root@ATLAS:/root]# yum install tmux
	Setting up Install Process
	Resolving Dependencies
	--> Running transaction check
	---> Package tmux.x86_64 0:1.6-3.el6 will be installed
	--> Finished Dependency Resolution
	Dependencies Resolved
	==========================================================================================================================================================================
	Package Arch Version Repository Size
	==========================================================================================================================================================================
	Installing:
	tmux x86_64 1.6-3.el6 epel 208 k
	Transaction Summary
	==========================================================================================================================================================================
	Install 1 Package(s)
	Total download size: 208 k
	Installed size: 494 k
	Is this ok [y/N]: y
	Downloading Packages:
	tmux-1.6-3.el6.x86_64.rpm | 208 kB 00:00
	warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
	Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	Importing GPG key 0x0608B895:
	Userid : EPEL (6) <epel@fedoraproject.org>
	Package: epel-release-6-8.noarch (installed)
	From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
	Is this ok [y/N]: y
	Running rpm_check_debug
	Running Transaction Test
	Transaction Test Succeeded
	Running Transaction
	Warning: RPMDB altered outside of yum.
	Installing : tmux-1.6-3.el6.x86_64 1/1
	Verifying : tmux-1.6-3.el6.x86_64 1/1
	Installed:
	tmux.x86_64 0:1.6-3.el6
	Complete!

view raw gistfile1.txt hosted with ❤ by GitHub

PSU bundle patch: Installed in the CDB but not in the PDB.

	[oracle@rhec2lab1:/home/oracle]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 20 15:04:33 2015
	Copyright (c) 1982, 2014, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	SYS@ATLAS> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	3 PDB MOUNTED
	[oracle@rhec2lab1:/u01/app/oracle/product/12.1.0.2/db1/OPatch]$ ./datapatch -verbose
	SQL Patching tool version 12.1.0.2.0 on Tue Oct 20 15:04:03 2015
	Copyright (c) 2015, Oracle. All rights reserved.
	Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10849_2015_10_20_15_04_03/sqlpatch_invocation.log
	Connecting to database...OK
	Note: Datapatch will only apply or rollback SQL fixes for PDBs
	that are in an open state, no patches will be applied to closed PDBs.
	Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
	(Doc ID 1585822.1)
	Bootstrapping registry and package to current versions...done
	Determining current state...done
	Current state of SQL patches:
	Bundle series PSU:
	ID 4 in the binary registry and not installed in any PDB
	Adding patches to installation queue and performing prereq checks...
	Installation queue:
	For the following PDBs: CDB$ROOT PDB$SEED
	Nothing to roll back
	The following patches will be applied:
	20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
	Installing patches...
	Patch installation complete. Total patches installed: 2
	Validating logfiles...
	Patch 20831110 apply (pdb CDB$ROOT): SUCCESS
	logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_ATLAS_CDBROOT_2015Oct20_15_04_22.log (no errors)
	Patch 20831110 apply (pdb PDB$SEED): SUCCESS
	logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_ATLAS_PDBSEED_2015Oct20_15_04_25.log (no errors)
	SQL Patching tool complete on Tue Oct 20 15:04:27 2015
	[oracle@rhec2lab1:/home/oracle]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 20 15:04:33 2015
	Copyright (c) 1982, 2014, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	SYS@ATLAS> alter pluggable database pdb open;
	Warning: PDB altered with errors.
	SYS@ATLAS> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	3 PDB READ WRITE
	[oracle@rhec2lab1:/u01/app/oracle/diag/rdbms/atlas/ATLAS/trace]$ tail -n 14 alert_ATLAS.log
	Tue Oct 20 15:04:49 2015
	alter pluggable database open
	ORA-65000 signalled during: alter pluggable database open...
	alter pluggable database pdb open
	Tue Oct 20 15:05:06 2015
	Database Characterset for PDB is WE8MSWIN1252
	***************************************************************
	WARNING: Pluggable Database PDB with pdb id - 3 is
	altered with errors or warnings. Please look into
	PDB_PLUG_IN_VIOLATIONS view for more details.
	***************************************************************
	Opening pdb PDB (3) with no Resource Manager plan active
	Pluggable database PDB opened read write
	Completed: alter pluggable database pdb open
	[oracle@rhec2lab1:/home/oracle]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 20 15:04:33 2015
	Copyright (c) 1982, 2014, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	SYS@ATLAS> col cause for a10
	SYS@ATLAS> col name for a10
	SYS@ATLAS> col message for a35 word_wrapped
	SYS@ATLAS> select name, cause, type, message, status
	2 from PDB_PLUG_IN_VIOLATIONS
	3 where name = 'PDB' and type = 'ERROR';
	NAME CAUSE TYPE MESSAGE STATUS
	---------- ---------- --------- ----------------------------------- ---------
	PDB SQL Patch ERROR PSU bundle patch 4 (Database Patch PENDING
	Set Update : 12.1.0.2.4
	(20831110)): Installed in the CDB
	but not in the PDB.

view raw gistfile1.txt hosted with ❤ by GitHub

	[oracle@rhec2lab1:/u01/app/oracle/product/12.1.0.2/db1/OPatch]$ ./datapatch -verbose
	SQL Patching tool version 12.1.0.2.0 on Tue Oct 20 16:00:27 2015
	Copyright (c) 2015, Oracle. All rights reserved.
	Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_13061_2015_10_20_16_00_27/sqlpatch_invocation.log
	Connecting to database...OK
	Note: Datapatch will only apply or rollback SQL fixes for PDBs
	that are in an open state, no patches will be applied to closed PDBs.
	Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
	(Doc ID 1585822.1)
	Bootstrapping registry and package to current versions...done
	Determining current state...done
	Current state of SQL patches:
	Bundle series PSU:
	ID 4 in the binary registry and ID 4 in PDB CDB$ROOT, ID 4 in PDB PDB$SEED
	Adding patches to installation queue and performing prereq checks...
	Installation queue:
	For the following PDBs: CDB$ROOT PDB$SEED
	Nothing to roll back
	Nothing to apply
	For the following PDBs: PDB
	Nothing to roll back
	The following patches will be applied:
	20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
	Installing patches...
	Patch installation complete. Total patches installed: 1
	Validating logfiles...
	Patch 20831110 apply (pdb PDB): SUCCESS
	logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_ATLAS_PDB_2015Oct20_16_00_45.log (no errors)
	SQL Patching tool complete on Tue Oct 20 16:00:46 2015
	Pluggable database altered.
	[oracle@rhec2lab1:/home/oracle]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 20 16:01:54 2015
	Copyright (c) 1982, 2014, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	SYS@ATLAS> col cause for a10
	SYS@ATLAS> col name for a10
	SYS@ATLAS> col message for a35 word_wrapped
	SYS@ATLAS> select name, cause, type, message, status
	2 from PDB_PLUG_IN_VIOLATIONS
	3 where name = 'PDB' and type = 'ERROR';
	NAME CAUSE TYPE MESSAGE STATUS
	---------- ---------- --------- ----------------------------------- ---------
	PDB SQL Patch ERROR PSU bundle patch 4 (Database Patch PENDING
	Set Update : 12.1.0.2.4
	(20831110)): Installed in the CDB
	but not in the PDB.
	SYS@ATLAS> alter pluggable database pdb close;
	Pluggable database altered.
	SYS@ATLAS> alter pluggable database pdb open;
	Pluggable database altered.
	SYS@ATLAS> col cause for a10
	SYS@ATLAS> col name for a10
	SYS@ATLAS> col message for a35 word_wrapped
	SYS@ATLAS> select name, cause, type, message, status
	2 from PDB_PLUG_IN_VIOLATIONS
	3 where name = 'PDB' and type = 'ERROR';
	NAME CAUSE TYPE MESSAGE STATUS
	---------- ---------- --------- ----------------------------------- ---------
	PDB SQL Patch ERROR PSU bundle patch 4 (Database Patch RESOLVED
	Set Update : 12.1.0.2.4
	(20831110)): Installed in the CDB
	but not in the PDB.

view raw gistfile1.txt hosted with ❤ by GitHub

Adding Oracle ASM disk to Amazon EC2 with command line tools.

	[oracle@rhec2lab1:/home/oracle]$ . oraenv
	ORACLE_SID = [+ASM] ?
	The Oracle base remains unchanged with value /u01/app/grid
	[oracle@rhec2lab1:/home/oracle]$ srvctl status asm
	ASM is running on rhec2lab1
	[oracle@rhec2lab1:/home/oracle]$ srvctl config asm
	ASM home: <CRS home>
	Password file: +DATA/ASM/orapwasm
	ASM listener: LISTENER
	Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.893677453
	ASM diskgroup discovery string:
	[oracle@rhec2lab1:/home/oracle]$ asmcmd -p
	ASMCMD [+] > lsdg
	State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
	MOUNTED EXTERN N 512 4096 4194304 30716 15616 0 15616 0 N DATA/
	MOUNTED EXTERN N 512 4096 4194304 10232 3252 0 3252 0 N FRA/
	ASMCMD [+] > lsdsk
	Path
	ORCL:DATA1
	ORCL:FRA1
	[oracle@rhec2lab1:/home/oracle]$ kfod disks=all
	--------------------------------------------------------------------------------
	Disk Size Path User Group
	================================================================================
	1: 30718 Mb ORCL:DATA1
	2: 10235 Mb ORCL:FRA1
	--------------------------------------------------------------------------------
	ORACLE_SID ORACLE_HOME
	================================================================================
	+ASM /u01/app/grid/12.1.0.2
	[oracle@rhec2lab1:/home/oracle/sh]$ ./oracleasm_device_map.sh
	ASM Disk Mappings
	----------------------------------------------------
	DATA1: /dev/xvde1
	FRA1: /dev/xvdd1
	[oracle@rhec2lab1:/home/oracle]$ ls -l /dev/xv*
	brw-rw----. 1 root disk 202, 0 Oct 30 2015 /dev/xvda
	brw-rw----. 1 root disk 202, 1 Oct 30 2015 /dev/xvda1
	brw-rw----. 1 root disk 202, 16 Oct 30 2015 /dev/xvdb
	brw-rw----. 1 root disk 202, 17 Oct 30 2015 /dev/xvdb1
	brw-rw----. 1 root disk 202, 48 Oct 30 2015 /dev/xvdd
	brw-rw----. 1 root disk 202, 49 Oct 30 2015 /dev/xvdd1
	brw-rw----. 1 root disk 202, 64 Oct 30 2015 /dev/xvde
	brw-rw----. 1 root disk 202, 65 Oct 30 2015 /dev/xvde1
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# http://docs.aws.amazon.com/AWSEC2/latest/CommandLineReference/set-up-ec2-cli-linux.html#setting_up_ec2_command_linux
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@rhec2lab1:/home/oracle]$ mkdir ec2
	[oracle@rhec2lab1:/home/oracle]$ cd ec2
	[oracle@rhec2lab1:/home/oracle/ec2]$ wget http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
	--2015-10-30 10:32:52-- http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
	Resolving s3.amazonaws.com... 54.231.10.176
	Connecting to s3.amazonaws.com|54.231.10.176|:80... connected.
	HTTP request sent, awaiting response... 200 OK
	Length: 17566389 (17M) [binary/octet-stream]
	Saving to: ec2-api-tools.zip
	100%[=======================================================================================================================>] 17,566,389 12.3M/s in 1.4s
	2015-10-30 10:32:54 (12.3 MB/s) - ec2-api-tools.zip
	[oracle@rhec2lab1:/home/oracle/ec2]$ unzip ec2-api-tools.zip -d ec2-api-tools
	Archive: ec2-api-tools.zip
	creating: ec2-api-tools/ec2-api-tools-1.7.5.1/
	inflating: ec2-api-tools/ec2-api-tools-1.7.5.1/THIRDPARTYLICENSE.TXT
	...
	inflating: ec2-api-tools/ec2-api-tools-1.7.5.1/notice.txt
	[oracle@rhec2lab1:/home/oracle/ec2]$ cd ec2-api-tools
	[oracle@rhec2lab1:/home/oracle/ec2/ec2-api-tools]$ ll
	total 4
	drwxr-xr-x. 4 oracle oinstall 4096 Sep 7 07:56 ec2-api-tools-1.7.5.1
	[oracle@rhec2lab1:/home/oracle/ec2/ec2-api-tools]$ cd ec2-api-tools-1.7.5.1/
	[oracle@rhec2lab1:/home/oracle/ec2/ec2-api-tools/ec2-api-tools-1.7.5.1]$ ll
	total 96
	drwxr-xr-x. 2 oracle oinstall 28672 Sep 7 07:56 bin
	drwxr-xr-x. 2 oracle oinstall 4096 Sep 7 07:56 lib
	-rw-r--r--. 1 oracle oinstall 4852 Sep 7 04:01 license.txt
	-rw-r--r--. 1 oracle oinstall 539 Sep 7 04:01 notice.txt
	-rw-r--r--. 1 oracle oinstall 46468 Sep 7 04:01 THIRDPARTYLICENSE.TXT
	[oracle@rhec2lab1:/home/oracle/ec2/ec2-api-tools/ec2-api-tools-1.7.5.1]$ cd
	[oracle@rhec2lab1:/home/oracle]$ export JAVA_HOME="/usr/lib/jvm/jre-1.7.0-openjdk.x86_64"
	[oracle@rhec2lab1:/home/oracle]$ export EC2_HOME=/home/oracle/ec2/ec2-api-tools/ec2-api-tools-1.7.5.1
	[oracle@rhec2lab1:/home/oracle]$ export PATH=$PATH:$EC2_HOME/bin
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# https://console.aws.amazon.com/iam/home?#security_credential
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@rhec2lab1:/home/oracle]$ export AWS_ACCESS_KEY="..."
	[oracle@rhec2lab1:/home/oracle]$ export AWS_SECRET_KEY="..."
	[oracle@rhec2lab1:/home/oracle]$ ec2-describe-regions
	REGION eu-west-1 ec2.eu-west-1.amazonaws.com
	REGION ap-southeast-1 ec2.ap-southeast-1.amazonaws.com
	REGION ap-southeast-2 ec2.ap-southeast-2.amazonaws.com
	REGION eu-central-1 ec2.eu-central-1.amazonaws.com
	REGION ap-northeast-1 ec2.ap-northeast-1.amazonaws.com
	REGION us-east-1 ec2.us-east-1.amazonaws.com
	REGION sa-east-1 ec2.sa-east-1.amazonaws.com
	REGION us-west-1 ec2.us-west-1.amazonaws.com
	REGION us-west-2 ec2.us-west-2.amazonaws.com
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# http://docs.aws.amazon.com/AWSEC2/latest/CommandLineReference/ApiReference-cmd-DescribeVolumes.html
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@rhec2lab1:/home/oracle]$ ec2-describe-volumes
	VOLUME vol-ed40fd01 50 snap-2e0be09c us-east-1a in-use 2015-10-19T13:24:52+0000 io1 500 Not Encrypted
	ATTACHMENT vol-ed40fd01 i-b4accf0b /dev/sdb attached 2015-10-19T13:24:52+0000 true
	VOLUME vol-d940fd35 6 snap-1a0be0a8 us-east-1a in-use 2015-10-19T13:24:52+0000 gp2 18 Not Encrypted
	ATTACHMENT vol-d940fd35 i-b4accf0b /dev/sda1 attached 2015-10-19T13:24:52+0000 true
	VOLUME vol-35853fd9 30 us-east-1a in-use 2015-10-20T15:54:23+0000 standard Not Encrypted
	ATTACHMENT vol-35853fd9 i-b4accf0b /dev/sde attached 2015-10-21T15:10:59+0000 false
	VOLUME vol-8c7ac060 10 us-east-1a in-use 2015-10-20T15:54:50+0000 standard Not Encrypted
	ATTACHMENT vol-8c7ac060 i-b4accf0b /dev/sdd attached 2015-10-20T15:56:40+0000 false
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# http://docs.aws.amazon.com/AWSEC2/latest/CommandLineReference/ApiReference-cmd-CreateVolume.html
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@rhec2lab1:/home/oracle]$ ec2-create-volume --size 10 --availability-zone us-east-1a
	VOLUME vol-cabfc226 10 us-east-1a creating 2015-10-30T14:47:58+0000 standard Not Encrypted
	[oracle@rhec2lab1:/home/oracle]$ ec2-describe-volumes | grep "vol-cabfc226"
	VOLUME vol-cabfc226 10 us-east-1a available 2015-10-30T14:47:58+0000 standard Not Encrypted
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	# http://docs.aws.amazon.com/AWSEC2/latest/CommandLineReference/ApiReference-cmd-AttachVolume.html
	##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##--~~##
	[oracle@rhec2lab1:/home/oracle]$ ec2-attach-volume vol-cabfc226 -i i-b4accf0b -d /dev/sdf
	ATTACHMENT vol-cabfc226 i-b4accf0b /dev/sdf attaching 2015-10-30T14:51:57+0000
	[oracle@rhec2lab1:/home/oracle]$ ec2-describe-volumes | grep "vol-cabfc226"
	VOLUME vol-cabfc226 10 us-east-1a in-use 2015-10-30T14:47:58+0000 standard Not Encrypted
	ATTACHMENT vol-cabfc226 i-b4accf0b /dev/sdf attached 2015-10-30T14:51:56+0000 false
	[oracle@rhec2lab1:/home/oracle]$ ls -l /dev/xv*
	brw-rw----. 1 root disk 202, 0 Oct 30 2015 /dev/xvda
	brw-rw----. 1 root disk 202, 1 Oct 30 2015 /dev/xvda1
	brw-rw----. 1 root disk 202, 16 Oct 30 2015 /dev/xvdb
	brw-rw----. 1 root disk 202, 17 Oct 30 2015 /dev/xvdb1
	brw-rw----. 1 root disk 202, 48 Oct 30 2015 /dev/xvdd
	brw-rw----. 1 root disk 202, 49 Oct 30 2015 /dev/xvdd1
	brw-rw----. 1 root disk 202, 64 Oct 30 2015 /dev/xvde
	brw-rw----. 1 root disk 202, 65 Oct 30 2015 /dev/xvde1
	brw-rw----. 1 root disk 202, 80 Oct 30 10:52 /dev/xvdf # <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	[root@rhec2lab1:/root]# fdisk /dev/xvdf
	Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
	Building a new DOS disklabel with disk identifier 0x63f3f645.
	Changes will remain in memory only, until you decide to write them.
	After that, of course, the previous content won't be recoverable.
	Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
	WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
	switch off the mode (command 'c') and change display units to
	sectors (command 'u').
	Command (m for help): n
	Command action
	e extended
	p primary partition (1-4)
	p
	Partition number (1-4): 1
	First cylinder (1-1305, default 1):
	Using default value 1
	Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305):
	Using default value 1305
	Command (m for help): w
	The partition table has been altered!
	Calling ioctl() to re-read partition table.
	Syncing disks.
	[root@rhec2lab1:/root]# ls -l /dev/xvdf*
	brw-rw----. 1 root disk 202, 80 Oct 30 10:59 /dev/xvdf
	brw-rw----. 1 root disk 202, 81 Oct 30 10:59 /dev/xvdf1
	[root@rhec2lab1:/root]# /etc/init.d/oracleasm createdisk FRA2 /dev/xvdf1
	Marking disk "FRA2" as an ASM disk: [ OK ]
	[root@rhec2lab1:/root]# /etc/init.d/oracleasm listdisks
	DATA1
	FRA1
	FRA2
	[oracle@rhec2lab1:/home/oracle]$ kfod disks=all
	--------------------------------------------------------------------------------
	Disk Size Path User Group
	================================================================================
	1: 30718 Mb ORCL:DATA1
	2: 10235 Mb ORCL:FRA1
	3: 10236 Mb ORCL:FRA2
	--------------------------------------------------------------------------------
	ORACLE_SID ORACLE_HOME
	================================================================================
	+ASM /u01/app/grid/12.1.0.2

view raw gistfile1.txt hosted with ❤ by GitHub

DBMS_METADATA generating incorrect syntax for the CREATE TABLE statements with ROW ARCHIVAL enabled (12.1.0.2)

While experiencing with the In-Database Archiving feature in 12.1, I stumble upon this unpublished bug with DBMS_METADATA.GET_DDL.

	SQL> select patch_id, patch_uid, version, status, description
	2 from dba_registry_sqlpatch
	3 where bundle_series = 'PSU';
	DESCRIPTION
	--------------------------------------------------------------------------------
	Database Patch Set Update : 12.1.0.2.4 (20831110)
	SQL> create table t (x int, y varchar2(30)) row archival;
	Table created.
	SQL> col column_name for a20
	SQL> col data_type for a20
	SQL> col nullable for a20
	SQL> select column_id, column_name, data_type, data_length, nullable, hidden_column
	2 from user_tab_cols
	3 where table_name = 'T'
	4 order by column_id;
	COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE HID
	---------- -------------------- -------------------- ----------- -------------------- ---
	1 X NUMBER 22 Y NO
	2 Y VARCHAR2 30 Y NO
	ORA_ARCHIVE_STATE VARCHAR2 4000 Y YES
	SQL> set long 2000
	SQL> select dbms_metadata.get_ddl('TABLE', table_name, owner) as tab_ddl
	2 from all_tables where owner = user
	3 and table_name = 'T';
	TAB_DDL
	--------------------------------------------------------------------------------
	CREATE TABLE "C##ZEUS"."T"
	( "X" NUMBER(*,0),
	"Y" VARCHAR2(30)
	) SEGMENT CREATION DEFERRED
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	NOCOMPRESS LOGGING
	TABLESPACE "USERS"
	ILM ENABLE LIFECYCLE MANAGEMENT
	SQL> CREATE TABLE "C##ZEUS"."T"
	2 ( "X" NUMBER(*,0),
	3 "Y" VARCHAR2(30)
	4 ) SEGMENT CREATION DEFERRED
	5 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	6 NOCOMPRESS LOGGING
	7 TABLESPACE "USERS"
	8 ILM ENABLE LIFECYCLE MANAGEMENT
	ILM ENABLE LIFECYCLE MANAGEMENT
	*
	ERROR at line 8:
	ORA-00933: SQL command not properly ended

view raw gistfile1.txt hosted with ❤ by GitHub

The clause "ILM ENABLE LIFECYCLE MANAGEMENT" was returned instead of "ROW ARCHIVAL" which causing an ORA-00933 at creation time. And here is what I found on My Oracle Support about this issue who is also impacting datapump imports in 12c:

CAUSE
The cause of this problem has been identified in:
unpublished Bug:17653443 - DBMS_METADATA.GET_DDL GENERATES WRONG DDL FOR TABLE WITH ROW ARCHIVAL
closed as duplicate of:
unpublished Bug:17654567 - DATA FROM ROW ARCHIVAL COLUMN ORA_ARCHIVE_STATE NOT TRANSFERRED WITH DATA PUMP
It is caused by DBMS_METADATA generating incorrect syntax for the CREATE TABLE statement in the dump file.
Bug:17654567 is fixed in RDBMS 12.2.

ORA-39083 and ORA-0093 When Using IMPDP to Import a Table With Row Archival Enabled (Doc ID 1999047.1)

Move datafile like a "lying child" - Oracle 12c

I tried to move a PDB datafile (PDB1) with the fullpath name from the CDB$ROOT container.

	[oracle@orcl12c ~]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 25 16:03:02 2013
	Copyright (c) 1982, 2013, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
	With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
	and Real Application Testing options
	SYS@ORCL12C1> show con_name
	CON_NAME
	------------------------------
	CDB$ROOT
	SYS@ORCL12C1> select d.file#, d.name as file_name, c.name as con_name
	from v$datafile d, v$containers c where d.con_id = c.con_id order by 2;
	FILE# FILE_NAME CON_NAME
	---------- -------------------------------------------------------------------------------- ----------
	3 +DATA/ORCL12C1/DATAFILE/sysaux.257.829484337 CDB$ROOT
	1 +DATA/ORCL12C1/DATAFILE/system.258.829484423 CDB$ROOT
	4 +DATA/ORCL12C1/DATAFILE/undotbs1.260.829484499 CDB$ROOT
	6 +DATA/ORCL12C1/DATAFILE/users.259.829484497 CDB$ROOT
	7 +DATA/ORCL12C1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.829484593 PDB$SEED
	5 +DATA/ORCL12C1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.829484593 PDB$SEED
	12 +DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/sysaux.276.829486259 PDB1
	11 +DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/system.278.829486259 PDB1
	13 +DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/users.280.829753193 PDB1
	9 rows selected.
	SYS@ORCL12C1> ALTER DATABASE MOVE DATAFILE '+DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/users.280.829753193' to '+DATA/ORCL12C1/TEST/users.dbf';
	ALTER DATABASE MOVE DATAFILE '+DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/users.280.829753193' to '+DATA/ORCL12C1/TEST/users.dbf'
	*
	ERROR at line 1:
	ORA-01516: nonexistent log file, data file, or temporary file "13"

view raw gistfile1.sql hosted with ❤ by GitHub

And the database is acting like a "lying child" responding it doesn't know file #13 ...

As you can see, you need to be in the right container to make it work.

	SYS@ORCL12C1> alter session set container = PDB1;
	Session altered.
	SYS@ORCL12C1> show con_name
	CON_NAME
	------------------------------
	PDB1
	SYS@ORCL12C1> ALTER DATABASE MOVE DATAFILE '+DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/users.280.829753193' to '+DATA/ORCL12C1/TEST/users.dbf'
	Database altered.
	SYS@ORCL12C1> select d.file#, d.name as file_name, c.name as con_name
	from v$datafile d, v$containers c where d.con_id = c.con_id order by 2;
	FILE# FILE_NAME CON_NAME
	---------- -------------------------------------------------------------------------------- ----------
	12 +DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/sysaux.276.829486259 PDB1
	11 +DATA/ORCL12C1/E958346A045F61C6E04380424364CF28/DATAFILE/system.278.829486259 PDB1
	13 +DATA/ORCL12C1/TEST/users.dbf PDB1

view raw gistfile1.sql hosted with ❤ by GitHub