各位大蝦。。如何修改oracle的靜態(tài)參數(shù)文件，煩請賜教？？

你是用那種參數(shù)文件的呢。pfile？還是spfile？（用show

新邵網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、APP開發(fā)、成都響應式網(wǎng)站建設等網(wǎng)站項目制作，到程序開發(fā)，運營維護。創(chuàng)新互聯(lián)于2013年成立到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設就選創(chuàng)新互聯(lián)。

parameter

spfile

可以查看）

如果你是用spfile，在數(shù)據(jù)庫open的狀態(tài)下使用alter

system

set

parameter=value

scope=??

就可以更改了

如果你是使用pfile，修改$ORACLE_HOME/dbs/initSID.ora這個就可以了，用vim就可以改

還要注意你需要修改的參數(shù)是靜態(tài)的還是動態(tài)的

怎么修改oracle 的全局事務協(xié)議屬性參數(shù)

最好的方法是批量修改,即每次修改5000條(一次修改不要超過一萬條,否則影響性能).

雖然在11g中，我們也可以選擇使用merge命令，但你的這種情況最好先修改一部分然后看看影響，畢竟在生產(chǎn)環(huán)境作這樣的操作風險很大。

如果是誤操作，最好還是請DBA來恢復，雖然這樣做會被挨罵，但總比錯上加錯，最后連挨罵的機會都沒有要好得多。

如果對這些修改真的有信心，而只是從性能考慮，那可以用下面的方法(pk_col 是表的主鍵)：

merge into xxx aa

using (select pk_col from xxx) bb

on (aa.pk_col=bb.pk_col)

when matched then

update set aa.datatype=66 where aa.datatype is null;

如何修改oracle參數(shù)sessions

先備份spfile

1.通過SQLPlus修改

Oracle的sessions和processes的關(guān)系是

sessions=1.1*processes + 5

使用sys，以sysdba權(quán)限登錄：

SQL show parameter processes;

NAME TYPE VALUE

------------------------------------ ----------- ---------------------------------------

aq_tm_processes integer 1

db_writer_processes integer 1

job_queue_processes integer 10

log_archive_max_processes integer 1

processes integer 150

SQL alter system set processes=400 scope = spfile;

系統(tǒng)已更改。

SQL show parameter processes;

NAME TYPE VALUE

------------------------------------ ----------- -----------------------------------------

aq_tm_processes integer 1

db_writer_processes integer 1

job_queue_processes integer 10

log_archive_max_processes integer 1

processes integer 150

SQL create pfile from spfile;

文件已創(chuàng)建。

重啟數(shù)據(jù)庫,OK！

【注:sessions是個派生值,由processes的值決定,公式sessions=1.1*process + 5】

2.通過修改oracle配置文件進行修改

修改SPFILEORCL.ORA文件中的processes的值。8.1.5中是init.ora文件，在9i中修改init.ora文件是無效的，這個文件由于是一個二進制的文件，不能直接使用notepad此類的編輯器打開。

否則會報錯誤ORA-27101 Shared memory realm does not exist。使用UltraEdit或者EditPlus之類的可以編輯二進制文件的編輯器打開此文件（直接編輯二進制文件），然后在Windows服務中重新啟動Oracle服務器即可。

3.通過Oracle Enterprise Manager Console在圖形化管理器中修改

a.以系統(tǒng)管理員的身份登入

b.進入數(shù)據(jù)庫的例程-配置-一般信息-所有初始化參數(shù)

c.修改processes的值

d.Sessions的值由公式sessions=1.1*process + 5派生得到

oracle中怎么重建控制文件或是修改控制文件參數(shù)

環(huán)境:

OS:Red Hat Linux As 5

DB:10.2.0.4

在全部控制文件丟失或損壞，而且沒有備份的情況下,可以使用重建控制文件的辦法打開數(shù)據(jù)庫.以下模擬所有的控制文件丟失的情況下重建控制文件.

1.備份控制文件(數(shù)據(jù)庫mount或是open狀態(tài))

SQL select status from v$instance;

STATUS

------------

OPEN

SQLalter database backup controlfile to trace as '/u01/ftp/bak_controlfile';

2.刪除控制文件

[oracle@hxl oracl]$ rm control01.ctl

rm: remove regular file `control01.ctl'? y

[oracle@hxl oracl]$ rm control02.ctl

rm: remove regular file `control02.ctl'? y

[oracle@hxl oracl]$ rm control03.ctl

rm: remove regular file `control03.ctl'? y

3.關(guān)閉數(shù)據(jù)庫后嘗試打開數(shù)據(jù)庫

SQL shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL startup

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 1221564 bytes

Variable Size 218106948 bytes

Database Buffers 511705088 bytes

Redo Buffers 2969600 bytes

ORA-00205: error in identifying control file, check alert log for more info

這個時候數(shù)據(jù)無法打開,以為我們已經(jīng)刪除了控制文件.

4.查看備份控制文件的內(nèi)容

[oracle@hxl ftp]$ more bak_controlfile

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="oracl"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=2

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_10=ENABLE

--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

-- Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORACL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/oracl/system01.dbf',

'/u01/app/oracle/oradata/oracl/undotbs01.dbf',

'/u01/app/oracle/oradata/oracl/sysaux01.dbf',

'/u01/app/oracle/oradata/oracl/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/oracl/system01.dbf',

'/u01/app/oracle/oradata/oracl/undotbs01.dbf',

'/u01/app/oracle/oradata/oracl/sysaux01.dbf',

'/u01/app/oracle/oradata/oracl/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

5.從備份控制文件中提取我們需要的部分,這里我們選擇RESETLOGS,將如下內(nèi)容保存文件為

create_confile.sql

CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/oracl/system01.dbf',

'/u01/app/oracle/oradata/oracl/undotbs01.dbf',

'/u01/app/oracle/oradata/oracl/sysaux01.dbf',

'/u01/app/oracle/oradata/oracl/users01.dbf'

CHARACTER SET WE8ISO8859P1

;

6.執(zhí)行create_confile.sql

SQLset sqlblanklines on -- 因為文件中有空行,需要將該選項打開,否則執(zhí)行的時候報語法錯誤

SQL@/u01/ftp/create_confile.sql

SQL alter database open resetlogs;

Database altered.

說明:

重建控制文件后,若備份信息是存儲在控制文件的,該信息會丟失.

網(wǎng)頁名稱：如何修改oracle參數(shù),oracle定義參數(shù)
網(wǎng)頁路徑：http://www.rwnh.cn/article36/dsdjspg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、全網(wǎng)營銷推廣、網(wǎng)站營銷、網(wǎng)站排名、軟件開發(fā)、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)