关于修改数据库参数

最新推荐文章于 2023-08-02 15:27:51 发布

congman1570

最新推荐文章于 2023-08-02 15:27:51 发布

阅读量410

点赞数

修改数据库参数时务必要做好参数文件备份。
SQL> create pfile='****' from spfile;
免得引起不必要的麻烦
从pfile文件起库
SQL> startup pfile='******' ;
没有做参数文件备份，可从二进制文件spfile文件查找相关参数进行恢复。

[oracle@oracle11z ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 09:39:30 2015

Connected to an idle instance.

SQL> startup
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed
SQL> exit
Disconnected

这时没有做参数文件备份
[root@oracle11z dbs]# cat spfiletest
"ytest.__db_cache_size=708837376
test.__java_pool_size=4194304
test.__large_pool_size=8388608
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=327155712
test.__sga_target=977272832
test.__shared_io_pool_size=0
test.__shared_pool_size=239075328
test.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u02CC"}e/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/u02/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'
*.fal_server='STANDBY'
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(test,testb)'
*.log_archive_dest_1=''
*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=325058560
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=977272832
*.undo_tablespace='UNDOTBS1'
"ytest.__db_cache_size=708837376
test.__java_pool_size=4194304
test.__large_pool_size=8388608
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=327155712
test.__sga_target=977272832
test.__shared_io_pool_size=0
test.__shared_pool_size=239075328
test.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u02CC"}e/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/u02/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'
*.fal_server='STANDBY'
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(test,testb)'
*.log_archive_dest_1=''
*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=325058560
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=977272832
*.undo_tablespace='UNDOTBS1'
更正相应错误参数，创建pfile文件

vi ***
test.__db_cache_size=708837376
test.__java_pool_size=4194304
test.__large_pool_size=8388608
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=327155712
test.__sga_target=977272832
test.__shared_io_pool_size=0
test.__shared_pool_size=239075328
test.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u02^AC^@^@C" ^@^@^C^@^@^@^@^@^@^@^@^@^A^D}e^@^@/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/u02/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'
*.fal_server='STANDBY'
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(test,testb)'
*.log_archive_dest_1=''
*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=325058560
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=977272832
*.undo_tablespace='UNDOTBS1'
[oracle@oracle11z dbs]$ vi pfile.ora
test.__db_cache_size=708837376
test.__java_pool_size=4194304
test.__large_pool_size=8388608
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=327155712
test.__sga_target=977272832
test.__shared_io_pool_size=0
test.__shared_pool_size=239075328
test.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u02/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/u02/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'
*.fal_server='STANDBY'
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(test,testb)'
*.log_archive_dest_1=''
*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=325058560
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=977272832
*.undo_tablespace='UNDOTBS1'
~
从pfile启动

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile.ora';
ORACLE instance started.

Total System Global Area 972898304 bytes
Fixed Size                  2259160 bytes
Variable Size             608175912 bytes
Database Buffers          356515840 bytes
Redo Buffers                5947392 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

创建spfile文件

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile.ora';

File created.
正常重启数据库

参数更正可参考首次启动时报错。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30134870/viewspace-1478373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30134870/viewspace-1478373/