2020年4月19日日曜日

CentOS8 oracle19 インストール

■Rocky Linux8 ORACLE RPM インストール

# curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

# dnf install  oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

https://download.oracle.com/otn/linux/oracle19c/190000/oracle-database-ee-19c-1.0-1.x86_64.rpm
# dnf install oracle-database-ee-19c-1.0-1.x86_64.rpm

 # /etc/init.d/oracledb_ORCLCDB-19c configure

# rpm -q --scripts  oracle-database-ee-19c                         # スクリプト確認

$ vi ~/.bash_profile
umask 022
export ORACLE_SID=ORCLCDB
export ORACLE_BASE=/opt/oracle/oradata
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=Japanese_Japan.AL32UTF8                                 # sqlplus 文字化け

#----------------------------------------------------------------------------------------------
【エラー】
ORA-01078: failure in processing system parameters
LRM-00109:
SQL>  create spfile from pfile;

#----------------------------------------------------------------------------------------------

【オラクルバックアップ】

$ exp user/password@sid  file=/tmp/FULL.dmp log=/tmp/FULL.log full=y

$ nice -n 19 openssl enc -aes-256-cbc -e -in FULL.dmp -out FULL.dmp.enc


【OS】
vi /etc/yum.conf << __EOF__
proxy=http://10.0.19.2:3128
__EOF__
dnf install net-tool
dnf install https://repo.zabbix.com/zabbix/5.4/rhel/8/x86_64/zabbix-agent2-5.4.12-1.el8.x86_64.rpm
cat  <<__EOF__ >  /etc/zabbix/zabbix_agent2.d/plugins.d/z99-local.conf 
LogFileSize=1
Server=10.0.19.119
ServerActive=10.0.19.119:10051
Hostname=AMI-ROCKY8-60G
#HostMetadata=Rockylinux
HostMetadataItem=system.uname
ControlSocket=/run/zabbix/agent.sock
AllowKey=system.run[*]
__EOF__
firewall-cmd --add-port=10050/tcp --zone=public --permanent
firewall-cmd --reload
systemctl start zabbix-agent2
systemctl enable zabbix-agent2

dnf install cockpit
systemctl enable --now cockpit.socket
firewall-cmd --list-all
firewall-cmd --add-service=cockpit
firewall-cmd --runtime-to-permanent

【マウントした他ディスクにデータを置く場合。】
/u01にデータを格納するディスクをあらかじめマウント
# mkdir /u01
# vi /etc/fstab
/dev/vdb /u01 xfs defaults
# sync;reboot

※ メモリーは、8G以上が望ましいと思われる。（4Gだと、insufficient free space 発生？）

# dnf -y install  bc binutils gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libgcc.i686 libstdc++ libstdc++-devel libXi libXtst libnsl make sysstat unzip smartmontools ksh sysstat

【Oracle Linux 7 】
# dnf install bc binutils elfutils-libelf glibc glibc-devel ksh libaio libXrender libX11 libXau libXi libXtst libgcc libstdc++ libxcb make policycoreutils policycoreutils-python smartmontools sysstat


# grep MemTotal /proc/meminfo
MemTotal:        7869668 kB
# grep SwapTotal /proc/meminfo
SwapTotal:       4136956 kB

# df -h /tmp
ファイルシス サイズ 使用 残り 使用% マウント位置
/dev/mapper/rl-root 17G 5.3G 12G 31% /
# df -h /dev/shm
ファイルシス サイズ 使用 残り 使用% マウント位置
tmpfs            3.8G     0  3.8G    0% /dev/shm

※ HugePage無効化の確認。（有効の場合は、ORACLE自動メモリ管理が動作しない。）
#  cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
--------------------無効化は、GRUB修正---------------------------------------------------------------
# vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet transparent_hugepage=never"
#  grub2-mkconfig -o /boot/grub2/grub.cfg
# systemctl reboot
#  cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]

---------------------------------------------------------------------------------------------------------------
※IOスケジューラの確認
# cat /sys/block/vda/queue/scheduler
[none] mq-deadline kyber bfq 


【カーネルパラメータ設定】
# vi /home/takahab/settings-oracle.sh
echo =========================変更前=============================
sysctl -a | grep -e aio-max-nr  -e  file-max -e shmmax -e shmmni -e shmall 

MEMTOTAL=$(free -b | sed -n '2p' | awk '{print $2}')
SHMMAX=$(expr $MEMTOTAL / 2)
SHMMNI=4096
PAGESIZE=$(getconf PAGE_SIZE)

cat > /etc/sysctl.d/90-oracle_database_sysctl.conf << __EOF__
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = $SHMMAX
kernel.shmall = $(expr \( $SHMMAX / $PAGESIZE \) \* \( $SHMMNI / 16 \))
kernel.shmmni = $SHMMNI
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.panic_on_oops = 1
__EOF__
echo ========================変更後==============================
sysctl -a | grep -e aio-max-nr  -e  file-max -e shmmax -e shmmni -e shmall 


# sh /home/takahab/settings-oracle.sh
=========================変更前=============================
fs.aio-max-nr = 65536
fs.file-max = 780731
kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096
========================変更後==============================
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 251829248
kernel.shmmax = 4029270016
kernel.shmmni = 4096

# /sbin/sysctl --system

※　カーネルパラメータ反映にはrebootが必要。


# vi /etc/security/limits.d/90-oracle_database_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          hard    memlock         134217728
oracle          soft    memlock         134217728
oracle          soft    data            unlimited
oracle          hard    data            unlimited

# sync;systemctl reboot
# ulimit -Sn; ulimit -Hn;ulimit -Su; ulimit -Hu;ulimit -Ss; ulimit -Hs;ulimit -Sl; ulimit -Hl;


