orcl12cr1onlinux7(2インスタンス目)


# Oracle 12cr1 インスタンス作成 on Linux7(2インスタンス目)

## 環境
| 項目|1インスタンス目 |2インスタンス目
|:-----------------|:-----------------|:-----------------
|ホスト名|tdb01|tdb01||
|IPアドレス|192.168.10.71|192.168.10.71|
|DB_NAME|tremoro|kotaro
|ORACLE_SID|tremoro|kotaro

## 手順
---
'1. 1インスタンス目作成

Oracle 12cr1 インストール on Linux7(1インスタンス目)
https://qiita.com/kotarosan/items/ead0a4e680e847b46d43
に従いインスタンス１を作成する（tremoro)

'2. ディレクトリ作成

```
su - oracle
mkdir /u01/app/oracle/diag
mkdir -p /u01/app/oracle/admin/kotaro/adump
mkdir /data/oracle
mkdir /data/oracle/kotaro
mkdir /data/oracle/kotaro/redo
mkdir /data/oracle/kotaro/dbf
mkdir /data/oracle/kotaro/fast_recovery_area
```

'3. initora作成

```
su - oracle
export ORACLE_SID=kotaro
cd $ORACLE_HOME/dbs
cp -p init.ora init$ORACLE_SID.ora

vi init$ORACLE_SID.ora
```

```init$ORACLE_SID.ora
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)

db_name='kotaro'
db_unique_name='kotarop'
memory_target=512M
processes = 150
audit_file_dest='/u01/app/oracle/admin/kotaro/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/data/oracle/kotaro/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/data/oracle/kotaro/kotaro_control1.ctl, /data/oracle/kotaro/kotaro_control2.ctl)
compatible ='12.1.0'

```

'4. SPファイル生成(oracle)

```
(oracle)
sqlplus / as sysdba
CREATE SPFILE FROM PFILE;
```

'5. マウント

```
STARTUP NOMOUNT
```

'6. CREATE DATABASE

```
CREATE DATABASE kotaro
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/data/oracle/kotaro/redo/redo11.log','/data/oracle/kotaro/redo/redo12.log') SIZE 100M,
        GROUP 2 ('/data/oracle/kotaro/redo/redo21.log','/data/oracle/kotaro/redo/redo22.log') SIZE 100M,
        GROUP 3 ('/data/oracle/kotaro/redo/redo31.log','/data/oracle/kotaro/redo/redo32.log') SIZE 100M
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET JA16SJISTILDE
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/data/oracle/kotaro/dbf/system01.dbf'
  SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/data/oracle/kotaro/dbf/sysaux01.dbf'
  SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
   DATAFILE '/data/oracle/kotaro/dbf/users01.dbf'
   SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
   TEMPFILE '/data/oracle/kotaro/dbf/temp01.dbf'
   SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
   DATAFILE '/data/oracle/kotaro/dbf/undotbs01.dbf'
   SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
```

'7. スクリプト実行

```
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql

connect system/system_password
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
```

'8. DB起動確認

```
SELECT STATUS FROM V$INSTANCE;
```


'9. Archive Logモード(必須ではない)

```
export ORACLE_SID=kotaro

sqlplus / as sysdba

select log_mode from v$database;

shutdown

startup mount

alter database archivelog;

alter database open;

SELECT STATUS FROM V$INSTANCE;

select log_mode from v$database;
```

'10. Force Loggingモード(必須ではない)

```
select force_logging FROM v$database;

alter database force logging;

select force_logging FROM v$database;
```

'11. 初期化パラメータ設定設定(必要に応じて実施 一例)

```
sqlplus / as sysdba

set linesize 1000
set pagesize 10000
col NAME_COL_PLUS_SHOW_PARAM FORM A50;
col TYPE form A20;
col VALUE_COL_PLUS_SHOW_PARAM form A200;
set trimspool off

show parameter DB_NAME
show parameter DB_UNIQUE_NAME
show parameter LOG_ARCHIVE_CONFIG
show parameter CONTROL_FILES
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter REMOTE_LOGIN_PASSWORDFILE
show parameter LOG_ARCHIVE_FORMAT
show parameter FAL_SERVER
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET db_unique_name=kotarop SCOPE = SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(kotarop,kotaroos)' SCOPE = SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=kotarop' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=kotaros ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kotaros' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=kotaros SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;

shutdown

startup

set linesize 1000
set pagesize 10000
col NAME_COL_PLUS_SHOW_PARAM FORM A50;
col TYPE form A20;
col VALUE_COL_PLUS_SHOW_PARAM form A200;
set trimspool off

show parameter DB_NAME
show parameter DB_UNIQUE_NAME
show parameter LOG_ARCHIVE_CONFIG
show parameter CONTROL_FILES
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter REMOTE_LOGIN_PASSWORDFILE
show parameter LOG_ARCHIVE_FORMAT
show parameter FAL_SERVER
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT
```

