Azure Oracle Database(19c) 생성 후 Azure Data Factory(ADF) 연결

 

0. Oracle Database VM 설치

참고 url :

Create an Oracle database in an Azure VM - Azure Virtual Machines

Create an Oracle database in an Azure VM - Azure Virtual Machines

 

 

 

 

 

 

1. 기본환경구성

1.1 VM Disk attach

az vm disk attach --name oradata01 --new --resource-group oracle-19-test-rg --size-gb 64 --sku StandardSSD_LRS --vm-name oracle-19-vm

1.2 VM Create network rules

az network nsg rule create \\
    --resource-group oracle-19-test-rg \\
    --nsg-name oracle-19-vm-nsg \\
    --name allow-oracle \\
    --protocol tcp \\
    --priority 1001 \\
    --destination-port-range 1521

az network nsg rule create \\
    --resource-group oracle-19-test-rg \\
    --nsg-name oracle-19-vm-nsg \\
    --name allow-oracle-EM \\
    --protocol tcp \\
    --priority 1002 \\
    --destination-port-range 5502

1.3 Disk partition and mount

[root@oracle-19-vm ~]# parted /dev/sdb mklabel gpt
Information: You may need to update /etc/fstab.

[root@oracle-19-vm ~]# parted -a optimal /dev/sdb mkpart primary 0GB 64GB
Information: You may need to update /etc/fstab.

[root@oracle-19-vm ~]# parted /dev/sdb print
Model: Msft Virtual Disk (scsi)
Disk /dev/sdb: 68.7GB
Sector size (logical/physical): 512B/4096B
Partition Table: gpt
Disk Flags:

Number  Start   End     Size    File system  Name     Flags
 1      1049kB  64.0GB  64.0GB               primary

[root@oracle-19-vm ~]# mkfs -t ext4 /dev/sdb1
mke2fs 1.42.9 (28-Dec-2013)
Discarding device blocks: done
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
3907584 inodes, 15624704 blocks
781235 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2164260864
477 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

[root@oracle-19-vm ~]# parted /dev/sdb print
Model: Msft Virtual Disk (scsi)
Disk /dev/sdb: 68.7GB
Sector size (logical/physical): 512B/4096B
Partition Table: gpt
Disk Flags:

Number  Start   End     Size    File system  Name     Flags
 1      1049kB  64.0GB  64.0GB  ext4         primary

[root@oracle-19-vm ~]# mkdir /u02
[root@oracle-19-vm ~]# mount /dev/sdb1 /u02
[root@oracle-19-vm ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1        59G   53M   56G   1% /u02

[root@oracle-19-vm ~]# chmod 777 /u02
[root@oracle-19-vm ~]# echo "/dev/sdb1               /u02                    ext4    defaults        0 0" >> /etc/fstab
/dev/sda2               /                       btrfs   subvol=root     0 0
UUID=90b82350-a900-475f-8d8f-23db65d7f70a /boot                   xfs     defaults        0 0
UUID=3B29-C04C          /boot/efi               vfat    defaults,uid=0,gid=0,umask=0077,shortname=winnt 0 0
/dev/sdb1               /u02                    ext4    defaults        0 0

1.4 hosts 추가

• 생성규칙 echo "<Public IP> <VMname>.eastus.cloudapp.azure.com <VMname>" >> /etc/hosts

[root@oracle-19-vm ~]# echo "20.196.206.71 oracle-19-vm.eastus.cloudapp.azure.com oracle-19-vm" >> /etc/hosts
[root@oracle-19-vm ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

20.196.206.71 oracle-19-vm.eastus.cloudapp.azure.com oracle-19-vm

1.5 방화벽 설정

[root@oracle-19-vm ~]# firewall-cmd --zone=public --add-port=1521/tcp --permanent
success
[root@oracle-19-vm ~]# firewall-cmd --zone=public --add-port=5502/tcp --permanent
success
[root@oracle-19-vm ~]# firewall-cmd --reload

success
[root@oracle-19-vm ~]#
[root@oracle-19-vm ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0
  sources:
  services: dhcpv6-client ssh
  ports: 1521/tcp 5502/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

2. 오라클 설정

2.1 오라클 리스너 실행

[root@oracle-19-vm ~]# sudo su - oracle

[oracle@oracle-19-vm ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 05:19:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle-19-vm/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle-19-vm.eastus.cloudapp.azure.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-SEP-2021 05:19:04
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle-19-vm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle-19-vm.eastus.cloudapp.azure.com)(PORT=1521)))
The listener supports no services
The command completed successfully

2.2 database 생성

[oracle@oracle-19-vm ~]$ mkdir /u02/oradata
[oracle@oracle-19-vm ~]$ dbca -silent \\
	 -createDatabase \\
   -templateName General_Purpose.dbc \\
   -gdbname oratest1 \\
   -sid oratest1 \\
   -responseFile NO_VALUE \\
   -characterSet KO16KSC5601 \\
   -sysPassword qwer1234 \\
   -systemPassword qwer1234 \\
   -createAsContainerDatabase false \\
   -databaseType MULTIPURPOSE \\
   -automaticMemoryManagement false \\
   -storageType FS \\
   -datafileDestination "/u02/oradata/" \\
   -ignorePreReqs

10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
50% complete
54% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/oratest1.
Database Information:
Global Database Name:oratest1
System Identifier(SID):oratest1
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oratest1/oratest1.log" for further details.

2.3 환경변수 설정

[oracle@oracle-19-vm ~]$ export ORACLE_SID=oratest1
[oracle@oracle-19-vm ~]$ echo "export ORACLE_SID=oratest1" >> ~oracle/.bashrc
[oracle@oracle-19-vm ~]$ source ~oracle/.bashrc

3. 테스트 환경 구성

3.1 테스트 user 생성

[oracle@oracle-19-vm ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 30 05:44:51 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE USER tester1 IDENTIFIED BY qwer1234;
User created.

SQL> grant all privileges to tester1;
Grant succeeded.

SQL> exit

3.2 테스트 데이터 생성

[oracle@oracle-19-vm ~]$ sqlplus tester1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 30 05:42:44 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE TABLE test ( seq NUMBER(4)  NOT NULL );
Table created.

SQL> insert into test values (1) ;
1 row created.

SQL> insert into test values (2) ;
1 row created.

SQL> insert into test values (3) ;
1 row created.

SQL> select * from test;

       SEQ
----------
         1
         2
         3

4. Azure DataFactory 연결

4.1 Oracle 데이터 세트 생성

4.2 Connect to Oracle

4.3 Table 선택

 

 

5. Automate database startup and shutdown

[oracle@oracle-db-vm ~]$ sudo su -
[root@oracle-db-vm ~]# sed -i 's/:N/:Y/' /etc/oratab
[root@oracle-db-vm ~]# cat /etc/oratab
# /etc/oratab
oratest1:/u01/app/oracle/product/19.0.0/dbhome_1:Y
[root@oracle-db-vm ~]#

[root@oracle-db-vm ~]# vi /etc/init.d/dbora
[root@oracle-db-vm ~]# cat /etc/init.d/dbora
#!/bin/sh
# chkconfig: 345 99 10
# Description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to $ORACLE_HOME.
ORA_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac
[root@oracle-db-vm ~]#

[root@oracle-db-vm ~]# chgrp dba /etc/init.d/dbora
[root@oracle-db-vm ~]# chmod 750 /etc/init.d/dbora
[root@oracle-db-vm ~]# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
[root@oracle-db-vm ~]# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
[root@oracle-db-vm ~]# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
[root@oracle-db-vm ~]# reboot