AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2019-07-24 834
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 我通过脚本和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
oracle云服务器 ECS
云服务器 ECS测试
测试ecs
相关实践学习
2分钟自动化部署人生模拟器
本场景将带你借助云效流水线Flow实现人生模拟器小游戏的自动化部署
7天玩转云服务器
云服务器ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，可降低 IT 成本，提升运维效率。本课程手把手带你了解ECS、掌握基本操作、动手实操快照管理、镜像管理等。了解产品详情: https://www.aliyun.com/product/ecs
警长是只猫
目录
相关文章
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
147 8
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
56 1
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
48 8
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
41 0
游客km5tgzjfur5hk
|
3月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
47 3
1436047922066202
|
22天前
|
存储 Oracle 关系型数据库
服务器数据恢复—华为S5300存储Oracle数据库恢复案例
服务器存储数据恢复环境： 华为S5300存储中有12块FC硬盘，其中11块硬盘作为数据盘组建了一组RAID5阵列，剩下的1块硬盘作为热备盘使用。基于RAID的LUN分配给linux操作系统使用，存放的数据主要是Oracle数据库。 服务器存储故障： RAID5阵列中1块硬盘出现故障离线，热备盘自动激活开始同步数据，在同步数据的过程中又一块硬盘离线，RAID5阵列瘫痪，上层LUN无法使用。
1436047922066202
19 0
土木林森
|
1月前
|
存储 弹性计算 NoSQL
"从入门到实践，全方位解析云服务器ECS的秘密——手把手教你轻松驾驭阿里云的强大计算力！"
【10月更文挑战第23天】云服务器ECS（Elastic Compute Service）是阿里云提供的基础云计算服务，允许用户在云端租用和管理虚拟服务器。ECS具有弹性伸缩、按需付费、简单易用等特点，适用于网站托管、数据库部署、大数据分析等多种场景。本文介绍ECS的基本概念、使用场景及快速上手指南。
土木林森
88 3
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
62 4
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
38 4
游客km5tgzjfur5hk
|
2月前
|
弹性计算 前端开发 JavaScript
高校学生在家实践ECS弹性云服务器
简单谈谈我这几周使用ECS弹性云服务器的体验感
游客km5tgzjfur5hk
39 6

热门文章

最新文章

  • 1
    如何正确地使用Redis（附性能测试实验结果）
  • 2
    运行QTP测试脚本后，将编译结果写入制定文件（三）
  • 3
    关于dual表的破坏性测试
  • 4
    Waymo发布360度全景视频，庆祝自动驾驶测试里程突破500英里
  • 5
    prototype中Array的测试
  • 6
    命令行及脚本发送邮件测试的方法
  • 7
    [深入JUnit] 测试运行的入口
  • 8
    尝鲜Vue3之四：如何运行Jest单元测试
  • 9
    qq开放平台测试
  • 10
    JavaScript 测试及校验工具
  • 1
    ECS使用体验的文章
    43
  • 2
    workbench远程登录ECS
    177
  • 3
    阿里云ecs使用体验
    144
  • 4
    高校学生在家实践ECS弹性云服务器
    47
  • 5
    Web应用程序的会话管理是一种跟踪和识别特定用户与Web服务器之间交互的技术
    266
  • 6
    服务器Linux系统配置mysql数据库主从自动备份
    218
  • 7
    香港服务器需要备案吗
    187
  • 8
    LabVIEW中如何在网络上使用远程VI服务器
    96
  • 9
    LabVIEW将视觉生成器AI用作OPC服务器
    75
  • 10
    阿里云服务器计算型c7、计算型c7a、计算型c8a、计算型c8y实例区别及选择参考
    217

    • 相关课程

    更多
  • ECS上云入门三部曲
  • 服务器硬件基础
  • Linux Web服务器Nginx搭建与配置
  • 7天玩转云服务器
  • 云服务器选型、迁云最佳实践
  • 云服务器ECS基本操作

    • 相关电子书

    更多
  • 如何运维千台以上游戏云服务器
  • 网站/服务器取证 实践与挑战
  • ECS块储存产品全面解析

    • 相关实验场景

    更多
  • 使用ECS和RDS搭建个人博客
  • 利用云备份Cloud Backup实现ECS文件备份
  • 基于ECS搭建FTP服务
  • 基于ECS和NAS搭建个人网盘
  • 使用ECS和OSS搭建个人网盘
  • 基于ECS搭建云上博客

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    DataWorks售前咨询