Oracle的系统数据库设计包括容灾、备份

程序员文章站 2024-03-20 20:48:16

...

基于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;

Oracle的系统数据库设计包括容灾、备份

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;

Oracle的系统数据库设计包括容灾、备份

创建第二个角色和用户

创建角色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;

Oracle的系统数据库设计包括容灾、备份

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 
);

创建完成后表结构如下
Oracle的系统数据库设计包括容灾、备份
创建视图计算每个用户购车中单个商品的总价

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;

Oracle的系统数据库设计包括容灾、备份

用户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;

Oracle的系统数据库设计包括容灾、备份
存储过程、函数执行分析

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

select BOOK_PACKAGE.getcartsumprice(20011) from dual;

Oracle的系统数据库设计包括容灾、备份
使用存储过程adduser插入用户数据

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

Oracle的系统数据库设计包括容灾、备份

执行计划分析

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');

Oracle的系统数据库设计包括容灾、备份
表空间使用状况

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

Oracle的系统数据库设计包括容灾、备份

7.备份恢复

备份./rman_level0.sh

Oracle的系统数据库设计包括容灾、备份

查看备份内容

Oracle的系统数据库设计包括容灾、备份

删除数据

Oracle的系统数据库设计包括容灾、备份

恢复备份

Oracle的系统数据库设计包括容灾、备份

数据已恢复

8.容灾

主库查询确认组数

Oracle的系统数据库设计包括容灾、备份

主库增加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;

Oracle的系统数据库设计包括容灾、备份

在备库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;

Oracle的系统数据库设计包括容灾、备份

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


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/

Oracle的系统数据库设计包括容灾、备份

在备库增加静态监听

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/aaa@qq.com auxiliary sys/aaa@qq.com

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;

容灾完成！

Oracle的系统数据库设计包括容灾、备份

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

1.创建表空间

2. 创建角色及用户

3. 在用户qhl_1下创建表

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

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

7.备份恢复

8.容灾