從SEED種子資料庫還原出其他庫
--準備引數檔案,還原後的庫名稱為gz
[root@RAC1 ~]# su - oracle
[oracle@RAC1 ~]$cd $ORACLE_HOME/dbs
[oracle@RAC1 dbs]$ cat init.ora | grep -v ^# | grep -v ^$ > initgz.ora
[oracle@RAC1 dbs]$ vi initgz.ora
db_name='gz'
sga_target=500m
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
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 = (ora_control1, ora_control2)
compatible ='11.2.0'
[oracle@RAC1 dbs]$ export ORACLE_SID=gz
[oracle@RAC1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 04:50:33 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> exit
Disconnected
--修改控制檔案位置
[oracle@RAC1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 04:50:44 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 159385928 bytes
Database Buffers 352321536 bytes
Redo Buffers 7999488 bytes
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/ora_control1,
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ora_control2
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set control_files='/u01/app/oracle/oradata/gz/control01.ctl' scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--從dbca模板複製control檔案
[oracle@RAC1 dbs]$ cd $ORACLE_HOME/assistants/dbca/templates/
[oracle@RAC1 templates]$ ls
Data_Warehouse.dbc New_Database.dbt Seed_Database.dfb example01.dfb
General_Purpose.dbc Seed_Database.ctl example.dmp
[oracle@RAC1 templates]$ mkdir -p /u01/app/oracle/oradata/gz
[oracle@RAC1 templates]$ cp Seed_Database.ctl /u01/app/oracle/oradata/gz/control01.ctl
[oracle@RAC1 templates]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 04:54:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 159385928 bytes
Database Buffers 352321536 bytes
Redo Buffers 7999488 bytes
ORA-01103: database name 'SEEDDATA' in control file is not 'GZ'
SQL> create pfile from spfile;
File created.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--修改資料庫名為SEEDDATA
[oracle@RAC1 templates]$ cd $ORACLE_HOME/dbs
[oracle@RAC1 dbs]$ cp initgz.ora initSEEDDATA.ora
[oracle@RAC1 dbs]$ vi initSEEDDATA.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/gz/control01.ctl'
*.db_block_size=8192
*.db_name='SEEDDATA'
*.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
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=500m
*.undo_tablespace='UNDOTBS1'
[oracle@RAC1 dbs]$ export ORACLE_SID=SEEDDATA
[oracle@RAC1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 04:56:23 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> exit
Disconnected
[oracle@RAC1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 04:57:48 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 159385928 bytes
Database Buffers 352321536 bytes
Redo Buffers 7999488 bytes
Database mounted.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ----------------------------------------
1 /ade/b/385031636/oracle/oradata/seeddata/system01.dbf
2 /ade/b/385031636/oracle/oradata/seeddata/sysaux01.dbf
3 /ade/b/385031636/oracle/oradata/seeddata/undotbs01.dbf
4 /ade/b/385031636/oracle/oradata/seeddata/users01.dbf
--修改redo log檔案位置
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- -------------- -------------- ------------------------------------------------------ ------
1 ONLINE /ade/b/385031636/oracle/oradata/seeddata/redo01.log NO
2 ONLINE /ade/b/385031636/oracle/oradata/seeddata/redo02.log NO
3 ONLINE /ade/b/385031636/oracle/oradata/seeddata/redo03.log NO
SQL> alter database rename file '/ade/b/385031636/oracle/oradata/seeddata/redo01.log' to '/u01/app/oracle/oradata/gz/redo01.log';
Database altered.
SQL> alter database rename file '/ade/b/385031636/oracle/oradata/seeddata/redo02.log' to '/u01/app/oracle/oradata/gz/redo02.log';
Database altered.
SQL> alter database rename file '/ade/b/385031636/oracle/oradata/seeddata/redo03.log' to '/u01/app/oracle/oradata/gz/redo03.log';
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--註冊SEED備份片並還原
[oracle@RAC1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 20 05:09:21 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SEEDDATA (DBID=4090761500, not open)
RMAN> catalog start with '/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/Seed_Database.dfb';
searching for all files that match the pattern /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/product/11.2.0/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/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
--還原並恢復
RMAN> run
2> {set newname for datafile 1 to '/u01/app/oracle/oradata/gz/system01.dbf';
3> set newname for datafile 2 to '/u01/app/oracle/oradata/gz/sysaux01.dbf';
4> set newname for datafile 3 to '/u01/app/oracle/oradata/gz/undotbs01.dbf';
5> set newname for datafile 4 to '/u01/app/oracle/oradata/gz/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 20-JUL-13
using channel ORA_DISK_1
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/gz/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/gz/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/gz/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/gz/user01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/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:01:05
Finished restore at 20-JUL-13
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=821253781 file name=/u01/app/oracle/oradata/gz/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=821253782 file name=/u01/app/oracle/oradata/gz/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=821253782 file name=/u01/app/oracle/oradata/gz/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=821253782 file name=/u01/app/oracle/oradata/gz/user01.dbf
Starting recover at 20-JUL-13
using channel ORA_DISK_1
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 995547 complete
Finished recover at 20-JUL-13
RMAN> exit
Recovery Manager complete.
--開啟資料庫,增加臨時表空間
[oracle@RAC1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 06:03:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open resetlogs;
Database altered.
--增加臨時表空間
SQL> select name from v$tempfile;
NAME
----------------------------------------
/ade/b/385031636/oracle/oradata/seeddata/temp01.dbf
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/gz/temp01.dbf' size 100m;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--資料庫改名為gz
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 159385928 bytes
Database Buffers 352321536 bytes
Redo Buffers 7999488 bytes
Database mounted.
SQL> !nid target=sys/oracle dbname=gz
DBNEWID: Release 11.2.0.3.0 - Production on Sat Jul 20 06:07:50 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database SEEDDATA (DBID=4090761500)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/gz/control01.ctl
Change database ID and database name SEEDDATA to GZ? (Y/[N]) => y
Proceeding with operation
Changing database ID from 4090761500 to 3321831351
Changing database name from SEEDDATA to GZ
Control File /u01/app/oracle/oradata/gz/control01.ctl - modified
Datafile /u01/app/oracle/oradata/gz/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/gz/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/gz/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/gz/user01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/gz/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/gz/control01.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to GZ.
Modify parameter file and generate a new password file before restarting.
Database ID for database GZ changed to 3321831351.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--修改引數檔案,將庫名改為gz
[oracle@RAC1 ~]$ cd $ORACLE_HOME/dbs
[oracle@RAC1 dbs]$ cp initSEEDDATA.ora initgz.ora
[oracle@RAC1 dbs]$ vi initgz.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/gz/control01.ctl'
*.db_block_size=8192
*.db_name='gz'
*.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
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=500m
*.undo_tablespace='UNDOTBS1'
[oracle@RAC1 dbs]$ export ORACLE_SID=gz
--檢查資料庫改名是否成功
[oracle@RAC1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 20 06:09:43 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initgz.ora';
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 159385928 bytes
Database Buffers 352321536 bytes
Redo Buffers 7999488 bytes
SQL> set line 120
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_name_convert string
db_name string gz
db_unique_name string gz
global_names boolean FALSE
instance_name string gz
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string gz
SQL> alter database mount;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL> col name for a50
SQL> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- --------------
/u01/app/oracle/oradata/gz/system01.dbf SYSTEM
/u01/app/oracle/oradata/gz/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/gz/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/gz/user01.dbf ONLINE
SQL> select name,status from v$tempfile;
NAME STATUS
-------------------------------------------------- --------------
/u01/app/oracle/oradata/gz/temp01.dbf ONLINE
SQL> create spfile from pfile;
File created.
至此,已經通過SEED種子資料庫還原出庫gz。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/429786/viewspace-776255/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle匯出資料庫與還原Oracle資料庫
- 還原資料庫資料庫
- 資料庫還原資料庫
- 通過資料庫檔案還原資料庫資料庫
- 還原SQL Server資料庫SQLServer資料庫
- Sqlserver資料庫使用 .bak 檔案還原資料庫SQLServer資料庫
- 織夢資料庫_織夢還原資料庫_織夢資料庫很卡資料庫
- MSSQL 備份資料庫還原SQL資料庫
- RMAN資料庫還原測試資料庫
- 還原Android彩信資料庫Android資料庫
- 從32位資料庫還原到64bit資料庫open的時候報錯資料庫
- SVN庫匯出與還原
- 【Mongodb】資料庫備份與還原MongoDB資料庫
- sqlserver資料庫的備份還原SQLServer資料庫
- 「MySQL」資料庫備份和還原MySql資料庫
- 「Oracle」Oracle 資料庫備份還原Oracle資料庫
- 批量備份和還原資料庫資料庫
- 還原點和閃回資料庫資料庫
- MySQL資料庫備份與還原MySql資料庫
- sql server 資料庫還原問題SQLServer資料庫
- sqlserver資料庫備份,還原操作SQLServer資料庫
- 隨機數種子(random seed)隨機random
- GoldenGate12.2從DataGuard備庫同步資料到其他Oracle資料庫GoOracle資料庫
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 資料庫單表備份還原shell資料庫
- 使用RMAN還原和恢復資料庫資料庫
- Mysql 5.7透過mysqldump還原資料庫MySql資料庫
- 【RMAN】利用備份片還原資料庫資料庫
- 如何將Azure SQL 資料庫還原到本地資料庫例項中SQL資料庫
- 還原sql server 2000資料庫的坑,不同版本資料庫SQLServer資料庫
- 2-53. 種子資料庫製作資料庫
- Laravel從已有資料庫表生成對應的migration和seed檔案Laravel資料庫
- SQL Server還原資料庫,修改還原後的物理檔名稱SQLServer資料庫
- 【備份恢復】下:還原和恢復資料庫(即將一個庫還原到另外一個庫)資料庫
- 【備份恢復】上:還原和恢復資料庫(即將一個庫還原到另外一個庫)資料庫
- SQLSERVER完整資料庫還原(完整恢復模式)SQLServer資料庫模式
- DM7使用dmrestore工具還原資料庫REST資料庫
- mysql資料庫-備份與還原實操MySql資料庫