unix_12c_db_init - 润新知

unix_12c_db_init

sample 1: add a new cdb

1.for the new db Aprod

please apply two new direcotry in Cdb3/Cdb4
/Aprod 50G
/Aprodlog 20G

Aproddb 目前挂载在Cdb4，相关信息如下：

文件系统：
/dev/vg_Aproddb/Aproddb                   52101120   79414 48770357    0% /Aproddb
/dev/vg_Aproddblog/Aproddblog                   20840448   71752 19470660    0% /Aproddblog

VIP： 192.168.4.124

4.

##在listener.ora 文件找到正确的listener.ora 如listener1
###dbca -slient LISTENERS listener1

export ORACLE_SID=Aprod
export pdb_name=pBprod
export file_dest=/Aproddb/data
dbca -silent -createDatabase -templateName New_Database.dbt -gdbname $ORACLE_SID -sid $ORACLE_SID
-SysPassword oracle123 -systemPassword oracle123 -createAsContainerDatabase true -numberofPDBs 1
-pdbName $pdb_name -pdbAdminPassword oracle123 -datafileDestination $file_dest -responseFile NO_VALUE
-characterset AL32UTF8 -memoryPercentage 10 -emConfiguration none -storageType FS -listener listener1

5.

UAT:
/uniuatdb/data/system01.dbf
/uniuatdb/data/pdbseed/system01.dbf
/uniuatdb/data/sysaux01.dbf
/uniuatdb/data/pdbseed/sysaux01.dbf
/uniuatdb/data/undotbs01.dbf
/uniuatdb/data/users01.dbf
/uniuatdb/data/pBuat/system01.dbf
/uniuatdb/data/pBuat/sysaux01.dbf

SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;

CDB

SYSAUX                                  2048          1247   60.89%         801
       732

SYSTEM                                  1024        799.44   78.07%      224.56
       224

UNDOTBS1                                1024         27.37    2.67%      996.63
       970

USERS                                    128          1.37    1.07%      126.63
    126.63

SQL> conn system/oracle123@pBuat
Connected.
SQL> /

----------
B_DATA                               2048          1.44    0.07%     2046.56
   2045.69

B_IDX                                1024             1    0.10%        1023
      1023

SYSAUX                                634.41        597.78   94.23%       36.63
        25
SYSTEM                                 307.2        266.26   86.67%       40.94
     40.19

6.
ORA_ORAENV=/usr/local/bin/ora_oraenv

SIDs on this machine are;
   xprprod
   pcc
   corpprod
   bizprod
   oasprod
   bpsprod
   tgpsprod
   wrlprod
   icomprod

--
ADD /ETC/ORATAB

7. parmeter
-totalMemory 2048
-sampleSchema true
-initparams audit_file_dest='/u01/app/oracle/admin/UXOCDB/adump'
-initparams compatible='12.1.0'
-initparams db_create_file_dest='+DATA'
-initparams db_create_online_log_dest_1='+DATA'
-initparams db_create_online_log_dest_2='+FRA'
-initparams db_recovery_file_dest='+FRA'
-initparams diagnostic_dest='/u01/app/oracle'
-initparams parallel_max_servers=8
-initparams processes=400

8.
cd /Aproddb/
mkdir pfile

cd /opt/oracle12c/product/12.1/dbs

sqlplus / as sysdba
create pfile='/opt/oracle12c/product/12.1/dbs/initAprod.ora' from spfile;

mv *Aprod* /Aproddb/pfile

--in db3/db4,sync /etc/listener.ora

ln -s /Aproddb/pfile/initAprod.ora   initAprod.ora
ln -s /Aproddb/pfile/orapwAprod      orapwAprod

SQL> shutdown immediate
SQL> startup

10.

192.168.4.124   pBprod
192.168.4.124   pAprod

--in db3/db4. sync /etc/listener.ora and /etc/tnsnames.ora local_listener

pBprod =
(ADDRESS_LIST =
        (ADDRESS = (PROTOCOL=IPC)(KEY= REGISTER_pBprod))
        (ADDRESS = (PROTOCOL = TCP)(HOST = pBprod)(PORT = 15021))
        (ADDRESS = (PROTOCOL = TCPS)(HOST = pBprod)(PORT = 15022))
)

SID_LIST_pBprod=
(SID_LIST =
    (SID_DESC =
      (SID_NAME=Aprod)
      (ORACLE_HOME=/opt/oracle12c/product/12.1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pBprod)
      (SID_NAME = Aprod)
      (ORACLE_HOME=/opt/oracle12c/product/12.1)
    )
)

SECURE_REGISTER_pBprod = (IPC)
SECURE_CONTROL_pBprod =(TCPS,IPC)
ADMIN_RESTRICTIONS_pBprod = ON
DIAG_ADR_ENABLED_pBprod = OFF

