netgo

How to use the Oracle database basically?

Recently, I need to study about the Oracle Database. In fact, I am not DBA. In this post, I will write the database queries to use later. I will use the Oracle SQL developer for this test.

1. Download and Connect database

I followed this instruction, which introduce how to connect to database. I need to download Oracle SQL developer from here. After download, I can connect database with connect button on the left of top.

2. Check the Database Properties.

I can see configuration or properties from query like below. For example, I can see the "Default Temp Tablespace" which the user information is stored in.

3. Create (Drop) User

This is important part to understand about Oracle Database. In Oracle, there is "schema" term. I think this is also user. All of objects such as table are connected to this schema. For example, "schema.table_name" is used as the table name when I create table. This instruction show how to create user.

I can get the all of users information in this database like below.

Now, I will create new user which is identified by the password.

I can see the new user created with "select * from dba_users;".

If you want to remove or revoke user, follows the command below.

4. Grant Privileges and role.

However, this user does not have any role and privileges. In this instruction, there are comments for roles.  

In my case, I am AWS Oracle RDS database. Look at the role assigned.

There are so many roles assigned.  There is no any role in user created before. However, there are so many role assigned for 'RDSADMIN' and master account.

Now I will give 3 role for user which I created like below.

After this I can check the role status.

So far, I studied how to grant the privileges. However, I need to revoke these sometimes. 

After granting roles to user, I need to give some privileges to this user. In oracle, there are two types of privileges, system and table.  At first, I will grant some privileges for the system.

I can give(remove) some system privileges like below.

After this, I can verify the status with "select * from dba_sys_privs" query.

Now, the table privileges left. In fact, I can not explain about this without the table. I will add more detail later in this post. Just look at the command how to see.

5. Create(Drop) Table

Before I create the Data table, I need to create table space. In this instruction, what kinds of parameters are required is explained. This command show the table spaces list. 

There are some necessary factor for this table space. Fist is "Contents". There are three values to category. In the instruction, there are comments like below.

From this statements above, "Undo tablespace" is not friendly rather than others. Please, read these instructions, https://oracle-base.com/articles/9i/automatic-undo-management and https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm#ADMIN11462. For this "Undo tablespace",  "automatic undo management" is required. The recently version is set as defaults. Also, I tried to change this configuration in AWS RDS. Howerer, I can not alter this.

Second is the datafile conecpt such as "bigfile" or "smallfile". In this instruction, 

From this statements, I can not understand "smallfile tablespace can contain multiple datafiles". Please look at this link, 

However, this command does not work in AWS RDS. Because RDS does not give any permission for this like below. I can not create the datafile.

There are so many options, however I can not handle all of things. I think I can create tablespace with these information. I hope this instruction will be helpful.

After this command, I can see the "select * from dba_tablespaces;" and "select * from dba_data_files;"

Now, I created table spaces. I can start to create table. Please look at this, there are sample example to create table. 

This command create "error" like below. This error is occurred by table name.

In oracle, table name must be "schema (=user)"+"table name". This is the reason why the schema (=user) is import. Because of this, I have to revise the command below. "MYADMIN" is created user before.

After this command I can see the table information with "select * from dba_tables;"

There are 2 things special. I insert "MYADMIN.user_info" as the table name. Howerver, oracle re-arrange this table name with "owner" and "table_name". Also, "USERS" tablespace is allocated for this table, which is default permanent tablespace. However I want that this table is located in "MYSAPCE" table space. Thus, it should be revised like below.

However, I can create this. Because this table name has already existed. Thus I need to drop this table with this command.

Now, this is what I want.

6. Insert, Update, Delete Data

Before start this part, I need to covert account with "MYADMIN", which is used as the "schema name".

"user_tables" show the information which belonged to logon account. If I can see table only I create, it is OK.

However, there is other way to find out logon current user. In this instruction, "select user from dual;" is command.

I will follow this instruction. At first, I will try to insert some data into "user_info" table;

Please note that I can use both of "schema+table name" and "table name", because the current user is same as the schema name. After this command, look at the table information like below.

Now, I will update the data. Please note I use "schema+table name" as the table name. It does not affect the result.

