Ansicht
Dokumentation

Setting up a second data base
Tip-No.: 2976 ( INFO2976 )

Short text

Setting up a second data base
Tip-No.: 2976

Symptom

Setting up a second data base.

Cause

Test and production system.

Solution

Procedure for installation of a second data base
------------------------------------------------

In the examples, the source DB is called C11 and the target DB X11.

The following procedure is available for all of the options mentioned
below:

Step 1: Set up the file system
Step 2: Import the ORACLE software
Step 3: Import the data files
Step 4: Modify the UNIX environment and the file names with dbinst
Step 5: Additional manual modifications

a. Modify the profile for brbackup and brarchive
to: /usr/ora//dbs/init.sap
Compare the file with the file (modified by the customer)
init.sap from the initial installation.

b. Activate the crontab
C11: crontab -l > file
Change the SID in "file".
T11: crontab file

c. Set the transport system as user DDIC:
use SM31 to enter the new SID into the Table TSYST.

Use SM31 to maintain TAble TADIR:
PGM-ID : 'HEAD'
OBJ_TYPE: 'SYST'
Search
Maintain
Orginalsystem: 'X11'
Version-ID: 'X11K000001'
to save
When creating a transport request with 'se01',
then 'X11' should appear as source system.

Delete the content of the Table 'TLOCK'
sqlplus system/manager
SQL> delete from sapr3.TLOCK;



Case I: New installation of a second DB from the installation tape
to a different CPU to the existing DB


Step 1: Set up the file system - see installation guidelines

Step 2: Import the ORACLE software - see the installation guidelines

Step 3: Import the data files
Copy the data files to the new ORACLE_HOME

Step 4: Modify the UNIX environment and the file names
Start the dbinst.
The script modifies the UNIX envir. as usual, names all data
base files under UNIX for the new SID and modifies the control
files of the DB via a "rename".

Step 5: see above.




Case II: New installation of a second DB from installation tape onto
same CPU as the existing DB.


Step 1: Set up the file system - see Inst. Guidelines

Step 2: Import the ORACLE software - no longer required|
/usr/ora/exe was set up during the initial installation. The
symbolic links from /usr/ora//... to /usr/ora/exe/...
are created in Step 3.

Step 3: Import the data files
Copy the data files into the new ORACLE_HOME

Step 4: Modify the UNIX environment and the file names
Start the dbinst.
The script modifies the UNIX environment as usual and names all
data base files under UNIX to the new SID. For security reasons,
(access to the existing data base), the dbinst script deletes
the control files of the new data base and creates them again
with the new file names.

Step 5: see above.





Case III: Copy of an existing customer data base on the new CPU
with a new SID


Step 1: Set up the file system
See Inst. Guidelines for procedure. The size of the file systems
must however be aligned with the size of the source DB that
being copied.

Step 2: Import the ORACLE software - see Inst. Guidelines

Step 3: Copy the data files
Procedure:
o Export to the computer with the source DB all files systems
belonging to the DB (/etc/exports)
o Create on the target computer temporary NFS-Mounts
of all of these file systems - eg under /tmp/mnt.
o Copy the data files to the target computer:
cd /tmp/mnt
find . -print ? cpio -pvd /usr/ora/

Step 4: Modify the UNIX environment and the file names

dbinst modifies the UNIX envir. as usual and normally also
modifies the control files of the data base. As the new
target DB is located on a different CPU to the existing one,
dbinst can perform a "rename" on the DB here. This uses
the script "RENAME.sql".
This script must be modified to the strucure of the source DB
before starting dbinst (the customer has extended some
table spaces in the source DB, for example).

The current source DB data files are accessed under sqlplus
with the following selects:
select * from v$dbfile;
select * from v$logfile;

After adapting "RENAME.sql" to the current data files, you
start dbinst as usual.

Step 5: see above.




Case IV: Copy of an existing customer DB to the same CPU with a new SID


Step 1: Set up the file system
See Inst. Guidelines for procedure. The size of the file systems
must however be modified to that of the DB to be copied.

Step 2: Import the ORACLE software
no longer required|
/usr/ora/exe was already set up during initial installation.
The symbolic links from /usr/ora//... to /usr/ora/exe/...
are set up in Step 3.

Step 3: Copy the data files into the new ORACLE_HOME

cd /usr/ora/
find . -print ? cpio -pvd /usr/ora/

Step 4: Modify the UNIX environment and the file names

