Blog

In this blog , we are going to see how to clone database using rman backup.

SOURCE SERVER

Take full db backup on source server

RMAN> RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;
  ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'
  INCREMENTAL LEVEL 0 DATABASE
  CURRENT CONTROLFILE
  FORMAT '/data/rman/%d_C_%T_%u'
  SPFILE
  FORMAT '/data/rman/%d_S_%T_%u'
  PLUS ARCHIVELOG
  FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
  RELEASE CHANNEL ch12;
  RELEASE CHANNEL ch13;
}

Move source db backup from source to target server

[oracle@prim rman]$ scp * oracle@192.168.0.81:/data/rman/
The authenticity of host '192.168.0.81 (192.168.0.81)' can't be established.
ECDSA key fingerprint is SHA256:qgLbwdXhj9QeIn/Qf7D28RCyTq+Nys7mcWxcnk01bWk.
ECDSA key fingerprint is MD5:60:fa:c6:91:81:51:54:5e:c4:0b:02:5f:48:6d:21:c0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.81' (ECDSA) to the list of known hosts.
oracle@192.168.0.81's password:
PRIM_A_20220514_4k0tg25s_s148_p1                                           100%  119MB  52.7M
PRIM_A_20220514_4l0tg25s_s149_p1                                           100%   69MB  53.0M
PRIM_A_20220514_4m0tg25s_s150_p1                                           100%   34MB  52.4M
PRIM_A_20220514_4n0tg25t_s151_p1                                           100% 6409KB  45.8M
PRIM_A_20220514_4o0tg25u_s152_p1                                           100% 2986KB  47.8M
PRIM_A_20220514_570tg26v_s167_p1                                           100%   22KB   5.1M
PRIM_C_20220514_550tg26t                                                   100%   18MB  51.5M
PRIM_D_20220514_4p0tg25v_s153_p1                                           100% 1379MB  51.0M
PRIM_D_20220514_4q0tg25v_s154_p1                                           100%  798MB  53.1M
PRIM_D_20220514_4r0tg25v_s155_p1                                           100%  240MB  56.0M
PRIM_D_20220514_4s0tg267_s156_p1                                           100%  762MB  52.1M
PRIM_D_20220514_4t0tg26f_s157_p1                                           100%  570MB  50.4M
PRIM_D_20220514_4u0tg26j_s158_p1                                           100%   27MB  55.4M
PRIM_D_20220514_4v0tg26k_s159_p1                                           100%  267MB  50.3M
PRIM_D_20220514_500tg26l_s160_p1                                           100%  251MB  51.8M
PRIM_D_20220514_510tg26o_s161_p1                                           100%  228MB  57.0M
PRIM_D_20220514_520tg26q_s162_p1                                           100%  218MB  54.8M
PRIM_D_20220514_530tg26q_s163_p1                                           100%   85MB  53.1M
PRIM_D_20220514_540tg26s_s164_p1                                           100%   34MB  57.0M
PRIM_S_20220514_560tg26u                                                   100%  112KB  24.7M
[oracle@prim rman]$
[oracle@prim rman]$

Target Server

Create pfile from source and edit to target

[oracle@test dbs]$ cat initdevdb.ora
devdb.__data_transfer_cache_size=0
devdb.__db_cache_size=503316480
devdb.__inmemory_ext_roarea=0
devdb.__inmemory_ext_rwarea=0
devdb.__java_pool_size=16777216
devdb.__large_pool_size=16777216
*.__oracle_base='/data/app/oracle'# ORACLE_BASE set from environment
devdb.__pga_aggregate_target=620756992
*.__reload_lsnr='0'# lreg reload listener
devdb.__sga_target=922746880
devdb.__shared_io_pool_size=50331648
devdb.__shared_pool_size=318767104
devdb.__streams_pool_size=0
devdb.__unified_pga_pool_size=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._b_tree_bitmap_plans=TRUE
*._bloom_serial_filter='ON'
*._complex_view_merging=TRUE
*._compression_compatibility='19.0.0'
*._diag_adr_trace_dest='/data/app/oracle/diag/rdbms/devdb/devdb/trace'
*._ds_xt_split_count=1
*._eliminate_common_subexpr=TRUE
*._fast_full_scan_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._key_vector_create_pushdown_threshold=20000
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._mv_access_compute_fresh_data='ON'
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_enhance_nnull_detection=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_ads_use_partial_results=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_or_expansion='ON'
*._optimizer_cluster_by_rowid_control=129
*._optimizer_control_shard_qry_processing=65528
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_model='CHOOSE'
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=192
*._optimizer_join_order_control=3
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_or_expansion='DEPTH'
*._optimizer_proc_rate_level='BASIC'
*._optimizer_system_stats_usage=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_dist_agg_partial_rollup_pushdown='ADAPTIVE'
*._px_groupby_pushdown='FORCE'
*._px_partial_rollup_pushdown='ADAPTIVE'
*._px_shared_hash_join=FALSE
*._px_wif_dfo_declumping='CHOOSE'
*._sql_model_unfold_forloops='RUN_TIME'
*._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
*._xt_sampling_scan_granules='ON'
*.audit_file_dest='/data/app/oracle/admin/devdb/adump'
*.audit_trail='DB'
*.compatible='19.0.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='/data/app/oracle/oradata/DEVDB/control01.ctl','/data/app/oracle/oradata/DEVDB/control02.ctl','/data/app/oracle/oradata/DEVDB/control03.ctl'#Restore Controlfile
*.core_dump_dest='/data/app/oracle/diag/rdbms/devdb/devdb/cdump'
*.cpu_count=1
*.cpu_min_count='1'
*.db_block_size=8192
*.db_file_name_convert='/data/app/oracle/oradata/PRIM/','/data/app/oracle/oradata/DEVDB/'
*.db_name='DEVDB'
*.diagnostic_dest='/data/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=devdbXDB)'
*.enable_pluggable_database=TRUE
*.local_listener='(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.0.81)(PORT=1522))'
*.log_archive_dest_1='LOCATION=/data/archive'
*.log_buffer=7136K# log buffer update
*.log_file_name_convert='/data/app/oracle/oradata/PRIM/','/data/app/oracle/oradata/DEVDB/'
*.memory_target=1472M
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=500
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=400
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=3776K
*.service_names='test.learomate.org'
*.undo_tablespace='UNDOTBS1'

Create required directory structure on target server

/data/app/oracle/oradata/DEVDB/
/data/app/oracle/admin/devdb/adump

Start target database in nomount state with pfile

SQL> startup nomount pfile='initdevdb.ora';
ORACLE instance started.

Total System Global Area 1543501032 bytes
Fixed Size                  9135336 bytes
Variable Size             889192448 bytes
Database Buffers          637534208 bytes
Redo Buffers                7639040 bytes

Connect to RMAN and run duplicate command

[oracle@test dbs]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 14 20:30:36 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DEVDB (not mounted)

RMAN> DUPLICATE DATABASE TO devdb BACKUP LOCATION '/data/rman/' NOFILENAMECHECK;

Post Cloning activity

Add tns entry

Veryfy DB status and DBID

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

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
DEVDB     READ WRITE           1009427986

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.