Zero, environment
Main library: rhel 6.5 oracle 11.2.0.3
ip 192.168.18.66 ora11g
Standby database: centos 7.9 orale 11.2.0.3
ip 192,168.18.77 ora11g_std
2. Main library installation (ignore this step in production environment)
1. Create users and groups
[root@ora11g soft]# groupadd oinstall
[root@ora11g soft]# groupadd dba
[root@ora11g soft]# useradd -g oinstall -G dba oracle
[root@ora11g soft]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
[root@ora11g soft]#
2. Create directory authorization
[root@ora11g soft]# mkdir -p /u01/app/oracle
[root@ora11g soft]# chown -R oracle:oinstall /u01/app
[root@ora11g soft]# chmod -R 775 /u01/app/oracle/
3. Kernel parameters
vi /etc/sysctl.conf add the following content
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
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
[root@ora11g soft]# sysctl -p — takes effect immediately
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
error: “net.bridge.bridge-nf-call-ip6tables” is an unknown key
error: “net.bridge.bridge-nf-call-iptables” is an unknown key
error: “net.bridge.bridge-nf-call-arptables” is an unknown key
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
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
vi /etc/security/limits.conf add the following content
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
4. Modify environment variables
export ORACLE_SID=ora11g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=
O
R
A
C
L
E
H
O
M
E
/
b
i
n
:
ORACLE_HOME/bin:
ORACLEH?OME/bin:ORACLE_HOME/OPatch:$PATH
alias sqlplus=rlwrap sqlplus’
alias rman=rlwrap rman’
export NLS_LANG=“simplified chinese”_china.AL32UTF8
export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS’
export NLS_TIMESTAMP_FORMAT=yyyy-mm-dd HH24:MI:SSXFF’
export NLS_TIMESTAMP_TZ_FORMAT=yyyy-mm-dd HH24:MI:SSXFF TZR’
mkdir -p /u01/app/oracle/product/11.2.0/db_1
5. Install dependency packages
yum configuration
cd /etc/yum.repos.d/
[root@ora11g yum.repos.d]# mv rhel-source.repo rhel-source.repo.bak
[root@ora11g yum.repos.d]# vi base.repo
[base]
name=Red Hat Enterprise Linux
baseurl=file:///mnt
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
[root@ora11g yum.repos.d]# mount /dev/cdrom /mnt/
[root@ora11g yum.repos.d]# yum list | more
Install dependency packages
yum install -y binutils
compat-libstdc + ±33
elfutils-libelf
elfutils-libelf-devel
elfutils-libelf-devel-static
gcc
gcc-c++
glibc
glibc-common
glibc-devel
glibc-headers
kernel-headers
ksh
libaio
libaio-devel
libgcc
libgomp
libstdc++
libstdc+±devel
make
numactl
sysstat
unixODBC
unixODBC-devel
libXext
6. Unzip the database media
[root@ora11g soft]#ll
total 2442056
drwxr-xr-x. 8 root root 4096 Sep 22 2011 database
-rw-r–r–. 1 root root 1358454646 Oct 18 12:18 p10404530_112030_Linux-x86-64_1of7.zip
-rw-r–r–. 1 root root 1142195302 Oct 18 12:18 p10404530_112030_Linux-x86-64_2of7.zip
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip
[root@ora11g soft]# chown oracle:oinstall -R /soft
7. Install database software (silent installation)
Response file:
oracle.install.option=INSTALL_DB_SWONLY //29 lines installation type
ORACLE_HOSTNAME=java-linux-test //37 lines host name (your own corresponding host name, you can use hostname to view) UNIX_GROUP_NAME=oinstall //42 lines installation group INVENTORY_LOCATION=/soft/u01/oraInventory //47 lines INVENTORY directory must be outside the ORACLE_BASE path SELECTED_LANGUAGES=en,zh_CN,zh_TW //78 lines select language ORACLE_HOME=/soft/u01/app/oracle/product/11.2.0/db_1 //83 lines oracle_home ORACLE_BASE=/soft/u01/app/oracle //88 lines oracle_base oracle.install.db.InstallEdition=EE //99 lines oracle version oracle.install.db.isCustomInstall=true //Line 108 Custom installation oracle.install.db.DBA_GROUP=dba //Line 142 dba user group oracle.install.db.OPER_GROUP=oinstall //Line 147 oper user group oracle.install.db.config.starterdb.type=GENERAL_PURPOSE //160 lines database type oracle.install.db.config.starterdb.globalDBName=orcl //Line 165 globalDBName oracle.install.db.config.starterdb.SID=orcl //170 lines SID oracle.install.db.config.starterdb.memoryLimit=512 //Line 192 Minimum memory for automatic memory management (M) (Line 200) oracle.install.db.config.starterdb.password.ALL=oracle //Line 233 sets the same password for all database users DECLINE_SECURITY_UPDATES=true //Line 385 Set security updates
Install DB silently
./runInstaller -silent -force -ignorePrereq -responseFile /soft/database/response/db_install.rsp
Install monitoring silently
[oracle@ora11g database]$ netca -silent -responseFile /soft/database/response/netca.rsp
Silently create an instance
Response file:
GDBNAME="ora11g" //Line 78 Global database name = SID + host domain name SID="ora11g" //Line 149 SID CHARACTERSET="AL32UTF8" //Line 415 database character set encoding, be careful to remove the # sign in front (change to ZHS16GBK for testing) NATIONALCHARACTERSET="UTF8" //425 lines of encoding SYSPASSWORD="oracle"//190 lines, the password can be set by yourself SYSTEMPASSWORE="oracle"//200 lines, the password can be set by yourself
dbca -silent -responseFile /soft/database/response/dbca.rsp
2. Main library configuration
1. Planning log group
rule:
The file size of the standby redo log is the same as the file size of the primary database online redo log.
The number of standby redo log log file groups is calculated according to the following principles:
Standby redo log group number formula >= (number of log groups for each instance + 1)*number of instances
If there is only one node, this node has three groups of redo logs, so the number of Standby redo log groups >= (3 + 1)*1 == 4, so at least 4 groups of Standby redo logs need to be created.
View current log group
SQL> select thread#,group#,bytes/1024/1024 from v$log;
THREAD# GROUP# BYTES/1024/1024
1 1 50
1 2 50
1 3 50
SQL> col member for a50
SQL> select group#,member from v$logfile;
GROUP# MEMBER
3 /u01/app/oracle/oradata/ora11g/redo03.log
2 /u01/app/oracle/oradata/ora11g/redo02.log
1 /u01/app/oracle/oradata/ora11g/redo01.log
Add standby log group
alter database add standby logfile group 4 /u01/app/oracle/oradata/ora11g/redo04.log’ size 50M;
alter database add standby logfile group 5 /u01/app/oracle/oradata/ora11g/redo05.log’ size 50M;
alter database add standby logfile group 6 /u01/app/oracle/oradata/ora11g/redo06.log’ size 50M;
alter database add standby logfile group 7 /u01/app/oracle/oradata/ora11g/redo07.log’ size 50M;
2. Modify parameters
mkdir -p /u01/app/oracle/arch
alter system set log_archive_config=dg_config=(ora11g,ora11g_std)’;
alter system set log_archive_dest_1=location=/u01/app/oracle/arch’;
alter system set log_archive_dest_2=SERVICE=ora11g_std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_std’;
alter system set log_archive_dest_state_1=ENABLE’;
alter system set log_archive_dest_state_2=ENABLE’;
alter system set standby_file_management=auto’;
alter system set undo_management=AUTO’ scope=spfile;
shutdown immediate;
3. Turn on archiving and forced logging
startup mount
alter database archivelog;
alter database force logging;
alter database open;
3. Standby database installation
1. Modify the host name
[root@localhost ~]# hostnamectl set-hostname ora11g_std
2. Modify hosts
[root@ora11g_std ~]# vi /etc/hosts
192.168.18.66 ora11g
192.168.18.77 ora11g_std
3. Create users and groups
[root@ora11g_std ~]# groupadd oinstall
[root@ora11g_std ~]# groupadd dba
[root@ora11g_std ~]# useradd -g oinstall -G dba oracle
[root@ora11g_std ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
4. Create directory authorization
[root@ora11g_std ~]# mkdir -p /u01/app/oracle
[root@ora11g_std ~]# chown -R oracle:oinstall /u01/app
[root@ora11g_std ~]# chmod -R 775 /u01/app/oracle/、
5. Kernel parameters
vi /etc/sysctl.conf add the following content
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
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
vi /etc/security/limits.conf add the following content
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
service iptables stop
setenforce 0
vim /etc/selinux/config set SELINUX=disabled
6. Modify environment variables
export ORACLE_SID=ora11g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=
O
R
A
C
L
E
H
O
M
E
/
b
i
n
:
ORACLE_HOME/bin:
ORACLEH?OME/bin:ORACLE_HOME/OPatch:$PATH
7. Install dependency packages
yum configuration
[root@ora11g_std ~]# cd /etc/yum.repos.d/
[root@ora11g_std yum.repos.d]# mkdir repo_bak
[root@ora11g_std yum.repos.d]# mv ./*.repo repo_bak/
[root@ora11g_std yum.repos.d]# ls
[root@ora11g_std yum.repos.d]# vi local.repo
[local]
name=CentOS7.6-local
baseurl=file:///mnt
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
[root@ora11g_std yum.repos.d]# mount /dev/cdrom /mnt/
mount: /dev/sr0 is write-protected, mounting read-only
[root@ora11g_std yum.repos.d]# yum makecache
Install dependencies:
yum -y install
binutils
compat-libcap1
compat-libstdc + ±33
compat-libstdc + ±33*.i686
elfutils-libelf-devel
gcc
gcc-c++
glibc*.i686
glibc
glibc-devel
glibc-devel*.i686
ksh
libgcc*.i686
libgcc
libstdc++
libstdc + +.i686
libstdc+±devel
libstdc + ±devel.i686
libaio
libaio*.i686
libaio-devel
libaio-devel*.i686
make
sysstat
unixODBC
unixODBC*.i686
unixODBC-devel
unixODBC-devel*.i686
libXp
7. Unzip the database media
[root@ora11g_std ~]# cd /soft/
[root@ora11g_std soft]# ls
p10404530_112030_Linux-x86-64_1of7.zip p10404530_112030_Linux-x86-64_2of7.zip
[root@ora11g_std soft]# unzip p10404530_112030_Linux-x86-64_1of7.zip
[root@ora11g_std soft]# unzip p10404530_112030_Linux-x86-64_2of7.zip
chown oracle:oinstall -R /soft
8. Install database software (silent)
Response file: oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ora11g_std
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false —-Default
oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2. 0.4.0, oracle.rdbms.lbac:11.2.0.4.0, oracle.rdbms.rat:11.2.0.4.0 —-Default
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
DECLINE_SECURITY_UPDATES=true
./runInstaller -silent -force -ignoreSysPrereqs -ignorePrereq -showprogress -responseFile /soft/database/response/db_install.rsp
4. DG deployment
1. Nomount instance of standby database
pri:
create pfile from spfile;
cd $ORACLE_HOME/dbs
scp initora11g.ora ora11g_std:/home/oracle/
std
vi initora11g.ora modify the following parameters:
*.db_unique_name=’ora11g_std
*.log_archive_config=dg_config=(ora11g_std,ora11g)’
*.log_archive_dest_2=SERVICE=ora11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g’
Create a directory
[oracle@ora11g_std ~]$ mkdir -p /u01/app/oracle/arch
[oracle@ora11g_std ~]$ mkdir -p /u01/app/oracle/admin/ora11g/adump
[oracle@ora11g_std oracle]$ mkdir /u01/app/oracle/fast_recovery_area/ora11g
[oracle@ora11g_std oracle]$ mkdir -p /u01/app/oracle/oradata/ora11g
cd $ORACLE_HOME/dbs
cp /home/oracle/initora11g.ora ./
[oracle@ora11g_std ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 18 17:25:07 2023
Copyright ? 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initora11g.ora’;
File created.
SQL> startup nomount
2. Monitoring configuration
pri
cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
vi listener.ora add the following content
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= ora11g_pri)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=ora11g)
)
)
[oracle@ora11g admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 18-October-2023 17:36:48
Copyright ? 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Command execution successful
[oracle@ora11g admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 18-10-2023 17:36:53
Copyright ? 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
LISTENER’s STATUS
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Launch date 18-October-2023 15:46:40
Uptime 0 days 1 hour 50 minutes 17 seconds
Trace level off
Security ON: Local OS Authentication
SNMP OFF
Listener parameter file /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener log file /u01/app/oracle/diag/tnslsnr/ora11g/listener/alert/log.xml
Listening endpoint summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=1521)))
Service Summary…
Service “ora11g” contains 1 instance.
Instance “ora11g”, status READY, contains 1 handler for this service…
Service “ora11gXDB” contains 1 instance.
Instance “ora11g”, status READY, contains 1 handler for this service…
Service “ora11g_pri” contains 1 instance.
Instance “ora11g”, status UNKNOWN, contains 1 handler for this service…
Command execution successful
[oracle@ora11g admin]$ vi tnsnames.ora Add the following content:
ORA11G_PRI=
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.66)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_pri)
)
)
ORA11G_STD=
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_std)
)
)
std:
cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.77)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= ora11g_std)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=ora11g)
)
)
3. Copy the database
orapwd file=orapwora11g password=oracle force=y
Test connection:
[oracle@ora11g_std dbs]$ sqlplus sys/oracle@ora11g_pri as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 18 17:59:17 2023
Copyright ? 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name;
NAME TYPE VALUE
db_file_name_convert string
db_name string ora11g
db_unique_name string ora11g
global_names boolean FALSE
instance_name string ora11g
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ora11g
SQL>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11g_std dbs]$ sqlplus sys/oracle@ora11g_std as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 18 17:59:32 2023
Copyright ? 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
db_file_name_convert string
db_name string ora11g
db_unique_name string ora11g_std
global_names boolean FALSE
instance_name string ora11g
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ora11g_std
SQL>exit
rman target sys/oracle@ora11g_pri auxiliary sys/oracle@ora11g_std
duplicate target database for standby from active database nofilenamecheck;
[oracle@ora11g_std ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 18 18:08:36 2023
Copyright ? 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
MOUNTED
SQL> SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 12
pri:
SQL> create table t1 (id int);
Table has been created.
SQL> insert into t1 values(1);
It has created a row.
SQL> commit;
Submission completed.
SQL> select * from t1;
ID
1
SQL> insert into t1 values(2);
It has created a row.
SQL> commit;
Submission completed.
SQL>
std:
SQL> select * from t1;
ID
1
2
SQL>
5. Switch
pri
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
ORA11G PRIMARY TO STANDBY
std:
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
ORA11G PHYSICAL STANDBY NOT ALLOWED
pri:
SQL> alter database commit to switchover to physical standby with session shutdown;
The database has changed.
SQL>
NAME DATABASE_ROLE SWITCHOVER_STATUS
ORA11G PHYSICAL STANDBY RECOVERY NEEDED
std:
SQL> alter database commit to switchover to primary with session shutdown wait;
Database altered.
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
ORA11G PRIMARY NOT ALLOWED
SQL> alter database open;
Database altered.
SQL> select * from t1;
ID
1
2
SQL>
std (original main library):
SQL> shutdown abort;
ORACLE routine has been closed.
SQL> startup mount
ORACLE routine has been started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
The database is loaded.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
RECOVERY NEEDED
SQL> alter database open;
The database has changed.
SQL> alter database recover managed standby database using current logfile disconnect from session;
The database has changed.
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
ORA11G READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
SQL>