HOW TO BACKUP AN INITIALIZATION PARAMETER FILE BY QUERYING A DATABASE VIEW

A query to list all of the parameter values currently used by an instance.

QUERY

V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance.

SET LINES 300 
SET PAGES 999
SET FEEDBACK OFF
COL "INITIALIZATION PARAMETERS" FOR A110

SELECT '*.' || NAME || '=' || DISPLAY_VALUE "INITIALIZATION PARAMETERS"
FROM V$SYSTEM_PARAMETER WHERE ISDEFAULT='FALSE' ORDER BY 1;

QUERY EXAMPLE

INITIALIZATION PARAMETERS
--------------------------------------------------------------------------------------------------------------
*.audit_file_dest=/u01/app/oracle/admin/tst19c/adump
*.audit_trail=DB
*.compatible=19.0.0
*.control_files=/u01/app/oracle/oradata/TST19C/control01.ctl, /u01/app/oracle/oradata/TST19C/control02.ctl
*.db_block_size=8192
*.db_domain=vmware.local
*.db_name=tst19c
*.diagnostic_dest=/u01/app/oracle
*.dispatchers=(PROTOCOL=TCP) (SERVICE=tst19cXDB)
*.local_listener=LISTENER_TST19C
*.nls_language=AMERICAN
*.nls_territory=AMERICA
*.open_cursors=300
*.pga_aggregate_target=512M
*.processes=300
*.remote_login_passwordfile=EXCLUSIVE
*.sga_target=2G
*.undo_tablespace=UNDOTBS1

An initialization parameter file contains exactly the same parameter values

SQL> !cat $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

tst19c.__data_transfer_cache_size=0
tst19c.__db_cache_size=1342177280
tst19c.__inmemory_ext_roarea=0
tst19c.__inmemory_ext_rwarea=0
tst19c.__java_pool_size=0
tst19c.__large_pool_size=16777216
tst19c.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tst19c.__pga_aggregate_target=536870912
tst19c.__sga_target=2147483648
tst19c.__shared_io_pool_size=134217728
tst19c.__shared_pool_size=637534208
tst19c.__streams_pool_size=0
tst19c.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/tst19c/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TST19C/control01.ctl','/u01/app/oracle/oradata/TST19C/control02.ctl'
*.db_block_size=8192
*.db_domain='vmware.local'
*.db_name='tst19c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tst19cXDB)'
*.local_listener='LISTENER_TST19C'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.undo_tablespace='UNDOTBS1'