Taking backup of the Oracle SPFILE

Backup & Recovery / 14/07/2022

Aqui não tem muito segredo. Para criar um backupset com o conteúdo do spfile:

RMAN> BACKUP SPFILE;
 
Starting backup at 2021-03-15:05:33:19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-15:05:33:19
channel ORA_DISK_1: finished piece 1 at 2021-03-15:05:33:20
piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnsnf_TAG20210315T053319_j4y6yhp9_.bkp tag=TAG20210315T053319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-15:05:33:20
 
Starting Control File and SPFILE Autobackup at 2021-03-15:05:33:20
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067232800_j4y6ykdy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:05:33:21

Criando o backup como image copy:

RMAN> BACKUP AS COPY SPFILE FORMAT '/tmp/spfile.ora';
 
Starting backup at 2021-03-15:05:34:40
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-15:05:34:41
channel ORA_DISK_1: finished piece 1 at 2021-03-15:05:34:42
piece handle=/tmp/spfile.ora tag=TAG20210315T053440 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-15:05:34:42
 
Starting Control File and SPFILE Autobackup at 2021-03-15:05:34:42
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067232882_j4y712ts_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:05:34:43
RMAN> host 'ls /tmp/spfile.ora';
 
/tmp/spfile.ora
host command complete

Listando os detalhes dos backups:

RMAN> LIST BACKUP OF SPFILE;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    96.00K     DISK        00:00:00     2021-03-15:05:33:19
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T053319
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnsnf_TAG20210315T053319_j4y6yhp9_.bkp
  SPFILE Included: Modification time: 2021-03-15:04:52:58
  SPFILE db_unique_name: RMANDB
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
34      Full    10.20M     DISK        00:00:01     2021-03-15:05:33:21
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T053320
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067232800_j4y6ykdy_.bkp
  SPFILE Included: Modification time: 2021-03-15:04:52:58
  SPFILE db_unique_name: RMANDB
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
35      Full    96.00K     DISK        00:00:01     2021-03-15:05:34:41
        BP Key: 35   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T053440
        Piece Name: /tmp/spfile.ora
  SPFILE Included: Modification time: 2021-03-15:04:52:58
  SPFILE db_unique_name: RMANDB
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
36      Full    10.20M     DISK        00:00:00     2021-03-15:05:34:42
        BP Key: 36   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T053442
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067232882_j4y712ts_.bkp
  SPFILE Included: Modification time: 2021-03-15:04:52:58
  SPFILE db_unique_name: RMANDB

Limpando arquivos de backup:

RMAN> DELETE BACKUP OF SPFILE;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
33      33      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnsnf_TAG20210315T053319_j4y6yhp9_.bkp
35      35      1   1   AVAILABLE   DISK        /tmp/spfile.ora
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnsnf_TAG20210315T053319_j4y6yhp9_.bkp RECID=33 STAMP=1067232799
deleted backup piece
backup piece handle=/tmp/spfile.ora RECID=35 STAMP=1067232881
Deleted 2 objects

Por último, podemos gerar um pfile (arquivo editável no S.O) usando como referência o SPFILE ou até mesmo os valores que estão em memória:

RMAN> CREATE PFILE='/oracle/18.0.0/product/dbs/bss.ora' FROM SPFILE;
 
Statement processed
 
RMAN> host 'ls /oracle/18.0.0/product/dbs/bss.ora';
 
/oracle/18.0.0/product/dbs/bss.ora
host command complete
 
RMAN> host 'cat /oracle/18.0.0/product/dbs/bss.ora';
 
