ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 从SEED种子数据库还原出其他库

从SEED种子数据库还原出其他库

原创 Linux操作系统 作者:wailon 时间:2013-11-10 22:05:16 0 删除 编辑

--准备参数文件,还原后的库名称为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 10g手工建库
请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290574