configure device type disk parallelism 1 backup type to compressed backupset;
configure snapshot controlfile name to '/u01/app/Backup/snapshot_controlfile';
*.log_archive_dest_1='location=+FRA/dpuli/Archive' # <<<<---- archive="archive" create="create" dir="dir" div="div" fail="fail" for="for" in="in" it="it" log="log" other="other" this="this" will="will" wise="wise">
*.log_archive_format='Log%s_%t_%r.Arc'
*.memory_target=471859200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@oel5u4-orcl dbs] pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
Create password file
[oracle@oel5u4-orcl dbs] orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdpuli password=oracle
[oracle@oel5u4-orcl dbs] ls -ltr
total 9572
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
drwx------ 2 oracle oinstall 4096 Oct 1 2010 peshm_DBUA0_0
-rw-rw---- 1 oracle oinstall 1544 Oct 1 2010 hc_DBUA0.dat
drwx------ 2 oracle dba 4096 Oct 1 2010 peshm_orcl_0
-rw-rw---- 1 oracle dba 1544 Oct 1 2010 hc_orcl.dat
-rw-r----- 1 oracle dba 24 Oct 1 2010 lkORCL
-rw-r----- 1 oracle oinstall 35 Oct 1 2010 initorcl.ora
-rw-r----- 1 oracle oinstall 1536 Oct 1 2010 orapworcl
-rw-r----- 1 oracle dba 9748480 Nov 24 18:57 snapcf_orcl.f
-rw-r----- 1 oracle oinstall 1536 Nov 24 22:02 orapwdpuli
cd /u01/app/oracle/admin
mkdir dpuli
mkdir dpdump adump pfile --- <<<<<<<<<<<<<<<
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=dpuli
sqlplus sys/oracle as sysdba
[oracle@oel5u4-dpuli dbs] sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 22:05:02 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora
[oracle@oel5u4-dpuli dpuli] !sql
sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 22:20:02 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> def
DEFINE _DATE = "24-NOV-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "dpuli" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "" (CHAR)
DEFINE _O_RELEASE = "" (CHAR)
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora
ORACLE instance started.
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 406849584 bytes
Database Buffers 58720256 bytes
Redo Buffers 5980160 bytes
[oracle@oel5u4-dpuli dpuli] rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 24 22:27:36 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: DPULI (not mounted)
duplicate database to 'dpuli' backup location '/u01/app/Backup';
RMAN> duplicate database to 'dpuli' backup location '/u01/app/Backup';
[oracle@oel5u4-orcl ~] export ORACLE_SID=dpuli
[oracle@oel5u4-dpuli ~] !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 25 07:52:32 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora
ORACLE instance started.
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 406849584 bytes
Database Buffers 58720256 bytes
Redo Buffers 5980160 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
after nomount ....
[oracle@oel5u4-dpuli ~] ps -ef|grep -i smon
oracle 6666 1 0 07:32 ? 00:00:02 asm_smon_+ASM
oracle 7870 1 1 07:56 ? 00:00:02 ora_smon_dpuli
oracle 7988 7602 38 07:59 pts/4 00:00:00 grep -i smon
[oracle@oel5u4-dpuli ~] rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Nov 25 08:00:16 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: DPULI (not mounted)
RMAN> duplicate database to 'dpuli' backup location '/u01/app/Backup';
Starting Duplicate Db at 25-NOV-12
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 411043888 bytes
Database Buffers 54525952 bytes
Redo Buffers 5980160 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/dpuli/controlfile/current.274.800266185'', ''+DATA/dpuli/controlfile/current.273.800266189'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DPULI'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/app/Backup/controlfile_16.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/dpuli/controlfile/current.274.800266185'', ''+DATA/dpuli/controlfile/current.273.800266189''
comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DPULI'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 411043888 bytes
Database Buffers 54525952 bytes
Redo Buffers 5980160 bytes
Starting restore at 25-NOV-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
output file name=+DATA/dpuli/controlfile/current.274.800266185
output file name=+DATA/dpuli/controlfile/current.273.800266189
Finished restore at 25-NOV-12
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
contents of Memory Script:
{
set until scn 1427605;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-NOV-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/Backup/datafile_11_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/app/Backup/datafile_11_1.bak tag=DATAFILE_DAILY
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:25:08
Finished restore at 25-NOV-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=800267909 file name=+DATA/dpuli/datafile/system.272.800266397
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=800267911 file name=+DATA/dpuli/datafile/sysaux.271.800266407
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=800267912 file name=+DATA/dpuli/datafile/undotbs1.269.800266419
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=800267913 file name=+DATA/dpuli/datafile/users.268.800266427
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=800267914 file name=+DATA/dpuli/datafile/example.270.800266415
contents of Memory Script:
{
set until scn 1427605;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-NOV-12
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/Backup/archivelog_14_1.arch
channel ORA_AUX_DISK_1: piece handle=/u01/app/Backup/archivelog_14_1.arch tag=ARCHIVELOG_DAILY
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
archived log file name=+FRA/dpuli/archive/log41_1_731227289.arc thread=1 sequence=41
channel clone_default: deleting archived log(s)
archived log file name=+FRA/dpuli/archive/log41_1_731227289.arc RECID=1 STAMP=800268038
media recovery complete, elapsed time: 00:00:54
Finished recover at 25-NOV-12
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''DPULI'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 411043888 bytes
Database Buffers 54525952 bytes
Redo Buffers 5980160 bytes
sql statement: alter system set db_name = ''DPULI'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 411043888 bytes
Database Buffers 54525952 bytes
Redo Buffers 5980160 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DPULI" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DATA/dpuli/datafile/system.272.800266397'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/dpuli/datafile/sysaux.271.800266407",
"+DATA/dpuli/datafile/undotbs1.269.800266419",
"+DATA/dpuli/datafile/users.268.800266427",
"+DATA/dpuli/datafile/example.270.800266415";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/sysaux.271.800266407 RECID=1 STAMP=800268593
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/undotbs1.269.800266419 RECID=2 STAMP=800268594
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/users.268.800266427 RECID=3 STAMP=800268594
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/example.270.800266415 RECID=4 STAMP=800268595
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=800268593 file name=+DATA/dpuli/datafile/sysaux.271.800266407
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=800268594 file name=+DATA/dpuli/datafile/undotbs1.269.800266419
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=800268594 file name=+DATA/dpuli/datafile/users.268.800266427
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=800268595 file name=+DATA/dpuli/datafile/example.270.800266415
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-NOV-12
RMAN>
SQL> select OPEN_MODE from v$database ;
OPEN_MODE
--------------------
READ WRITE
select INSTANCE_NAME,DATABASE_STATUS,HOST_NAME,STATUS from v$instance
INSTANCE_NAME DATABASE_STATUS HOST_NAME STATUS
---------------- ----------------- ---------------------------------------------------------------- ------------
dpuli ACTIVE oel5u4 OPEN
SQL> show parameter spfile ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfiledpuli.ora
SQL> create pfile='/tmp/initdupli.txt' from spfile ;
File created.
Now New spfile <<<<<<<<<<<<<<<<<<<
SQL> !cat /tmp/initdupli.txt
*.__db_cache_size=56M
dpuli.__db_cache_size=54525952
*.__java_pool_size=8M
dpuli.__java_pool_size=8388608
*.__large_pool_size=4M
dpuli.__large_pool_size=4194304
*.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment
*.__pga_aggregate_target=200M
dpuli.__pga_aggregate_target=209715200
*.__sga_target=252M
dpuli.__sga_target=264241152
*.__shared_io_pool_size=0
dpuli.__shared_io_pool_size=0
*.__shared_pool_size=172M
dpuli.__shared_pool_size=180355072
*.__streams_pool_size=4M
dpuli.__streams_pool_size=4194304
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._compression_compatibility='11.2.0.0.0'
*._connect_by_use_union_all='TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_coalesce_subqueries=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_connect_by_elim_dups=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=224
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_group_by_placement=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_factorization=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_undo_cost_change='11.2.0.1'
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_minus_intersect=TRUE
*._px_pwg_enabled=TRUE
*._px_ual_serial_input=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest='/u01/app/oracle/admin/dpuli/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/dpuli/controlfile/current.274.800266185','+DATA/dpuli/controlfile/current.273.800266189'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/diag/rdbms/dpuli/dpuli/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='us.oracle.com'
*.db_name='DPULI'#Reset to original value by RMAN
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4062M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=+FRA/dpuli/Archive'
*.log_archive_format='Log%s_%t_%r.Arc'
*.log_buffer=5783552# log buffer update
*.memory_target=452M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1184K
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Total System Global Area 472887296 bytes
Fixed Size 1337296 bytes
Variable Size 411043888 bytes
Database Buffers 54525952 bytes
Redo Buffers 5980160 bytes
Database mounted.
create pfile='/tmp/initdupli1.txt' from spfile ;
SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA/dpuli/archive
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
