备案控制台
探索云世界
开发者社区 数据库 文章 正文

Oracle 11g on ECS 测试实践--DB篇

2019-07-24 788
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云服务器 ECS，每月免费额度200元 3个月
推荐场景：
部署并使用Docker 快速搭建云上博客 搭建微信小程序
云服务器ECS，u1 2核4GB 1个月
推荐场景：
搭建2048小游戏 搭建turtle画布 搭建wiki知识库
简介： 我通过脚本和RMAN克隆两种方法创建数据库一.通过SQL创建数据库参考文档1.创建pfile[oracle@orcl1 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs[oracle@orcl1 dbs]$ cat bak.

我通过脚本和RMAN克隆两种方法创建数据库
一.通过SQL创建数据库
参考文档
1.创建pfile

[oracle@orcl1 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@orcl1 dbs]$ cat  bak.initORCL.ora 
db_name='orcl'
db_unique_name='orcl1'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
compatible ='11.2.0'

2.启动创建数据库

##创建数据库的脚本
[oracle@orcl1 dbs]$ cat /home/oracle/scripts/createdb.sql 
CREATE DATABASE ORCL
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
CONTROLFILE REUSE
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL  
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('/oradata/ORCL/redo01a.log','/oradata/ORCL/redo01b.log') SIZE 50M BLOCKSIZE 512,
GROUP 2('/oradata/ORCL/redo02a.log','/oradata/ORCL/redo02b.log') SIZE 50M blocksize 512,
GROUP 3('/oradata/ORCL/redo03a.log','/oradata/ORCL/redo03b.log') SIZE 50M BLOCKSIZE 512
DATAFILE '/oradata/ORCL/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '/oradata/ORCL/sysaux01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL/temp01.dbf' SIZE  10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/ORCL/undotbs01.dbf'SIZE  10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '/oradata/ORCL/users01.dbf' SIZE  10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
/
##基表、动态性能视图等
[oracle@orcl1 dbs]$ cat /home/oracle/scripts/env.sql      
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
conn system/oracle;
@?/sqlplus/admin/pupbld.sql;
[oracle@orcl1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 22:11:55 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/scripts/createdb.sql;
SQL> @/home/oracle/scripts/env.sql;

二、通过DBCA中的自带的备份片进行克隆
1.创建基于seed的pfile
DBCA使用的备份片，dbname是seeddata，如使用11.2.0.4版本，pfile应如下

[oracle@orcl1 dbs]$ cat initseed.ora 
db_name=seeddata
sga_target=500M
control_files=/oradata/ORCL/control01.ctl 
compatible ='11.2.0.4'

2.复制控制文件

[oracle@orcl1 templates]$ cd /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates
[oracle@orcl1 templates]$ cp Seed_Database.ctl /oradata/ORCL/control01.ctl

3.启动seed实例

[oracle@orcl1 ~]$ export ORACLE_SID=seed
[oracle@orcl1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 24 22:29:21 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     521936896 bytes

Fixed Size                     2254824 bytes
Variable Size                159385624 bytes
Database Buffers             356515840 bytes
Redo Buffers                   3780608 bytes

4.修改redo位置


SQL> select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member,'/ade/b/2232964209/oracle/oradata/seeddata','/oradata/ORCL') ||chr(39)||';' from v$logfile;

'ALTERDATABASERENAMEFILE'||CHR(39)||MEMBER||CHR(39)||'TO'||CHR(39)||REPLACE(MEMBER,'/ADE/B/2232964209/ORACLE/ORADATA/SEEDDATA','/ORADATA/ORCL')||CHR(39)||';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo01.log' to '/oradata/ORCL/redo01.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo02.log' to '/oradata/ORCL/redo02.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo03.log' to '/oradata/ORCL/redo03.log';

SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo01.log' to '/oradata/ORCL/redo01.log';

Database altered.

SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo02.log' to '/oradata/ORCL/redo02.log';

Database altered.

SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo03.log' to '/oradata/ORCL/redo03.log';

Database altered.

5.RMAN恢复数据库

RMAN> catalog start with '/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb

Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb

RMAN> run
2> {set newname for datafile 1 to '/oradata/ORCL/system01.dbf';
3>  set newname for datafile 2 to '/oradata/ORCL/sysaux01.dbf';
4>  set newname for datafile 3 to '/oradata/ORCL/undotbs01.dbf';
5>  set newname for datafile 4 to '/oradata/ORCL/user01.dbf';
6>  restore database;
7>  switch datafile all;
8>  recover database;
9>  }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/ORCL/user01.dbf
channel ORA_DISK_1: reading from backup piece /ade/b/2232964209/oracle/oradata/Seed_Database.dfb
channel ORA_DISK_1: errors found reading piece handle=/ade/b/2232964209/oracle/oradata/Seed_Database.dfb
channel ORA_DISK_1: failover to piece handle=/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb tag=NULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-JUL-19

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1014503999 file name=/oradata/ORCL/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1014503999 file name=/oradata/ORCL/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1014503999 file name=/oradata/ORCL/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1014503999 file name=/oradata/ORCL/user01.dbf

Starting recover at 24-JUL-19
using channel ORA_DISK_1

starting media recovery

RMAN-08187: WARNING: media recovery until SCN 925701 complete
Finished recover at 24-JUL-19

RMAN> alter database open resetlogs;

database opened

6.修复临时文件

[oracle@orcl1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 22:52:08 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201:
'/ade/b/2232964209/oracle/oradata/seeddata/temp01.dbf'
SQL> alter tablespace temp add tempfile  '/oradata/ORCL/temp01.dbf' size 10m;

Tablespace altered.
SQL>  alter tablespace temp drop  tempfile  '/ade/b/2232964209/oracle/oradata/seeddata/temp01.dbf';

Tablespace altered.

7.修改dbname

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2254824 bytes
Variable Size             159385624 bytes
Database Buffers          356515840 bytes
Redo Buffers                3780608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcl1 dbs]$ nid target=sys/oracle dbname=ORCL  

DBNEWID: Release 11.2.0.4.0 - Production on Wed Jul 24 23:17:23 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database SEEDDATA (DBID=4152976186)

Connected to server version 11.2.0

Control Files in database:
    /oradata/ORCL/control01.ctl

Change database ID and database name SEEDDATA to ORCL? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4152976186 to 1542337155
Changing database name from SEEDDATA to ORCL
    Control File /oradata/ORCL/control01.ctl - modified
    Datafile /oradata/ORCL/system01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/sysaux01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/undotbs01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/user01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/temp01.db - dbid changed, wrote new name
    Control File /oradata/ORCL/control01.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1542337155.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

8.使用ORCL的pfile启动实例

[oracle@orcl1 dbs]$ cat initORCL.ora 
db_name='ORCL'
db_unique_name='ORCL1'
memory_target=512m
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_recovery_file_dest='/orabackup/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/oradata/ORCL/control01.ctl'
compatible ='11.2.0.4'
[oracle@orcl1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 23:27:44 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2254952 bytes
Variable Size             385877912 bytes
Database Buffers          142606336 bytes
Redo Buffers                3723264 bytes
Database mounted.
SQL> alter database open resetlogs; 

Database altered.

DB创建完成完成。

文章标签：
云服务器 ECS
关系型数据库
Oracle
弹性计算
数据库
SQL
OLAP
网络协议
关键词：
测试实践
云服务器 ECS实践
Oracle数据库一键上云ecs
oracle云服务器 ECS
Oracle数据库一键上云11g
相关实践学习
一小时快速掌握 SQL 语法
本实验带您学习SQL的基础语法，快速入门SQL。
7天玩转云服务器
云服务器ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，可降低 IT 成本，提升运维效率。本课程手把手带你了解ECS、掌握基本操作、动手实操快照管理、镜像管理等。了解产品详情: https://www.aliyun.com/product/ecs
警长是只猫
目录
相关文章
游客cxahk3uf2jeks
|
2月前
|
存储 弹性计算 运维
阿里云服务器ECS经济型e实例详细介绍_性能测试和租用价格
阿里云服务器ECS经济型e实例详细介绍_性能测试和租用价格，阿里云服务器ECS推出经济型e系列，经济型e实例是阿里云面向个人开发者、学生、小微企业，在中小型网站建设、开发测试、轻量级应用等场景推出的全新入门级云服务器，CPU采用Intel Xeon Platinum架构处理器，支持1:1、1:2、1:4多种处理器内存配比，e系列性价比优选
游客cxahk3uf2jeks
160 2
坚持学习永不言弃
|
4月前
|
Oracle 关系型数据库 数据库
使用docker安装配置oracle 11g
使用docker安装配置oracle 11g
坚持学习永不言弃
152 0
非著名运维
|
6月前
|
Oracle 关系型数据库 数据库
Oracle 11G常见性能诊断报告(AWR/ADDM/ASH)收集
Oracle 11G常见性能诊断报告(AWR/ADDM/ASH)收集
非著名运维
92 0
机器学习Zero
|
1月前
|
存储 Oracle 关系型数据库
手把手教你安装Oracle——以oracle 11g为例
手把手教你安装Oracle——以oracle 11g为例
机器学习Zero
35 0
手把手教你安装Oracle——以oracle 11g为例
三分钟热度的鱼
|
1月前
|
弹性计算 分布式计算 DataWorks
DataWorks报错问题之ecs自建数据库连通性测试报错如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台，支持数据集成、数据开发、数据治理等功能；在本汇总中，我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答，以助用户在数据处理和分析工作中提高效率，降低难度。
三分钟热度的鱼
16 0
ap1253j8y
|
4月前
|
SQL Oracle 关系型数据库
docker 方式安装ORACLE 11g
docker 方式安装ORACLE 11g
ap1253j8y
198 4
vohelon
|
5月前
|
Oracle 关系型数据库 数据库
在Flink CDC中，使用Oracle 11g数据库的NUMBER类型作为主键
在Flink CDC中，使用Oracle 11g数据库的NUMBER类型作为主键
vohelon
48 1
讓丄帝愛伱
|
5月前
|
SQL Oracle 安全
window下Oracle 11G安装文档
window下Oracle 11G安装文档
讓丄帝愛伱
37 0
小小鱼儿小小林
|
7月前
|
Oracle 关系型数据库
Oracle 11g和12c的主要区别
Oracle 11g和12c的主要区别
小小鱼儿小小林
53 0
ftw2fzqaoykua
|
7月前
|
弹性计算 关系型数据库 测试技术
通过性能测试PTS对云服务器ECS进行规格选择与性能压测
本文为您介绍如何利用性能测试PTS对云服务器ECS进行规格选择与性能压测。
ftw2fzqaoykua
173 0

热门文章

最新文章

  • 1
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 2
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 3
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 4
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 5
    深入理解自动化测试框架Selenium的设计与实现
  • 6
    oracle 访问控制
  • 7
    Oracle的三重奏：密码文件、警告文件与跟踪文件
  • 8
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
  • 9
    Oracle日志文件：数据王国的“记事本”
  • 10
    Oracle数据区：数据王国的黄金地段
  • 1
    Linux系统之安装ServerBee服务器监控工具
    39
  • 2
    MongoDB的简介和安装（在服务器上）
    11
  • 3
    ECS使用体验
    8
  • 4
    阿里云ECS的使用心得
    14
  • 5
    OpenStack私有云裸机物理服务器角色规划
    18
  • 6
    服务器数据恢复—昆腾存储StorNext文件系统数据恢复案例
    15
  • 7
    Servlet 教程 之 Servlet 服务器 HTTP 响应 3
    14
  • 8
    Windows如何远程连接服务器？服务器远程连接图文教程
    20
  • 9
    什么情况下物理服务器会运行出错？
    28
  • 10
    ECS使用体验
    27

    • 相关课程

    更多
  • MSE微服务测试最佳实践 - 自动化回归
  • ECS快速入门
  • ECS基础运维管理
  • ECS上云入门三部曲
  • 服务器迁移上云
  • 云服务器ECS基本操作

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • ECS实例选型最佳实践
  • 测试场景（RDS无优惠）
  • ECS操作系统无法登录诊断能力
  • 使用阿里云ECS搭建笔记系统
  • 基于ECS AMD实例部署ChatGLM-6B大语言模型
  • 为ECS添加安全组规则

    • 推荐镜像

    更多
  • oracle
  • mysql
  • arch4edu
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）