如何修改oracle參數,oracle定義參數
各位大蝦。。如何修改oracle的靜態參數文件，煩請賜教？？
你是用那種參數文件的呢。pfile？還是spfile？（用show
新邵網站制作公司哪家好，找創新互聯！從網頁設計、網站建設、微信開發、APP開發、成都響應式網站建設等網站項目制作，到程序開發，運營維護。創新互聯于2013年成立到現在10年的時間，我們擁有了豐富的建站經驗和運維經驗，來保證我們的工作的順利進行。專注于網站建設就選創新互聯。
parameter
spfile
可以查看）
如果你是用spfile，在數據庫open的狀態下使用alter
system
set
parameter=value
scope=??
就可以更改了
如果你是使用pfile，修改$ORACLE_HOME/dbs/initSID.ora這個就可以了，用vim就可以改
還要注意你需要修改的參數是靜態的還是動態的
怎么修改oracle 的全局事務協議屬性參數
最好的方法是批量修改,即每次修改5000條(一次修改不要超過一萬條,否則影響性能).
雖然在11g中，我們也可以選擇使用merge命令，但你的這種情況最好先修改一部分然后看看影響，畢竟在生產環境作這樣的操作風險很大。
如果是誤操作，最好還是請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參數sessions
先備份spfile
1.通過SQLPlus修改
Oracle的sessions和processes的關系是
sessions=1.1*processes + 5
使用sys，以sysdba權限登錄：
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;
系統已更改。
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;
文件已創建。
重啟數據庫,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.以系統管理員的身份登入
b.進入數據庫的例程-配置-一般信息-所有初始化參數
c.修改processes的值
d.Sessions的值由公式sessions=1.1*process + 5派生得到
oracle中怎么重建控制文件或是修改控制文件參數
環境:
OS:Red Hat Linux As 5
DB:10.2.0.4
在全部控制文件丟失或損壞，而且沒有備份的情況下,可以使用重建控制文件的辦法打開數據庫.以下模擬所有的控制文件丟失的情況下重建控制文件.
1.備份控制文件(數據庫mount或是open狀態)
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.關閉數據庫后嘗試打開數據庫
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
這個時候數據無法打開,以為我們已經刪除了控制文件.
4.查看備份控制文件的內容
[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,將如下內容保存文件為
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.執行create_confile.sql
SQLset sqlblanklines on -- 因為文件中有空行,需要將該選項打開,否則執行的時候報語法錯誤
SQL@/u01/ftp/create_confile.sql
SQL alter database open resetlogs;
Database altered.
說明:
重建控制文件后,若備份信息是存儲在控制文件的,該信息會丟失.
網頁名稱：如何修改oracle參數,oracle定義參數
網頁路徑：http://vcdvsql.cn/article36/dsdjspg.html
成都網站建設公司_創新互聯，為您提供商城網站、全網營銷推廣、網站營銷、網站排名、軟件開發、搜索引擎優化
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 優化人員如何修改百度已收錄的文章？ 2015-06-12
- 網站排名被百度收錄了沒流量怎么辦? 2013-12-28
- 網站上線后，如何實現網站快速收錄 2016-09-10
- 如何提高企業網站收錄量 2021-09-10
- seo優化時網站收錄少怎么辦？這些因素你都了解過嗎？ 2020-08-10
- 為什么新做的網站搜索引擎不收錄？ 2016-11-28
- 做好站內seo優化提高收錄 2013-05-26
- 網站收錄量增加關鍵詞排名為什么下降 2016-11-06
- 網站文章有收錄，為什么搜索標題卻找不到？ 2017-02-12
- 分析營銷型網站不被收錄的幾個主要原因 2016-08-24
- 九招讓搜索引擎快速收錄 2015-03-30
- 怎么寫文章才能更好地被搜索引擎收錄。成都網站建設 2016-10-28