728x90
-- vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 4294967296
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
/sbin/sysctl -p 확인
 

-- vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock unlimited
oracle hard memlock unlimited
-- 방화벽 끄기
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
-- selinux 끄기
setenforce 0
getenforce
 
vi /etc/selinux/config
SELINUX=disabled

-- vi /etc/hosts

127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4
::1        localhost localhost.localdomain localhost6 localhost6.localdomain6
PC IP      hostname
-- 패키지설치
-- group, user 생성
groupadd dba
useradd -g dba oracle
mkdir -p /oracle/base/product/12.1
chown -R oracle:dba /oracle
chmod -R 775 /oracle
 

-- 환경변수 설정 

su - oracle
vi .bash_profile
 
export PATH
umask 022
export EDITOR=vi
export PS1="[\u@\h-$ORACLE_SID:\w]$"
export ORACLE_BASE=/oracle/base
export ORACLE_HOME=$ORACLE_BASE/product/19.3
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:.:
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:.:$PATH
export ORACLE_TERM=xterm
stty erase ^H
alias ss='sqlplus / as sysdba'
alias log='tail -f $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log'
alias dbs='cd $ORACLE_HOME/dbs'
alias dp='cd $ORACLE_BASE/admin/orcl/dpdump'
 
. .bash_profile
(계정 다시 접속 시 자동 저장)
-- display 설정 
[root]
export DISPLAY=localhost:0.0
export $DISPLAY
xhost +
[oracle]
export DISPLAY=localhost:0.0
export $DISPLAY

-- 설치 

cd $ORACLE_HOME
./runInstaller
 
 
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] INS-08101  (0) 2022.06.29
[ORACLE] RAC AIX 19.9 이상 권고 사항  (0) 2022.06.29
[ORACLE] RAC password file 변경  (0) 2022.06.29
[ORACLE] RAC Archive log mode 변경  (0) 2022.06.28
[ORACLE] Interconnect Interface Name 변경  (0) 2022.06.28
728x90

1. password file 설정 유무 확인

$srvctl config database -d orcl

Database unique name: orcl
Database name: orcl
Oracle home: /oracle/base/product/19
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.268.1106093209
Password file: +DATA/ORCL/PASSWORD/pwdorcl.256.1106091249
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: orcl1,orcl2
Configured nodes: rac1,rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

--> resource에 password file이 지정되어있을 경우 생성이 안됨

$srvctl modify database -d orcl -pwfile

--> password file이 있을 경우 제거!!

 
2. password file 생성
ASMCMD>pwcreate --dbuniquename ORCL --format 12 +DATA/ORCL/PASSWORD/orapwdORCL oracle_4U!@#
$orapwd file='+DATA/rac/PASSWORD/orapwdrac2' dbuniquename='ORCL' password='oracle!@#' format=12
 
3. password file 생성 유무 확인
 
$crsctl stat res ora.rac.db -f | grep PWFILE
PWFILE=+DATA/orcl/PASSWORD/orapwdorcl

ASMCMD [+DATA/rac/PASSWORD] > pwget --dbuniquename orcl
+DATA/orcl/PASSWORD/orapwdorcl
 
 
4. user의 password file 유무 확인
SQL> select * from v$pwfile_users
 
USERNAME   SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS              PASSWORD_P LAST_LOGIN LOCK_DATE           EXPIRY_DATE      EXTERNAL_N AUTHENTI COM     CON_ID
---------- ----- ----- ----- ----- ----- ----- ------------------------------ ---------- ---------- ------------------ ------------------ ---------- -------- --- ----------
SYS       TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN                                        PASSWORD NO       0
 
* 일반 유저에 sysdba 권한 부여
SQL> create user rman identified by "oracle!@#$";
User created.
 
SQL> grant sysdba to rman;  
Grant succeeded.
 
SQL> select * from v$pwfile_users;
 
USERNAME   SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS              PASSWORD_P LAST_LOGIN LOCK_DATE           EXPIRY_DATE      EXTERNAL_N AUTHENTI COM     CON_ID
---------- ----- ----- ----- ----- ----- ----- ------------------------------ ---------- ---------- ------------------ ------------------ ---------- -------- --- ----------
SYS       TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN                                        PASSWORD NO       0
RMAN       TRUE  FALSE FALSE FALSE FALSE FALSE OPEN                                        PASSWORD NO       0
 
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC AIX 19.9 이상 권고 사항  (0) 2022.06.29
[ORACLE] Single DB 설치 전 환경 조성  (0) 2022.06.29
[ORACLE] RAC Archive log mode 변경  (0) 2022.06.28
[ORACLE] Interconnect Interface Name 변경  (0) 2022.06.28
[ORACLE] RAC HAIP  (0) 2022.06.28
728x90