【グループとユーザの作成】
# i=2000; for group in oinstall dba oper backupdba dgdba kmdba asmdba asmoper asmadmin racdba; do
groupadd -g $i $group; i=$(expr $i + 1)
done
# useradd -u 2000 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,racdba -d /home/oracle oracle
# passwd oracle

# su  - oracle
$ vi ~/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRATY_PATH:$ORACLE_HOME/lib
export LANG=ja_JP.utf-8
export NLS_LANG=Japanese_Japan.AL32UTF8
export ORACLE_SID=ORCL
#export ORACLE_SID=arcsdbms
export CV_ASSUME_DISTID=RHEL8.2
export TNS_ADMIN=${ORACLE_HOME}/network/admin
umask 022

$ .  ./.bash_profile

※●SJIS
$　export NLS_LANG=Japanese_Japan.JA16SJIS

sudo sh -c  'echo "oracle ALL=(ALL) NOPASSWD:ALL" > /etc/sudoers.d/oracle'
sudo chmod 0440 /etc/sudoers.d/oracle

【ダウンロード LINUX.X64_193000_db_home.zip】


# su - oracle
$ mkdir Download
$ mv LINUX.X64_193000_db_home.zip  ~/Download/
# su
# mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 
# chown -R oracle:oinstall /u01/app
# chmod -R 775 /u01

# mkdir -p /u01/app/oraInventory                ＃ 自動的に作られる。
# chown oracle:oinstall /u01/app/oraInventory
# chmod -R 775 /u01

# dnf install unzip
# su - oracle
$ ln -s /u01/app/oracle/product/19.0.0/dbhome_1 database
$ unzip ~/Download/LINUX.X64_193000_db_home.zip -d ~/database/

【サイレントインストール】
# su - oracle
mkdir -p Install/response/
$ grep -v '^\s*#' ~/database/install/response/db_install.rsp |grep -v '^\s*$'
$ cp  /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_install.rsp ./Install/response/

$ vi ./Install/response/db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=
oracle.install.db.rootconfig.configMethod=
oracle.install.db.rootconfig.sudoPath=
oracle.install.db.rootconfig.sudoUserName=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=ARCSDBMS
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=512
oracle.install.db.config.starterdb.installExampleSchemas=
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=sys
oracle.install.db.config.starterdb.password.SYSTEM=system
oracle.install.db.config.starterdb.password.DBSNMP=dbsnmp
oracle.install.db.config.starterdb.password.PDBADMIN=pdbadmin
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=

$ grep -v '^\s*#' ~/Install/response/db_install.rsp |grep -v '^\s*$'

$ cd  /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
$ cp init.ora initARCSDBMS.ora
$ vi initARCSDBMS.ora
db_name='ARCSDBMS'
<ORACLE_BASE> を '/u01/app/oracle'                 # $ORACLE_BASEの内容
audit_file_dest='/u01/app/oracle/admin/ARCSDBMS/adump'
compatible ='19.0.0' に変更

$ mkdir $ORACLE_BASE/fast_recovery_area
:
# vi /u01/app/oracle/product/19.0.0/dbhome_1/dbs/initARCSDBMS.ora
-- 確認 --

su - oracle
cd /u01/app/oracle/product/19.0.0/dbhome_1 
./runInstaller -waitforcompletion  -silent -noconfig -responseFile  ~/Install/response/db_install.rsp

$ grep -v '^\s*#'  /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_2024-05-23_05-54-36PM.rsp |grep -v '^\s*$'

$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
$ mkdir -p $ORACLE_HOME/rdbms/audit
$ chmod 770 $ORACLE_BASE/admin/$ORACLE_SID/adump
$ chmod 770 $ORACLE_HOME/rdbms/audit

$ ${ORACLE_HOME}/bin/netca -silent -responsefile ~/Install/response/netca.rsp

$ ${ORACLE_HOME}/bin/dbca -silent -createDatabase -responseFile ~/Install/response/dbca.rsp

# mkdir /data
# chown oracle:oinstall /data

/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbca -silent -createDatabase \
-templateName /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc \
-gdbName orcl.oracle.com \
-sid orcl \
-sysPassword change_on_install \
-systemPassword manager \
-datafileDestination /data \
-characterSet "AL32UTF8" \
-nationalCharacterSet "UTF8"

$ sqlplus / as sysdba
startup

【ログ確認】
vi /tmp/InstallActions2024-05-21_10-55-39AM/installActions2024-05-21_10-55-39AM.log
vi /u01/app/oracle/product/19.0.0/dbhome_1/install/make.log

rootユーザーとして次のスクリプトを実行します:
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/oracle/product/19.0.0/dbhome_1/root.sh 

1) 起動
# firewall-cmd --zone=public --add-port=5500/tcp --permanent
# firewall-cmd --zone=public --add-port=1521/tcp --permanent
# firewall-cmd --reload

---------------------------------------------------------------------------------------------------------------
【下記エラーが発生したため、rocky linux9 は断念しrocky linux8 で実行】

【エラー】
■/usr/bin/ld: /usr/lib64/libpthread_nonshared.a が見つかりません
→ dnf --enablerepo=devel install compat-libpthread-nonshared

■/usr/bin/ld: -lclntshcore が見つかりません
lrwxrwxrwx. 1 oracle oinstall        21  5月 17 19:27 libclntshcore.so -> libclntshcore.so.19.1
libclntshcore.so.19.1が無い。
→ もう一度zipから展開したら解決
su
rm -rf  /u01/app
mkdir -p /u01/app/oracle/product/19c/dbhome_1 
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01
su - oracle
#ln -s /u01/app/oracle/product/19c/dbhome_1 database
unzip ~/Download/LINUX.X64_193000_db_home.zip -d ~/database/
cd  /u01/app/oracle/product/19c/dbhome_1/lib/
ln -s  stubs/libc.so.6     libc.so
chown oracle:oinstall libc.so

