[20171124]手工使用種子庫建庫.txt
[20171124]手工使用Seed_Database.dfb和Seed_Database.ctl建庫.txt
--//昨天看yueli34的帖子,連結http://www.itpub.net/thread-2094530-1-1.html,註解與說明來自連結,感謝yueli34操作提示.
--//自己測試看看:
1.建立引數檔案
--//建立引數檔案，目錄為$ORACLE_HOME/dbs：
$ cat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initseeddata.ora
db_name=seeddata
sga_target=1024M
control_files=/u01/app/oracle/oradata/ora11g/control01.ctl
compatible=11.2.0.4
$ mkdir -p /u01/app/oracle/oradata/ora11g/
--//啟動到nomount狀態進行驗證
$ export ORACLE_SID=seeddata
SYS@seeddata> startup nomount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 297796488 bytes
Database Buffers 759169024 bytes
Redo Buffers 9711616 bytes
2.建立控制檔案
$ cd $ORACLE_HOME/assistants/dbca/templates
$ ls $ORACLE_HOME/assistants/dbca/templates -l
total 301856
-rw-r--r-- 1 oracle oinstall 5104 2013-08-24 12:08:38 Data_Warehouse.dbc
-rwxr-xr-x 1 oracle oinstall 21741568 2015-01-29 11:12:03 example01.dfb
-rwxr-xr-x 1 oracle oinstall 1507328 2015-01-29 11:12:03 example.dmp
-rw-r--r-- 1 oracle oinstall 4984 2013-08-24 12:08:44 General_Purpose.dbc
-rw-r--r-- 1 oracle oinstall 11489 2013-05-01 08:24:26 New_Database.dbt
-rwxr-xr-x 1 oracle oinstall 9748480 2015-01-29 11:11:15 Seed_Database.ctl
-rwxr-xr-x 1 oracle oinstall 275750912 2015-01-29 11:11:15 Seed_Database.dfb
$ cp $ORACLE_HOME/assistants/dbca/templates/Seed_Database.ctl /u01/app/oracle/oradata/ora11g/control01.ctl
$ ls -l /u01/app/oracle/oradata/ora11g/control01.ctl
-rwxr-xr-x 1 oracle oinstall 9748480 2017-11-23 10:43:13 /u01/app/oracle/oradata/ora11g/control01.ctl
--//啟動到mount狀態進行驗證：
SYS@seeddata> alter database mount ;
Database altered.
--//處理日誌檔案路徑,控制檔案中日誌檔案路徑為：
SYS@seeddata> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_REC GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ---------- ---------------------------------------------------- ------ ------ ------- --------- ----------- --------- ------- ------ ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /ade/b/2232964209/oracle/oradata/seeddata/redo01.log NO 1 1 70 52428800 512 1 NO INACTIVE 889458 2013-08-24 12:03:35 894960 2013-08-24 12:03:36
2 ONLINE /ade/b/2232964209/oracle/oradata/seeddata/redo02.log NO 2 1 71 52428800 512 1 NO INACTIVE 894960 2013-08-24 12:03:36 920281 2013-08-24 12:04:15
3 ONLINE /ade/b/2232964209/oracle/oradata/seeddata/redo03.log NO 3 1 72 52428800 512 1 NO CURRENT 920281 2013-08-24 12:04:15 2.814750E+14
--//是一個不存在的路徑，需要rename到/u01/app/oracle/oradata/ora11g/路徑。
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo01.log' to '/u01/app/oracle/oradata/ora11g/redo01.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo02.log' to '/u01/app/oracle/oradata/ora11g/redo02.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo03.log' to '/u01/app/oracle/oradata/ora11g/redo03.log';
SYS@seeddata> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_REC GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ----------------------------------------- ------ ------ ------- --------- ----------- --------- ------- ------ ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /u01/app/oracle/oradata/ora11g/redo01.log NO 1 1 70 52428800 512 1 NO INACTIVE 889458 2013-08-24 12:03:35 894960 2013-08-24 12:03:36
2 ONLINE /u01/app/oracle/oradata/ora11g/redo02.log NO 2 1 71 52428800 512 1 NO INACTIVE 894960 2013-08-24 12:03:36 920281 2013-08-24 12:04:15
3 ONLINE /u01/app/oracle/oradata/ora11g/redo03.log NO 3 1 72 52428800 512 1 NO CURRENT 920281 2013-08-24 12:04:15 2.814750E+14
--OK.
3.註冊資料檔案備份集
--//註冊備份集Seed_Database.dfb：
RMAN> catalog start with '$ORACLE_HOME/assistants/dbca/templates/Seed_Database.dfb';
searching for all files that match the pattern $ORACLE_HOME/assistants/dbca/templates/Seed_Database.dfb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
--//檢視備份集，可以看到一個相同的備份集有兩個copy。一個是控制檔案原有的，一個是catalog新註冊進去。其中原有是其實不存在
--//，可以透過crosscheck加delete去除。
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
1 Full 262.97M
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 925701 2013-08-24 12:07:43 /ade/b/2232964209/oracle/oradata/seeddata/system01.dbf
2 Full 925701 2013-08-24 12:07:43 /ade/b/2232964209/oracle/oradata/seeddata/sysaux01.dbf
3 Full 925701 2013-08-24 12:07:43 /ade/b/2232964209/oracle/oradata/seeddata/undotbs01.dbf
4 Full 925701 2013-08-24 12:07:43 /ade/b/2232964209/oracle/oradata/seeddata/users01.dbf
Backup Set Copy #1 of backup set 1
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:31 2013-08-24 12:08:24 YES
List of Backup Pieces for backup set 1 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
1 1 AVAILABLE /ade/b/2232964209/oracle/oradata/Seed_Database.dfb
Backup Set Copy #2 of backup set 1
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:31 2017-11-23 10:47:42 YES
List of Backup Pieces for backup set 1 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
2 1 AVAILABLE /u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
--//校驗和刪除expired backup
--//RMAN> crosscheck backup;
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/ade/b/2232964209/oracle/oradata/Seed_Database.dfb RECID=1 STAMP=824299673
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb RECID=2 STAMP=960806862
Crosschecked 2 objects
--//RMAN> delete expired backup;
RMAN> delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /ade/b/2232964209/oracle/oradata/Seed_Database.dfb
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/ade/b/2232964209/oracle/oradata/Seed_Database.dfb RECID=1 STAMP=824299673
Deleted 1 EXPIRED objects
--//以上步驟感覺不需要做.
--//修改資料檔案路徑,我註解recover database;因為這步沒有歸檔,寫不寫都一樣.
run{
set newname for datafile 1 to '/u01/app/oracle/oradata/ora11g/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/ora11g/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/ora11g/users01.dbf';
restore database;
switch datafile all;
##recover database;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2017-11-23 10:49:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1409 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 /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.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.4/dbhome_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 2017-11-23 10:50:39
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=960807039 file name=/u01/app/oracle/oradata/ora11g/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=960807039 file name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=960807039 file name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=960807039 file name=/u01/app/oracle/oradata/ora11g/users01.dbf
Starting recover at 2017-11-23 10:50:39
using channel ORA_DISK_1
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 925701 complete
Finished recover at 2017-11-23 10:50:41
3.開啟資料庫:
SYS@seeddata> alter database open read only ;
Database altered.
--// 補充測試open read only可以開啟.
SYS@seeddata> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@seeddata> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SYS@seeddata> select CONTROLFILE_TYPE from v$database ;
CONTROLFILE_TY
--------------
BACKUP
4.嘗試使用建立新控制檔案是否可行:
SYS@seeddata> alter database backup controlfile to trace ;
Database altered.
$ cat cc.txt
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SEEDDATA" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf'
CHARACTER SET US7ASCII
;
--//注意這樣安裝的資料庫字符集是US7ASCII,這也很好理解US7ASCII是所有字符集的子集.
SYS@seeddata> @ /tmp/cc.txt
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 297796488 bytes
Database Buffers 759169024 bytes
Redo Buffers 9711616 bytes
CREATE CONTROLFILE REUSE DATABASE "SEEDDATA" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread
$ oerr ora 01192
01192, 00000, "must have at least one enabled thread"
// *Cause: You must specify at least two logfiles from at least one thread
// at the create contolfile command line.
// *Action: Find the missing logfiles and resubmit the command with the newly
// found logfiles included in the command line.
--//重來!!
SYS@seeddata> alter database clear logfile group 1 ;
Database altered.
SYS@seeddata> alter database clear logfile group 2 ;
Database altered.
SYS@seeddata> alter database clear logfile group 3 ;
Database altered.
--//依舊不行.
--//安裝一些文章的提示建立的控制檔案只能RESETLOGS.
SYS@seeddata> alter database open RESETLOGS;
Database altered.
--//關閉資料庫重新建立控制檔案ok.
SYS@seeddata> @ /tmp/cc.txt
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 297796488 bytes
Database Buffers 759169024 bytes
Redo Buffers 9711616 bytes
Control file created.
SYS@seeddata> alter database open ;
Database altered.
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 SEEDDATA 4152976186 CURRENT 925702 2017-11-23 11:43:14
5.補充建立臨時檔案:
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' REUSE;
--//這樣建立報錯.
$ touch /u01/app/oracle/oradata/ora11g/temp01.dbf
SYS@seeddata> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' size 100m REUSE;
Tablespace altered.
6.最後yueli34想實現NORESETLOGS開庫,而原始的控制檔案本身是備份型別,如果能修改為當前,估計可以.
SYS@seeddata> select CONTROLFILE_TYPE from v$database ;
CONTROLFILE_TY
--------------
BACKUP
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2147692/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Git 子庫使用Git
- oracle手工建庫後rman無法啟用（RMAN-04015）Oracle
- 簡單介紹Oracle 19c RAC 手工建庫的過程Oracle
- 2.4.10 Step 9:手工建立資料庫資料庫
- 2-53. 種子資料庫製作資料庫
- [20190225]測試如何使用dg快速主庫.txt
- [20181224]使用odbc連線oracle資料庫.txtOracle資料庫
- Spring Boot中Thymeleaf和htmx助手工具庫Spring Boot
- [20190104]bbed手工插入資料.txt
- 達夢資料庫手工恢復相關命令資料庫
- 主備資料庫狀態手工比對（一）資料庫
- 主備資料庫狀態手工比對（二）資料庫
- CMakeLists.txt --- 匯入介面庫(預編譯庫)編譯
- 使用rollup打包庫的一種基本配置
- [20200825]手工安裝HR schema.txt
- 使用汽車應用庫構建應用
- [20180305]手工模擬buffer busy wait.txtAI
- 使用 flask 構建我的 wooyun 漏洞知識庫Flask
- Python中內建資料庫！SQLite使用指南！ ⛵Python資料庫SQLite
- oracle建庫指令碼Oracle指令碼
- 企業使用資料庫的12種姿勢資料庫
- [20210428]資料庫連線加密.txt資料庫加密
- 2020年建築電工(建築特殊工種)考試題庫及建築電工(建築特殊工種)考試總結
- MySQL建庫建表索引規範MySql索引
- 墨者學院-SQL手工注入漏洞測試(MySQL資料庫)MySql資料庫
- 關於tsup工具構建專案庫使用過程
- 2020年建築電工(建築特殊工種)考試題庫及建築電工(建築特殊工種)實操考試視訊
- Oracle OCP(36)：DBCA建庫Oracle
- 建設yum源倉庫
- [20180529]克隆資料庫與dblinks注意.txt資料庫
- [20200102]資料庫安裝問題.txt資料庫
- [20201112]nid改變資料庫名字.txt資料庫
- [20210529]延遲開啟資料庫.txt資料庫
- [20211020]改變備庫的rman配置.txt
- [20180412]手工拷貝檔案到另外主機.txt
- Python標準庫06 子程式Python
- idea內建資料庫 + sql語句庫表操作Idea資料庫SQL
- 使用Python庫pyqt5製作TXT閱讀器（一）-------UI設計PythonQTUI
- Python基礎系列講解——內建庫之collections的使用Python