[20151124]快速建立測試資料庫.txt

[20151124]快速建立測試資料庫.txt

-- 以建立11.2.0.4的資料庫為例子說明，以前寫過使用記憶體來執行測試資料庫，以這個為基礎並且做一個記錄。

-- 重新刪除在建立資料庫。
startup nomount;
alter system enable restricted session;
RMAN> drop database including backups;

1.建立記憶體盤:

# mkdir -p /mnt/ramdisk
# mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk
# su - oracle

2.檢查環境變數是否設定正確
$ env | grep -i oracle
odbs=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
onetadmin=/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin
onet=/u01/app/oracle/product/11.2.0.4/dbhome_1/network
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/lib:/lib64:/usr/lib64:/usr/local/lib64
ORACLE_SID=book
oh=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
MAIL=/var/spool/mail/oracle
PATH=.:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.4/dbhome_1/bin
ob=/u01/app/oracle
PWD=/home/oracle
obdump=/u01/app/oracle/admin/dbcn1/bdump
oudump=/u01/app/oracle/admin/dbcn1/udump
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

--設定如下環境變數
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'

3.執行dbca:
--注意在執行前檢查/etc/oratab檔案，刪除以前的遺留資訊。

$ export ORACLE_SID=
$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName book -sid book -sysPassword oracle \
-systemPassword oracle -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -listeners LISTENER -sampleSchema  true -totalMemory 800 \
-databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk -redoLogFileSize 50

-- 最好加入-sampleSchema  true 這樣安裝測試帶的測試例子。在我的測試環境大約5分鐘安裝完成。

4.檢查並設定：
$ export ORACLE_SID=book
$ rlsql
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:14:42 2015
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
SYS@book> show sga
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             180356136 bytes
Database Buffers          436207616 bytes
Redo Buffers                7507968 bytes

--有3個細節要注意，建立的資料庫沒有開啟archivelog：
SYS@book> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Current log sequence           5

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BOOK     1337401710       PARENT  1          2013-08-24 11:37:30
2       2       BOOK     1337401710       CURRENT 925702     2015-11-24 09:11:12

--實際上安裝是做了1次open resetlogs開啟的。檢查/u01/app/oracle/cfgtoollogs/dbca/book目錄：
$ grep resetlogs *
trace.log:[Thread-14] [ 2015-11-24 09:11:12.764 CST ] [CloneDBCreationStep.executeImpl:517]  alter database "book" open resetlogs;

--另外就是example表空間屬性NOLOGGING：
CREATE TABLESPACE EXAMPLE DATAFILE
  '/mnt/ramdisk/book/example01.dbf' SIZE 320640K AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

5.修改相關設定：
$ mv /u01/app/oracle/fast_recovery_area/book/control02.ctl /mnt/ramdisk/book/

$ tar czvf book.tgz /mnt/ramdisk/book/
--做1個冷備份，壞了直接覆蓋就ok了。

SYS@book> startup nomount
SYS@book> alter system set control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl' scope=spfile;
System altered.

SYS@book> alter system set log_archive_dest_1="location=/u01/app/oracle/archivelog/book" ;
System altered.

SYS@book> alter database archivelog ;
Database altered.

SYS@book> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

SYS@book> alter user scott account unlock ;
User altered.

SYS@book> password scott
Changing password for scott
New password:
Retype new password:
Password changed