Colours To The Shag Bands

Copy Windows data in LogMiner

The request was

the textual creation of a database as another, but with another name . In some cases this was to replicate the production environment for use as a test. In others, simply test allowed to recover the backup database on another machine and leave the operational database as a second environment.

When the database can keep the same name and same SID is as easy as copying datafiles, redo, ControlFiles ORA files and directories on the same original on a machine with an Oracle server installed

*

.

* Of course, same OS platform, version and release of Oracle.

However, in cases where the name should be different, we must be very precise with the steps. Previous two steps included as options in case they are useful.

This example assumes that you have the possibility of doing a cold backup of the database. Preliminary Steps

(recommended) :
- Backup cold, hot backup, export, Transport Tablespaces, backup, backup, backup of everything. - Reduction in size of the datafiles. (Tom Kyte has a fabulous script for it). Again backup.
Steps: 1 .-


Cold Backup of original DB

2 .- generation of new DB for pfile 3 .- Edit the pfile to replace: control_files, DB_NAME, and _DUMP_DEST folders. .. 4 .- Define the new ORACLE_SID
5 .- Create the new service.
6 .- Creation
password file (if the pfile is REMOTE_LOGIN_PASSWORDFILE = exclusive) 7 .- sqlplus as SYSDBA connection
8 .- Startup the instance and creating the SPFILE parameter file Backup
9 .- the controlfile to trace the original DB
10 .- Recreation controlfile to clause
SET NAME. 11 .- Open the database with OPEN RESETLOGS mode

.




1 .- Cold Backup of the original DB.
Restore backup on new location.
- Implement the outcome of the next sentence and script.
- NOTE: Beware of duplicate file names. ------------------------------------------------- ------------ select 'Immediate shutdown;' from dual union all

select 'host copy' 2 .- Generation pfile for the new database

SQL> create pfile = '? \\ Admin \\ sid \\ pfile \\ inittest.ora' from spfile;
file created.



3 .- Edit the pfile to replace: control_files, DB_NAME, and folders _DUMP_DEST ...





4 .- Define the new ORACLE_SID


c: \\> set ORACLE_SID = test




5 .- Create the new service.


c: \\> oradim-NEW-SRVC-StartMode OracleServicetest self


6 .- Creating the password file


c: \\> orapwd file = C: \\ orant \\ ora92 \\ database \\ PWDtest.ora password = xxxxxxxxx


7 .- sqlplus as SYSDBA connection


C: \\> sqlplus SQL * Plus: Release 9.2.0.6.0 - Production on Fri July 29 16:41:22 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter the user name: sys / xxxxxxxxxx
as sysdba Connected to an idle instance.


8 .- Startup the instance and file creation parameters


SPFILE SQL> STARTUP PFILE NOMOUNT = 'C: \\ DIRECTORIO_DESTINO \\ inittest.ora'
ORACLE Instance initiated. Total System Global Area 1074866044 bytes Fixed Size 456572 bytes

Variable Size 905969664 bytes Database Buffers 167772160 bytes

Redo Buffers 667648 bytes
SQL> create spfile from pfile = 'C: \\ DIRECTORIO_DESTINO \\ inittest.ora';
File created.



9 .- PROD backup of controlfile to trace.


SQL> alter database backup controlfile to trace;

Database altered.



10 .- From the trace control file. Recreation
controlfile with SET NAME to the new name.


CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS
NOARCHIVELOG REUSE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES
MaxInstance 1 133 453
MAXLOGHISTORY

LOGFILE GROUP 2 'C: \\ DATA \\ TEST \\ REDO \\ REDO02.LOG' SIZE 100M ,
GROUP 3 'C: \\ DATA \\ TEST \\ REDO \\ REDO03.LOG' SIZE 100M, GROUP 4
'C: \\ DATA \\ TEST \\ REDO \\ REDO04.LOG' SIZE 150M, GROUP 5
'C: \\ DATA \\ TEST \\ REDO \\ REDO05.LOG 'SIZE 150M, GROUP 6
' C: \\ DATA \\ TEST \\ REDO \\ REDO06.LOG ' SIZE 200M - STANDBY LOGFILE DATAFILE

(... files ...)

'C: \\ DATA \\ TEST \\ SYSTEM \\ SYSTEM01.DBF'
'C: \\ DATA \\ TEST \\ DATA \\ DATA01.DBF' CHARACTER SET WE8MSWIN1252

;
Control file created.


11 .- Open the database with OPEN RESETLOGS mode.

SQL> alter database open RESETLOGS;

Database altered.




12 .- Verification.


SQL> select instance_name from v $ instance;
INSTANCE_NAME
----------------

test SQL> select name from v $ database;


NAME --------- TEST


SQL> select status from v $ thread;

OPEN STATUS ------




12 .- Shutdown and Open database.


SQL> shutdown Immediate; Database closed. Database removed. ORACLE Instance
closed.

SQL> startup ORACLE instance started
.

Total System Global Area 1074866044 bytes Fixed Size 456572 bytes

Variable Size 905969664 bytes Database Buffers 167772160 bytes

Redo Buffers
667648 bytes Database mounted.
open database.