S3

## 数据库信息

- Datafiles can autoextend but can not more than 2G.  
  数据文件支持自动扩展，但不能超过 2G。
  
- Database Name User/Password:  
  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

### **任务 1：创建数据库链接（Create a Database Link）**

1. PROD2 is corrupted (Implicit subject).  
   PROD2 数据库已损坏（隐含主题）。

- Redo log is deleted.  
     重做日志被删除。

- The datafile in the tablespace of users is deleted.  
     用户表空间中的数据文件被删除。

```sql
--PROD2模拟故障
cd /u01/app/oracle/oradata/PROD2
mkdir bak
mv redo* bak
mv user01.dbf bak

--PROD2恢复
export ORACLE_SID=PROD2
rman target / catalog rc_admin/RC_ADMIN@prod4
--查看故障
list failure;
--生成故障建议
advise failure;
--预览恢复脚本
repair failure preview;
--执行恢复脚本
repair failure;
--结尾出现 alter database open resetlogs;成功

--恢复后再次备份
backup as compressed backupset format '/home/oracle/files/full_prod2.%U' database plus archivelog;

```
1.1. Create a database link called PROD_LINK to permit all users in PROD2 database to access the database. All PROD2 database users should be able to connect to PROD1 using the link and connect as user SYSTEM on the PROD1 database.  
创建一个名为 PROD_LINK 的数据库链接，允许 PROD2 数据库中的所有用户访问数据库。所有 PROD2 数据库用户应能通过该链接连接到 PROD1 数据库，并以 PROD1 数据库中的 SYSTEM 用户身份连接。
```sql
create public database link prod_link connect to system identified by oracle using 'prod1';

--验证
 select owner,db_link,username from dba_db_links;
```

### **任务 3：优化星型查询（Optimizing Star Queries）**

3.1. Configure a star transformation query in the SH schema of the PROD1 database.  
在 PROD1 数据库中配置 SH 模式的星型转换查询。

Examine the `/home/oracle/scripts/star.sql` script. Make the required changes to ensure that the query executes a star transformation query.  
检查 `/home/oracle/scripts/star.sql` 脚本，并进行必要修改以确保查询执行为星型转换查询。
```sql
-- star.sql 脚本用于执行星型查询转换的设置和验证

-- 1. 声明表结构
-- 共有四张表：sales、times、customers、channels
-- sales 表是事实表，与其他三张表的关联列为 time_id, cust_id, channel_id

-- 2. 查询 SALES 表的外键约束
-- 获取 SALES 表中与其他表的外键约束
select constraint_name  from user_constraints  where table_name = 'SALES'  and constraint_type = 'R';

-- 3. 查询 SALES 表的外键列信息
-- 获取 SALES 表中与外键相关的列名
select constraint_name, table_name, column_name  from user_cons_columns  where table_name = 'SALES'  and column_name in ('TIME_ID','CUST_ID','CHANNEL_ID');

-- 4. 启用星型转换功能
-- 通过设置参数启用星型转换
alter system set star_transformation_enabled=true;

-- 5. 显示当前星型转换设置状态
show parameter star_transformation_enabled;

-- 6. 创建事实表 SALES 关联列的本地位图索引
-- 为 sales 表的关联列创建本地位图索引以优化查询性能
create bitmap index sales_times_indx on sh.sales(time_id) local;
create bitmap index sales_channel_indx on sh.sales(channel_id) local;
create bitmap index sales_cust_indx on sh.sales(cust_id) local;

-- 7. 验证是否使用星型查询转换
-- 通过设置 autotrace 以获取查询执行计划，若看到 "star transformation used" 表示成功
set autotrace trace exp;
-- 执行星型查询脚本
@/home/oracle/scripts/star.sql;
-- 关闭 autotrace
set autotrace off;

-- 8. 以 SYSDBA 身份连接数据库
conn / as sysdba;

```

### **任务 4：创建插件表空间（Create a Plug-in Tablespace）**

Create a plug-in tablespace, TRANS_TBS, by using the transportable tablespace feature in the database.  
使用可传输表空间功能创建一个名为 TRANS_TBS 的插件表空间。

4.1. The data file to be transported is created on a big endian platform, that is, Solaris 32-bit.  
需要传输的数据文件是在大端平台（Solaris 32 位）上创建的。

```
-- 设置查询结果中 PLATFORM_NAME 列的格式为A36，表示该列的宽度为36个字符
COLUMN PLATFORM_NAME FORMAT A36

-- 1. 查询可传输的平台信息
-- 从 V$TRANSPORTABLE_PLATFORM 视图中选择所有列，并按 PLATFORM_NAME 列进行排序
SELECT *  FROM V$TRANSPORTABLE_PLATFORM  ORDER BY PLATFORM_NAME;

-- 2. 查询当前数据库平台信息和字节序格式
-- 从 V$TRANSPORTABLE_PLATFORM 视图和 V$DATABASE 视图中选择平台名称和字节序格式
-- 通过平台名称进行联接，以获取当前数据库的平台信息
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

-- 3. 查询当前数据库的平台名称
-- 从 V$DATABASE 视图中选择当前数据库的平台名称
SELECT platform_name  FROM v$database;

```
4.2. Use the `/home/oracle/scripts/transport_tbs01.dbf` data file and the `EXPDAT_GENERATETTS000` dump file located in the `/home/oracle/scripts` directory.  
使用 `/home/oracle/scripts` 目录下的 `transport_tbs01.dbf` 数据文件和 `EXPDAT_GENERATETTS000` 转储文件。
```
-- 设置 Oracle 实例的 SID（系统标识符）
export ORACLE_SID=PROD1

-- 连接到 RMAN (Recovery Manager)，用于管理和备份数据库
rman target /

-- 使用 RMAN 的 CONVERT 命令将数据文件从一个平台转换到另一个平台
CONVERT DATAFILE '/home/oracle/scripts/transport_tbs01.dbf'

-- 指定目标平台为 Linux IA (32-bit)
TO PLATFORM="Linux IA (32-bit)"

-- 指定源平台为 Solaris (32-bit)
FROM PLATFORM="Solaris[tm] OE (32-bit)"

-- 指定文件转换时的路径映射
-- DB_FILE_NAME_CONVERT 用于定义原始文件路径和目标文件路径的映射
DB_FILE_NAME_CONVERT='/home/oracle/scripts/','/u01/app/oracle/oradata/PROD1/';

```
4.3. The exported user is TRANS_USER. The tablespace objects should belong to the TRANS_USER user.  
导出的用户为 TRANS_USER，表空间内的对象应属于 TRANS_USER 用户。

1.考试环境使用 Solaris[tm] OE (64-bit),用户名称、表空间名称注意审题。
2.命令行方式 impdp导入完成，不要忘记 read write表空间，否则影响场景七 DG备
```
-- 创建一个目录对象，指向文件系统路径
CREATE DIRECTORY dir AS '/home/oracle/scripts';

-- 授予公共用户对该目录的读和写权限
GRANT READ, WRITE ON DIRECTORY dir TO PUBLIC;

-- 创建用户 trans_user 并授予 connect 和 resource 权限，同时设置密码
GRANT CONNECT, RESOURCE TO trans_user IDENTIFIED BY oracle;

-- 设置 Oracle 环境变量，以指定要连接的数据库实例
EXPORT ORACLE_SID=PROD1

-- 使用 Data Pump 导入工具 impdp，导入指定的转储文件
IMPDP system/oracle DIRECTORY=dir DUMPFILE=EXPDAT_GENERATETTS000021.DMP;

-- 查询数据库中与 'TRAN' 相关的表空间的状态和名称
SELECT status, tablespace_name FROM dba_tablespaces WHERE tablespace_name LIKE '%TRAN%';

-- 将表空间 trans_tbs 设置为可读写模式
ALTER TABLESPACE trans_tbs READ WRITE;

-- 再次查询表空间的状态以确认更改
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = 'TRANS_TBS';

-- 连接到 trans_user 用户
CONN trans_user/oracle;

-- 查询当前用户下的表的数量
SELECT COUNT(*) FROM user_tables;

-- 验证表空间的状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = 'TRANS_TBS';

-- 再次连接到 trans_user 用户
CONN trans_user/oracle;

-- 查询当前用户下的所有表的数量
SELECT COUNT(*) FROM tabs;

-- 以 SYSDBA 权限连接到数据库
CONN / AS SYSDBA;

```

### **任务 5：创建外部表（External Tables）**

5.1. In the scripts directory, you will find `prod_master.dat.gz` and `prod_master.ctl`. Using the information in these files, create an Oracle Loader external table that must run a preprocessor to manipulate the data.  
在 `/home/oracle/scripts` 目录中，有 `prod_master.dat.gz` 和 `prod_master.ctl` 文件。使用这些文件中的信息创建一个 Oracle Loader 外部表，该表需要运行一个预处理器来操作数据。
```
# 切换到脚本目录
cd /home/oracle/scripts/

# 创建或编辑控制文件 prod_master.ctl
vi prod_master.ctl
LOAD DATA
# 指定输入文件，使用 .gz 压缩格式
INFILE '/home/oracle/scripts/prod_master.dat.gz'

# 指定错误和丢弃文件
BADFILE '/home/oracle/scripts/prod_master.bad'
DISCARDFILE '/home/oracle/scripts/prod_master.dsc'

# 预处理步骤（可选），在加载数据之前解压缩文件
# PREPROCESSOR execdir:'/home/oracle/scripts/uncompress.sh'

# 追加记录到目标表
APPEND
INTO TABLE sh.prod_master

# 指定字段分隔符和处理方式
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
    sname,
    cname,
    sno,
    cno,
    cname1,
    cname2,
    sdate,
    corder,
    sorder
)

# 解压缩数据文件并显示内容
zcat prod_master.dat.gz

# 使用 sh 用户连接到数据库
conn sh/sh

# 创建目标表 prod_master
create table prod_master (
    sname varchar2(20),
    cname varchar2(20),
    sno number(10),
    cno number(10),
    cname1 varchar2(20),
    cname2 varchar2(20),
    sdate varchar2(20),
    corder varchar2(20),
    sorder varchar2(20)
);

# 设置 Oracle 环境变量
export ORACLE_SID=PROD1

# 使用 SQL*Loader 加载数据
sqlldr sh/sh control=prod_master.ctl

# 创建或编辑外部日志文件
vi external.log

# 使用 sh 用户连接到数据库
conn sh/sh

# 删除已有的 prod_master 表
drop table prod_master;

# 创建外部表 prod_master，使用 Oracle Loader
CREATE TABLE prod_master (
    "SNAME" VARCHAR2(20),
    "CNAME" VARCHAR2(20),
    "SNO" NUMBER(10),
    "CNO" NUMBER(10),
    "CNAME1" VARCHAR2(20),
    "CNAME2" VARCHAR2(20),
    "SDATE" VARCHAR2(20),
    "CORDER" VARCHAR2(20),
    "SORDER" VARCHAR2(20)
)
ORGANIZATION external (
    TYPE oracle_loader
    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        PREPROCESSOR 'SYS_SQLLDR_XT_TMPDIR_00000':'uncompress.sh'
        BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad'
        DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.dsc'
        LOGFILE 'external.log_xt'
        READSIZE 1048576
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL FIELDS
        (
            "SNAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "SNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME1" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME2" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "SDATE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CORDER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "SORDER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        )
    )
    LOCATION (
        'prod_master.dat.gz'
    )
) REJECT LIMIT UNLIMITED;

# 创建目录对象 SYS_SQLLDR_XT_TMPDIR_00000，指定路径
create directory SYS_SQLLDR_XT_TMPDIR_00000 as '/home/oracle/scripts';

# 授予公共用户对该目录的读、写、执行权限
grant read, write, execute on directory SYS_SQLLDR_XT_TMPDIR_00000 to public;

# 再次使用 sh 用户连接到数据库
conn sh/sh
# 执行外部日志文件
@/home/oracle/scripts/external.log

# 查询 prod_master 表中的记录总数
select count(*) from prod_master;

```

### **任务 6：配置并行执行（Configure Parallel Execution）**

Configure parallel execution in the PROD1 database. Use a plan table that persists across logins.  
在 PROD1 数据库中配置并行执行功能，使用一个可跨登录持久化的计划表。

6.1. Parallel execution is to be done automatically by Oracle only when statements execute for more than 5 seconds.  
仅当语句执行时间超过 5 秒时，Oracle 才自动进行并行执行。
```
alter system set parallel_min_time_threshold=5;
```
6.2. Ensure that the maximum degree of parallelism for a statement is determined automatically when running the statement.  
确保语句的最大并行度由 Oracle 自动决定。
```
alter system set parallel_degree_policy=limited;
```
6.3. Allow up to 100 parallel execution slaves with 10% of the slaves defined as permanent.  
允许最多使用 100 个并行执行从属进程，其中 10% 的从属进程为永久从属进程。
```
alter system set parallel_max_servers=100;
alter system set parallel_min_servers=10; 
```
6.4. If a statement that requires more than 5 seconds cannot execute in parallel, it should not execute.  
如果语句需要超过 5 秒且不能并行执行，则不应被执行。
```
alter system set parallel_min_percent=100 scope=spfile; 
```
6.5. Set the SH.SALES table to have a default degree of parallelism of 8.  
将 SH.SALES 表的默认并行度设置为 8。
```
alter table sh.sales parallel 8;
```
6.6. Examine the `parallel.sql` script located in the `/home/oracle/scripts` folder. Make necessary changes to the `EXPLAIN PLAN` statement within the script, and then execute it as the SH user and ensure the execution of the query. Do not drop or truncate the plan table after executing the script.  
查看 `/home/oracle/scripts` 文件夹下的 `parallel.sql` 脚本。修改脚本中的 `EXPLAIN PLAN` 语句，然后以 SH 用户执行脚本，确保查询的并行执行。执行脚本后不要删除或截断计划表。
```
/home/oracle/scripts/parallel.sql

alter table sh.sales parallel(degree default);
alter session set parallel_degree_policy=limited
/
truncate table sh.plan_table
/
explain plan
SET STATEMENT_ID = 'st1'
into sh.plan_table
for update /*+ parallel */ sh.sales set amount_sold = amount_sold*1
/
select * from table(dbms_xplan.display('sh.PLAN_TABLE','st1','all'))
/
commit
/
insert into sh.STATEMENT values('st1')
/
commit
/

```

DBLOG

导航1234

最近发表

友情链接