從SEED種子資料庫還原出其他庫

wailon發表於2013-11-10

--準備引數檔案,還原後的庫名稱為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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章