Oracle Database 11gR2 Installation on Linux Virtual Box

Step by step Installation of Oracle 11gR2 on OEL 6.3 in Virtual Box

Prerequisites and links to download required software

1. Virtual box software Click Here to Download
2. Oracle Linux Operating System 6.3 Click Here to Download
3. Oracle 11g Grid Infrastructure Software Click Here to Download 
4. Oracle 11g Database Software Click Here to Download

Chapter 1 : Create and configure virtual machine 

1.  Double click to Open Virtual Box and click new to create new virtual machine

2. Enter the machine name, select the type as Linux and set Linux version as Oracle - 64 Bit. Next set the RAM (Memory) for 2 GB as specified below, then click on Create button

3. Now Virtual Box will prompt for location of virtual hard disk file and size of the virtual machine. We have specified the location and size set to 60 GB

4. We have created the VM here, next we have to set network and shared folder. Now select the VM and right click get Settings options, Click on the settings

5. Now you will get settings pane, on the left side bar you will find network option. Click on the Network option

6. Now select the network options and set the adapter 1 setting as specified in the screen shot below, then click on adapter 2

7. Select the network options and set the adapter 2 setting as specified in the screen shot below, then click on Shared Folders

8. Click on the add folder symbol to the top right corner

9. Click on the browse the path which you want to make Shared folder in your local system for the VM, check read-only and auto-mount options. Then click OK

10. Again click on OK, now we are done with VM creation for installing Linux OS. Next chapter we will start installing Linux on Virtual Machine

Chapter 2 : Installing Linux on Virtual Machine

11. We will start the OEL installation in the virtual machine created. Please download the OEL software from the link mentioned in the prerequisites section. Now click on button to start the Virtual Machine

12. Once virtual machine starts there will be a popup window to select the Linux installation media. Here you can select the media ISO file having Linux installation or if you have the CD/DVD, please specify the DVD drive. We are selecting the Linux installation ISO to start OS installation

13. We shall specify the ISO path

14. Now Click on Start button to begin installation

15. Select the first option and press Enter

15. This step checks to installation media if its Select skip option

16. Click on next button to proceed

17. Select the regional language and click Next

18. Select keyboard as US English and click Next

19. Select checkbox basic storage device and click Next

20. Click on Yes, discard any data option and again click Next

21. Now provide the hostname for the virtual machine as specified below or you can choose hostname of your wish, but we have set pract1.localdomain as the hostname. After hostname is specified we have to configure the network, so click in the Configure Network button

22. Click on eth0 and press Edit button to specify IP Address and other details

23. Now check on connect automatically, specify the IP Address, netmask and gateway information as mentioned below and click on Apply button

24. Next edit eth1, check the Connect Automatically option, leave all the other option default and click Apply

25. Here we are completed with network configuration, Now click on Next

26.  Select the timezone and Click Next to proceed

27. Provide the root password, if password is weak wizard popup alert, click on Use Anyway and click on next button

28, Select the appropriate option for partitioning, we are creating custom partitions as per our own requirement. Check Create Custom Layout and click Next

29. Now double click on Free to get Add Partition wizard. We add partition for / mount point with 12 GB or 12000 MB. Please provide information as specified below and click OK

30. Again double click on Free to add swap space of about 4 GB and click on OK

31. Again double click on Free to add another mount point /u01 , check maximum allowable size and click on OK

32. Now we have partitioned the disk, click on Next

33. Click on Format button

34. Click on Write changes to disk button and later click on Next

35. After formatting click on Next

36. Keep the default settings and click Next to proceed further

37. Select Database Server option, check the option Customize now as shown in screen and click Next

38. Click on Base systems and select Compatibility Libraries

39. Select Desktops and select all the components

40. Select Applications and select Internet Browser components

41. Select Development then check Development tools, Additional Development

42. Click on Next

43. Now Installation of Linux begins

46. After installation of Oracle is complete, Click on the Reboot

47. After reboot, click on Forward

48. Now accept the licence agreement and click on forward

49. Here we will choose to register later, so select No option and click forward

50. Click on No Thanks, button to proceed further

51. Click on Forward

52. We can create users later so keep all the fields blank and click on Next

53. Click on Forward

54. Click on Finish. With this step we complete Linux Installation

55. Now click on other

 56. Enter the Username as root

57. Enter the password and click on Login

58. Now you have logged in. In the next chapter we will prepare the system for Oracle Database software installation

Chapter 3 : Prepare the system for Oracle Database Software Installation 

59. Install guest additions CD image. To do that we will mount the image first

60. Double click on the VBOXADDITIONS image, then click on Run

61. Now Click OK button

62. After installation is completed press Enter or close the prompt. Now our shared folder on the local system gets mounted automatically as we have installed guest additions

63. Now we will create the required groups and users for oracle installation

64. Create required directories for Oracle Installation and copy the software from the shared disks.

65. We will install pre-requisite RPM's required for Oracle installation as below command
# yum install oracle-rdbms-server-11gR2-preinstall
then press y and press Enter

66. We have to disable the firewall. Click on System > Administration > Firewall