1. archive log mode 확인

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /oracle/base/product/19c/dbs/arch
Oldest online log sequence     15
Current log sequence           16
 
2. cluster 중지
 
SQL> alter system set cluster_database=false scope=spfile sid='*';
 
System altered.
 
3. DB 중지
 
[oracle@rac1-:~]$srvctl stop database -d orcl
 
4. 한쪽 노드 DB mount
 
[oracle@rac1-:~]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 15 01:43:58 2021
Version 19.10.0.0.0
 
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup mount
 
ORACLE instance started.
 
Total System Global Area 2399140608 bytes
Fixed Size            8899328 bytes
Variable Size          620756992 bytes
Database Buffers     1761607680 bytes
Redo Buffers            7876608 bytes
Database mounted.
 
5. archive log 경로 및 format 설정
 
SQL> alter system set log_archive_dest_1='location=+RECO' scope=spfile;
 
System altered.
 
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
 
System altered.
 
6. archive log mode 변경
 
SQL> alter database archivelog;
 
Database altered.
 
7. cluster 기동
 
SQL> alter system set cluster_database=true scope=spfile sid='orcl';
 
System altered.
 
8. DB 전체 기동
 
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
 
[oracle@rac1-:~]$srvctl start database -d orcl
[oracle@rac1-:~]$srvctl start service -d orcl
 
[oracle@rac1-:~]$sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 15 01:49:23 2021
Version 19.10.0.0.0
 
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
 
9. archive log mode 변경 확인
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           +RECO
Oldest online log sequence     15
Next log sequence to archive   16
Current log sequence           16
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Single DB 설치 전 환경 조성  (0) 2022.06.29
[ORACLE] RAC password file 변경  (0) 2022.06.29
[ORACLE] Interconnect Interface Name 변경  (0) 2022.06.28
[ORACLE] RAC HAIP  (0) 2022.06.28
[ORACLE] Client Version 호환 설정 (sqlnet.ora)  (0) 2022.06.28
728x90
1. new interface 명 추가
oifcfg setif -global hbbond/192.168.200.0:cluster_interconnect,asm
 
2. crsctl stop crs (양 쪽 노드 전부)
 
3. OS 설정 변경
 
4. crsctl start crs - 정상 기동 확인
 
5. old interface 명 삭제
oifcfg delif -global eno5

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC password file 변경  (0) 2022.06.29
[ORACLE] RAC Archive log mode 변경  (0) 2022.06.28
[ORACLE] RAC HAIP  (0) 2022.06.28
[ORACLE] Client Version 호환 설정 (sqlnet.ora)  (0) 2022.06.28
[ORACLE] DBMS_METADATA.GET_DDL  (0) 2022.06.28
728x90

<HAIP - Highly Available IP>

- 일반적으로 NIC의 이중화를 위해 OS 레벨의 NIC 본딩을 사용
- 11gR2부터 GI(Grid Infrastructure)에서도 이중화 관리
- 1개의 private network가 설정되어있다면 해당 NIC에 169.254 HAIP가 자동 설정
- HAIP 최대 4개 까지 설정 가능
[root@rac1 ~]# oifcfg getif
enp0s3  192.168.79.0  global  public
enp0s8  10.10.10.0  global  cluster_interconnect,asm
 
[root@rac1 ~]# oifcfg iflist -p -n
enp0s3  192.168.79.0  PRIVATE  255.255.255.0
enp0s8  10.10.10.0  PRIVATE  255.255.255.0
enp0s8  169.254.0.0  UNKNOWN  255.255.224.0
virbr0  192.168.122.0  PRIVATE  255.255.255.0
 