■ck_rand_load_file.c:(.text+0xd4): undefined reference to `stat'


■ /usr/bin/ld: /u01/app/oracle/product/19c/dbhome_1/lib//libclntshcore.so: undefined reference to `atexit'





$ cd database
$ export LANG=C
$  ./runInstaller

1) Set up Software Only
2) Single instance database installation
x) Server class
3) Enterprise Edition
4) Oracle base:  /u01/app/oracle
5) Inventory Directory:  /u01/app/oraiventory
x) General Purpose / Transaction Processing
x) Global database name: arcsdbms
x) Oracle sysstem Identifier(SID): arcsdbms
x) Create as Container database: False
x) Enable Automatic Memory Management: 40％
x) Use Unicode(AL32UTF8)
x) Install sample chemas in the database: False 
x) Specify database file location: /u01/app/oracle/oradata
x) Oracle Automatic Straage Management:(ASM) : False
x) Register with Enterprise Manager Cloud control: False
x) Enable Recovery: True / File system: /u01/app/oracle/recovery_area
x) sys / password    , system / password
     oralinventory Group Name: oinstall
6) dba/oper/backupdba/dgdba/kmdba/racdba
7) Automatically run configuration scripts              # Autoにしなければ、手動で実行。
    Use "root" user credential   
    Password  xxxxxxx
8) Verification Result Swap Size :  Ignore All

The registration of Oracle Database was successful 
close

# su - oracle
$ export LANG=C
$ dbca
1) Create a database
2) Advanced configuration
3) Oracle Single Instance database／ Custom Database
4) Global database name: orcl.world
     SID: orcl           
     Create as Container database
     Create a Container database with on or more PDBs
     PDB name: pdb_orcl
5) Use following for the database storage attributes/ File System /
      {ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/ Use oracle-Managed Files(OMF)
6) Fast Recovery Option ( Specify Fast .../ Enable archiving )
7) Create new listener (Listener name：LISTENER   port:1521)
8) Database components ( all off )
9) Memory: Use Automatic Shared Memory Management
10) Sizing:8192/320
11) Character sets: Use Unicode (AL32UTF8)  japaneze/japan
12) Conneciton mode:Shared server mode
13) Samle schemas(off)
14) Management Option: Cofigure Enterprise Manger(EM) database express/5500
       Cofigure EM database express port as global port -> on
15) Use the same administrative password for al accounts( **%00****% )
16) Creation Option : Create database

すごく時間がかかる！！！

# /u01/app/oraInventory/orainstRoot.sh
# /opt/app/oracle/product/19.0.0/dbhome_1/root.sh 



※dbca 日本語が字化けする。
$ vi /u01/app/oracle/product/19c/dbhome_1/bin/dbca
#JRE_DIR=/home/oracle/oracle19c/jdk/jre
JRE_DIR=/usr/lib/jvm/jre

1) 起動
# firewall-cmd --zone=public --add-port=5500/tcp --permanent
# firewall-cmd --zone=public --add-port=1521/tcp --permanent
# firewall-cmd --reload

$ sqlplus user@password@databasename   (or sid )
or
$ sqlplus / as sysdba
SQL> staratup
SQL>  alter user sys identified by password;
SQL> show con_name                                       # Container Name 確認

■ EM Expressの有効化
SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);
SQL> select dbms_xdb_config.getHttpsPort() from dual;
SQL> exec dbms_xdb_config.SetGlobalPortEnabled(TRUE);
SQL> exit

$ lsnrctl status LISTENER
$ lsnrctl start    LISTENER
$ lsnrctl stop     LISTENER

URL: https://localhost:5500/em
USR: sys
PW:password
Container Name:CDB$ROOT 


【漢字コード変更】

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE JA16SJISTILDE;
SHUTDOWN IMMEDIATE;
STARTUP;


【初期パスワード】
sys         ： change_on_install
system ： manager
→初期パスワードでは、emのログインできなかった。
    パスワード設定を行う必要あり。

【起動手順-まとめ】
■起動
$ sqlpluse / as sysdba
sql > startup
SQL>  alter user sys identified by password;
sql> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);
sql> select dbms_xdb_config.getHttpsPort() from dual;
sql> exec dbms_xdb_config.SetGlobalPortEnabled(TRUE);
$!
URL https://192.168.100.203:5500/em
URL: https://localhost:5500/em
USR: sys
PW:password
Container Name:CDB$ROOT 
※起動するまでに少し時間がかかる。

■ PDB作成（データベースの作成）
$ sqlplus / as sysdba
sql> show con_name
sql> create pluggable database salespdb admin user salesadm identified by password;

sql> alter pluggable database salespdb open;
sql> show pdbs        <- マウント状態確認
sql>  alter session set container=salespdb;
sql> create user admin identified by password;
sql> alter USER admin identified by "new password";
sql> grant connect to admin;
sql> grant create table to admin;
sql> grant SELECT any table ,UPDATE any table ,DELETE any table ,INSERT any table to admin;
sql> grant CREATE any table to admin;
sql> grant DROP any table to admin;
sql> grant unlimited tablespace to admin;

sql>  grant create any index to admin;
sql> grant drom any index to admin;
sql> grant alter table to admin;

sql> alter pluggable database salespdb close;
sql> drop pluggable database salespdb including datafiles;

sql> conn admin @pdb_orcl
sql> col table_name for a30
sql> col tablespace_name for a15
sql> select table_name,tablespace_name, num_rows, status, blocks from user_tables;
sql> def
sql> alter pluggable database pdb_orcl close immediate;               # 指定PDB停止
sql>  alter pluggable database all close immediate;                            # 全停止
sql>  shutdown immediate;                                                                            # CDB停止 