67. Now click on OK and press disable option

68. Click on Yes and proceed

69. Edit /etc/selinux/config, here change the parameter SELINUX=disabled
#vi /etc/selinux/config
i
To edit file press i, after the parameter edited press escape then type as mentioned below w for save and q for quit
:wq

70. Now set the hostname, to do that edit /etc/hosts file as mentioned below
#vi /etc/hosts
enter 
192.168.25.11 pract1.localdomain pract1
To edit file press i, after the parameter edited press escape then type as mentioned below w for save and q for quit
:wq

71. Change the owner of the mount point where Oracle has to be installed then restart the machine 

#chown oracle:oinstall -R /u01 

#init 6 

enter 

Chapter 4 : Oracle Installation 

72. After restart login as oracle user

73. Now unzip the Oracle Software Installation Setup files using below command. After unzip log on to database directory and run the installer to install Oracle Database Software

$unzip linux.x64.11gR2.database_1of2.zip 
==>> Pres Enter
$unzip linux.x64.11gR2.database_2of2.zip 
==>> Press Enter

$cd database 

$./runInstaller 

==>> Press Enter

74. Uncheck the "I wish to receive security updates...." checkbox and click Next 

75. Click on No and proceed

76. Select Install Database Software Only option and click Next

77. Select Single instance database installation option and click Next

78. Click Next to proceed

79. Select Enterprise Edition and Click Next

80. Specify Oracle Base location and Oracle Home Location then click Next

81. Specify Oracle Inventory Location and its group as dba, then Click Next

82. Select the OSDBA and OSOPER group as specified below, then click on Next

83. Check ignore all option and click Next

84. Verify the parameters and click on Finish

85. Now Oracle installation starts

Installation proceeds.....

86. While installation is in process, installer will prompt for running couple of scripts as root users

87. Login as root user and run orainstRoot.sh
$su - root
Enter the password
#/u01/app/oraInventory/orainstRoot.sh
Enter the password

88. Now run root.sh script
#/u01/app/oracle/product/11.2.0/dbhome_1/root.sh
Enter the password

89. Now we have completed Oracle Installation. Finally click on Close button, next chapter we will see how to create the database

Chapter 5: Post Installation Step

90. Set profile with ORACLE_HOME location and PATH

[oracle@pract1 ~]$ vi .bash_profile

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH

press esc button 

:wq

[oracle@pract1 ~]$ . .bash_profile

Chapter 6: Database Creation

91. Run dbca, on the GUI click on Next

[oracle@pract1 ~]$ dbca

92. Check on Create a Database option and click Next

93. Select General Purpose or Transaction Processing option and click Next

94. Enter the Database Name and SID, the click Next

95. Uncheck Configure Enterprise Manager and Click Next

96. Enter the password for sys and system account and click Next

97. Click on Yes

98. Set the data files location and click Next

99. Here I have not enabled archive log mode and flash back mode, so will uncheck both the  options and click Next

100. Click Next and proceed

101. Here we will set SGA and PGA memory, then click Next

102. Now Click on Next

103. Check the Create Database option and Click Finish

104. Click on Ok, afterwards database creation starts

database creation proceeds

105. Click on Exit button. Now we have completed Oracle database installation

Using Data Definition File SOURCEDEF in GoldenGate or DEFGEN Utility

Source Definition in GoldenGate is used when we have different table structure on source and target database or databases are different.

Please find the steps to generate Goldengate source definition file using defgen utility and use it on target to get source table definition

Step 1:- Go to GGSCI prompt and create the defgen parameter file


GGSCI (Hostname) 1> edit params defgen

defsfile ./dirsql/SOURCE_DEFINITION.def
userid ggowner password *******
TABLE SOURCE_SCHEMA.*;

Step 2:- Invoke the DEFEGN utility from GoldeGate home location

$./defgen paramfile ./dirprm/defgen.prm

Step 3:- Use the SCP or FTP to copy the definition file SOURCE_DEFINITION.def from source to target server

Step 4:- Specify the copied source definition file SOURCE_DEFINITION.def on the replicat parameter file in target database

SOURCEDEFS ./dirsql/SOURCE_DEFINITION.def

Oracle GoldenGate Architecture

Introduction

Oracle GoldenGate is product used to replicate the data in database from system to another. It can replicate the data across various heterogeneous environments. Let’s say we have source database is Oracle running on Linux, data from source can be replicated on other database Microsoft SQL server running on windows using GoldenGate.

Please see some of the databases supported on GoldenGate

• Oracle
• MySQL
• IBM DB2
• Microsoft SQL Server
• Teradata
• Sybase etc. 

GoldenGate Topologies  

• Uni-Directional: We have one source and one target, data gets replicated from source to target not the other way around
• Bi-Directional: We have one source and one target, all the transaction happening on source will be replicated on to the target system and system behaves in the same way i.e. replication happens from target to source Here both the database will be active-active mode
• Consolidation: In this topology, there are multiple source databases and all the transactions happening on various source would be replicated on the singe database. Typically this topology is used to implement reporting system
• Broadcasting: There is single source and multiple target databases
• Peer-to-Peer: This topology is useful in load balancing and high availability
• Cascading: Data from system A would be replicated on system B, from system B data gets replicated on to the various target systems. This topology is used to reduce the contention on single system