dbinst modifies the UNIX envir. and normally performs mod.s
to the control files of the DB. As the target DB is located
on the same CPU as the source DB, dbinst deletes for security
reasons the control files of the target DB and creates them
again with the new paths. Immed. after the copying of the DB
files, the control files in the source DB and target DB are
are identical. As their contents point to the same DB files
on the same CPU, the new DB may under no circumstances access
the control files while in this condition.

dbinst uses, to create the control files, the script
make_cntrl.sql in which the structure of the delivered DB is
stored. Before starting dbinst, you must therefore adapt
make_cntrl.sql to the current DB files of the source DB.
(customer has, e.g., expanded some tablespaces in the source
Data base).

Modification: make_cntrl.sql:
Option 1:
Start (in source data base): sqlplus sapr3/sap.
SQL> start cntrl.sql
"cntrl.sql" requests the SID of target DB and generates the
script make_cntrl.sql from the source DB information.

Option 2:
Adapt the existing "make_cntrl.sql" to the structure of the
source Db.
Source-DB:
select * from v$dbfile;
select * from v$logfile;

Adapt the "make_cntrl.sql" to the current data files and then
start dbinst as usual.

Step 5: see above.



Case V: Copy of an existing customer DB to another hardware platform

ORACLE is not binary-compatible to UNIX. The DB must be re-built (or
easier: install DB from installation tape and modify). Then the customer
data is exported from the source DB and importred into the target DB.


Step 1: Set up the file system
See Inst. Guidelines

Step 2: Installation of a complete DB onto a new platform
vom Installationsband
See Inst. Guidelines

Step 3: Create a script "tablespace_.sql"

This script is later used to create the tablespaces again.
This then merges several data files of a tablespace into one
tablespace.

a. Determine the current DB files and the size of the table-
spaces in the source DB
sqlplus sapr3/sap
SQL> spool tablespace.prot
SQL> select tablespace_name, file_name, bytes
from dba_data_files order by tablespace_name,
file_name;
SQL> exit;
File "tablespace.prot" contains all of the info you need.

b. Create from the info the script for all of the tablespaces
tablespace_.sql

Model:

CREATE TABLESPACE PSAPSTABD
DATAFILE '/usr/ora/X11/sapdata1/stabd_1/stabd.data1'
SIZE M
DEFAULT STORAGE(INITIAL 512K NEXT 64K MAXEXTENTS 300
PCTINCREASE 0);

o Merge several files of a tablespace into one data file
- i.e. is the sum of all data files of the
tablespace in MB|
o The tablespaces/data files SYSTEM, PSAPTEMP and
PSAPROLL are not deleted. Ignore tablespace:.sql.

Step 4: Delete from tablespaces with contents:

Important|
The tablespaces SYSTEM, PSAPTEMP and PSAPROLL are not deleted

Example:
sqlplus system/manager
SQL> alter tablespace PSAPSTABI offline;
SQL> drop tablespace PSAPSTABI including contents;

Then delete the corresponding UNIX files.
(not: dbs.dbf, temp.data1 and roll.data1).
You now have a runnable DB without SAP data.

Step 5: Rebuild data tablespaces

Align the existing directory structure with your script
tablespace_.sql. Create sub-directories if necessary.


Create new (empty) tablespaces:

sqlplus system/manager
SQL> start tablespace_.sql

Step 6: Export the data in the source DB

Start, as user "ora", the shell script:
/usr/ora//sapscripts/reorg/expFULL.sh

In directory /usr/ora//sapreorg, there must be
enough space for the export file (approx. half the size of
the entire DB).

Check the log file|
/usr/ora//sapreorg/exp.prot


Step 7: Import onto new DB

Ensure that the export file is available under
/usr/ora//sapreorg.
(mount+softlink or ftp)

Start in the import of the customer data in the target DB:
/usr/ora//sapscripts/reorg/impFULL.sh

Check the log file|
/usr/ora//sapreorg/imp.prot

Step 9: Delete the ABAP loads (the screen loads are not hardware-
dependent).

Use "sapdba" to export the table structures of the tables
D010L, D010Y, D010Q, D010LINF.
(Export without data).

Check the generated SQL scripts in directory
/usr/ora//sapreorg//LOADD.sql

Drop the 4 tables unfer sqldba.

SQLDBA> connect sapr3/sap;
SQLDBA> drop table D010L;
...

Create the tables again empty:
SQLDBA> ?LOADD.sql
(The question mark replaces the square brackets)