热门搜索:
您现在的位置是：首页 > 文章详情

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

日期：2019-07-23点击：567

我通过脚本和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业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。

相关文章

    文章评论

    共有0条评论来说两句吧...

    文章二维码

    扫描即可查看该文章

    点击排行

    推荐阅读

    最新文章