manual process of copy an Oracle database.

Here is a simple manual process of copy an Oracle database.

Ref: http://proora.com/oracle/copy_database

1. Copy $ORACLE_HOME/dbs/initSOURCE.ora to $ORACLE_HOME/dbs/initTARGET.ora
change in initTARGET.ora:
  db_name,
  control_files,
  user_dump_dest,
  background_dump_dest,
  core_dump_dest
and may be audit_file_dest,log_archive_dest
  Create the bdump, udump and cdump directories

2. Create the script that will re-create the controlfile
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/somedir/cre_controlfile.sql';

Remove everything up to the "START NOMOUNT" statement and
everything after the semicolon at the end of the "CREATE CONTROLFILE" statement.
Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE.
On the same line, modify the database name changing it from SOURCE to TARGET.
On the same line, change the keyword NORESETLOGS to RESETLOGS.
Change datafile where necessary.
remove blank lines and comments

STARTUP NOMOUNT PFILE='/dev_orahome/oracle/10.2.0/dbs/initTARGET.ora'
or
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TARGET" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 64
    MAXLOGMEMBERS 4
    MAXDATAFILES 512
    MAXINSTANCES 4
    MAXLOGHISTORY 10452
LOGFILE
  GROUP 1 (
    '/target/redolog01a.dbf',
    '/target/redolog01b.dbf'
  ) SIZE 512M,
  GROUP 2 (
    '/target/redolog02a.dbf',
    '/target/redolog02b.dbf'
  ) SIZE 512M,
DATAFILE
  '/target/system01.dbf',
  '/target/undotbs01.dbf',
  '/target/users01.dbf',
  '/target/sysaux01.dbf'
;

3. Copy or FTP source database
Shutdown SOURCE
Copy data files and redo log files ( v$datafile, v$logfile )
Do not copy v$controlfile -- it will be created in next step
Do not copy v$tempfile -- it will be created in step 6
SOURCE could startup

4.Create the new controlfile for TARGET using cre_controlfile.sql
sqlplus /nolog
connect / as sysdba
STARTUP NOMOUNT
@/somedir/cre_controlfile.sql
Control file created.

5.Open the TARGET database
--alter database recover database until ccanccel using backup controlfile;
--alter database recover cancel;
alter database open resetlogs;

6. Check the source and create temp files
select substr(name,1,40),BYTES from v$tempfile;
SUBSTR(NAME,1,40)                             BYTES
----------------------------------------  -----------
/source/temp01.dbf                       2147483648


ALTER TABLESPACE temp ADD TEMPFILE '/target/temp01.dbf' size 2G;

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more