Finally, Delete is left. This is not difficult to run.

7. Troubleshooting. (table privileges with other schema)

I am happy to learn how to insert, update and delete data. However, I need to think about my privileges. So far, I did not give any permission about table. The reason why this can be done is that this user is the "schema user". It look like admin user for this table. If I create another user (=schema), I wonder if this user can insert, update and delete with this table.

This user does not any role and privileges at the beginning. 

I can not logon into the Database. I add the role with "connect"

Now, I can login. However, I can not select, insert, update and delete over table. I need to give some privileges.

With administrator account, I will give this user some permissions. Please, look at the this instruction.

Now, I can select, insert, update and delete.

If I want to know what kinds of table privileges does this user has, use this command

This is basic oracle database concept. I hope it will be helpful. If I have enough time to learn, I will handle more. However, I am not DBA engineer. I hope I can keep touch about this part again.

Reference

[ 1 ] https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/USER_ConnectToOracleInstance.html

[ 2 ] https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

[ 3 ] https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8003.htm#SQLRF01503

[ 4 ] https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/

[ 5 ] https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm

[ 6 ] https://stackoverflow.com/questions/8496152/how-to-create-a-tablespace-with-multiple-datafiles

[ 7 ] https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9

[ 8 ] https://www.java2s.com/Code/Oracle/User-Previliege/Getcurrentusername.htm

[ 9 ] https://www.oracle-dba-online.com/sql/insert_update_delete_merge.htm

How does the flannel work?

Recently, I am studying kubernetis. During studying, I have known about the flannel. There is some instruction to reproduct simply. I will follow this instruction.

1. Pre-requisite

There are some preparation. Docker and Etcd should be installed before. Here is instruction for installation of Docker. I will install community engine on 2 nodes running "ubuntu 16.04".

And then, I will install Etcd. I have already written how to install and use Etcd in this post. However, it is more simple in this instruction. In my case, I will run the command in "root directory"

After installation, I will edit the "/etc/hosts" file before running this Etcd. Please note that host name has only single IP address on this file.

Each nodes can communicate with each other with these IP addresses.

Now, I am ready to start the flannel configuration.

2. Flannel installation.

Download and install the flannel command on each nodes. In my case, I will run the command in "root directory"

I will make configuration file to create the network topology.

In this documentation, there are the meaning of the above parameters. Especially, "SubnetLen" is IP address range allocated in each host. Flannel use the configuration from Etcd, /coreos.com/network/config. I will set the configuration on Node 1

I can check if the configuration is set or not on Node 2.

Now, I am ready to start the flannel. Before start flannel, I have to look at my network interface status.

After start flannel, I can see new interface which is named with "flannel.VNI". In this case, It should be flannel.172.

Also, I can get some information from Etcd.

This mean that each host has these subnets each. I can see more detail. 

This is configuration for the flannel. I can also see the what flannel network is assigned with "/var/run/flannel/subnet.env". Please note that this file will be used for next step, docker daemon configuration.

3. Docker daemon configuration.

Docker does not use flannel default. It has swarm mode to make overlay network. Therefore, it is necessary to change to use this flannel as default network module. At first, I have to stop the Docker daemon on each hosts, node1 and node2.

I will restart Docker daemon with this flannel configuration.

Before restart Docker daemon, there is "docker0" interface default which has "172.17.0.1" IP address. It will be changed with the network what I defined.

4. Create the Test container and Start it.

Now, I will create 2 container for the test. 

Depends on the container properties, the container can be stopped after exit. "sh -c "while true; do sleep 3600; done" make this container keep alive for 1 hour. It is enough for the test.

5. Analysis container networks.

In this post, I will explain how to work in docker swarm mode. It is good to analysis the network topology of docker. At first, go to "/var/run/docker", there is the "netns" directory. There is the network configuration of the container.

After this symbolic link, I can see the network namespace list like below. "ip netns list" show the namespace ID.

I can see more detail information with this ID. "ip netns exec" show the same result with "docker exec". Thus I can see the same result with "docker exec test1 ip -d addr show"

6. Test and Troubleshooting

Now, I have know that "172.16.75.2" is container IP address on Node 1 and "172.16.68.2" is container IP address on Node 2. Flannel offers the overlay network between hosts. So, I will send ICMP (ping) from node1 to node2

Hmm. It does not work. From Docker 1.13 default iptables policy for FORWARDING is DROP,

Wow, I can send ICMP each other.

This is the flannel.

Reference

[ 1 ] https://docs.docker.com/install/linux/docker-ce/ubuntu/

[ 2 ] https://docker-k8s-lab.readthedocs.io/en/latest/docker/docker-etcd.html

[ 3 ] https://docker-k8s-lab.readthedocs.io/en/latest/docker/docker-flannel.html

[ 4 ] https://github.com/coreos/flannel/blob/master/Documentation/configuration.md

How to use AWS AppStream 2.0?

Have you ever used "Google Docs"? In this case, there is no host like PC. AppStream make like this. I can access my application from outside, such as Internet. AppStream has some advantage and disadvantage. It is simple to access and manage. Also it is more secure. Because the user can not do other things on this host. However, It is not easy to deploy with right size, even if AWS offer auto-scaling. I need to how much use can be used at the same time. Most of this post are referenced by this.

1. Create Image

AppStream is auto-scaling system. Thus, I need customized standard image to deploy.  In this image, I can install my application. It will be deployed. Please note that I will set this instance to create image over the subnet which can communicate with Internet, however the real-instance can be located over the private subnet which can not communicate with Internet. In Images, there are 2 category, Image Registry and Image Builder. At the first, I will select Image Builder to create my image which is installed for terminal. Launch Image Builder. 

Now, I need to select basic OS to create my image. In my case, I will choose Windows 2012 R2, which is named wit "Base-Image-Builder-06-12-2018". Please note this image can be different by the AWS Region.

Insert the image name and select the type for CPU and Memory. These parameters can not changed after first creation. Thus, I need another images when I need other size of instance.

Select the network which the instance attach to. Please note that this is the network only for image creation. When the real-system is deployed, I can define the network. I will touch at the next time. In this case, I need to download terminal tools from Internet. Thus, this instance will be located over the subnet which can communicate with Internet.

Review and Launch.

2. Connect Image

Now, I need to install my application on the launched image. After status change from Pending to Running, I can connect this image.

After connect, I can see the screen like below. I need to select user to login. At this time, I will select "Administrator" at the first.

Now, I can see the windows which is login with "Administrator" account. Open the CMD and Run "ipconfig" to see the network interface information. There are 2 interface. "11.8.48.79" is interface which is one of VPC subnet network. "198.19.168.187" is the interface which offer the display for users. "198.19.168.187" interface is controlled by AWS managed. Most of traffic is pass through the "11.8.48.79" interface. I can download some files from this interface.

On the desktop, there is firefox. Open this and write "https://www.putty.org/". In my case, I will download "Putty" for the sample application.

After download the file, (Default, the downloaded file will be located in documents directory), Install this application.

Please remember the installation path for the next step. In this case, the putty is installed under "C:\Programs". After installation, close all of windows. In the desktop, I can see the "Image Assistant". This application help me to register my application for the AWS AppStream.

AppStream 2.0 Image Assistant application should be run. Follow the step by this application.

3. AppStream 2.0 Image Assistant.

I need to register my application for AppStream 2.0. Click "Add App", Find out the running file. 

"App Launch Setting" menu is opened. In this instruction, there are more information about this. "Launch Parameters" and "Working Directory" are depend on my application. In this case, I will leave blank.. Just click "Save"

Click "Next"

This is important step. In this part, I can customize my application. I need to re-login with Template User. In this mode, I can define my application how to work. Click "Switch User"

Select "Template User"

After login, I can change OS configuration and Application settings. In my case, I will register sample session information. "my-sample" session information is updated over my application.

Also, I can run this application with what I want to do. I open SSH to my sample host.

I have done all of things. In the desktop, there is Images Assistant is located. Run it. And back to main with Administrator account.

Select "Administrator" and login again.

After login, I can see "Save settings" button activated. Click this button and click "Next"

Now, I have to verify if this application work correctly. I will switch user with "user" account.

Select "Test User"

After login, I can run "Putty" again. And I can confirm that "my-sample" session in configuration is remained.

