Monday, October 22, 2018

Oracle Database EM 18 XE Available to Remote Clients

I found lot of posts about Oracle Database 18 XE. It's very interesting for me.  I didn't blog about how to install, because it's very easy for using rpm package and document very helpful.
I was interested in Enterprise Manager Database Express 18.4.0.0.0. How it looks like?
- Installing. I used CentOS7.
[student@centos-learning ~]$ sudo yum -y localinstall oracle-database*18c*
[student@centos-learning ~]$ sudo rpm -qa |grep oracle
oracle-database-preinstall-18c-1.0-1.el7.x86_64
oracle-database-xe-18c-1.0-1.x86_64

[student@centos-learning ~]$ sudo /etc/init.d/oracle-xe-18c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
The password you entered contains invalid characters. Enter password:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
*********
Enter SYSTEM user password:
********
Enter PDBADMIN User Password:
*********
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
31% complete
34% complete
38% complete
41% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.
Connect to Oracle Database using one of the connect strings:
     Pluggable database: centos-learning.surachartopun.com/XEPDB1
     Multitenant container database: centos-learning.surachartopun.com
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
[student@centos-learning ~]$ netstat -ltn |grep 5500
tcp        0      0 127.0.0.1:5500          0.0.0.0:*               LISTEN
- As I didn't want to connect 127.0.0.1, I changed binding - "Making Oracle Database EM Express Available to Remote Clients"
SQL> !netstat -ltn |grep 5500
tcp        0      0 127.0.0.1:5500          0.0.0.0:*               LISTEN
SQL> !lsnrctl status | grep HTTP
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
SQL>
SQL>
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
PL/SQL procedure successfully completed.
SQL> !netstat -ltn |grep 5500
tcp        0      0 0.0.0.0:5500            0.0.0.0:*               LISTEN
SQL> !lsnrctl status | grep HTTP
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centos-learning.surachartopun.com)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
- Browsed it - https://IP:5500/em

However, I got some error like "Connection with database failed. Database instance might be down."

- Checked alert log file and fixed.
Error: Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
bash-4.2$ tail -f alert_XE.log
2018-10-22T22:06:32.890217+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:06:38.489011+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:10:32.402822+07:00
Resize operation completed for file# 3, old size 501760K, new size 512000K
2018-10-22T22:15:55.791490+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:18:02.248906+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
Setting the Global Port for EM Express to Manage a CDB and the PDBs. (It might not be the right solution, but I just wanted to see EM).
SQL> select dbms_xdb_config.getHttpsPort() from dual;
DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500
SQL> exec dbms_xdb_config.SetGlobalPortEnabled(TRUE)
PL/SQL procedure successfully completed.
- Login again.

It worked fine for now.

Reference: Installation Guide for Linux x86-64

Wednesday, September 19, 2018

How to allow private connectivity across organizations(GCP)?

It's interesting, when you would like to allow private connectivity across two VPC networks that they belong to the different project/organization on Google Cloud Platform (GCP).

As google document that we can use VPC Network Peering? VPC Network Peering is a decentralized or distributed approach to multi-project networking. Additional, it works with Compute Engine, Kubernetes Engine,and App Engine flexible environments.

I did a lab about Virtual Private Cloud (VPC) Network Peering. There shows to do VPC Network Peering between VPC networks in the same project. So, I would like to see how it works on across organizations.

Example: On My Organization (my project), I would like to connect server (Private IP Address) on another Organization (another project).

As a subnet CIDR prefix in one peered VPC network cannot overlap with a subnet CIDR prefix in another peered network. So, both VPC networks must have the different CIDR prefix.

My Organization [myproject] {ubuntu-test, default/10.146.0.2} <======> No organization [qwiklabs-gcp***]{privatenet-us-vm/172.16.0.2}

On No organization [qwiklabs-gcp***]: VPC network name is "privatenet".

VPC:
Firewall:


My Organization [myproject]: I used "default" VPC and default firewall.


Then, starting to create "VPC Network Peering".

- To create "VPC Network Peering" on myproject:
Networking => "VPC network" => "VPC network peering".
Click "Create Peering Connection".  name = "peering-to-lab".
Note: you must know Project ID and VPC network name for network destination.































It should show "Waiting for peer network to connect".

-  To create "VPC Network Peering" on another Project: On No organization [qwiklabs-gcp***], "Create Peering Connection".  name = "peering-to-mygcp".


After clicking "Create". It should show "Connected" on both projects (if configuration corrects) like.

On myproject:

- Finally, test connection: ssh to my vm and test (ssh) connection to {privatenet-us-vm/172.16.0.2}.
Note: (as firewall allow icmp/ssh). No need to do on firewall.

opun@ubuntu-test:~$ ssh 172.16.0.2
opun@172.16.0.2's password:
Linux privatenet-us-vm 4.9.0-8-amd64 #1 SMP Debian 4.9.110-3+deb9u4 (2018-08-21) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Wed Sep 19 06:22:05 2018 from 10.146.0.2
Could not chdir to home directory /home/opun: No such file or directory
$ w
 06:22:55 up 43 min,  2 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM             LOGIN@   IDLE   JCPU   PCPU WHAT
opun     pts/1    10.146.0.2       06:22    1.00s  0.00s  0.00s w ************
Reference: https://cloud.google.com/vpc/docs/vpc-peering

Saturday, April 21, 2018

GCP - How to manage SSH keys on VM Instance?

On Google Cloud Platform, adding SSH keys in Metadata (project-wide public SSH keys). It can help to ssh to every VM instances on Compute Engine easily but it's not a good idea. We are able to do for test, but should not use on Production. We should add SSH Key in OS login. 
https://cloud.google.com/compute/docs/instances/adding-removing-ssh-keys#risks



Question:
How can we block SSH Keys from Metadata(project-wide public SSH keys) on VM instance?
Answer: We can block by checking "Block project-wide SSH keys" on each instance.

In case we have SSH Key on metadata. We are able to ssh by using private key and login like.

So, we block it... On "Compute Engine" - "VM Instances", click [instance name] and "Edit". To check "Block project-wide SSH keys" and "Save".


VM instance should refuse Key what 's not in SSH  Keys of VM instance. (You can remove SSH Keys of project owner on instance, but it will be automatic added when you click "SSH" on GUI).

Additional, we should review and remove SSH Keys in metadata(project-wide public SSH keys), if we ensure we have not used. (Don't remove ssh key of project owner).

After removing, We would like to add SSH Key and don't want to add it in OS login. We are able to add it in SSH Keys on Instance like.


Assume: username is "myuser".

First of all, we have to generate Private and Public Keys. Example uses "PuTTY Key Generator". Because I use "Putty.


Then "Save private key" (We have to use when putty to server) and "Save public key".

To use public key on VM instance, click "Add item".



Example: It's [public key] [username]  

 then "Save".
Note: In picture, it's highlight about [username]

Open "putty", select "Private key file for authentication", fill in ip address and connect.

it's easy, right?
myuser@centos7:~$ id
uid=1003(myuser) gid=1004(myuser) groups=1004(myuser),4(adm),30(dip),44(video),46(plugdev),1000(google-sudoers)
If we use command "id [user in project-wide SSH keys], we still see it, but it's unable to ssh on this VM instance.
myuser@centos7:~$ id opun
uid=1001(opun) gid=1002(opun) groups=1002(opun),4(adm),30(dip),44(video),46(plugdev),
Reference: