netgo

How install oracle 11g on Windows 2012 R2?  (Enable archive log and Supplemental log)?

Recently, I need to learn the oracle to migrate data from on-prem to aws. In fact, I am not the DBA, therefore, I can not handle deep dive. I hope the I can help someone like me who is the first time about oracle.

1. Pre-requsite.

I have Windows 2012 R2 which supports oracle database 11g. For successful installation, I need the ".Netframe 3.5" installed. In server manager. Click "Add Roles and Features" at first.

In Features, choose the ".NET Framework 3.5 Feature" and Install this.

After then, I am ready to install oracle database.

2. Install oracle database.

The reason why I select Windows is simple to consist rather than Linux. I will follow two instructions mainly. https://www.youtube.com/watch?v=YgFfjLHY-wE is helpful for me. At the beginning, I need installation file. I can download from oralce site.

In my case, I will download "Microsoft Windows (X64)". There are two files. After extract both of files, I need to merge and make together in same directory. I copied and pasted whole directory from secondary to first.

After merge, I can see the directory inside. There is the "setup" icon. Click it.

Click Run and Start Installation for oracle database.

Depends on the system environments, Installer make warnning messages like this. However, I can ignore this, Click "yes"

Uncheck "I wish to receive security updates via My Oracle support", because I do not need this feature. It does not matter for next step. In email field, I can leave with empty. Just click "Next"

After Next, the warning message come. However it is also not matter for next step. Just click "yes".

Choose "Create and configure as a database". Because this is first installtion on this server. Click "Next".

In my case, my operating system is windows 2012 R2 server. Therefore, I will choose "Sever class".

I will install with standalone mode. Choose "single instance database installation" and click "Next".

Choose "Advanced Install" and Next.

This step is more important rather than before. In this insturction, there are some explain about the fields. I overview about below. 

Select "Langurage"

I will select "Standard Edition One" (Please note I can select what I want as the oracle database engine).

I select my custom home directory like below.

Select configuration type. In my case, I will choose genera purpose.

"Global database name" is not SID. However, I choosed single instance mode. Becuase of this, the SID can be same as Global database name. However, I can make different in Advanced mode.

I will adjust some paramters for memory, characters set, security and sample schema.

I will UTF-8 for character sets.

I will not make sample schema. Un-check "Create database with sample schema"

During this installation, Oracle Enterprise Manager will be installed, even if I do not want. Just click Next

Select File system. Usually it is better to seperate from root disk.

This is the back up configuration. I don't want this at this time.

When I select "Typical Installation". There are Administrative password which is default password for schema (=users). 

However, In Advanced mode, I can set password individually like below.

If you use simple password, the warnning message happen like below. In my case, this is test oracle database. I will ignore this. Click yes.

This is summary, Click Finish to installation.

During installation, I can see the configuration assistant process like below.

During the installation, warnning message like below can be occure. In this case, I need to set variable in windows.

I will handle this later. At this time, just click next. I registered the "Administrative password" before. I have told it will be used as default password. However, I have another change to set the password for each schema (=user).

Click Password Management. I will set individual password for "SYS" and "SYSTEM". If you want more, you can set the password more. In oracle database, there are serveral sample test schema such as "HR" and "SCOTT".

Continue with Yes.

3. Access database

Now, I can access database. At this time, I can use sqlplus which is installed with.

I can access with SYSTEM schema account like below

4. Verify the oracle environment for enterprise manager.

During the installation, I see warning message below. Because of this, I need to check environment variables.

In this page, I need to check the status with "emctl status dbconsole". 

In this case, I need to set environment value. Properties > Advanced system settings.

In Advanced tap, there are environment value button.

I will add some values in User vriables for Administrator.

For the first issue, I will add "ORACLE_UNQNAME". 

I will reference the value from db_unique_name. In fact, there is value when environment is not set. run "select name,db_unique_name from v$database;".

I will try again "emctl status dbconsole". At this time, there is "Not found" error. 

Go into the directory. Thre is no localhost. Instead, 11.0.0.131 is existed.

To resolve this, I will add "ORACLE_HOSTNAME" like below.

After then, It will be like below. This is normal. However, It look like not perfect. "EM Daemon is not running" is happen.

I followed this video. To make success, I have to start service manually. At this time, I can not start after right button.

Find the file below. In emd properites file, "agentTZRegion=+06:00" should be adjusted with OS time.

I adjust the time 

Now, I can use this.

5. Oralce database stop, start and restart

In this instruction, there are several way to stop and start. In this part, I will overview sqlplus to stop and start.

6. Enable/Disable Archive log Mode

Sometime, it is necessary to enable this feature. It is utilized to synchronize databases with log-miner. For this, I will follow this instruction. run "archive log list". The result is displaied. It is not enabled default.

I need to shutdown with "shutdown immediate". My status is changed to "nolog".

I have to change the status to "mount", which I can change configuration on. Run "startup mount".

Run "alter database archivelog;" to enable

After then, I open the database "alter database open;"

If you want to disable, I use "alter database noarchivelog;"

7. Enable/Disable supplemental logging 

This action should be done in database. To check the status, run "select supplemental_log_data_min from v$database;"

To enable and disable, run query like below.

This is the basic configuration for the installation. 

Reference 

[ 1 ] https://www.youtube.com/watch?v=YgFfjLHY-wE

[ 2 ] https://medium.com/@Dracontis/how-to-install-oracle-11g-database-on-windows-server-2012-a4d30d4dc727

[ 3 ] https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

[ 4 ] https://docs.oracle.com/cd/E25178_01/server.1111/e10897/install.htm

[ 5 ] http://www.dba-oracle.com/t_oracle_unqname.htm

[ 6 ] http://blog.mclaughlinsoftware.com/2012/08/23/whats-oracle_unqname/

[ 7 ] https://www.youtube.com/watch?v=Y8i41J7aLwo

[ 8 ] https://docs.oracle.com/cd/B14117_01/win.101/b10113/admin.htm#CDCEJADC

[ 9 ] http://www.oracledistilled.com/oracle-database/backup-and-recovery/enabledisable-archive-log-mode-10g11g/

How to user the Oracle View and Joins (Inner and outer)

Recently, I have been studying about the Oracle Database. In this post, I wrote about the basic concept. In this post, I will handle about View and Joins.

1. Pre-requisite

I will create 2 tables like below.

After run these command, I can get the tables with datas

Now, I am ready to start.

2. Inner and  Right, Left, Full Joins

I will follow this instructions. In this instructions, It has more detail. In this post, I will handle the way how to use this. Joins are one of filters with tables. Therefore, I can extract the matched data from tables.

2-1. Inner Join

As the figure, It shows the result after Inner Join. I can extract the matched data as intersection. This is the command. 

This command means that I can extract intersection from both of "MYADMIN.users" table and "MYADMIN.lessons" table.

2-2. Left outer Join

With this option, Left side of tables are the source. This source start to compare data of the Right side of tables. When condition is matched. It extract matched data from both of table. However, the condition is not matched, the data from the source can be obtained but the data from right side of table inserted as the "<null>"

2-3. Right outer Join

This is reverse concept of the left outer join above. At this time, left side of tables is the source. Therefore, the value could be "<null>" from right side of tables, when condition does not match.

2-4. Full outer Join

With this option, left side and right side can be source. If the column from left side which does not exist in right side, this is the source. If the column from right side which does not exist in left side, this is also the source.  

3. Views

I will follow this instruction for this part. Views is the virtual table which is not existed in real. Sometime, I need to combine or make complex database query. In this case, this views is quite useful. For example, I will create view with the query of full outer join.

I have already told that the view is not real table. Thus, I can not find out the information from "user_tables".

However, I can see the view information from "user_views". In the "TEXT", I can see the query which I used to create.

I think "the views is belonged to the schema (=user), even if it is virtual table". I can give some grant option like below.

After login again with "MYUSER" account, I can show the data from this view also.

However, I can not show any detail information with "user_views" or "user_table".

Because, it only show the data whch is created by "schema". Therefore, if you want to see, you need to search through grant option like below.

If I want delete (drop) the view, Use this command below

Reference 

[ 1 ] https://www.techonthenet.com/oracle/joins.php

[ 2 ] https://createnetech.tistory.com/32

[ 3 ] https://www.techonthenet.com/oracle/views.php

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