RMANDB.__data_transfer_cache_size=0
RMANDB.__db_cache_size=1107296256
RMANDB.__inmemory_ext_roarea=0
RMANDB.__inmemory_ext_rwarea=0
RMANDB.__java_pool_size=16777216
RMANDB.__large_pool_size=33554432
RMANDB.__oracle_base='/oracle/18.0.0/base'#ORACLE_BASE set from environment
RMANDB.__pga_aggregate_target=536870912
RMANDB.__sga_target=1610612736
RMANDB.__shared_io_pool_size=83886080
RMANDB.__shared_pool_size=352321536
RMANDB.__streams_pool_size=0
*.audit_file_dest='/oracle/18.0.0/base/admin/RMANDB/adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_file_record_keep_time=90
*.control_files='/oracle/dados/RMANDB/controlfile/o1_mf_h8nytrhd_.ctl','/oracle/fra/RMANDB/controlfile/o1_mf_h8nytrty_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/dados'
*.db_name='RMANDB'
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/oracle/18.0.0/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RMANDBXDB)'
*.log_archive_dest_1='LOCATION=/oracle/archives'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_date_format='YYYY-MM-DD:HH24:MI:SS'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1536m
*.undo_tablespace='UNDOTBS1'
host command complete

RMAN> CREATE PFILE='/oracle/18.0.0/product/dbs/bss1.ora' FROM MEMORY;
 
using target database control file instead of recovery catalog
Statement processed
 
RMAN> host 'ls /oracle/18.0.0/product/dbs/bss1.ora';
 
/oracle/18.0.0/product/dbs/bss1.ora
host command complete
 
RMAN> host 'cat /oracle/18.0.0/product/dbs/bss1.ora';
 
# Oracle init.ora parameter file generated by instance RMANDB on 03/15/2021 05:41:57
__data_transfer_cache_size=0
__db_cache_size=1056M
__inmemory_ext_roarea=0
__inmemory_ext_rwarea=0
__java_pool_size=16M
__large_pool_size=32M
__oracle_base='/oracle/18.0.0/base' # ORACLE_BASE set from environment
__pga_aggregate_target=512M
__sga_target=1536M
__shared_io_pool_size=80M
__shared_pool_size=336M
__streams_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='18.0.0'
_diag_adr_trace_dest='/oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/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
_hang_resolution_scope='OFF' # _hang_resolution_scope updated by kjznhm
_improved_outerjoin_card=TRUE
_improved_row_length_enabled=TRUE
_index_join_enabled=TRUE
_key_vector_create_pushdown_threshold=20000
_ksb_restart_policy_times='0'
_ksb_restart_policy_times='60'
_ksb_restart_policy_times='120'
_ksb_restart_policy_times='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
_odci_index_pmo_rebuild=FALSE # domain index pmo rebuild
_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'
_sqltune_category_parsed='DEFAULT' # parsed sqltune_category
_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='/oracle/18.0.0/base/admin/RMANDB/adump'
audit_trail='DB'
compatible='18.0.0'
connection_brokers='((TYPE=DEDICATED)(BROKERS=1))'
connection_brokers='((TYPE=EMON)(BROKERS=1))' # connection_brokers default value
control_file_record_keep_time=90
control_files='/oracle/dados/RMANDB/controlfile/o1_mf_h8nytrhd_.ctl'
control_files='/oracle/fra/RMANDB/controlfile/o1_mf_h8nytrty_.ctl'
core_dump_dest='/oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/cdump'
db_block_size=8192
db_create_file_dest='/oracle/dados'
db_name='RMANDB'
db_recovery_file_dest='/oracle/fra'
db_recovery_file_dest_size=2G
diagnostic_dest='/oracle/18.0.0/base'
dispatchers='(PROTOCOL=TCP) (SERVICE=RMANDBXDB)'
log_archive_dest_1='LOCATION=/oracle/archives'
log_archive_format='%t_%s_%r.dbf'
log_buffer=7588K # log buffer update
nls_date_format='YYYY-MM-DD:HH24:MI:SS'
nls_language='AMERICAN'
nls_territory='AMERICA'
open_cursors=300
optimizer_mode='ALL_ROWS'
pga_aggregate_target=512M
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=300
query_rewrite_enabled='TRUE'
remote_login_passwordfile='EXCLUSIVE'
result_cache_max_size=7872K
sga_target=1536M
undo_tablespace='UNDOTBS1'
host command complete
 
RMAN>

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish