基于Oracle的书籍交易系统数据库设计

本是古典何须时尚 2023-06-16 03:59 6阅读 0赞

### 基于Oracle的书籍交易系统数据库设计 ###

#### 1.创建表空间 ####

*  space\_qhl001

Create Tablespace space_qhl001
    datafile
    '/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl001_1.dbf'
      SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED,
    '/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl001_2.dbf'
      SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

*  space\_qhl002

Create Tablespace space_qhl002
    datafile
    '/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl002_1.dbf'
      SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED,
    '/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest_qhl002_2.dbf'
      SIZE 100M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

![在这里插入图片描述][20191124170844137.png]

#### 2. 创建角色及用户 ####

**用户默认使用表空间space\_qhl001**  
**创建第一个角色和用户**

*  创建角色qhl1将connect,resource,create view授权给qhl1
 *  创建用户qhl\_1
 *  分配60M空间给qhl\_1并将角色qhl1授权给用户qhl\_1

CREATE ROLE qhl1;
    
    GRANT connect,resource,CREATE VIEW TO qhl1;
    
    CREATE USER qhl_1 IDENTIFIED BY 123 DEFAULT TABLESPACE space_qhl001 TEMPORARY TABLESPACE temp;
    
    ALTER USER qhl_1 QUOTA 60M ON space_qhl001;
    
    GRANT qhl1 TO qhl_1;

![在这里插入图片描述][20191124171242757.png]

**创建第二个角色和用户**

*  创建角色qhl2，将connect,resource权限给qhl2
 *  创建用户qhl\_2
 *  分配60M空间给qhl\_2并将角色qhl2授权给用户qhl\_2

CREATE ROLE qhl2;
    
    GRANT connect,resource TO qhl2;
    
    CREATE USER qhl_2 IDENTIFIED BY 123 DEFAULT TABLESPACE space_qhl001 TEMPORARY TABLESPACE temp;
    
    ALTER USER qhl_2 QUOTA 60M ON space_qhl001;
    
    GRANT qhl2 TO qhl_2;

![在这里插入图片描述][20191124171505981.png]

#### 3. 在用户qhl\_1下创建表 ####

**创建管理员表**

*  id为主键

CREATE TABLE ADMINISTRATOR 
    (
      ID NUMBER(*, 0) NOT NULL 
    , PASSWORD VARCHAR2(20 BYTE) NOT NULL 
    , ADMIN VARCHAR2(20 BYTE) NOT NULL 
    , CONSTRAINT ADMINISTRATOR_PK PRIMARY KEY 
      (
        ID 
      )
      USING INDEX 
      (
          CREATE UNIQUE INDEX ADMINISTRATOR_PK ON ADMINISTRATOR (ID ASC) 
          LOGGING 
          TABLESPACE SPACE_QHL001 
          PCTFREE 10 
          INITRANS 2 
          STORAGE 
          ( 
            BUFFER_POOL DEFAULT 
          ) 
          NOPARALLEL 
      )
      ENABLE 
    ) 
    LOGGING 
    TABLESPACE SPACE_QHL001 
    PCTFREE 10 
    INITRANS 1 
    STORAGE 
    ( 
      BUFFER_POOL DEFAULT 
    ) 
    NOCOMPRESS 
    NO INMEMORY 
    NOPARALLEL;

**创建用户表**

*  id为主键
 *  根据注册日期按范围分区
 *  分为2018和2019年两个分区，每年按季度划4个子分区

CREATE TABLE BOOKUSER 
    (
      ID NUMBER(*, 0) NOT NULL 
    , PASSWORD VARCHAR2(20 BYTE) NOT NULL 
    , USERNAME VARCHAR2(50 BYTE) NOT NULL 
    , PHONE VARCHAR2(20 BYTE) NOT NULL 
    , ADDRESS VARCHAR2(30 BYTE) NOT NULL 
    , REGISTRATIONDATE DATE NOT NULL 
    , CART_ID NUMBER(*, 0) NOT NULL 
    , CONSTRAINT U_PK PRIMARY KEY 
      (
        ID 
      )
      USING INDEX 
      (
          CREATE UNIQUE INDEX U_PK ON BOOKUSER (ID ASC) 
          LOGGING 
          TABLESPACE SPACE_QHL001 
          PCTFREE 10 
          INITRANS 2 
          STORAGE 
          ( 
            BUFFER_POOL DEFAULT 
          ) 
          NOPARALLEL 
      )
      ENABLE 
    ) 
    TABLESPACE SPACE_QHL001 
    PCTFREE 10 
    INITRANS 1 
    STORAGE 
    ( 
      BUFFER_POOL DEFAULT 
    ) 
    NOCOMPRESS 
    NOPARALLEL 
    PARTITION BY RANGE (REGISTRATIONDATE) 
    SUBPARTITION BY RANGE (REGISTRATIONDATE) 
    (
      PARTITION DATE2018 VALUES LESS THAN (TO_DATE(' 2018-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY 
      (
        SUBPARTITION DATE2018_3 VALUES LESS THAN (TO_DATE(' 2018-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2018_6 VALUES LESS THAN (TO_DATE(' 2018-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2018_9 VALUES LESS THAN (TO_DATE(' 2018-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2018_12 VALUES LESS THAN (TO_DATE(' 2018-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      )  
    , PARTITION DATE2019 VALUES LESS THAN (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY 
      (
        SUBPARTITION DATE2019_3 VALUES LESS THAN (TO_DATE(' 2019-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2019_6 VALUES LESS THAN (TO_DATE(' 2019-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2019_9 VALUES LESS THAN (TO_DATE(' 2019-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      , SUBPARTITION DATE2019_12 VALUES LESS THAN (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
        NOCOMPRESS NO INMEMORY  
      )  
    );

**创建商品表**

CREATE TABLE COMMODITY 
    (
      ID NUMBER(*, 0) NOT NULL 
    , PID NUMBER(*, 0) NOT NULL 
    , BOOKSNAME VARCHAR2(20 BYTE) NOT NULL 
    , PRICE NUMBER NOT NULL 
    , DESCRIBE VARCHAR2(50 BYTE) NOT NULL 
    , NUM NUMBER(*, 0) NOT NULL 
    , ADMIN_ID NUMBER(*, 0) NOT NULL 
    , CONSTRAINT COMMODITY_PK PRIMARY KEY 
      (
        ID 
      )
      USING INDEX 
      (
          CREATE UNIQUE INDEX COMMODITY_PK ON COMMODITY (ID ASC) 
          LOGGING 
          TABLESPACE SPACE_QHL001 
          PCTFREE 10 
          INITRANS 2 
          STORAGE 
          ( 
            BUFFER_POOL DEFAULT 
          ) 
          NOPARALLEL 
      )
      ENABLE 
    ) 
    LOGGING 
    TABLESPACE SPACE_QHL001 
    PCTFREE 10 
    INITRANS 1 
    STORAGE 
    ( 
      BUFFER_POOL DEFAULT 
    ) 
    NOCOMPRESS 
    NO INMEMORY 
    NOPARALLEL;

**创建购物车表**

*  用户表字段BOOKUSER\_ID为购物车表的外键
 *  购物车采用引用分区

CREATE TABLE CART 
    (
      ID NUMBER(*, 0) NOT NULL 
    , AMOUNT NUMBER(*, 0) NOT NULL 
    , PID NUMBER(*, 0) NOT NULL 
    , BOOKUSER_ID NUMBER(*, 0) NOT NULL 
    , CONSTRAINT CART_PK PRIMARY KEY 
      (
        ID 
      )
      USING INDEX 
      (
          CREATE UNIQUE INDEX CART_PK ON CART (ID ASC) 
          LOGGING 
          TABLESPACE SPACE_QHL001 
          PCTFREE 10 
          INITRANS 2 
          STORAGE 
          ( 
            BUFFER_POOL DEFAULT 
          ) 
          NOPARALLEL 
      )
      ENABLE 
    , CONSTRAINT CART_BOOKUSER FOREIGN KEY
      (
      BOOKUSER_ID 
      )
      REFERENCES BOOKUSER
      (
      CART_ID 
      )
      ENABLE 
    ) 
    PCTFREE 10 
    PCTUSED 40 
    INITRANS 1 
    STORAGE 
    ( 
      BUFFER_POOL DEFAULT 
    ) 
    NOCOMPRESS 
    NOPARALLEL 
    PARTITION BY REFERENCE (CART_BOOKUSER) 
    (
      PARTITION DATE2018_3 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2018_6 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2018_9 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2018_12 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2019_3 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2019_6 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2019_9 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    , PARTITION DATE2019_12 
      LOGGING 
      TABLESPACE SPACE_QHL001 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        BUFFER_POOL DEFAULT 
      ) 
      NOCOMPRESS NO INMEMORY  
    );

**论坛表**

CREATE TABLE TABLE1 
    (
      ID INT NOT NULL 
    , CONTENT NVARCHAR2(50) NOT NULL 
    , CONSTRAINT TABLE1_PK PRIMARY KEY 
      (
        ID 
      )
      ENABLE 
    );

**创建完成后表结构如下**  
![在这里插入图片描述][20191124185157702.png]  
**创建视图计算每个用户购车中单个商品的总价**

create or replace  view view_SinglePriceSum
    as 
    select b.id,b.username,co.booksname,(co.price*ca.amount) pricesum from COMMODITY co,cart ca,BOOKUSER b where co.pid=ca.pid and ca.BOOKUSER_ID =b.id;
    select * from view_SinglePriceSum;

![在这里插入图片描述][20191125095857883.png]

**用户qhl\_1空间不足，修改qhl\_1空间大小**

ALTER USER qhl_1 QUOTA 90M ON space_qhl001;

#### 4. 插入用户、商品、购物车数据 ####

declare
      id number(38,0);
      username varchar2(50);
      phone varchar2(20);
      address varchar2(30);
      REGISTRATIONDATE date;
      booksname varchar2(50);
      price number(5,2);
      num number(38,0);
      amount number(38,0);
      
    begin
      for i in 1..20000
      loop
        if i mod 2 =0 then
          REGISTRATIONDATE:=to_date('2018-5-6','yyyy-mm-dd')+(i mod 60);
        else
          REGISTRATIONDATE:=to_date('2019-5-6','yyyy-mm-dd')+(i mod 60);
        end if;
    
        --插入用户
        id:=SEQ_ORDER_ID.nextval; --应该将SEQ_ORDER_ID.nextval保存到变量中。
        username := 'aa'|| 'aa';
        username := 'wang' || i;
        phone := '131785693' || i;
    	booksname := '唐诗三百首版本号' || i;
    	address :='成都'|| '四川';
    	price :=(dbms_random.value() * 100);
    	num :=(i mod 5);
        insert /*+append*/ into bookuser (id,password,username,phone,address,REGISTRATIONDATE,cart_id)
          values (id,username,username,phone,address,REGISTRATIONDATE,id);
    	--插入货品
    		
    	insert into commodity(id,pid,booksname,price,describe,num,admin_id)
    		values (id,id,booksname,price,'good',num,1);
    	--插入购物车
    	amount :=(id mod 3 ) + 1;
    	insert into cart(id,amount,pid,bookuser_id)
    	 	values (id,amount,id,id);
    
        IF I MOD 1000 =0 THEN
          commit; --每次提交会加快插入数据的速度
        END IF;
      end loop;
     
    end;

#### 6.创建程序包、存储过程、函数执行分析计划 ####

**创建程序包**

*  函数getcartsumprice计算每个用户的购物车商品总金额
 *  存储过程adduser插入用户信息

create or replace PACKAGE book_package Is
       function getcartsumprice(user_id number) return number;
       procedure adduser(password varchar2,username varchar2,phone varchar2,address varchar2,registerdate VARCHAR2);
    end book_package;

**创建函数、存储过程**

create or replace PACKAGE body book_package Is
     
           function getcartsumprice(user_id number) return number as
              begin
                declare cart_sum number;
    			query_sql varchar2(200);
                begin
    			query_sql:='select sum(pricesum) from view_SinglePriceSum where ID=' || user_id;
                  execute immediate query_sql into cart_sum;
    			  return cart_sum;
                end;
            end getcartsumprice;
                      procedure addUser(password varchar2,username varchar2,phone varchar2,address varchar2,registerdate varchar2) as
                begin
                  declare maxId number;
                  begin
                    select max(id) into maxId from bookuser;
                    insert into bookuser values(maxId+1,password,username,phone,address,to_date(registerdate,'yyyy-mm-dd'),maxId+1);
                    commit;
                  end;
                end adduser;
        end book_package;

![在这里插入图片描述][20191125095810637.png]  
**存储过程、函数执行分析**

使用自定义函数getcartsumprice（）查询id号为20011的用户购物车商品总价

select BOOK_PACKAGE.getcartsumprice(20011) from dual;

![在这里插入图片描述][20191125100143946.png]  
使用存储过程adduser插入用户数据

set serveroutput on
    declare
    begin
    BOOK_PACKAGE.addUser('131','cwd','125626','hongkong','2019-05-02');
    end;

![在这里插入图片描述][20191125100439164.png]  
![在这里插入图片描述][20191125100530193.png]  
**执行计划分析**

select * from BOOKUSER b,COMMODITY co,CART ca where b.id=ca.BOOKUSER_ID and ca.PID=co.PID and
    b.REGISTRATIONDATE between to_date('2018-1-1','yyyy-mm-dd') and to_date('2018-6-1','yyyy-mm-dd');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70]  
**表空间使用状况**

SELECT a.tablespace_name "表空间名",
    total "表空间大小",
    free "表空间剩余大小",
    (total - free) "表空间使用大小",
    total / (1024 * 1024 * 1024) "表空间大小(G)",
    free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
    (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
    round((total - free) / total, 4) * 100 "使用率 %"
    FROM (SELECT tablespace_name, SUM(bytes) free
    FROM dba_free_space
    GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) total
    FROM dba_data_files
    GROUP BY tablespace_name) b
    WHERE a.tablespace_name = b.tablespace_name

![在这里插入图片描述][20191125105910852.png]

#### 7.备份恢复 ####

*  备份./rman\_level0.sh

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 1]

*  查看备份内容

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 3]

*  删除数据

![在这里插入图片描述][20191125143657950.png]

*  恢复备份

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 5]

*  数据已恢复  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 6]

#### 8.容灾 ####

*  主库查询确认组数

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 7]

*  主库增加standbylogfile：

alter database add standby logfile  group 8 '/home/oracle/app/oracle/oradata/orcl/stdredo1.log' size 50m;
    alter database add standby logfile  group 9 '/home/oracle/app/oracle/oradata/orcl/stdredo2.log' size 50m;
    alter database add standby logfile  group 10 '/home/oracle/app/oracle/oradata/orcl/stdredo3.log' size 50m;
    alter database add standby logfile  group 11 '/home/oracle/app/oracle/oradata/orcl/stdredo4.log' size 50m;

*  主库环境开启强制归档并修改参数

ALTER DATABASE FORCE LOGGING;
    
    alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdorcl)' scope=both sid='*';
    alter system set log_archive_dest_1='LOCATION=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;
    alter system set LOG_ARCHIVE_DEST_2='SERVICE=stdorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdorcl' scope=both sid='*';
    alter system set fal_client='orcl' scope=both sid='*';    
    alter system set FAL_SERVER='stdorcl' scope=both sid='*';  
    alter system set standby_file_management=AUTO scope=both sid='*';
    alter system set DB_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/stdorcl/','/home/oracle/app/oracle/oradata/orcl/' scope=spfile sid='*';  
    alter system set LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/stdorcl/','/home/oracle/app/oracle/oradata/orcl/' scope=spfile sid='*';
    alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
    alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
    alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 8]

*  在备库oracle用户创建归档目录，数据目录并设置权限

mkdir -p /u01/app/oracle/diag/orcl
    mkdir -p /u01/app/oracle/oradata/stdorcl/
    mkdir -p /u01/arch
    mkdir -p /u01/rman
    mkdir -p /u01/app/oracle/oradata/stdorcl/pdbseed/
    mkdir -p /u01/app/oracle/oradata/stdorcl/pdb/

*  备库下执行

$sqlplus / as sysdba
    shutdown immediate;
    startup mount exclusive restrict; 
    drop database;
    startup nomount

*  修改主库及备库下文件  
    /home/oracle/app/oracle/product/12.1.0/dbhome\_1/network/admin/tnsnames.ora  
    加入配置：

ORCL =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT = 1521))  //**
        )
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    
    stdorcl =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))  //**
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = orcl)
        )
      )

*  在主库上生成备库的参数文件

create pfile from spfile;

![在这里插入图片描述][20191126212911661.png]

*  将主库的参数文件，密码文件拷贝到备库

scp /home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/initorcl.ora 192.168.1.103:/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/
    scp /home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl 192.168.1.103:/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/

![在这里插入图片描述][20191126212939119.png]

*  在备库增加静态监听

gedit /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/db_1)
          (SID_NAME = orcl)
        )
      )

*  将主库复制到备库

rman target sys/123@orcl auxiliary sys/123@stdorcl
    
    run{  
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate AUXILIARY channel c4 type disk;
    allocate AUXILIARY channel c5 type disk;
    allocate AUXILIARY channel c6 type disk;
    DUPLICATE TARGET DATABASE
      FOR STANDBY
      FROM ACTIVE DATABASE
      DORECOVER
      NOFILENAMECHECK;
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    release channel c5;
    release channel c6;
    }

输出结果

> using target database control file instead of recovery catalog  
> allocated channel: c1  
> channel c1: SID=133 device type=DISK
> 
> allocated channel: c2 channel c2: SID=254 device type=DISK
> 
> allocated channel: c3 channel c3: SID=366 device type=DISK
> 
> allocated channel: c4 channel c4: SID=355 device type=DISK
> 
> allocated channel: c5 channel c5: SID=6 device type=DISK
> 
> allocated channel: c6 channel c6: SID=126 device type=DISK
> 
> Starting Duplicate Db at 26-11月-19 current log archived
> 
> contents of Memory Script: \{ backup as copy reuse targetfile  
> ‘/home/oracle/app/oracle/product/12.1.0/dbhome\_1/dbs/orapworcl’  
> auxiliary format  
> ‘/home/oracle/app/oracle/product/12.1.0/dbhome\_1/dbs/orapworcl’ ; \}  
> executing Memory Script
> 
> Starting backup at 26-11月-19 Finished backup at 26-11月-19
> 
> contents of Memory Script: \{ restore clone from service ‘orcl’  
> standby controlfile; \} executing Memory Script
> 
> Starting restore at 26-11月-19
> 
> channel c4: starting datafile backup set restore channel c4: using  
> network backup set from service orcl channel c4: restoring control  
> file channel c4: restore complete, elapsed time: 00:00:03 output file  
> name=/home/oracle/app/oracle/oradata/orcl/control01.ctl output file  
> name=/home/oracle/app/oracle/fast\_recovery\_area/orcl/control02.ctl  
> output file  
> name=/home/oracle/app/oracle/fast\_recovery\_area/orcl/control03.ctl  
> Finished restore at 26-11月-19
> 
> contents of Memory Script: \{ sql clone ‘alter database mount  
> standby database’; \} executing Memory Script
> 
> sql statement: alter database mount standby database
> 
> contents of Memory Script: \{ set newname for tempfile 4 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdborcl\_temp2-PM.dbf”;  
> set newname for tempfile 5 to  
> “/home/oracle/app/oracle/oradata/orcl/temp02.dbf”; set newname for  
> tempfile 6 to  
> “/home/oracle/app/oracle/oradata/orcl/pdbseed/pdbseed\_temp012.dbf”;  
> switch clone tempfile all; set newname for datafile 1 to  
> “/home/oracle/app/oracle/oradata/orcl/system01.dbf”; set newname  
> for datafile 3 to  
> “/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf”; set newname  
> for datafile 4 to  
> “/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf”; set newname  
> for datafile 5 to  
> “/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf”; set  
> newname for datafile 6 to  
> “/home/oracle/app/oracle/oradata/orcl/users01.dbf”; set newname for  
> datafile 7 to  
> “/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf”; set  
> newname for datafile 8 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf”; set  
> newname for datafile 9 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf”; set  
> newname for datafile 10 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE\_SCHEMA\_users01.dbf”;  
> set newname for datafile 11 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf”; set  
> newname for datafile 12 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_1.dbf”;  
> set newname for datafile 13 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_2.dbf”;  
> set newname for datafile 16 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_3.dbf”;  
> set newname for datafile 17 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_4.dbf”;  
> set newname for datafile 77 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_1.dbf”;  
> set newname for datafile 78 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_2.dbf”;  
> set newname for datafile 79 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_1.dbf”;  
> set newname for datafile 80 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_2.dbf”;  
> set newname for datafile 81 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_1.dbf”;  
> set newname for datafile 82 to  
> “/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_2.dbf”;  
> restore from service ‘orcl’ clone database ; sql ‘alter  
> system archive log current’; \} executing Memory Script
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> renamed tempfile 4 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdborcl\_temp2-PM.dbf in  
> control file renamed tempfile 5 to  
> /home/oracle/app/oracle/oradata/orcl/temp02.dbf in control file  
> renamed tempfile 6 to  
> /home/oracle/app/oracle/oradata/orcl/pdbseed/pdbseed\_temp012.dbf in  
> control file
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> executing command: SET NEWNAME
> 
> Starting restore at 26-11月-19
> 
> channel c4: starting datafile backup set restore channel c4: using  
> network backup set from service orcl channel c4: specifying  
> datafile(s) to restore from backup set channel c4: restoring datafile  
> 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf channel c5:  
> starting datafile backup set restore channel c5: using network backup  
> set from service orcl channel c5: specifying datafile(s) to restore  
> from backup set channel c5: restoring datafile 00003 to  
> /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf channel c6: starting  
> datafile backup set restore channel c6: using network backup set from  
> service orcl channel c6: specifying datafile(s) to restore from backup  
> set channel c6: restoring datafile 00004 to  
> /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf channel c6: restore  
> complete, elapsed time: 00:00:34 channel c6: starting datafile backup  
> set restore channel c6: using network backup set from service orcl  
> channel c6: specifying datafile(s) to restore from backup set channel  
> c6: restoring datafile 00005 to  
> /home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf channel c6:  
> restore complete, elapsed time: 00:02:15 channel c6: starting datafile  
> backup set restore channel c6: using network backup set from service  
> orcl channel c6: specifying datafile(s) to restore from backup set  
> channel c6: restoring datafile 00006 to  
> /home/oracle/app/oracle/oradata/orcl/users01.dbf channel c6: restore  
> complete, elapsed time: 00:00:36 channel c6: starting datafile backup  
> set restore channel c6: using network backup set from service orcl  
> channel c6: specifying datafile(s) to restore from backup set channel  
> c6: restoring datafile 00007 to  
> /home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf channel c4:  
> restore complete, elapsed time: 00:04:03 channel c4: starting datafile  
> backup set restore channel c4: using network backup set from service  
> orcl channel c4: specifying datafile(s) to restore from backup set  
> channel c4: restoring datafile 00008 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf channel c5:  
> restore complete, elapsed time: 00:04:08 channel c5: starting datafile  
> backup set restore channel c5: using network backup set from service  
> orcl channel c5: specifying datafile(s) to restore from backup set  
> channel c5: restoring datafile 00009 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf channel c4:  
> restore complete, elapsed time: 00:01:35 channel c4: starting datafile  
> backup set restore channel c4: using network backup set from service  
> orcl channel c4: specifying datafile(s) to restore from backup set  
> channel c4: restoring datafile 00010 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE\_SCHEMA\_users01.dbf  
> channel c6: restore complete, elapsed time: 00:02:47 channel c6:  
> starting datafile backup set restore channel c6: using network backup  
> set from service orcl channel c6: specifying datafile(s) to restore  
> from backup set channel c6: restoring datafile 00011 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf channel c4:  
> restore complete, elapsed time: 00:00:57 channel c4: starting datafile  
> backup set restore channel c4: using network backup set from service  
> orcl channel c4: specifying datafile(s) to restore from backup set  
> channel c4: restoring datafile 00012 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_1.dbf  
> channel c5: restore complete, elapsed time: 00:02:38 channel c5:  
> starting datafile backup set restore channel c5: using network backup  
> set from service orcl channel c5: specifying datafile(s) to restore  
> from backup set channel c5: restoring datafile 00013 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_2.dbf  
> channel c4: restore complete, elapsed time: 00:00:02 channel c4:  
> starting datafile backup set restore channel c4: using network backup  
> set from service orcl channel c4: specifying datafile(s) to restore  
> from backup set channel c4: restoring datafile 00016 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_3.dbf  
> channel c5: restore complete, elapsed time: 00:00:03 channel c5:  
> starting datafile backup set restore channel c5: using network backup  
> set from service orcl channel c5: specifying datafile(s) to restore  
> from backup set channel c5: restoring datafile 00017 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_4.dbf  
> channel c4: restore complete, elapsed time: 00:00:03 channel c4:  
> starting datafile backup set restore channel c4: using network backup  
> set from service orcl channel c4: specifying datafile(s) to restore  
> from backup set channel c4: restoring datafile 00077 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_1.dbf  
> channel c5: restore complete, elapsed time: 00:00:06 channel c5:  
> starting datafile backup set restore channel c5: using network backup  
> set from service orcl channel c5: specifying datafile(s) to restore  
> from backup set channel c5: restoring datafile 00078 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_2.dbf  
> channel c4: restore complete, elapsed time: 00:00:07 channel c4:  
> starting datafile backup set restore channel c4: using network backup  
> set from service orcl channel c4: specifying datafile(s) to restore  
> from backup set channel c4: restoring datafile 00079 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_1.dbf  
> channel c5: restore complete, elapsed time: 00:00:02 channel c5:  
> starting datafile backup set restore channel c5: using network backup  
> set from service orcl channel c5: specifying datafile(s) to restore  
> from backup set channel c5: restoring datafile 00080 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_2.dbf  
> channel c4: restore complete, elapsed time: 00:00:04 channel c4:  
> starting datafile backup set restore channel c4: using network backup  
> set from service orcl channel c4: specifying datafile(s) to restore  
> from backup set channel c4: restoring datafile 00081 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_1.dbf  
> channel c4: restore complete, elapsed time: 00:00:04 channel c4:  
> starting datafile backup set restore channel c4: using network backup  
> set from service orcl channel c4: specifying datafile(s) to restore  
> from backup set channel c4: restoring datafile 00082 to  
> /home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_2.dbf  
> channel c5: restore complete, elapsed time: 00:00:07 channel c4:  
> restore complete, elapsed time: 00:00:03 channel c6: restore complete,  
> elapsed time: 00:00:51 Finished restore at 26-11月-19
> 
> sql statement: alter system archive log current current log archived
> 
> contents of Memory Script: \{ restore clone force from service  
> ‘orcl’  
> archivelog from scn 49141009; switch clone datafile all; \} executing Memory Script
> 
> Starting restore at 26-11月-19
> 
> channel c4: starting archived log restore to default destination  
> channel c4: using network backup set from service orcl channel c4:  
> restoring archived log archived log thread=1 sequence=1635 channel c5:  
> starting archived log restore to default destination channel c5: using  
> network backup set from service orcl channel c5: restoring archived  
> log archived log thread=1 sequence=1636 channel c4: restore complete,  
> elapsed time: 00:00:01 channel c5: restore complete, elapsed time:  
> 00:00:01 Finished restore at 26-11月-19
> 
> datafile 1 switched to datafile copy input datafile copy RECID=22  
> STAMP=1025370182 file  
> name=/home/oracle/app/oracle/oradata/orcl/system01.dbf datafile 3  
> switched to datafile copy input datafile copy RECID=23  
> STAMP=1025370182 file  
> name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf datafile 4  
> switched to datafile copy input datafile copy RECID=24  
> STAMP=1025370182 file  
> name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf datafile 5  
> switched to datafile copy input datafile copy RECID=25  
> STAMP=1025370182 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf  
> datafile 6 switched to datafile copy input datafile copy RECID=26  
> STAMP=1025370182 file  
> name=/home/oracle/app/oracle/oradata/orcl/users01.dbf datafile 7  
> switched to datafile copy input datafile copy RECID=27  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf  
> datafile 8 switched to datafile copy input datafile copy RECID=28  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf  
> datafile 9 switched to datafile copy input datafile copy RECID=29  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf  
> datafile 10 switched to datafile copy input datafile copy RECID=30  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE\_SCHEMA\_users01.dbf  
> datafile 11 switched to datafile copy input datafile copy RECID=31  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf  
> datafile 12 switched to datafile copy input datafile copy RECID=32  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_1.dbf  
> datafile 13 switched to datafile copy input datafile copy RECID=33  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_2.dbf  
> datafile 16 switched to datafile copy input datafile copy RECID=34  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_3.dbf  
> datafile 17 switched to datafile copy input datafile copy RECID=35  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users02\_4.dbf  
> datafile 77 switched to datafile copy input datafile copy RECID=36  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_1.dbf  
> datafile 78 switched to datafile copy input datafile copy RECID=37  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_users03\_2.dbf  
> datafile 79 switched to datafile copy input datafile copy RECID=38  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_1.dbf  
> datafile 80 switched to datafile copy input datafile copy RECID=39  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl001\_2.dbf  
> datafile 81 switched to datafile copy input datafile copy RECID=40  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_1.dbf  
> datafile 82 switched to datafile copy input datafile copy RECID=41  
> STAMP=1025370183 file  
> name=/home/oracle/app/oracle/oradata/orcl/pdborcl/pdbtest\_qhl002\_2.dbf
> 
> contents of Memory Script: \{ set until scn 49142428; recover  
> standby clone database  
> delete archivelog ; \} executing Memory Script
> 
> executing command: SET until clause
> 
> Starting recover at 26-11月-19
> 
> starting media recovery
> 
> archived log for thread 1 with sequence 1635 is already on disk as  
> file /home/oracle/arch/1\_1635\_865166162.arc archived log for thread 1  
> with sequence 1636 is already on disk as file  
> /home/oracle/arch/1\_1636\_865166162.arc archived log file  
> name=/home/oracle/arch/1\_1635\_865166162.arc thread=1 sequence=1635  
> archived log file name=/home/oracle/arch/1\_1636\_865166162.arc thread=1  
> sequence=1636 media recovery complete, elapsed time: 00:00:01 Finished  
> recover at 26-11月-19 Finished Duplicate Db at 26-11月-19
> 
> released channel: c1
> 
> released channel: c2
> 
> released channel: c3
> 
> released channel: c4
> 
> released channel: c5
> 
> released channel: c6