※ パスワードを無期限に変更
sql> alter profile default limit password_life_time unlimited;
※ ロック解除
sql> alter user <username> account unlock;
※ 再設定
sql> alter user ADMIN identified by "パスワード";
※ 確認 
sql> select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';
sql>SELECT u.username, p.limit FROM dba_users u
        INNER JOIN dba_profiles p    ON         u.profile = p.profile
                                                                    AND p.resource_name = 'PASSWORD_LIFE_TIME'  
        WHERE u.username = 'ADMIN';

$ sqlp admin/PW%xxxxxx%@localhost:1521/arcsdbms.world
$ cat file | sqlp -s admin/PW%xxxxxx%@localhost:1521/arcsdbms.world

Dbeaver でアクセス可能
サービス名：salespdb.world
ID：                  admin
PW:                   password


■ オラクル自動起動
# vi /etc/oratab
orcl:/u01/app/oracle/product/19c/dbhome_1:Y

# vi /etc/sysconfig/env.oracle
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
PATH=$PATH:/usr/bin:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRATY_PATH:$ORACLE_HOME/lib
NLS_LANG=Japanese_Japan.AL32UTF8
ORACLE_SID=orcl

# vi /etc/systemd/system/oracle.service
[Unit]
Description=Oracle Databaes Service
After=network.target oraclelsnr.service
[Service]
Type=forking
RemainAfterExit=yes
TimeoutStopSec=5min
EnvironmentFile=/etc/sysconfig/env.oracle
ExecStart=/u01/app/oracle/product/19c/dbhome_1/bin/dbstart  $ORACLE_HOME ExecStop=/u01/app/oracle/product/19c/dbhome_1/bin/dbshut   $ORACLE_HOME Restart=no
User=oracle
Group=oinstall
KillMode=none
[Install]
WantedBy=multi-user.target

# systemctl daemon-reload
# systemctl start oracle

■ pdbの自動起動
sql> alter pluggable database salespdb open;
sql> alter pluggable database all save state;
sql> alter pluggable database pdb1, pdb2 save state;        #個別の場合
※ 上記コマンドは、今の状態をセーブするコマンドなので、起動した状態で実行。

【ZABBIXでoracle監視】 
Rocky8.7 + oracle19c + zabbix6.4


ホストマクロ設定（ホスト→マクロ→ホストマクロ）
{$ORACLE.CONNSTRING}   : tcp://localhost:1521
{$ORACLE.SERVICE}              : zabbix
{$ORACLE.PASSWORD}        : zbx%xxxxxx%
{$ORACLE.USER}                     : zbx_monitor

Install Oracle Instant Client.
# dnf install libnsl
# rpm -ivh oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm

# vi /etc/profile.d/oracle.sh
export LD_LIBRARY_PATH=/usr/lib/oracle/19.20/client64/lib:$LD_LIBRARY_PATH
# .  /etc/profile
# vi /etc/sysconfig/httpd
LD_LIBRARY_PATH=/usr/lib/oracle/19.20/client64/lib/:$LD_LIBRARY_PATH
# vi /etc/ld.so.conf.d/oracle-instantclient.conf
/usr/lib/oracle/19.20/client64/lib
# ldconfig
# ldconfig  -p |grep libclntsh

# mkdir /etc/systemd/system/zabbix-agent2.service.d
#  vi /etc/systemd/system/zabbix-agent2.service.d/limit.conf
# cat /etc/systemd/system/zabbix-agent2.service.d/limit.conf
[Service]
LimitNOFILE=65536

# systemctl daemon-reload
# systemctl restart  zabbix-agent2

#

create pluggable database zabbix admin user zbx_admin identified by "system" file_name_convert=( 'ORCLCDB','ZABBIX');
alter pluggable database zabbix open;
alter pluggable database all save state;
alter session set container=zabbix;






To create Oracle user with required privileges, run:
CREATE USER zbx_monitor IDENTIFIED BY  "zbx%xxxxxx%";
-- Grant access to the zbx_monitor user.
GRANT CONNECT, CREATE SESSION TO zbx_monitor;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zbx_monitor;
GRANT SELECT ON DBA_TABLESPACES TO zbx_monitor;
GRANT SELECT ON DBA_USERS TO zbx_monitor;
GRANT SELECT ON SYS.DBA_DATA_FILES TO zbx_monitor;
GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY TO zbx_monitor;
GRANT SELECT ON V_$ARCHIVE_DEST TO zbx_monitor;
GRANT SELECT ON V_$ASM_DISKGROUP TO zbx_monitor;
GRANT SELECT ON V_$DATABASE TO zbx_monitor;
GRANT SELECT ON V_$DATAFILE TO zbx_monitor;
GRANT SELECT ON V_$INSTANCE TO zbx_monitor;
GRANT SELECT ON V_$LOG TO zbx_monitor;
GRANT SELECT ON V_$OSSTAT TO zbx_monitor;
GRANT SELECT ON V_$PGASTAT TO zbx_monitor;
GRANT SELECT ON V_$PROCESS TO zbx_monitor;
GRANT SELECT ON V_$RECOVERY_FILE_DEST TO zbx_monitor;
GRANT SELECT ON V_$RESTORE_POINT TO zbx_monitor;
GRANT SELECT ON V_$SESSION TO zbx_monitor;
GRANT SELECT ON V_$SGASTAT TO zbx_monitor;
GRANT SELECT ON V_$SYSMETRIC TO zbx_monitor;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO zbx_monitor;