GoldenGate Architecture

• Manager: This process runs on both source and target system. Manager is responsible for restarting extracts and replicat processes. It’s also responsible for purging old trail files on both source and target side

• Extract Process: It runs on the source side which is also known as capture process. Extract process captures committed transactions on the source side and writes them on to the trail files which is in GodenGate format. It reads the committed transactions from database logs

• Pump Process: It’s the optional component but highly recommended to use, because it protects the replication in case of potential network break down. Pump process reads the data from local trail files and send it to the target through network

• Trail Files: It holds the data collected from extract process in source and also similar trail files resides on the target database which is used on target side to consume the data. Data is organized in trail files in the order of the commits to maintain data integrity

• Collector Process: It receives the committed data sent from source extract or pump process through the network on to the target side. Collector process writes the collected data on to the remote trail files on target side and this file would be consumed by the replicat process.

• Replicat process: This process writes the transactions stored in the remote trail files and is also known as apply process

What is checkpoint table in Oracle GoldenGate

Oracle GoldenGate extract and replicat processes perform checkpoint operations. In the event of failure (extract or replicat), the checkpoint file or database table ensures extract and replicat processes re-start from the point failure and avoid re-capture and re-apply of transactions. Below mentioned command can be used to create checkpoint table 

GGSCI (stby.localdomain as ggowner@GGTGT) 2> add checkpointtable ggowner.checkpoint

Successfully created checkpoint table ggowner.checkpoint.

Frequently Used Queries for Oracle DBA

Check Database Status

col name for a8 
col open_mode for a13 
col status for a8 
col logins for a8 
col host_name for a20 
set lines 500 
alter session set NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss'; 
select name,host_name, open_mode, status, logins, startup_time from gv$instance, v$database;

Check Oracle 12c PDB Database Status

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

Check Database Size

select
    ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
     ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
    ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
    from dual;

Check Invalid Objects

set lines 200
set pages 999
col OBJECT_NAME for a40
select owner, object_name, object_type, status from dba_objects where status like 'INVALID';

Check RMAN Backup Status 

set lines 300 pages 500
col command_id format a25
col status format a35
col start_time format a25
col time_taken_display format a25
col INPUT_TYPE format a20
col output_device_type format a50
SELECT   b.command_id,b.status, to_char(b.start_time, 'MM-DD-YYYY HH24:MI') "Start_TIME", b.time_taken_display,b.input_type, b.output_device_type
FROM v$rman_backup_job_details b
WHERE (b.start_time > (SYSDATE - 20)) order by 3 desc;

Check Oracle Compononets Status 

set lines 200
set pages 999 
col comp_name for a40
select COMP_NAME, VERSION, STATUS, MODIFIED from dba_registry;

Check Table/Index/Segment Size

set lines 200
col segment_name for a20
select owner, segment_name, segment_type, bytes/1024/1024 "Size" from dba_segments where segment_name like 'TABLE_NAME';

Check Tablespace Usage

set pages 999 lines 999
column Tablespace_Name format A30
column "Free %" format 999.99
column "Largest Chunk" format 9999.999
column "Free Space" format 9,999,999.999 
column "Tot Space" format 9,999,999.999
column auto_mb_left hea 'Auto_MB_Left' format 9,999,999.999
compute sum of "Free Space" on report
compute sum of "Tot Space" on report
break on report
ttitle off
select Tablespace_Name,
        nvl(Sum_Alloc_Bytes,0)/(1024*1024) "Tot_Space_GB",
        nvl(Sum_Free_Bytes,0)/(1024*1024) "Free_Space_GB",
        nvl(Max_Bytes,0)/(1024*1024) "Largest Chunk",
        Count_Chunks,
        auto_mb_left,
        (100*((nvl(Sum_Free_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))/((nvl(Sum_Alloc_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))) AS "Free %"
from
        (select Tablespace_Name, SUM(Bytes) Sum_Alloc_Bytes
                from DBA_DATA_FILES
                group by Tablespace_Name),
        (select Tablespace_Name FS_TS_NAME,
                MAX(Bytes)  AS Max_Bytes,
                COUNT(Bytes)  AS Count_Chunks,
                SUM(Bytes) AS Sum_Free_Bytes
                from DBA_FREE_SPACE
                group by Tablespace_Name),
        ( SELECT d.tablespace_name auto_tbspname,
                floor(sum(decode(sign( ((d.maxbytes-d.bytes)/1024/1024) - (d.increment_by*to_number(p.value)/1024/1024) ),
                        -1,0,((d.maxbytes-d.bytes)/1024/1024)))) auto_MB_Left
          FROM dba_data_files d,  v$parameter p
          WHERE p.name='db_block_size'
            and d.autoextensible='YES'
          GROUP BY d.tablespace_name) b
where Tablespace_Name = FS_TS_NAME (+)
and Tablespace_name = auto_tbspname (+)
order by 7 desc;