*  在备库上更改参数文件

gedit /home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/initorcl.ora  
    
    orcl.__data_transfer_cache_size=0
    orcl.__db_cache_size=671088640
    orcl.__java_pool_size=16777216
    orcl.__large_pool_size=33554432
    orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=536870912
    orcl.__sga_target=1258291200
    orcl.__shared_io_pool_size=50331648
    orcl.__shared_pool_size=301989888
    orcl.__streams_pool_size=0
    *._allow_resetlogs_corruption=TRUE
    *._catalog_foreign_restore=FALSE
    *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
    *.audit_trail='db'
    *.compatible='12.1.0.2.0'
    *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_area/orcl/control02.ctl','/home/oracle/app/oracle/fast_recovery_area/orcl/control03.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_name_convert='/home/oracle/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/stdorcl/'
    *.db_name='orcl'
    *.db_unique_name='stdorcl'
    *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
    *.db_recovery_file_dest_size=4823449600
    *.diagnostic_dest='/home/oracle/app/oracle'
    *.dispatchers='(PROTOCOL=TCP)(dispatchers=1)(pool=on)(ticks=1)(connections=500)(sessions=1000)'
    *.enable_pluggable_database=true
    *.fal_client='stdorcl'
    *.fal_server='orcl'
    *.inmemory_max_populate_servers=2
    *.inmemory_size=157286400
    *.local_listener=''
    *.log_archive_config='DG_CONFIG=(stdorcl,orcl)'
    *.log_archive_dest_1='LOCATION=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdorcl'
    *.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
    *.log_archive_format='%t_%s_%r.arc'
    *.log_file_name_convert='/home/oracle/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/stdorcl/'
    *.max_dispatchers=5
    *.max_shared_servers=20
    *.open_cursors=400
    *.parallel_execution_message_size=8192
    *.pga_aggregate_target=511m
    *.processes=300
    *.recovery_parallelism=0
    *.remote_login_passwordfile='EXCLUSIVE'
    *.service_names='ORCL'
    *.sga_max_size=1572864000
    *.sga_target=1258291200
    *.shared_server_sessions=200
    *.standby_file_management='AUTO'
    *.undo_tablespace='UNDOTBS1'

