Sunday, April 10, 2016

manual DB creation using datafile copy method in same server (putting DB in begin backup mode)

--- put source DB in begin backup mode and copy the intended datafiles (here in my case i copied datafiles belong to system,sysaux & UNDO)

 note: in begin backup mode user still able to connect to DB normally but a huge number of archivelof generation might be expected

-- after datafiles copy finished end backup and cross check the view v$backup_files

-- sample pfile for the easy clone or datafile copy method database creation
-- make a special note of any conflicting parameters such as service names if you are starting the new database in same server

subhen_DBHOST: vi inittarget_DB.ora
"inittarget_DB.ora" [New file]
i*.aq_tm_processes=0
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.background_core_dump='PARTIAL'
*.background_dump_dest='/dboracle/admin/target_DB/bdump'
*.backup_tape_io_slaves=TRUE
*.COMPATIBLE='10.0.0'
*.control_file_record_keep_time=40
*.control_files='/dborafiles/rpoviw1/source_DB/oradata03/target_DB/control01.ctl','/dborafiles/rpoviw1/source_DB/oradata02/target_DB/control02.ctl'
*.core_dump_dest='/dboracle/admin/target_DB/cdump'
*.cursor_sharing='EXACT'
*.DB_BLOCK_CHECKSUM='TRUE'
*.DB_BLOCK_SIZE=16384
*.db_cache_size=950m
*.db_domain=''
*.DB_FILE_MULTIBLOCK_READ_COUNT=32
*.DB_FILES=100
*.db_name='target_DB'
*.db_recovery_file_dest_size=1073741824
*.db_recovery_file_dest='/dboracle'
*.db_unique_name='target_DB'
*.dg_broker_start=FALSE
*.fast_start_parallel_rollback='FALSE'
*.global_names=FALSE
*.instance_name='target_DB'
*.JAVA_POOL_SIZE=15M
*.JOB_QUEUE_PROCESSES=2
*.large_pool_size=8M
*.log_archive_dest_1='LOCATION=/dborafiles/rpoviw1/source_DB/oraarch01/target_DB'
*.log_archive_format='target_DB_%t_%s_%r.arch'
*.log_buffer=209715200
*.LOG_CHECKPOINT_INTERVAL=0
*.LOG_CHECKPOINT_TIMEOUT=0
*.log_checkpoints_to_alert=true
*.max_dump_file_size='10240000'
*.NLS_CURRENCY='£'
*.NLS_DATE_FORMAT='DD-MON-RRRR'
*.NLS_DATE_LANGUAGE='ENGLISH'
*.NLS_ISO_CURRENCY='UNITED KINGDOM'
*.NLS_LANGUAGE='ENGLISH'
*.NLS_TERRITORY='UNITED KINGDOM'
*.open_cursors=2000
*.OPTIMIZER_DYNAMIC_SAMPLING=1
*.optimizer_features_enable='10.2.0.2'
*.optimizer_index_cost_adj=1
*.optimizer_mode='ALL_ROWS'
*.os_roles=FALSE
*.PARALLEL_MAX_SERVERS=40# CR4449 161003
*.pga_aggregate_target=1048576000
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=FALSE
*.remote_os_roles=FALSE
*.resource_limit=TRUE
*.session_cached_cursors=50
*.session_max_open_files=50
*.sga_max_size=2048m
*.sga_target=2048m
*.shadow_core_dump='PARTIAL'
*.shared_pool_reserved_size=52428800
*.shared_pool_size=500m
*.sql_trace=FALSE
*.statistics_level='TYPICAL'
*.streams_pool_size=50M
*.TIMED_STATISTICS=TRUE
*.trace_enabled=FALSE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=10800
*.UNDO_TABLESPACE='UNDO'
*.user_dump_dest='/dboracle/admin/target_DB/udump'
*.diagnostic_dest='/dboracle'
"inittarget_DB.ora" [New file] 71 lines, 2166 characters
subhen_DBHOST: ls -ltr
-- start with the pfile

subhen_DBHOST: . oraenv
ORACLE_SID = [target_DB] ?
The Oracle base remains unchanged with value /dboracle
subhen_DBHOST: echo $ORACLE_SID
target_DB
subhen_DBHOST: echo $ORACLE_HOME
/dboracle/product/11.2.0.3
subhen_DBHOST: sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 6 14:47:46 2016

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