GRANT SELECT ANY DICTIONARY TO zbx_monitor;
GRANT UNLIMITED TABLESPACE TO zbx_monitor;
GRANT SELECT ANY DICTIONARY TO zbx_monitor;
GRANT SELECT ON V_$SESSION TO zbx_monitor;
GRANT SELECT ON V_$SYSTEM_EVENT TO zbx_monitor;
GRANT SELECT ON V_$EVENT_NAME TO zbx_monitor;
GRANT SELECT ON V_$RECOVERY_FILE_DEST TO zbx_monitor;
grant select on v_$active_session_history to zbx_monitor;
grant select on v_$archive_dest to zbx_monitor;
grant select on v_$asm_diskgroup to zbx_monitor;
grant select on v_$datafile to zbx_monitor;
grant select on v_$log to zbx_monitor;
grant select on v_$osstat to zbx_monitor;
grant select on v_$pgastat to zbx_monitor;
grant select on v_$process to zbx_monitor;
grant select on v_$recovery_file_dest to zbx_monitor;
grant select on v_$restore_point to zbx_monitor;
grant select on v_$sgastat to zbx_monitor;


create pluggable database zabbix admin user zbx_admin identified by "system" file_name_convert=( 'ORCLCDB','ZABBIX');
alter pluggable database zabbix open;
alter pluggable database all save state;
alter session set container=zabbix;

To create Oracle user with required privileges, run:
CREATE USER zbx_monitor IDENTIFIED BY  "zbx%xxxxxx%";
-- Grant access to the zbx_monitor user.

grant connect, create session to zbx_monitor;
grant select any dictionary to zbx_monitor;
grant select on dba_tablespace_usage_metrics to zbx_monitor;
grant select on dba_tablespaces to zbx_monitor;
grant select on dba_users to zbx_monitor;
grant select on sys.dba_data_files to zbx_monitor;

grant select on v_$active_session_history to zbx_monitor;
grant select on v_$archive_dest to zbx_monitor;
grant select on v_$asm_diskgroup to zbx_monitor;
grant select on v_$database to zbx_monitor;
grant select on v_$datafile to zbx_monitor;
grant select on v_$event_name to zbx_monitor;
grant select on v_$instance to zbx_monitor;
grant select on v_$log to zbx_monitor;
grant select on v_$osstat to zbx_monitor;
grant select on v_$pgastat to zbx_monitor;
grant select on v_$process to zbx_monitor;
grant select on v_$recovery_file_dest to zbx_monitor;
grant select on v_$recovery_file_dest to zbx_monitor;
grant select on v_$restore_point to zbx_monitor;
grant select on v_$session to zbx_monitor;
grant select on v_$sgastat to zbx_monitor;
grant select on v_$sysmetric to zbx_monitor;
grant select on v_$system_event to zbx_monitor;
grant select on v_$system_parameter to zbx_monitor;
grant unlimited tablespace to zbx_monitor;
                   
create profile ZABBIX limit PASSWORD_LIFE_TIME unlimited;
alter user zbx_monitor profile ZABBIX;
alter profile ZABBIX limit PASSWORD_LIFE_TIME unlimited;
set linesize 500
col resource_name for a30
col resource_type for a20
col profile for a20
col limit for a20
select * from dba_profiles where profile='ZABBIX' and resource_name='PASSWORD_LIFE_TIME';

接続確認
$ vi   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora << __EOF__

__EOF__

sqlplus zbx_monitor/password@zabbix


※client rpm install:　export TNS_ADMIN=/usr/lib/oracle/19.20/client64/lib/network/admin

$ sqlplus zbx_monitor/zbx%xxxxxx%@zabbix
$ zabbix_get -s oracle-host -k oracle.ping["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

 zabbix_get -s 10.0.18.211 -k oracle.ping['tcp://10.0.18.211:1521','zbx_monitor','system','zabbix.world']
zabbix_get -s 10.0.18.211 -k oracle.instance.info['tcp://10.0.18.211:1521','zbx_monitor','system','zabbix.world']


$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/takaha
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8


※オラクルクライアントのみをインストール。
dnf install libaio libnsl
rpm -ivh oracle-instantclient19.20-odbc-19.20.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm
vi  /etc/ld.so.conf.d/oracle-instantclient.conf  <<__EOF__
/usr/lib/oracle/19.20/client64/lib
__EOF__
vi /usr/lib/oracle/19.20/client64/lib/network/admin/tnsnames.ora
ZABBIX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zabbix.world)
    )
  )            



 export NLS_LANG=Japanese_Japan.AL32UTF8             
$ sqlplus zbx_monitor/system@zabbix
















【ZABBIXで監視】
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB_ORCL;
 セッションが変更されました。

SQL> startup
プラガブル・データベースがオープンされました。
SQL> CREATE USER zbx_monitor IDENTIFIED BY "No%xxxxxx%" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
ユーザーが作成されました。
SQL> GRANT CONNECT TO zbx_monitor;
SQL> GRANT RESOURCE TO zbx_monitor;
SQL> ALTER USER zbx_monitor DEFAULT ROLE ALL;
SQL> GRANT SELECT ANY TABLE TO zbx_monitor;
SQL> GRANT CREATE SESSION TO zbx_monitor;
 SQL> GRANT SELECT ANY DICTIONARY TO zbx_monitor;
SQL> GRANT UNLIMITED TABLESPACE TO zbx_ monitor;
SQL> GRANT SELECT ANY DICTIONARY TO zbx_monitor;
SQL> GRANT SELECT ON V_$SESSION TO zbx_monitor;
SQL> GRANT SELECT ON V_$SYSTEM_EVENT TO zbx_monitor;
SQL> GRANT SELECT ON V_$EVENT_NAME TO zbx_monitor;
SQL> GRANT SELECT ON V_$RECOVERY_FILE_DEST TO zbx_monitor;

