S1

# Skillset 1: (130 min)
##前提
1. **Database Server and Management Server Credentials**
   - User/Password: `oracle/ocm123`
   - Use `sudo` if root access is needed.
   - Oracle documentation URL: `file:///stage/doc/db/index.htm`.
   - Ensure the root directory maintains 15% free space.
   - Use Enterprise Manager Configuration Assistant (EMCA) utility.
2. **Database Credentials**

- **PROD1**: 
     - `sh/sh`, `hr/hr`, `oltp_user/oltp_user1`, `sys/oracle`, `system/oracle`
   
   - **PROD2/PROD3**: 
     - `sys/oracle`, `system/oracle`
   
   - **PROD4**: 
     - `sys/oracle`, `system/oracle`, `rc_admin/RC_ADMIN`
   
   - **EMREP**: 
     - `sys/Oracle123`, `system/Oracle123`, `sysman/Oracle123`

## Tasks

### Section 1: Create a Database（创建数据库）

- Create an 11g R2 database `PROD3` on the Management Server using command line.
- Specifications:
  - Database Name: `PROD3`, Instance Name: `PROD3`, `ORACLE_SID=PROD3`
  - Password for `SYS` and `SYSTEM`: `oracle`
  - Use sample parameter file `initPROD3.ora` from `/home/oracle/scripts`
  - SYSTEM tablespace should be locally managed
  - Use directory `/u01/app/oracle/oradata/PROD3/` for database files

```bash
#修改环境变量
vi .bash_profile
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD3

source  #刷新

#创建pfile
cd $ORACLE_HOME/dbs
cat init.ora | grep -v ^#| grep -v ^$ >initPROD3.ora
 vi initPROD3.ora
db_name='PROD3'
memory_target=120M
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD3/adump'
audit_trail ='db'
db_block_size=8192
db_domain='us.oracle.com'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=PROD3XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
control_files=（/u01/app/oracle/oradata/PROD3/control1.ctl,/u01/app/oracle/oradata/PROD3/control2.ctl ）
compatible ='11.2.0'

#根据pfile创建目录
mkdir -p /u01/app/oracle/admin/PROD3/adump
mkdir -p /u01/app/oracle/oradata/PROD3

#创建密码文件
orapwd file=orapwPROD3 password=oracle entries=30

#重新挂载/dev/shm
sudo mount -o remount,size=4G /dev/shm

#使用创建得pfile生成spfile启动到nomount
sqlplus / as sysdba
create spfile from pfile;
startup nomount

#根据官方文档创建db
vi createdb.sql
CREATE DATABASE lianxi
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/oradata/lianxi/redo01a.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/oradata/lianxi/redo02a.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/oradata/lianxi/redo03a.log') SIZE 100M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3 
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/oradata/lianxi/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/oradata/lianxi/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/oradata/lianxi/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/oradata/lianxi/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/oradata/lianxi/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      DATAFILE '/oradata/lianxi/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

#数据库中执行建库脚本：
 @createdb.sql

#执行官方文档得脚本
vi 1.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

@1.sql
```

### Section 2: Create and Configure a Tablespace（创建和管理表空间）

- **2.1**: Create a temporary tablespace group `TEMP_GRP` on `PROD1`:
  - Contains tablespaces `TEMP1` and `TEMP2`
  - Make `TEMP_GRP` default for all new users
 
```sql
--切换SID 
export ORACLE_SID=PROD1
create temporaary tablespace temp01 tempfile '/u01/app/oracle/oradata/PROD1/temp01.dbf' tablespace group TEMP_GRP;
create temporaary tablespace temp02 tempfile '/u01/app/oracle/oradata/PROD1/temp02.dbf' tablespace group TEMP_GRP;
alter database default temporary tablespace temp_grp;
```

- **2.2**: Create permanent tablespace `TEST` on `PROD1`:
  - Initial size: 400 MB, Max size: 4 TB
  - Initial extent size: 1 MB, Next extent size: 1 MB

```sql
export ORACLE_SID=PROD1

create bigfile tablespace test
datafile '/u01/app/oracle/oradata/PROD1/test.dbf' size 400M
autoextend on maxsize 4T
extent management local uniform size 1M;

```

- **2.3**: Create permanent tablespace `INDX` on `PROD1`:
  - File size: 40 MB
```
create tablespace index 
datafile '/u01/app/oracle/oradata/PROD1/index.dbf' size 40M;
```

- **2.4**: Create permanent tablespace `TOOLS` on `PROD1`:
  - File size: 10 MB
```
create tanblespace tools 
datafile '/u01/app/oracle/oradata/PROD1/tools.dbf' size 10M;
```

- **2.5**: Create permanent tablespace `OLTP` on `PROD1`:
  - File size: 48 MB
  - Initial extent size: 2 MB, Next extent size: 2 MB
```
CREATE TABLESPACE OLTP
DATAFILE '/u01/oradata/OLTP/oltp01.dbf' SIZE 48M 
AUTOEXTEND ON NEXT 2M MAXSIZE 2048M 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 2M 
SEGMENT SPACE MANAGEMENT AUTO;
```

### Section 3: Set Up Undo Management

- Set up automatic undo management on `PROD1`:
  - Prevent ORA-01555 error for 90-min queries
  - Support ~120 OLTP users during business hours
  - Support ~12-15 batch processes during evenings/weekends

```
--修改undo过期时间
show parameter undo 
alter system set undo_retention=5400;

desc dba_data_files;
select tablespace_name,file_id,AUTOEXTENSIBLE from dba_data_files where tablespace_name='UNDOTBS1';
--如果不是自动扩展（YES） 修改为自动扩展
alter database datafile 3 autoextend on;

--修改并发参数
alter system set process=120 scope=spfile;

--为运行批量脚本设置参数
show parameter job
alter system set job_queue_processes=15;

--重启数据库
shutdown immediate
startup
```

### Section 4: Server-Side and Client-Side Network Configuration(网络配置)
- **Server-Side**:
  - Create default listener
```
cd $ORACLE_HOME/network/admin
vi sqlnet.ora
NAMES.DIRECTORY_PATH=(tnsnames,ezconnect)
```
  - The TCP/IP protocol will be used for all connections,Use the machine name (not the IP address) for host

```
--查看服务名、实例名

--（1）listener中的 GLOBAL_DBNAME=PROD1.us.oracle.com与 show parameter name 查看 service_names名称一致。
--（2）listener中的 SID_NAME=PROD1与 show parameter name 查看 instance_name 名称一致，区分大小写。

show parameter name
vi listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1521))
))

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD1.us.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD1))
(SID_DESC=
(GLOBAL_DBNAME=PROD2.us.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD2)
))

LSNR2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1526))
))

```
    - Add second listener `LSNR2` on port 1526
```    
LSNR2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1526))
)
alter system set local_listener='lsnr2';

```
  - Register `PROD1` with `LSNR2`
```
alter system register;
```
  - Start both listeners
```
lsnrctl 
start
reload
```
- **Client-Side**:
  - `PROD1` alias: Default listener, dedicated connection
  - `PROD_S` alias: `LSNR2`, shared connection
  - `PROD2`, `PROD3`, `PROD4`, `EMREP` aliases: Default listener, dedicated connection
  - `RACDB` alias: Connect to `RACDB` service on RAC Cluster
```
PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1.us.oracle.com)
))
PROD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD2.us.oracle.com)
))
PROD3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD3.us.oracle.com)
))
PROD4 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD4.us.oracle.com)
))
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EMREP.us.oracle.com)
))
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
))
PROD_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1526))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = PROD1.us.oracle.com)
))
```
### Section 5: Configure the Shared Server

- Configure `PROD1` for 300 sessions, 100 dedicated
- Support 3 TCP dispatchers, max 10
- Support 10-30 shared server processes

```
alter system set shared_server_sessions=200;
alter system set sessions=300 scope=spfile;
alter system set dispatchers='(PROTOCOL=TCP)(DIS=3)';
alter system set max_dispatchers=10;
alter system set shared_servers=10;
alter system set max_shared_servers=30;

shutdown immediate
startup
```
### Section 6: Configure the Database

- Adjust `PROD1` parameter file for optimal environment
- Set directories for PL/SQL I/O: `/home/oracle`, `/home/oracle/temp`, `/home/oracle/scripts`

```sql
alter system set utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;

shutdown immediate
startup

show parameter utl_file_dir
```
### Section 7: Collect Schema Statistics

- Collect statistics for `HR` schema in `PROD1` for cost-based optimization

```
exec dbms_stats.gather_schema_stats('HR')
```

### Section 8: Database Backup and Availability

- Triplex control file on `PROD1`
```
--查看控制文件
select name from v$controlfile;

--修改参数
alter system set control_files=
'/u01/app/oracle/oradata/PROD1/control01.ctl',
'/u01/app/oracle/fast_recovery_area/control02.ctl',
'/u01/app/oracle/oradata/PROD1/control03.ctl' scope=spfile;

shutdown immediate

host cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control03.ctl

--重启后验证
select name from v$controlfile;
```
- Backup `PROD1` and `PROD2` for complete recovery
```
--PROD1、PROD2同样操作
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

shutdown immediate

cd /u01/app/oracle/oradata/PROD1/
cp $ORACLE_HOME/dbs/spfilePROD1.ora ./
tar -czvf /home/oracle/files/cold_prod1.tar.gz
```

### Section 9: Configure the Parameter File

- Store critical error events in `ORACLE_HOME` for `PROD1`（配置诊断日志目录）

```
alter system set diagnostic_dest='/u01/app/oracle/product/11.2.0/dbhome_1';
```

### Section 10: Improve Access to Network File Directories

- Use mounted NFS `/u02/oradata/prod1` for `PROD1`
```
---最好在冷备库之后，PROD1 库处于关闭状态时,先做 NFS这道题
df -h

vi /etc/oranfstab
server:server
path:edbjr2p2.example.com
local:edbjr2p1.example.com
export:/vol/oradata mount:/u02/oradata/prod1

cd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib
make -f ins_rdbms.mk dnfs_on

make -f ins_rdbms.mk dnfs_on

--启动数据库
startup
```
- Create tablespace `SHARED_DATA`, 10 MB size, on NFS
```
create tablespace shared_data datafile '/u02/oradata/prod1/shared_data01.dbf' size 10M;

--验证
select * from v$dnfs_files;
select * from v$dnfs_servers;
```
### Section 11: Apply a Patch（PROD4在线打补丁）

- Apply patch `20871488` to `PROD4` using `p12834800_112030_LINUX.zip` from `/home/oracle/scripts`

```
cd
unzip p12834800_112030_LINUX.zip
cd 12834800
/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch lsinventory

cat README.txt

/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch apply online -connectString PROD4:sys:oracle:

/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch lsinventory

```
### Section 12: Create an ASM Instance and Configure Grid Infrastructure

- Start grid infrastructure and register `PROD4`, `EMREP` in OLR
```
--练习中安装
vi gi
/u01/app/11.2.0/grid/perl/bin/perl -I /u01/app/11.2.0/grid/perl/lib -I
/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

sudo chmod 777 gi
sudo ./gi

--考试中不用安装 直接启动
./crsctl stat res -t
cd /u01/app/11.2.0/grid/bin/
./crsctl start has
./crsctl stat res -t
```
- Register ASM and disk groups, and listener with grid
```
cd /u01/app/11.2.0/grid/bin
lsnrctl stop
./srvctl add listener -o /u01/app/oracle/product/11.2.0/db_1
./srvctl add database -d PROD4 -o /u01/app/oracle/product/11.2.0/db_1
./srvctl add database -d EMREP -o /u01/app/oracle/product/11.2.0/db_1
./srvctl add asm

./srvctl start database -d PROD4 -o open
./srvctl start database -d EMREP -o open
./srvctl start asm

--验证
 ./crs_stat -t
 lsnrctl status

```
- Create ASM instance with `DATA` (normal redundancy) and `FRA` (external redundancy) using specified ASM disks(创建磁盘组)
```
/u01/app/11.2.0/grid/bin
./asmca

--DATA磁盘组创建
--点击 Change Disk Discovery Path
--目录 /dev/oracleasm/disks
-磁盘组名 DATA
--冗余选择normal
--磁盘1234
--ok创建

--FRA磁盘组创建
--点击 Change Disk Discovery Path
--目录 /dev/oracleasm/disks
-磁盘组名 FAR
--冗余选择External
--磁盘5678
--ok创建
```

### 安装EM
```
alter user dbsnmp identified by oracle account unlock;
cd $ORACLE_HOME/bin
./emca -config dbcontrol db -repos recreate

--网页登陆
https://edbjr2p1.example.com:1158/em

点击 add an exception  → add exception →点击 get certificate
最后 confirm security exception 完成

命令
cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/
./emca

--状态修改、查询
./emctl stop/start/status dbconsole
```

DBLOG

导航1234

最近发表

友情链接