Connected to an idle instance.

SQL> startup nomount pfile=inittarget_DB.ora
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: SQL_TRACE initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size                  2161024 bytes
Variable Size             637535872 bytes
Database Buffers         1275068416 bytes
Redo Buffers              223756288 bytes

-- below is the sample controlfile ( created by using create control file to trace command in source DB and edited to suit)

subhen_DBHOST: view create_controlfile_target_DB.sql
"create_controlfile_target_DB.sql" [Read only] 24 lines, 941 characters
CREATE CONTROLFILE SET DATABASE "target_DB" RESETLOGS
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1024
    MAXINSTANCES 1
    MAXLOGHISTORY 10452
LOGFILE
  GROUP 1 (
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log1a.log',
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log1b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 2 (
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log2a.log',
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log2b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 3 (
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log3a.log',
    '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/redo_log3b.log'
  ) SIZE 100M BLOCKSIZE 512
DATAFILE
  '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/system_01.dbf',
  '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/sysaux_01.dbf',
  '/dborafiles/rpoviw1/source_DB/oradata03/target_DB/undo_01.dbf'
  CHARACTER SET WE8MSWIN1252;
~

--- mount DB

SQL> @/dboracle/product/11.2.0.3/dbs/create_controlfile_target_DB.sql

Control file created.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
target_DB  MOUNTED              PRIMARY

--- try to open DB

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/dborafiles/rpoviw1/source_DB/oradata03/target_DB/system_01.dbf'

-- do some log switch at source DB and give the archivelog location of primary DB

SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 13641415476848 generated at 04/06/2016 13:27:12 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6623_908035086.arch
ORA-00280: change 13641415476848 for thread 1 is in sequence #6623


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6623_908035086.arch
ORA-00279: change 13641416081656 generated at 04/06/2016 13:43:14 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6624_908035086.arch
ORA-00280: change 13641416081656 for thread 1 is in sequence #6624
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6623_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6624_908035086.arch
ORA-00279: change 13641416081660 generated at 04/06/2016 13:43:16 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6625_908035086.arch
ORA-00280: change 13641416081660 for thread 1 is in sequence #6625
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6624_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6625_908035086.arch
ORA-00279: change 13641416101823 generated at 04/06/2016 14:16:29 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6626_908035086.arch
ORA-00280: change 13641416101823 for thread 1 is in sequence #6626
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6625_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6626_908035086.arch
ORA-00279: change 13641417242879 generated at 04/06/2016 14:34:22 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6627_908035086.arch
ORA-00280: change 13641417242879 for thread 1 is in sequence #6627
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6626_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/dborafiles/rpoviw1/source_DB/oradata03/target_DB/system_01.dbf'

-- try some more log switch at source DB and apply in target DB

SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 13641417242879 generated at 04/06/2016 14:34:22 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6627_908035086.arch
ORA-00280: change 13641417242879 for thread 1 is in sequence #6627


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6627_908035086.arch
ORA-00279: change 13641418097028 generated at 04/06/2016 14:54:35 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6628_908035086.arch
ORA-00280: change 13641418097028 for thread 1 is in sequence #6628
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6627_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/dborafiles/rpoviw1/source_DB/oradata03/target_DB/system_01.dbf'


SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 13641418097028 generated at 04/06/2016 14:54:35 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6628_908035086.arch
ORA-00280: change 13641418097028 for thread 1 is in sequence #6628


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6628_908035086.arch
ORA-00279: change 13641418097034 generated at 04/06/2016 14:54:46 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6629_908035086.arch
ORA-00280: change 13641418097034 for thread 1 is in sequence #6629
ORA-00278: log file
'/dborafiles/rpoviw1/source_DB/oraarch01/standby_1_6628_908035086.arch' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/dborafiles/rpoviw1/source_DB/oradata03/target_DB/system_01.dbf'



SQL> Recover database until cancel using backup controlfile;
ORA-00279: change 13641418097034 generated at 04/06/2016 14:54:46 needed for
thread 1
ORA-00289: suggestion :
/dborafiles/rpoviw1/source_DB/oraarch01/target_DB/target_DB_1_6629_908035086.arch
ORA-00280: change 13641418097034 for thread 1 is in sequence #6629


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
target_DB  READ WRITE           PRIMARY

Posted by at No comments:
Subscribe to: Posts (Atom)