Oracle 11g on ECS 测试实践--DB篇-低调大师

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

2019-07-23 608

我通过脚本和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创建完成完成。

微信关注我们

原文链接：https://yq.aliyun.com/articles/710673

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

高射炮打蚊子，杀鸡用绝世好剑：在SAP Kyma上运行UI5应用

国人在表述“大材小用”这个场景时，总喜欢用一些实物来类比，比如：高射炮打蚊子。英国QF 3.7英寸(94mm)高射炮，战斗全重超过9.3吨，全长近5米，最大射程约18公里，最大射高超过9000米，炮弹重量12.7公斤，采用人工半自动装填，射速每分钟20发，炮班人数为7人。由英国维克斯公司研发，于上个世纪30年代第二次世界大战期间开始大量装备。相传女娲补天后留下四颗奇石，千百年后其中之"黑寒"由拜剑山庄获得，其时。当时火麒麟四处为祸，于是拜剑山庄致力于研究研究如何将"黑寒"制成一把至寒宝剑，用来克制至热的火麒麟。在完成铸剑的最后步骤，将三毒之血"贪"(剑贪之血), "瞋"(步惊云之血), "痴"(断浪之血)融入剑炉中，绝世好剑终于问世。在《风云》第一部中，步惊云掌持此剑同雄霸，绝无神等一流高手PK. Jerry从小也是个《风云》迷，至今还会时不时把马荣成这部大作拿出来翻翻。某宝上卖的锌合金1：1仿制的绝世好剑标价太贵了，Jerry买不起，只买得起下面这个比例的：图片里另一件，就是第一部里和绝世好剑齐名，人称“南山巅上火麟烈，北海浅深雪饮寒”的神兵：雪印狂刀。 Jerry还有一个真...

2019-07-24

595

前言继 AliyunLinux 15和17为代表的初代系统后，阿里云整合资源推出了 AliyunLinux 二代，而它也有一个很有意思的slogan：阿里云原生免费操作系统，这是一款云原生操作系统，也带来了很多优异的新特性来赋能 RHEL7 系的操作系统，让其更加贴合云计算的虚拟化环境并提升更多性能。那就让我们来看一下 AliyunLinux 2 究竟带来了那些黑科技吧！介绍 Aliyun Linux 2 是阿里云推出的下一代 Linux 发行版，它为云上应用程序环境提供 Linux 社区的最新增强功能，在提供云上最佳用户体验的同时，也针对阿里云基础设施做了深度的优化。Aliyun Linux 2 OS 镜像可以运行在阿里云全规格系列 VM 实例上，包括弹性裸金属服务器 (神龙)。官网：https://www.aliyun.com/product/alinux 特性新版云内核 Aliyun Linux 2默认搭载并启用最新版本阿里云云内核。新版云内核提供了以下特性：基于内核社区长期支持的4.19.24版本定制而成，增加适用于云场景的新特性、改进性能并修复重大缺陷。提供针对EC...

2019-07-25

615

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。