'12. tnsnames.ora設定(ノード1)
```
vi $ORACLE_HOME/network/admin/tnsnames.ora
```

```:tnsnames.ora
kotaro =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tdb01)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kotaro)
    )
  )

```
```
cat $ORACLE_HOME/network/admin/tnsnames.ora
```

'13. listener.ora設定
```
vi $ORACLE_HOME/network/admin/listener.ora
```

```:listener.ora

LISTENER_KOTARO=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=tdb01)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1522))))

SID_LIST_LISTENER_KOTARO=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=kotaro)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=kotaro)))

```
```
cat $ORACLE_HOME/network/admin/listener.ora

lsnrctl status LISTENER_KOTARO
lsnrctl start LISTENER_KOTARO
lsnrctl status LISTENER_KOTARO
ps -ef |grep LISTENER


sqlplus system/system_password@kotaro
quit
```

'14. 自動起動設定(sysvinit) ※この方法（従来型）か、15.のsystemd(Linux7の標準）のどちらかを利用する

```
su -
vi /etc/rc.d/init.d/oracle_kotaro
```

```:/etc/rc.d/init.d/oracle_kotaro
#!/bin/sh
#
# chkconfig: 2345 99 99

#
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_HOME_LISTENER=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=KOTARO
export ORACLE_LISTNER=LISTENER_KOTARO


# Source function library.
. /etc/rc.d/init.d/functions

# See how we were called.
case "$1" in
  start)
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        ;;
  stop)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER"
        ;;
  restart|reload)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        ;;
  status)
        $ORACLE_HOME/bin/lsnrctl status $ORACLE_LISTNER
        ;;
  *)
        echo $"Usage: $0 {start|stop|restart|reload}"
        exit 1
esac

exit 0
```

```
chmod 755 /etc/rc.d/init.d/oracle_kotaro
chkconfig --add oracle_kotaro
```

```
cat /etc/oratab
vi /etc/oratab
```

```:/etc/oratab
tremoro:/u01/app/oracle/product/12.1.0/dbhome_1:Y
kotaro:/u01/app/oracle/product/12.1.0/dbhome_1:Y

```
cat /etc/oratab
```


'15. 自動起動設定(systemd) 

```
su -
vi /etc/sysconfig/oracledb
```

```:/etc/sysconfig/oracledb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
```

```
vi /tmp/oracle.sh
```

```:/tmp/oracle.sh
#!/bin/sh

#
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_HOME_LISTENER=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_LISTNER1=LISTENER_TREMORO
export ORACLE_LISTNER2=LISTENER_KOTARO

# See how we were called.
case "$1" in
  start)
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER1"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER2"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        ;;
  stop)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER1"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER2"
        ;;
  restart|reload)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER1"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop $ORACLE_LISTNER2"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER1"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTNER2"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        ;;
  status)
        $ORACLE_HOME/bin/lsnrctl status $ORACLE_LISTNER1
        $ORACLE_HOME/bin/lsnrctl status $ORACLE_LISTNER2
        ;;
  *)
        echo $"Usage: $0 {start|stop|restart|reload}"
        exit 1
esac

exit 0
```

```
chmod 775 /tmp/oracle.sh
```


```
vi /usr/lib/systemd/system/oracledb.service
```

```:/usr/lib/systemd/system/oracledb.service
[Unit]
Description=Oracle Database service
After=network.target

[Service]
Type=forking
EnvironmentFile=/etc/sysconfig/oracledb
ExecStart=/tmp/oracle.sh start
ExecStop=/tmp/oracle.sh stop
User=root

[Install]
WantedBy=multi-user.target
```

```
cat /etc/oratab
vi /etc/oratab
```

```:/etc/oratab
tremoro:/u01/app/oracle/product/12.1.0/dbhome_1:Y
kotaro:/u01/app/oracle/product/12.1.0/dbhome_1:Y

```
cat /etc/oratab
```

```
systemctl daemon-reload
systemctl status oracledb 
systemctl enable oracledb
systemctl status oracledb
```