SQL> create profile ZABBIX limit PASSWORD_LIFE_TIME unlimited;
SQL> alter user zbx_monitor profile ZABBIX;
SQL> alter profile ZABBIX limit PASSWORD_LIFE_TIME unlimited;
SQL>  select * from dba_profiles where profile='ZABBIX' and resource_name='PASSWORD_LIFE_TIME';
PROFILE      RESOURCE_NAME             RESOURCE   LIMIT              COM INH IMP
---------------------------------------------------------------------------------------------------
ZABBIX         PASSWORD_LIFE_TIME    PASSWORD   UNLIMITED  NO    NO   NO
SQL> quit

※ ユーザ確認
set lines 400
col account_status for a10
col username for a20
col last_login for a40
col default_tablespace for a10
select con_id, user_id, username,default_tablespace, account_status,  expiry_date, last_login, common from cdb_users where common!='YES';

※ データベース／テーブル／列確認
$ sqlp / as sysdba
sql> show pdbs        <- マウント状態確認
sql>  alter session set container=arcsdbms;

$ sqlp arcsdbcb@localhost:1521/arcsdbms.world
sql> select table_name,tablespace_name, num_rows, status, blocks from user_tables;
sql> desc table名



【エラー】
ORA-28000: the account is locked

$ sqlp / as sysdba
sql> show pdbs        <- マウント状態確認
sql>  alter session set container=arcsdbms;
sql > col username for a20
sql > col profile for a20
sql> select username, account_status, profile from dba_users where username = 'ADMIN';
sql > alter user ADMIN account lock;
sql> alter user ADMIN account unlock;
sql>  alter USER ADMIN identified by "new password";

$ sqlp admin@localhost:1521/arcsdbms.world








＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃
ここまで確認ずみ
以下、未整備












※接続できなかった。
ORA-01102: cannot mount database in EXCLUSIVE mode
# ps -ef | grep ora_ | grep $ORACLE_SID
# ipcs -b
# ipcrm -m 
→プロセスや共有メモリを調べたが結局OSの再起動で解除された。

ORA-12777: A non-continuable error encountered.  Check the error stack for additional information [ksm_check_ob_paths:1], [ORACLE_BASE], [], [].
→ORACLE_BASEが設定されていない。(.bash_profile)
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
→ $ mkdir /home/oracle/app/oracle/fast_recovery_area
ORA-09925: Unable to create audit trail file
→audit_file_dest='/home/oracle/app/oracle/admin/ORCL/adump'
      $ mkdir /home/oracle/app/oracle/admin/ORCL/adump
ORA-12162: TNS:net service name is incorrectly specified
→export ORACLE_SID=asdb を.profileに追加。
ORA-00205: error in identifying control file, check alert log for more info
→show parameter control_files;
ORA-12541: TNS: リスナーがありません
→

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-AUG-2020 08:41:17

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

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                06-AUG-2020 08:19:48
Uptime                    0 days 0 hr. 21 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /usr/oracle/database/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/oracle/database/dbs/initorcl.ora'

dbca 日本語が字化けする。
$ vi /home/oracle/oracle19c/bin/dbca
#JRE_DIR=/home/oracle/oracle19c/jdk/jre
JRE_DIR=/usr/lib/jvm/jre

$ alternatives --config java    でjavaの場所を確認。

SQL> startup
ORA-12777:
ORA-08275: Environment variable unset
→export ORACLE_BASE=/u01/app/oracle              # .bash_profile に追加
ORA-01102: cannot mount database in EXCLUSIVE mode
→何か残ったようだ、再起動したらエラーは出なかった。


■ CDB切り替え
SQL> startup pfile=/u01/app/oracle/admin/CXDNEXT/pfile/init.ora.11162021183614
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;      # 現在のコンテナ
SQL> select instance_name,status  from v$instance;
SQL> col name for a40
SQL> select name, con_id, dbid, con_uid, guid from v$containers order by con_id;
SQL> select dbid, name, cdb from v$database;
SQL> alter session set container=arcsdbms;
SQL> show pdbs
SQL> create user admin identified by casio00;
SQL> alter user admin identified by password;
SQL> startup
SQL> STARTUP PLUGGABLE DATABASE arcsdbms OPEN READ WRITE;
SQL> alter session set container=CDB$ROOT;

SQL> set pagesize 100
SQL> col PROPERTY_VALUE for a40
SQL> col PROPERTY_NAME for a40
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE  FROM   DATABASE_PROPERTIES;




$ vi /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = orcl.world)
    )
  )

PDB_ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb_orcl.world)
    )
  )

$ sqlplus monitor/No%xxxxxxx%@PDB_ORCL
SQL*Plus: Release 19.0.0.0.0 - Production on 水 12月 15 18:21:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。


1） インスタンス一覧（show databases;)
SQL> select * from v$instance;
SQL> select host_name, instance_name,status  from v$instance;
2）テーブル一覧
SQL> col status for a20
SQL> col cluster_name for a20
SQL> col tablespace_name for a40
SQL> set pagesize 100
SQL> select table_name tablespace_name, cluster_name, status, blocks, empty_blocks  from user_tables order by table_name;

ユーザテーブル：USER_TABLES
アクセス可能：  ALL_TABLES
DB内全て    ：  DBA_TABLES

SQL> SELECT OWNER,TABLE_NAME FROM ALL_TABLES;
3) session確認
SQL> select USERNAME,OSUSER,MACHINE,TERMINAL,PROGRAM from v$session;

4)役割確認
select grantee, privilege from dba_sys_privs where grantee='SYS';

■
$ sqlplus / as sysdba
sql> startup
sql> show pdbs        <- マウント状態確認
sql> alter pluggable database pdb_orcl open;
sql> show pdbs        <- オープン状態確認（ read write )
sql> alter pluggable database pdb_orcl close;