pAprod =
(ADDRESS_LIST =
        (ADDRESS = (PROTOCOL=IPC)(KEY= REGISTER_pAprod))
        (ADDRESS = (PROTOCOL = TCP)(HOST = pAprod)(PORT = 15021))
        (ADDRESS = (PROTOCOL = TCPS)(HOST = pAprod)(PORT = 15022))
)

SID_LIST_pAprod=
(SID_LIST =
    (SID_DESC =
      (SID_NAME=Aprod)
      (ORACLE_HOME=/opt/oracle12c/product/12.1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pAprod)
      (SID_NAME = Aprod)
      (ORACLE_HOME=/opt/oracle12c/product/12.1)
    )
)

SECURE_REGISTER_pAprod = (IPC)
SECURE_CONTROL_pAprod =(TCPS,IPC)
ADMIN_RESTRICTIONS_pAprod = ON
DIAG_ADR_ENABLED_pAprod = OFF

sqlplus system/oracle123@//192.168.4.124:15021/Aprod
sqlplus system/oracle123@//192.168.4.124:15021/pBprod
sqlplus system/oracle123@//192.168.4.125:15021/pAprod

sqlplus system/oracle123@//192.168.4.124:15021/Aprod

create pluggable database pAprod from pBprod
FILE_NAME_CONVERT=('/Aproddb/data/Aprod/pBprod','/Aproddb/data/Aprod/pAprod');

11.

DB scripts: /etc/cmcluster/Aproddb/ora12c.sh，请修改脚本内容。

--------------
ORA_ver=12.1.0.2
SID_NAME=Aprod
ORACLE_HOME=/opt/oracle12c/product/12.1
LISTENER=yes
--modify LISTENER_NAME to LISTENER_PDB*_NAME
LISTENER_PDB1_NAME=pBprod
LISTENER_PDB2_NAME=pAprod
--
LISTENER_PASS=
MONITOR_INTERVAL=30
PACKAGE_NAME=Aprod
TIME_OUT=30
set -A MONITOR_PROCESSES ora_pmon_${SID_NAME} ora_dbw0_${SID_NAME} ora_ckpt_${SID_NAME} ora_smon_${SID_NAME} ora_lgwr_${SID_NAME} ora_reco_${SID_NAME}

HOST=`hostname`
DATE=`date`
PATH=${ORACLE_HOME}/bin:/sbin:/usr/bin:/usr/sbin:/etc:/bin
export ORACLE_SID=${SID_NAME}
export ORACLE_HOME

----modify LISTENER_NAME to LISTENER_PDB*_NAME

su oracle -c "${ORACLE_HOME}/bin/lsnrctl start ${LISTENER_PDB1_NAME}"
    if [[ $? != 0 ]]
    then
        print "Oracle lsnrctl start failed."
    else
        print "Oracle lsnrctl start done."
    fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl start ${LISTENER_PDB1_NAME}"
    if [[ $? != 0 ]]
    then
    print "Oracle lsnrctl start failed."
    else
    print "Oracle lsnrctl start done."
   fi

----modify LISTENER_NAME to LISTENER_PDB*_NAME

su oracle -c "${ORACLE_HOME}/bin/lsnrctl stop ${LISTENER_PDB1_NAME}"
    if [[ $? != 0 ]]
    then
        print "Oracle lsnrctl start failed."
    else
        print "Oracle lsnrctl start done."
    fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl stop ${LISTENER_PDB1_NAME}"
    if [[ $? != 0 ]]
    then
    print "Oracle lsnrctl start failed."
    else
    print "Oracle lsnrctl start done."
   fi

----modify LISTENER_NAME to LISTENER_PDB*_NAME
su oracle -c ${ORACLE_HOME}/bin/lsnrctl <<EOF
set password ${LISTENER_PASS}
stop ${LISTENER_PDB1_NAME}
exit
EOF

su oracle -c ${ORACLE_HOME}/bin/lsnrctl <<EOF1
set password ${LISTENER_PASS}
stop ${LISTENER_PDB2_NAME}
exit
EOF1

备份脚本信息：
/macro/dbbackup_total.sh -- > /macro/Aprod_dbbackup.sh  DB scripts /home/oracle/utility/backup_rman/rman_backup.sh

no need to change


13:
env

/Aproddblog
/Aproddblog/dump
/Aproddb/data/Aprod
pBprod

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION null;

done

12
SQL> show parameter diag

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
diagnostic_dest                      string
/opt/oracle12c/

--change    diag /opt/oracle12c/ to /Aproddb
--chanege db_recovery_file_dest to none

--chanege audit_file_dest   to /Aproddb/adump

audit_file_dest                      string
/tgpsproddb/adump

