Database Migration Scenario 01

Environment Description:

SOURCE database

Oracle Server version: Oracle9.2.0.8

Database name db_name = oradb instance_name=oradb

Operating system version: Windows 2003

Instance installation Location: $oracle _base = e:/oracle

Target Database

Oracle Server version: Oracle9.2.0.8

Database name db_name = ORCL INSTANCE_NAME=ORCL

Operating system version: Windows XP

Instance installation Location: $oracle _base = d:/oracle

Perform the steps. One, the database name and SID requirements are the same

1.1 concepts related to database names

One, the database name

The database name is the ID number of the database that is used to represent a database. denoted by db_name in the parameter file (?/database/initsid.ora).

*.db_domain= "

*.db_file_multiblock_read_count=16

*.db_name= ' oradb '

*.instance_name= ' oradb '

The database name is used when you install the database, create a new database, create a data control file, modify the database structure, and back up and restore the database.

Query database name:

n Select name, dbid from V$database;

u Show parameter db_name;

U view parameter File Initsid.ora

Second, the database instance name

The database instance name is the identity that the user and the operating system are contacting, meaning that the interaction between the database and the operating system uses the database instance name.

The instance name also exists in the parameter file, which is instance_name.

The database name and instance name can be the same or different, in general, the database name and instance name is a one-to-many relationship, but if the Oracle Parallel server architecture (that is, Oracle real-time application cluster), the database name and instance name is a one-to-multiple relationship.

Queries the current DB instance name.

Ø Use SQL statements. Select instance_name from V$instance;

Ø use the show command. Show parameter instance_name;

Ø view parameter file Initsid.ora.

Both the DB instance name and Oracle_sid represent Oracle instances, but there are differences. Instance_name is an Oracle database parameter. ORACLE_SID is an environment variable for the operating system. Oracle_sid is used to interact with the operating system, that is, to access the instance name from the operating system perspective and must pass ORACLE_SID.

Oracle_sid and instance_name must be consistent, otherwise users will be subject to an error. On the UNIX platform, is Oracle not available, on the Winnt platform, is TNS, the protocol adapter is wrong.

Third, database domain name and global database name

With the popularization of the distributed database composed of multiple databases, a single db_name representation of the database naming method has caused a certain burden to the management of the database. Because the database name in a distributed environment may be the same, resulting in management confusion.

To solve this situation, the Db_domain parameter is introduced so that the identity of the database is determined by the combination of the db_name and db_domain two parameters. Avoid the management confusion caused by the duplicate database name.

Query the database domain name.

Ø Use SQL command select value from v$parameter where name = ' Db_domain ';

Ø show parameter domain with show command

Ø View parameter file Initsid.ora

Four, database service name

This parameter is newly introduced by Oracle8i. Before 8i, we used SIDS to represent an instance of the database, but in Oracle's parallel environment, a database corresponds to multiple instances, which requires multiple network service names and is cumbersome to set up. In order to facilitate the setting in the parallel environment, the service_name parameters are introduced. This parameter corresponds to a database, not an instance. The initial value of this parameter is Db_name.db_domain, which is equal to Global_name. If the database has a domain name, the database server name is the global database name; Otherwise, the database service name is the same as the database name.

The method that queries the database service name.

Using the SQL statement: Select value from v$parameter where name = ' service_name ';

Use show command: Show parameter service_name;

View parameter file: Query in the parameter file Initsid.ora.

Five, Network service name

The Network service name, also known as the database alias, is the configuration that the client program needs to access the database. Shielded client How to connect to server-side details, to achieve the location of the database transparent characteristics. The network service name is recorded in the Tnsnames.ora file.

Vi., summary

Comparison of various named names in Oracle.

Name	Query method
Db_name	Select name from V$database
Instance_name	Select instance_name from V$instance
Oracle_sid	Values are the same as instance_name
Db_domain	Select value from V$parameter where name = ' Db_domain ';
Global_name	Db_name.db_dommain
Service_Name	Select value from V$parameter where name= ' service_name ';
Net_service_name	Check the Tnsnames.ora file

1.2 modifying the db_name and SIDS of an Oracle database

One, using Oracle's own tool, nid, to change the database name

In this example, assuming the original database name is ORCL, to change to ORADB, the original instance name (Service_Name, instance_name) ORCL to be changed to ORADB.

Nid is a self-bringing tool, under the Oracle_home/bin directory, the following method assumes logging into a database server that needs to modify db_name local processing.

1, use Nid to modify SID in Mount state

C:/Documents and Settings/administrator>sqlplus/nolog

Sql> Conn/as SYSDBA

sql> shutdown Immediate

sql> Startup Mount-Nid needs to be processed in the Mount state.

Sql> host nid target=/Dbname=oradb

You need to perform an interaction with the user during processing.