[root@rac1 ~]# ifconfig
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.79.61  netmask 255.255.255.0  broadcast 192.168.79.255
        inet6 fe80::d3f2:db91:dcb2:b03a  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:41:75:2b  txqueuelen 1000  (Ethernet)
        RX packets 9734  bytes 2011025 (1.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 8934  bytes 2324354 (2.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
enp0s3:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.79.65  netmask 255.255.255.0  broadcast 192.168.79.255
        ether 08:00:27:41:75:2b  txqueuelen 1000  (Ethernet)
 
enp0s3:3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.79.63  netmask 255.255.255.0  broadcast 192.168.79.255
        ether 08:00:27:41:75:2b  txqueuelen 1000  (Ethernet)
 
enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.61  netmask 255.255.255.0  broadcast 10.10.10.255
        inet6 fe80::712a:dc17:c375:3c61  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:2d:44:68  txqueuelen 1000  (Ethernet)
        RX packets 257367  bytes 169816028 (161.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 362211  bytes 378109687 (360.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
enp0s8:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 169.254.17.151  netmask 255.255.224.0  broadcast 169.254.31.255
        ether 08:00:27:2d:44:68  txqueuelen 1000  (Ethernet)
 
lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 61851  bytes 14470201 (13.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 61851  bytes 14470201 (13.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC Archive log mode 변경  (0) 2022.06.28
[ORACLE] Interconnect Interface Name 변경  (0) 2022.06.28
[ORACLE] Client Version 호환 설정 (sqlnet.ora)  (0) 2022.06.28
[ORACLE] DBMS_METADATA.GET_DDL  (0) 2022.06.28
[ORACLE] RAC Scan IP 추가, 제거, 변경  (1) 2022.06.28
728x90
vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=12a
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a
 
SQLNET.ALLOWED_LOGON_VERSION_CLIENT (CLIENT=<SERVER)
-해당 서버가 client로 쓰일 때 설정값보다 작은 버전으로 접속하면 에러
 
12a for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
8 for Oracle8i authentication protocol
SQLNET.ALLOWED_LOGON_VERSION_SERVER (SERVER=<CLIENT)
-해당 서버가 server로 쓰일 때 설정값보다 작은 버전으로 접속하면 에러
 
12a for Oracle Database 12c release 12.1.0.2 or later authentication protocols (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
9 for Oracle9i Database authentication protocol
8 for Oracle8i Database authentication protocol
-- 설정이 안 되어있을 경우 에러
ERROR:
ORA-28040: No matching authentication protocol
 
-- 조회
SQL> select username, account_status, password_versions from dba_users;
 
***19c에서 8로 설정 시 10,11,12 다 됨
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Interconnect Interface Name 변경  (0) 2022.06.28
[ORACLE] RAC HAIP  (0) 2022.06.28
[ORACLE] DBMS_METADATA.GET_DDL  (0) 2022.06.28
[ORACLE] RAC Scan IP 추가, 제거, 변경  (1) 2022.06.28
[ORACLE] RAC IP 변경 (Public, Private)  (0) 2022.06.28
728x90
* DDL 조회할 때 사용 
---tablespace
select dbms_metadata.get_ddl('TABLESPACE','USERS') || ' ;' from dual ;
 
---user
desc dba_users
select dbms_metadata.get_ddl('USER','EDU10') || ';' from dual ;
select dbms_metadata.get_ddl('USER','ORACLE_OCM') || ';' from dual ;
 
---index
select dbms_metadata.get_ddl('INDEX','index_name', 'user_name') || ';' from dual ;
 
---constraint
select dbms_metadata.get_ddl('CONSTRAINT','constraint_name', 'user_name') || ';' from dual ;
 
---table
--조회
set line 300
col table_name for a20
col owner for a10
select owner, table_name, tablespace_name from dba_tables where owner='SCOTT';
 
select dbms_metadata.get_ddl('TABLE', 'SALGRADE', 'SCOTT') || ' ;' from dual;
select dbms_metadata.get_ddl('TABLE', 'SALGRADE', 'SCOTT') || ' ;' from dba_tables;
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC HAIP  (0) 2022.06.28
[ORACLE] Client Version 호환 설정 (sqlnet.ora)  (0) 2022.06.28
[ORACLE] RAC Scan IP 추가, 제거, 변경  (1) 2022.06.28
[ORACLE] RAC IP 변경 (Public, Private)  (0) 2022.06.28
[ORACLE] DB LINK  (1) 2022.06.28
728x90
-- scan ip 제거 및 추가
$srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac1
 
$srvctl config scan
SCAN name: rac-scan, Network: 1
Subnet IPv4: 192.168.223.0/255.255.255.0/enp0s3, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 192.168.223.75
SCAN VIP is enabled.
 
$srvctl stop scan_listener
$srvctl stop scan
$srvctl remove scan_listener
Remove scan listener? (y/[n]) y
 
# ./srvctl remove scan
Remove the scan? (y/[n]) y
#./srvctl status scan
PRCS-1102 : Could not find any Single Client Access Name (SCAN) Virtual Internet Protocol (VIP) resources using filter TYPE=ora.scan_vip.type on network 1
# ./srvctl status scan_listener
PRCS-1103 : Could not find any Single Client Access Name (SCAN) listener resources using filter TYPE=ora.scan_listener.type on network 1
 
#./srvctl add scan -n rac-scan
 
# ./srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running
 
# ./srvctl config scan
SCAN name: rac-scan, Network: 1
Subnet IPv4: 192.168.223.0/255.255.255.0/enp0s3, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 192.168.223.75
SCAN VIP is enabled.
 
# ./srvctl add scan_listener
 
# ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running
 
# ./srvctl config scan_listener
SCAN Listeners for network 1:
Registration invited nodes:
Registration invited subnets:
Endpoints: TCP:1521
SCAN Listener LISTENER_SCAN1 exists
SCAN Listener is enabled.
 
$srvctl start scan_listener
-- scan ip 변경
# ./srvctl stop scan_listener
# ./srvctl stop scan
# vi /etc/hosts
# ./srvctl modify scan -n rac-scan
 
# ./srvctl config scan
SCAN name: rac-scan, Network: 1
Subnet IPv4: 192.168.223.0/255.255.255.0/enp0s3, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 192.168.223.76
SCAN VIP is enabled.
 
# ./srvctl start scan
# ./srvctl start scan_listener
 
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Client Version 호환 설정 (sqlnet.ora)  (0) 2022.06.28
[ORACLE] DBMS_METADATA.GET_DDL  (0) 2022.06.28
[ORACLE] RAC IP 변경 (Public, Private)  (0) 2022.06.28
[ORACLE] DB LINK  (1) 2022.06.28
[ORACLE] ASMCMD 명령어  (0) 2022.06.28
728x90
-- public ip 변경(RAC)
$srvctl stop database -d orcl --서비스 중지
 
$oifcfg getif
eth1  10.10.10.0  global  cluster_interconnect
eth0  192.168.79.0  global  public
 
$oifcfg delif -global eth0
$oifcfg setif -global eth0/192.168.223.0:public
 
$oifcfg getif
eth1  10.10.10.0  global  cluster_interconnect
eth0  192.168.223.0  global  public
 
#crsctl stop crs
 
vi /etc/hosts --편집
-- private ip 변경
$oifcfg getif
eth1  10.10.10.0  global  cluster_interconnect
eth0  192.168.223.0  global  public
 
$oifcfg setif -global eth2/10.10.20.0:cluster_interconnect
$oifcfg getif
eth1  10.10.10.0  global  cluster_interconnect
eth0  192.168.223.0  global  public
eth2  10.10.20.0  global  cluster_interconnect
 
#crsctl stop crs
ip 변경
vi /etc/host 변경
 
#crsctl start crs
 
$oifcfg delif -global eth1
$oifcfg getif
eth0  192.168.223.0  global  public
eth2  10.10.20.0  global  cluster_interconnect
 
$oifcfg setif -global eth1/10.10.20.0:cluster_interconnect
 
$oifcfg getif
eth0  192.168.223.0  global  public
eth2  10.10.20.0  global  cluster_interconnect
eth1  10.10.20.0  global  cluster_interconnect
 
$oifcfg delif -global eth2
$oifcfg getif
eth0  192.168.223.0  global  public
eth1  10.10.20.0  global  cluster_interconnect

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] DBMS_METADATA.GET_DDL  (0) 2022.06.28
[ORACLE] RAC Scan IP 추가, 제거, 변경  (1) 2022.06.28
[ORACLE] DB LINK  (1) 2022.06.28
[ORACLE] ASMCMD 명령어  (0) 2022.06.28
[ORACLE] TDE  (0) 2022.06.27
728x90
- Database Link 
클라이언트 또는 현재의 데이터베이스에서 네트워크상의 다른 데이터베이스에 접속하기 위한 접속 설정을 정의하는 오라클 객체 
다른 데이터베이스의 객체에 액세스 할 수있는 한 데이터베이스의 스키마 개체
고려 사항 :  ORACLE_INSTANCE 두개 이상, HOST_NAME ORACLE_SID 다름, NLS_CHARACTER_SET 동일

 

1. tnsname.ora 설정
LISTENER =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP> or <HOSTNAME>)(PORT = 1521))
 
     <SERVICE_NAME> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <IP> or <HOSTNAME>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = <SID_NAME>)
    )
  )
 
tnsping <SERVICE_NAME>
 
2. DB Link 조회
 select * from dba_db_links;
   
3. USER 생성 및 권한     
 create user test identified by "test"; [default tablespace test]
    grant resource, connect to test;
    grant create database link to test;
    grant create (public) database link to 유저명;
   
4. DB Link 설정
create <public> database link <link_name> connect to <username> identified by <password> using <service_name>;
 
    ex) create database link orcl connect to test identified by "test" using 'ORCL';
 
- public : 공용 데이터베이스 링크 생성 옵션, 사용하지 않을 경우 자신만 사용가능한 링크 생성
    
5. DB Link 사용
select * from <table명>@;</table명>
    
6. DB Link 삭제
   drop (public) database link <LINK_NAME>;
 
*tnsnames.ora 추가 없이 DB Link 설정 (1,2번 대신)
CREATE DATABASE LINK <LINK_NAME> CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD>
USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <IP> or <HOSTNAME>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = <SID_NAME>)
    )';
 
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC Scan IP 추가, 제거, 변경  (1) 2022.06.28
[ORACLE] RAC IP 변경 (Public, Private)  (0) 2022.06.28
[ORACLE] ASMCMD 명령어  (0) 2022.06.28
[ORACLE] TDE  (0) 2022.06.27
[ORACLE] Partitioning  (0) 2022.06.27

+ Recent posts

티스토리툴바