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

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