Change database ID and the database name ORCL to ORADB? (Y/[n]) = Y

2, modify db_name in Mount State

Once you have done this, you need to boot to the Mount state to modify the parameter file again.

sql> shutdown Immediate

Sql> Startup Mount

Sql> alter system set DB_NAME=ORADB Scope=spfile;

sql> shutdown Immediate

3. Rebuilding the Pwdsid.ora file

passwd files are usually placed under Oracle_home/database, file naming is Pwdsid.ora, Sid is instance name (instance_name)

Sql> host orapwd File=d:/oracle/ora92/database/pwdorcl.ora password=sys_47522341 entries=5;

4, open the database (to open resetlogs)

Sql> Startup Mount

sql> ALTER DATABASE open resetlogs;

5, check the modified results

Sql> select name from V$database;

Second, use the Oradim tool to modify the instance_name

After you perform the actions in step one, the database db_name becomes oradb, but instance_name remains orcl. This name needs to be modified using Oracle's Oradim tool.

1, if it is a Windows system, you must first shut down all Oracle services, or you will get an error.

2, remove the previous instance_name

C:/Documents and Settings/administrator>oradim-delete-sid ORCL

3. Create a password file

Sql> host orapwd File=d:/oracle/ora92/database/pwdoradb.ora password=sys_47522341 entries=5;

4, create a new SID.

C:/Documents and Settings/administrator>oradim-new-sid oradb

C:/Documents and Settings/administrator>oradim-new-sid oradb

5, enter Oracle and create SPFile

Set environment variable ORACLE_SID=ORADB

C:/Documents and Settings/administrator>set oracle_sid = oradb

Modify the instance information in the SPFile (include ZS in the path to Zstest,os directory ZS instead of zstest)
If you do not modify the path of ZS to Zstest, you should not need to rebuild the control file
C:/Documents and Settings/administrator>sqlplus Sys/admin as Sysdba

Sql*plus:release 10.2.0.1.0-production on Thu July 3 16:23:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to the idle routine.

Create Pfile from SPFile first

sql> create pfile= ' D:/oracle/product/10.2.0/admin/zs/pfile/init080703.ora ' from
Spfile= ' D:/oracle/product/10.2.0/db_1/database/spfilezs. ORA ';

The file was created.

Adjust the parameters in the Pfile and then create the SPFile
sql> Create spfile= ' d:/oracle/product/10.2.0/db_1/database/spfilezstest. ORA ' fro
M pfile= ' D:/oracle/product/10.2.0/admin/zstest/pfile/init080703.ora ';

The file was created.

5) Change Listener.ora and Tnsnames.ora and restart listener

Edit these two files directly to change the contents of Sid_name, dbname and service_name to Zstest

6) because changes to the OS Database directory path (ZS to zstest) need to be rebuilt Controlfiles
The script that creates the control file can be used in the following statement when the old database is available:
ALTER DATABASE backup Controlfile to trace as ' d:/controlfile.txt ';
You can use it if you make any changes.
Sql> CREATE controlfile Reuse DATABASE "Zstest" Resetlogs ARCHIVELOG
2 Maxlogfiles 16
3 Maxlogmembers 3
4 Maxdatafiles 100
5 Maxinstances 8
6 Maxloghistory 292
7 LOGFILE
8 GROUP 1 (
9 ' d:/oracle/product/10.2.0/oradata/zstest/redo01a. LOG ',
Ten ' e:/oradata/zstest/redo01b. LOG '
One) SIZE 250M,
GROUP 2 (
' d:/oracle/product/10.2.0/oradata/zstest/redo02a. LOG ',
' e:/oradata/zstest/redo02b. LOG '
) SIZE 250M,
GROUP 3 (
"D:/ORACLE/PRODUCT/10.2.0/ORADATA/ZSTEST/REDO03A." LOG ',
' e:/oradata/zstest/redo03b. LOG '
) SIZE 250M
DataFile
' D:/oracle/product/10.2.0/oradata/zstest/system01. DBF ',
' D:/oracle/product/10.2.0/oradata/zstest/undotbs01. DBF ',
D:/ORACLE/PRODUCT/10.2.0/ORADATA/ZSTEST/SYSAUX01 '. DBF ',
' D:/oracle/product/10.2.0/oradata/zstest/users01. DBF ',
' D:/oracle/product/10.2.0/oradata/zstest/example01. DBF ',
D:/oracle/product/10.2.0/oradata/zstest/zskj_data01 '. DBF ',
' E:/oradata/zstest/zskj_data02. DBF ',
D:/oracle/product/10.2.0/oradata/zstest/zs_data01 '. DBF ',
' E:/oradata/zstest/zs_data02. DBF ',
' D:/oracle/product/10.2.0/oradata/zstest/zs_idxdata01. DBF ',
To ' E:/ORADATA/ZSTEST/ZS_IDXDATA02. DBF ',
' D:/oracle/product/10.2.0/oradata/zstest/zskj_idxdata01. DBF ',
E:/oradata/zstest/zskj_idxdata02 '. DBF '
CHARACTER SET ZHS16GBK;