Run "Images Assistant" in the desktop, and go back to main with "Administrator" account.

Select "Administrator" again.

Now, I am ready to finish. Launch.

After complete to launch, click continue.

Fill with image name.

Review and Create Image.

The viewer will be disconnected. And the building image will be started.

It will take about 10~20 minutes. And then, I can see the my image on AWS console.

4. Snapshotting Image. 

During the time, I can see the AWS console to check the status. In the "Image Registry", there is "image" which is creating. 

In "Image Builder", my based image has the status "Snapshotting". Please note that I can not do any action in this "Snapshotting" status.

I can expect that "Snapshotted image" is registered in Image Registry.

5. Delete Image in "Image Builder" (Optional)

There is no relationship between image in registry and image in builder. So, I can delete and remove this image in the builder. If you have change to make another type of image include configuration, I will remain this image. However, this is not necessary in this post. I also show the relationship between them.

After the status is "Stopped", I can delete this image.

6. Create fleet.

I have a image which I want to deploy. I need to define the network to deploy. The fleet has this kinds of role. Create Fleet.

Insert Name for this fleet.

I can see my image which has been created. Select the image and Next.

From here, network and security, capacity for scaling are defined. Select type of CPU and RAM for this instance. This is not changeable. If I want to change, I need to create another fleet. In fleet type, there are 2 modes, "On-Demand" and "Always-on". I want to save my money, so I will select "On-Demand"

Maximum session duration means "User can stay during this time once". Disconnect timeout means "After session finish, this instance can not re-assign to other user until this time spent". Minimum and Maximum capacity define the number of con-current session. AppStream offer auto-scaling, however, it take 10~20 minutes. It is too long, therefore, I need to define with proper number. In Scaling detail, this is the parameters to scale-out.

In here, the nework and security group is defined. I can locate AppStream instance into the subnet which does not communicate with Internet. In image builder step, I located at the subnet which communicate with Internet.

Review and Create.

Now I have a fleet.

7. Create Stack and Connect with Fleet

Stack has the role to define "User Pattern". Create Stack

Insert Name

I can define if I use Home Folders for each sessions. In my case, I do not want that the user remain their files on the instance. Because this instance will be shared with others. Default, it is enabled. However, I disable the option.

I can also user behavior such as "Copy and Pasts" usages. Just define what you want.

Review and Create.

After creation of stack, I need to associate fleet. In Actions, there is "Associate Fleet".

Select the fleet which is created before,

And confirm the stack details. If you want to remove fleet, you have to dis-associate this relationship at first.

8. Create User Pool.

In workspace, I need AD system. Fortunately AppStream offer this feature through AWS console. Create User.

Insert information for user. The Email address should be correct. The access link, account and temporary password are sent to this email address.

After creation of user, I need to associate with stack. I associate with the stack already created. 

Select the stack.

Look at the details, I will resend welcom email which is include access link.

I will received this email.

9. Accessing and Login the AppStream.

Click the Link which is include in email. New windows is opened like below.

After first login, I need to change my password.

Re-login and I can see like below. I registered the putty application only. Therefore, Putty Icon is appeared.

I can see the my application opened.

This is the AWS AppStream, I hope this post help you!

10. Troubleshooting

10-1. Remote User.

In the AWS console, I can not delete the user. It is only possible with API. I will use awscli command.

10-2. Delete S3 bucket

AppStream can save Home folders and Application configuration in S3 bucket. This bucket will be created automatically. When I delete this S3 bucket, I can not make success. Because there are some limitation to protect S3 bucket. So I have to change this part at the first to remove.

After clear in Bucket Policy, I can remote this bucket.

Reference

[ 1 ] https://docs.aws.amazon.com/appstream2/latest/developerguide/tutorial-image-builder.html

[ 2 ] https://d1.awsstatic.com/product-marketing/AppStream2.0/Amazon%20AppStream%202.0%20Getting%20Started%20Guide%20April%202018.pdf

[ 3 ] https://docs.aws.amazon.com/appstream2/latest/developerguide/managing-network.html

[ 4 ] https://docs.aws.amazon.com/cli/latest/reference/appstream/delete-user.html