・共通ユーザ作成
SQL> create user c##user1 identified by password container = all;   # 共通ユーザ作成
SQL> drop user c##user1 ;         # 共通ユーザ削除
※共通ユーザは 接頭語[c##]が必要。

・ローカルユーザ作成
sql> alter pluggable database pdb_orcl open;
SQL> alter session set container = pdb_orcl;    # pdb に接続
SQL> create user pdb_user1 identified by  password container = current;
sql> grant dba to pdb_user1;
sql> ALTER USER username IDENTIFIED BY "new_password" ;
sql> alter pluggable database pdb_orcl close;


■ zabbix-oci-dbaas-master 入手

# ./zabbix-oci-dbaas-master/deploy_zabbix-oci-dbaas.sh "monitor" "No%xxxxxx%"
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_active.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_active.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_asmdata.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_asmdata.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_asmreco.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_asmreco.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_block.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_block.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_connection.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_connection.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_filesize.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_filesize.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_fra.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_fra.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_invalid.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_invalid.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_jobs.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_jobs.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_lock.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_lock.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_metric.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_metric.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_redo.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_redo.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_rman.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_rman.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_size.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_size.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_tbssysaux.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_tbssysaux.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_tbssystem.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_tbssystem.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_tbsundotbs1.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_tbsundotbs1.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_tbsusers.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_tbsusers.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/db_uptime.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/db_uptime.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/tb_list.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/tb_list.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/sql/tb_size.sql' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/sql/tb_size.sql'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/zabbix-oci-dbaas.conf.org' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/zabbix-oci-dbaas.conf'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/zabbix-oci-dbaas.sh' -> '/etc/zabbix/scripts/agentd/zabbix-oci-dbaas/zabbix-oci-dbaas.sh'
'./zabbix-oci-dbaas-master/zabbix-oci-dbaas/zabbix_agentd.conf' -> '/etc/zabbix/zabbix_agent2.d/zabbix-oci-dbaas.conf'

# vi /etx/zabbix/zabbix_agent2.conf
 UnsafeUserParameters=1
※他のリモート実行は設定済みとする。

# systemctl restart zabbix-agent2

※ 感謝
https://qiita.com/y-araki-qiita/items/f25e02182c7f279c0199




■ PDB作成（データベースの作成）
$ sqlplus / as sysdba
SQL> show con_name
SQL> alter session set container=PDB$SEED;
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_k5lfwyh0_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_k5lfwyh4_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_k5lfwyh5_.dbf

SQL> conn / as sysdba
SQL> !mkdir /u01/app/oracle/oradata/ARCSDBMS

SQL> create pluggable database arcsdbms admin user admin identified by cXsXo00
　　　file_name_convert = ('/u01/app/oracle/oradata/ORCL/datafile/',
　　 '/u01/app/oracle/oradata/ARCSDBMS/');

-------------------------------------------------------------------------------------------
SQL> create pluggable database arcsdbms admin user admin identified by cXsXo00
　　　file_name_convert = ('/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/',
　　 '/u01/app/oracle/oradata/ARCSDBMS/');
--------------------------------------------------------------------------------------------
SQL> !ls -l /u01/app/oracle/oradata/ARCSDBMS/

SQL> ALTER PLUGGABLE DATABASE arcsdbms UNPLUG INTO '/oracle/data/arcsdbms.xml';
SQL> alter session set container=arcsdbms;

SQL> DROP PLUGGABLE DATABASE arcsdbms INCLUDING DATAFILES;

ドロップしてから、再作成すると。
RA-01276: ファイル/u01/app/oracle/oradata/ARCSDBMS/o1_mf_system_jvjo4qhc_.dbfは追加できません。ファイルにOracle Managed Filesのファイル名があります。
作成できない！！なぜ？
# systemctl enable oracle

※ SElinuxわすれずに！！
# journalctl -xe

・プラガブル・データベースの自動起動
sqlp / as sysdba
sql> alter pluggable database pdb_orcl open;
sql> alter pluggable database pdb_orcl save state;









■ PDB作成（データベースの作成）
$ sqlplus / as sysdba
SQL> show con_name
SQL> alter session set container=PDB$SEED;
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_k5lfwyh0_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_k5lfwyh4_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_k5lfwyh5_.dbf

SQL> conn / as sysdba
SQL> !mkdir /u01/app/oracle/oradata/ARCSDBMS

SQL> create pluggable database arcsdbms admin user admin identified by cXsXo00
　　　file_name_convert = ('/u01/app/oracle/oradata/ORCL/datafile/',
　　 '/u01/app/oracle/oradata/ARCSDBMS/');

-------------------------------------------------------------------------------------------
SQL> create pluggable database arcsdbms admin user admin identified by cXsXo00
　　　file_name_convert = ('/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/',
　　 '/u01/app/oracle/oradata/ARCSDBMS/');
--------------------------------------------------------------------------------------------
SQL> !ls -l /u01/app/oracle/oradata/ARCSDBMS/

SQL> ALTER PLUGGABLE DATABASE arcsdbms UNPLUG INTO '/oracle/data/arcsdbms.xml';
SQL> alter session set container=arcsdbms;

SQL> DROP PLUGGABLE DATABASE arcsdbms INCLUDING DATAFILES;

ドロップしてから、再作成すると。
RA-01276: ファイル/u01/app/oracle/oradata/ARCSDBMS/o1_mf_system_jvjo4qhc_.dbfは追加できません。ファイルにOracle Managed Filesのファイル名があります。
作成できない！！なぜ？






$ sqlplus / as sysdba
SQL> show con_name
SQL> alter session set container=PDB$SEED;
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/o1_mf_system_jvjo4qhc_.dbf
/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/o1_mf_sysaux_jvjo4sm5_.dbf
/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/o1_mf_undotbs1_jvjo4tk1_.dbf

SQL> conn / as sysdba
SQL> !mkdir /u01/app/oracle/oradata/ARCSDBMS
SQL> create pluggable database arcsdbms admin user admin identified by casio00
　　　file_name_convert = ('/u01/app/oracle/oradata/ORCL/D317E86834565E66E055BE8FB122BE6E/datafile/',
　　 '/u01/app/oracle/oradata/ARCSDBMS/');
SQL> !ls -l /u01/app/oracle/oradata/ARCSDBMS/


SQL> ALTER PLUGGABLE DATABASE arcsdbms UNPLUG INTO '/oracle/data/arcsdbms.xml';
SQL> DROP PLUGGABLE DATABASE arcsdbms INCLUDING DATAFILES;

ドロップしてから、再作成すると。
RA-01276: ファイル/u01/app/oracle/oradata/ARCSDBMS/o1_mf_system_jvjo4qhc_.dbfは追加できません。ファイルにOracle Managed Filesのファイル名があります。

oracle   hard   nofile   65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock  134217728
oracle   soft   memlock  134217728
oracle   soft   data     unlimited
oracle   hard   data     unlimited

【カーネルパラメータ設定】
# vi /home/oracle/settings.sh
echo =========================変更前=============================
sysctl --system
MEMTOTAL=$(free -b | sed -n '2p' | awk '{print $2}')
SHMMAX=$(expr $MEMTOTAL / 2)
SHMMNI=4096
PAGESIZE=$(getconf PAGE_SIZE)

cat > /etc/sysctl.d/50-oracle.conf << __EOF__
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = $SHMMAX
kernel.shmall = $(expr \( $SHMMAX / $PAGESIZE \) \* \( $SHMMNI / 16 \))
kernel.shmmni = $SHMMNI
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
__EOF__
echo ========================変更後==============================
sysctl --system

# sh settings.sh
=========================変更前===========================
* Applying /usr/lib/sysctl.d/10-default-yama-scope.conf ...
kernel.yama.ptrace_scope = 0
* Applying /usr/lib/sysctl.d/50-coredump.conf ...
kernel.core_pattern = |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h %e
* Applying /usr/lib/sysctl.d/50-default.conf ...
kernel.sysrq = 16
kernel.core_uses_pid = 1
kernel.kptr_restrict = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.promote_secondaries = 1
net.core.default_qdisc = fq_codel
fs.protected_hardlinks = 1
fs.protected_symlinks = 1
* Applying /usr/lib/sysctl.d/50-libkcapi-optmem_max.conf ...
net.core.optmem_max = 81920
* Applying /usr/lib/sysctl.d/50-pid-max.conf ...
kernel.pid_max = 4194304
* Applying /usr/lib/sysctl.d/60-libvirtd.conf ...
fs.aio-max-nr = 1048576
* Applying /etc/sysctl.d/99-sysctl.conf ...
* Applying /etc/sysctl.conf ...
=========================変更後=============================
* Applying /usr/lib/sysctl.d/10-default-yama-scope.conf ...
kernel.yama.ptrace_scope = 0
* Applying /usr/lib/sysctl.d/50-coredump.conf ...
kernel.core_pattern = |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h %e
* Applying /usr/lib/sysctl.d/50-default.conf ...
kernel.sysrq = 16
kernel.core_uses_pid = 1
kernel.kptr_restrict = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.promote_secondaries = 1
net.core.default_qdisc = fq_codel
fs.protected_hardlinks = 1
fs.protected_symlinks = 1
* Applying /usr/lib/sysctl.d/50-libkcapi-optmem_max.conf ...
net.core.optmem_max = 81920
* Applying /etc/sysctl.d/50-oracle.conf ...
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = 16748978176
kernel.shmall = 1046811136
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
kernel.panic_on_oops = 1
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
* Applying /usr/lib/sysctl.d/50-pid-max.conf ...
kernel.pid_max = 4194304
* Applying /usr/lib/sysctl.d/60-libvirtd.conf ...
fs.aio-max-nr = 1048576
* Applying /etc/sysctl.d/99-sysctl.conf ...
* Applying /etc/sysctl.conf ...
==========================================================
【参考】https://www.server-world.info/query?os=CentOS_8&p=oracle19c&f=2

ログアウトして、oracleユーザでログイン仕直す。
$  cd database
$ export CV_ASSUME_DISTID=RHEL8.2
$ export LANG=C
$ ./runInstaller
1) Set up Software Only
2) Single instance database installation
3) Enterprise Edition
4) Oracle base:  /u01/app/oracle
5) Inventory Directory:  /u01/app/oraiventory
     oralinventory Group Name: oinstall
6) dba/oper/backupdba/dgdba/kmdba/racdba
7) Automatically run configuration scripts
    Use "root" user credential   
    Password  xxxxxxx
8) Verification Result Swap Size :  Ignore All











時刻:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)

zabbix7 amazon linux2023 インストール postgres15

【postgres】 dnf -y install postgresql15-server postgresql15-server-devel postgresql-setup initdb passwd postgres vi `find / -name pg_hba.con...

  • LVM ファイルシステムのマウント
    # mount /dev/nvme0n1p3 /mnt/m2 mount: /mnt/m2: 未知のファイルシステムタイプ 'LVM2_member' です. #  fdisk -l /dev/nvme0n1 ディスク /dev/nvme0n1: 953....
  • ZABBIX SNMP Trap 設定
    【snmp/snmptrap】 # yum -y install net-snmp # yum -y install net-snmp-utils # yum -y install snmptt # yum install perl-Sys-Syslog # fi...
  • CentOS8 mariaDBインストール
     ※Status code: 404 for https://dlm.mariadb.com/repo/mariadb-server/10.7....  → Ver.10 サポート切れ！！ # dnf remove MareaDB-client # curl -sS https:...