The control file has been created.

sql> ALTER DATABASE open resetlogs;

The database has changed.

After rebuilding the control file, remember to re-enable the temp table space, and then trace the control file script with hints
sql> ALTER tablespace TEMP ADD tempfile ' d:/oracle/product/10.2.0/oradata/zstest
/temp01. DBF '
2 SIZE 20971520 Reuse autoextend on NEXT 524288000 MAXSIZE 32767M;

Table space has changed.

Second, image migration data file, control file and archive log file

2.1 data File storage location

View V$datafile;

Select file#, name from V$datafile;

2.2 control where files are stored

View V$controlfile

Select name from V$controlfile;

2.3 online redo log file storage location

View V$logfile;

Select * from V$logfile;

Three, mirroring trace files and warning files to the target database

If the installation path of the source database is inconsistent with the target database, you need to create a initsid.ora in the source database using Spfilesid.ora, and then modify the configuration of the Initsid.ora corresponding file to the target database. Then create a spfile.ora.

3.1 Create the Pfile in the source database.

sql> create pfile= ' E:/oracle/admin/oradb/pfile/initoradb20090922.ora ' from spfile= ' e:/oracle/ora92/database/ Spfileoradb.ora ';

3.2 mirroring trace files and warning files to the target database

The source database path.

$oracle _base/admin

3.3 modifying pfile in the target database

Modify the Pfile file Initoradb20090922.ora generated in 3.1 to replace the destination database configuration path with the corresponding path in the target database

3.4 Common Manual modification of SPFile errors

Oracle's spfile is a binary file that cannot be maintained by manual modification. There are two types of maintenance that you can choose, one is to use the ALTER system set ... scope=both/spfile after logging in to the server. Or first through the Create Pfile from SPFile. Then modify the contents of the pfile. After starting the database with startup Pfile= ', then execute the Create create SPFile from Pfile to complete the modification of the spfile.

In a database migration, only the second method can be used.

Four, regenerate the control file

4.1 back up the control file to the trace file in the target database

First, we use the following command to back up the source database control files;

sql> ALTER DATABASE backup Controlfile to trace as ' d:/zhanglei.txt ';

4.2 Modify the contents of the control file

Replace the content in the control file that was generated by the above step with the destination database's path, using the target database.

CREATE controlfile Reuse DATABASE "oradb" Noresetlogs ARCHIVELOG

--SET STANDBY to maximize performance

Maxlogfiles 50

Maxlogmembers 5

Maxdatafiles 100

Maxinstances 1

Maxloghistory 226

LOGFILE

GROUP 1 ' e:/oracle/oradata/oradb/redo01. LOG ' SIZE 100M,

GROUP 2 ' e:/oracle/oradata/oradb/redo02. LOG ' SIZE 100M,

GROUP 3 ' e:/oracle/oradata/oradb/redo03. LOG ' SIZE 100M

--STANDBY LOGFILE

DataFile

' E:/oracle/oradata/oradb/system01. DBF ',

' E:/oracle/oradata/oradb/undotbs01. DBF ',

' E:/oracle/oradata/oradb/cwmlite01. DBF ',

' E:/oracle/oradata/oradb/drsys01. DBF ',

' E:/oracle/oradata/oradb/example01. DBF ',

' E:/ORACLE/ORADATA/ORADB/INDX01. DBF ',

' E:/oracle/oradata/oradb/odm01. DBF ',

' E:/oracle/oradata/oradb/tools01. DBF ',

' E:/oracle/oradata/oradb/users01. DBF ',

' E:/oracle/oradata/oradb/xdb01. DBF ',

' E:/oracle/oradata/oradb/assm. DBF ',

' E:/oracle/oradata/oradb/perfstat01. DBF ',

' E:/oracle/oradata/zhanglei. DBF ',

' E:/oracle/oradata/oradb/ctl01. DBF '

CHARACTER SET ZHS16GBK

;

4.3 executing the replacement control file in the target database

Take the above replaced SQL statement to the target database for execution

When you are finished, you can use the following statement to open the database.

sql> ALTER DATABASE open resetlogs;

4.4 Generate SPFile file

sql> create spfile= ' D:/oracle/ora92/database/spfileoradb.ora ' from pfile= ' d:/oracle/admin/oradb/pfile/ Initoradb20090922.ora ';

4.5 Restart database to complete database migration

By this step, the database has been migrated successfully.

Restart the database and log in using SPFile.

sql> shutdown immediate;

Sql> Startup

Ext.: http://blog.csdn.net/47522341/article/details/4580021

Database Migration Scenario 01