core_dump_dest                       string
/tgpsproddb/diag/rdbms/tgpspro
d/tgpsprod/cdump

--chanege audit_file_dest   to /Aproddb/adump

--change log_archive_dest_1 to LOCATION=/Aproddblog

--change log_archive_format to Aprod_%r_%t_%s.arc

--in db3/db4 sync /etc/tnsnames.ora local_listener

LISTENER_APROD =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.110)(PORT = 15035))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.124)(PORT = 15021))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.125)(PORT = 15021))
)

14. local
--clean tnsnames.ora
0 17 1,10,23 * * /home/oracle/utility/macro/tns_log_back_12c.sh pBprod.log >>/home/oracle/utility/log/tns_log_back_12c.log 2>&1
0 17 1,10,23 * * /home/oracle/utility/macro/tns_log_back_12c.sh pAprod.log >>/home/oracle/utility/log/tns_log_back_12c.log 2>&1

check:
ls -ltr /opt/oracle12c/product/12.1/network/log/*<name>*

--clean achive log
vi /home/oracle/utility/macro/call_autopurge_arch.sh
${SCRIPT_DIR}/autopurge_archlog.sh Aprod /Aproddb 24

check:
ls -tlr /Aproddblog/*.gz

--compress archive log
/home/oracle/utility/macro/compress_all.sh
$PRG_NAME Aprod /Aproddblog 10

check:
ls -tlr /Aproddblog/*.gz

--clean trace file_dest and clean adump
/home/oracle/utility/macro/pfdblist

Aprod /Aproddb/diag/rdbms/Aprod/Aprod/trace .trc 4
Aprod /Aproddb/diag/rdbms/Aprod/Aprod/trace .trm 4
Aprod /Aproddb/adump .aud 2

---check alert.log

no need change ,only add rows /etc/oratab

----detect_blocker.sh
no need change
0,5,10,15,20,25,30,35,40,45,50,55 0-23 * * * /home/oracle/utility/blocker/detect_blocker.sh bizprod 120 > /home/oracle/utility/trace/detect_blocker_bizprod.log 2>&1

15.

---for prod for 12c

加入监控体系

10,25,40,55 * * * *   /home/oracle/utility/macro/chk_conn_12c.sh 15     >> /database/log/mon_chk_conn_12c.log 2>&1

-EDIT line 26

check:
sqlplus c##oper/oper123@tnsname

35 8 * * * /home/oracle/monitor/segment_size/all.sh > /database/log/segment_size/tbs_unix_all.log 2>&1

-EDIT line 15 and 35

cd /database/log/segment_size
mkdir log_Aprod

check:

17,

双机切换：

16.

-listeners

PAPROD=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.125)(PORT = 15021))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pAprod)
    )
)

LISTENER_APROD =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.110)(PORT = 15035))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.124)(PORT = 15021))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.125)(PORT = 15021))
)

pBprod =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.124)(PORT = 15021))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pBprod)
    )
)

sample2:

sample 2 : add pdb to a exist cdb

1.
clone db

cd /centrproddb/data/centrprod

mkdir pdbprod

ls -l

---CREATE PLUGGABLE DATABASE pdbprod FROM PDB$SEED;
--sampe
--

create pluggable database pdbprod admin user pdb_admin
identified by pdbadm_123 roles=(CONNECT)
file_name_convert=('/centrproddb/data/centrprod/pdbseed','/centrproddb/data/centrprod/pdbprod')
/

select con_id, NAME, OPEN_MODE,DBID, CON_UID from V$PDBS;

alter pluggable database pdbprod close;

alter pluggable database pdbprod open read write;

SQL> select con_name, instance_name,state,restricted from dba_pdb_saved_states;

SQL> alter pluggable database all save state;

lsnrctl status

2.config listener add service pdbprod

edit /etc/hosts
new_vip pdbprod

edit /etc/listener.ora
/etc/tnsnames.ora

sync to another node

3.
DB scripts: /etc/cmcluster/Aproddb/ora12c.sh，请修改脚本内容。

--------------
ORA_ver=12.1.0.2
SID_NAME=Aprod
ORACLE_HOME=/opt/oracle12c/product/12.1
LISTENER=yes
--modify LISTENER_NAME to LISTENER_PDB*_NAME
LISTENER_PDB1_NAME=pBprod
LISTENER_PDB2_NAME=pAprod
LISTENER_PDB3_NAME=pdbprod

--
LISTENER_PASS=
MONITOR_INTERVAL=30
PACKAGE_NAME=Aprod
TIME_OUT=30
set -A MONITOR_PROCESSES ora_pmon_${SID_NAME} ora_dbw0_${SID_NAME} ora_ckpt_${SID_NAME} ora_smon_${SID_NAME} ora_lgwr_${SID_NAME} ora_reco_${SID_NAME}

HOST=`hostname`
DATE=`date`
PATH=${ORACLE_HOME}/bin:/sbin:/usr/bin:/usr/sbin:/etc:/bin
export ORACLE_SID=${SID_NAME}
export ORACLE_HOME

----modify LISTENER_NAME to LISTENER_PDB*_NAME

su oracle -c "${ORACLE_HOME}/bin/lsnrctl start ${LISTENER_PDB1_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl start failed."
else
print "Oracle lsnrctl start done."
fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl start ${LISTENER_PDB2_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl start failed."
else
print "Oracle lsnrctl start done."
fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl start ${LISTENER_PDB3_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl start failed."
else
print "Oracle lsnrctl start done."
fi

----modify LISTENER_NAME to LISTENER_PDB*_NAME

su oracle -c "${ORACLE_HOME}/bin/lsnrctl stop ${LISTENER_PDB1_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl start failed."
else
print "Oracle lsnrctl start done."
fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl stop ${LISTENER_PDB1_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl start failed."
else
print "Oracle lsnrctl start done."
fi
su oracle -c "${ORACLE_HOME}/bin/lsnrctl stop ${LISTENER_PDB3_NAME}"
if [[ $? != 0 ]]
then
print "Oracle lsnrctl stop failed."
else
print "Oracle lsnrctl stop done."
fi

----modify LISTENER_NAME to LISTENER_PDB*_NAME
su oracle -c ${ORACLE_HOME}/bin/lsnrctl <<EOF
set password ${LISTENER_PASS}
stop ${LISTENER_PDB1_NAME}
exit
EOF

su oracle -c ${ORACLE_HOME}/bin/lsnrctl <<EOF1
set password ${LISTENER_PASS}
stop ${LISTENER_PDB2_NAME}
exit
EOF1

su oracle -c ${ORACLE_HOME}/bin/lsnrctl <<EOF2
set password ${LISTENER_PASS}
stop ${LISTENER_PDB3_NAME}
exit
EOF2

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PENRSPROD READ WRITE NO
4 PIPAMPROD READ WRITE NO

3. config user and tablespace

done

4.db server crontab -e
add listern keep and sync to another node
0 12 1,10,20 * * /home/oracle/utility/macro/tns_log_back_12c.sh pprod.log >>/home/oracle/utility/log/tns_log_back_12c.log 2>&1

5.
---for prod for 12c

加入监控体系

10,25,40,55 * * * * /home/oracle/utility/macro/chk_conn_12c.sh 15 >> /database/log/mon_chk_conn_12c.log 2>&1

-EDIT line 26

check:
sqlplus c##oper/oper123@tnsname

35 8 * * * /home/oracle/monitor/segment_size/all.sh > /database/log/segment_size/tbs_unix_all.log 2>&1

-EDIT line 15 and 35

cd /database/log/segment_size
mkdir log_Aprod

check:

PS:

refer http://blog.sina.com.cn/s/blog_5d6df58d0101sotr.html

unix 区分大小写以及 and /

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/system01.dbf
/centrproddb/data/centrprod/pdbseed/system01.dbf
/centrproddb/data/centrprod/sysaux01.dbf
/centrproddb/data/centrprod/pdbseed/sysaux01.dbf
/centrproddb/data/centrprod/undotbs01.dbf
/centrproddb/data/centrprod/users01.dbf
/centrproddb/data/centrprod/penrsprod/system01.dbf
/centrproddb/data/centrprod/penrsprod/sysaux01.dbf
/centrproddb/data/centrprod/penrsprod/penrsprod_users01.dbf
/centrproddb/data/centrprod/pipamprod/system01.dbf
/centrproddb/data/centrprod/pipamprod/sysaux01.dbf

NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/penrsprod/enrs_data_f01.dbf
/centrproddb/data/centrprod/penrsprod/enrs_idx_f01.dbf
/centrproddb/data/centrprod/pipamprod/ipam_data_f01.dbf
/centrproddb/data/centrprod/pipamprod/ipam_idx_f01.dbf
/centrproddb/data/centrprod/pipamprod/test.dbf

16 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/centrproddb/data/centrprod/system01.dbf
/centrproddb/data/centrprod/sysaux01.dbf
/centrproddb/data/centrprod/undotbs01.dbf
/centrproddb/data/centrprod/users01.d
相关阅读:
selenium操作浏览器-窗口切换
 selenium操作浏览器
 selenium+java+chrome环境搭建
 python-s and s.strip()
java-趣味算法
 基础正则表达式介绍与练习
 python网络爬虫，知识储备，简单爬虫的必知必会，【核心】
django模型——数据库(二)
模型——数据库(一)
django的模板（二）
原文地址：https://www.cnblogs.com/feiyun8616/p/6656495.html