*  在备库增加静态监听

gedit /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora //运行
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/db_1)
          (SID_NAME = orcl)
        )
      )

*  然后重启备库

shudown
    startup
    alter database recover managed standby database disconnect;

容灾完成！

[20191124170844137.png]: https://img-blog.csdnimg.cn/20191124170844137.png
[20191124171242757.png]: https://img-blog.csdnimg.cn/20191124171242757.png
[20191124171505981.png]: https://img-blog.csdnimg.cn/20191124171505981.png
[20191124185157702.png]: https://img-blog.csdnimg.cn/20191124185157702.png
[20191125095857883.png]: https://img-blog.csdnimg.cn/20191125095857883.png
[20191125095810637.png]: https://img-blog.csdnimg.cn/20191125095810637.png
[20191125100143946.png]: https://img-blog.csdnimg.cn/20191125100143946.png
[20191125100439164.png]: https://img-blog.csdnimg.cn/20191125100439164.png
[20191125100530193.png]: https://img-blog.csdnimg.cn/20191125100530193.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191125104227639.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[20191125105910852.png]: https://img-blog.csdnimg.cn/20191125105910852.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191125114845629.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191125115309931.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191125115349586.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[20191125143657950.png]: https://img-blog.csdnimg.cn/20191125143657950.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2019112514294964.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191125143232511.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191125144041190.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/2019112620394326.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20191126212832132.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FiY18xMjNfYWJjXzEyM18=,size_16,color_FFFFFF,t_70
[20191126212911661.png]: https://img-blog.csdnimg.cn/20191126212911661.png
[20191126212939119.png]: https://img-blog.csdnimg.cn/20191126212939119.png