dbca静默建库

某些项目现场上线前因初始规划错误、图形化较麻烦等原因，建议优先选择静默建库的方式

1、准备responsefile

静默建库最核心的是responsefile的修改，下面是responsefile需要的配置,加粗的条目按照项目需求修改

##数据库名，此配置会决定db_name和db_unique_name（若有dataguard，可以加上站点前缀，建完修改##db_name即可，db_unique_name修改较麻烦；若无dataguard，和sid一样即可） gdbName=pr_cc ##实例名 sid=cc ##数据库类型:一般是SI即单实例；或者集群用RAC/RACONENODE databaseConfigType=SI policyManaged=false createAsContainerDatabase=false ##$ORACLE_HOME/assistant/dbca/templates下，8K使用General_Purpose.dbc，16K使用New_Database.dbt templateName=/oracle/product/19c/assistants/dbca/templates/General_Purpose.dbc ##sys密码 sysPassword=ZTEsoft123 systemPassword=ZTEsoft123 ##存储类型FS/ASM storageType=FS ##数据文件目录 datafileDestination=/oradata ##若为ASM类型，需要指定磁盘组名 diskGroupName= ##字符集类型，一般是utf8 characterSet=AL32UTF8 nationalCharacterSet=AL16UTF16 ##变量，指定ORACLE_HOME等 variables=DB_UNIQUE_NAME=pr_cc,ORACLE_BASE=/oracle,PDB_NAME=,DB_NAME=pr_cc,ORACLE_HOME=/oracle/product/19c,SID=cc ##修改spfile参数 initParams=undo_tablespace=UNDOTBS1,db_block_size=8192BYTES,db_name=pr_cc,processes=1000,nls_language=AMERICAN,sga_target=12GB,pga_aggregate_target=10GB,dispatchers=(PROTOCOL=TCP) (SERVICE=ccXDB),diagnostic_dest={ORACLE_BASE},audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,nls_territory=AMERICA,compatible=19.3.0,audit_trail=none,remote_login_passwordfile=EXCLUSIVE,open_cursors=2000,session_cached_cursors=200 databaseType=MULTIPURPOSE automaticMemoryManagement=false

2、开始建库

dbca -silent -createDatabase -responseFile rb.rsp -ignorePreReqs

3、修改参数

##集成标准参数 alter system set "_cursor_obsolete_threshold"=1024 scope=spfile; alter system set "_serial_direct_read"=never scope=both ; alter system set "_resource_manager_always_off"=true scope=spfile; alter system set "_optimizer_use_feedback"=false scope=spfile; alter system set "_partition_large_extents"=false scope=spfile; alter system set "_undo_autotune"=false scope=spfile; alter system set "_use_adaptive_log_file_sync"=false scope=both; alter system set "_use_single_log_writer"=true scope=spfile; alter system set "_sys_logon_delay"=0 scope=spfile; alter system set undo_retention=18000 SCOPE=BOTH; alter system set open_cursors=2000 scope=spfile; alter system set session_cached_cursors=200 scope=spfile; alter system set audit_trail=none scope=spfile; alter system set optimizer_adaptive_plans=FALSE scope=spfile; alter system set deferred_segment_creation=FALSE scope=spfile; alter system set archive_lag_target=1200 scope=spfile; alter system set parallel_execution_message_size=16384 scope=spfile; alter system set temp_undo_enabled=FALSE scope=spfile; alter system set log_checkpoints_to_alert=TRUE scope=spfile; alter system set fast_start_mttr_target=300 scope=spfile; alter system set fast_start_parallel_rollback=HIGH scope=spfile; alter system set session_max_open_files=20 scope=spfile; alter system set db_create_file_dest='' scope=spfile; alter system set audit_trail=none scope=spfile; alter system set db_files=10000 scope=spfile; alter system set filesystemio_options=setall scope=spfile; alter system set sec_case_sensitive_logon=false scope=spfile; alter system set resource_manager_plan=''; alter system set "_report_capture_cycle_time"=0 scope=both; alter system set streams_pool_size=512M scope=spfile; alter system set log_buffer =268435456 scope=spfile; alter system set sga_target=0 scope=spfile; exec dbms_workload_repository.modify_snapshot_settings(interval=>30); exec dbms_workload_repository.modify_snapshot_settings(retention=>30*24*60); exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL); exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL); exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

##以下参数根据现场内存配置相应修改 ALTER SYSTEM SET sga_max_size=160G SCOPE=SPFILE; alter system set db_cache_size=100G scope=spfile; ALTER SYSTEM SET pga_aggregate_target=40g SCOPE=SPFILE; ALTER SYSTEM SET shared_pool_size=40G SCOPE=SPFILE; ALTER SYSTEM SET processes=8000 SCOPE=SPFILE;

4、重启数据库生效

5、修改redo为2G、12组（如果redo切换每小时超10次，需要设为4G）

alter database add logfile group 4 ('/ccredo/cc/redo_4_1.dbf') size 2G ; alter database add logfile group 5 ('/ccredo/cc/redo_5_1.dbf') size 2G ; alter database add logfile group 6 ('/ccredo/cc/redo_6_1.dbf') size 2G ; alter database add logfile group 7 ('/ccredo/cc/redo_7_1.dbf') size 2G ; alter database add logfile group 8 ('/ccredo/cc/redo_8_1.dbf') size 2G ; alter database add logfile group 9 ('/ccredo/cc/redo_9_1.dbf') size 2G ; alter database add logfile group 10 ('/ccredo/cc/redo_10_1.dbf') size 2G ; alter database add logfile group 11 ('/ccredo/cc/redo_11_1.dbf') size 2G ; alter database add logfile group 12 ('/ccredo/cc/redo_12_1.dbf') size 2G ;

删除并重建dbca自建的1、2、3 log组

alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system checkpoint; 等待1、2、3为inactive状态 set lines 400 pages 9999 col MEMBER for a40 select v1.status, v1.group#, v1.bytes/1024/1024,member,thread# ,sequence#, first_change# from v$log v1, v$logfile v2 where v1.group# = v2.group# order by v1.group#, member ; alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database add logfile group 1 ('/ccredo/cc/redo_1_1.dbf') size 2G reuse; alter database add logfile group 2 ('/ccredo/cc/redo_2_1.dbf') size 2G reuse; alter database add logfile group 3 ('/ccredo/cc/redo_3_1.dbf') size 2G reuse;

6、增加数据文件到undo、temp表空间

alter tablespace UNDOTBS1 add datafile '/oradata/cc/undotbs01.dbf' size 30G autoextend off,'/oradata/cc/undotbs02.dbf' size 30G autoextend off; alter TEMPORARY TABLESPACE TEMP add TEMPFILE '/oradata/cc/temp01.dbf' SIZE 30G AUTOEXTEND off,'/oradata/cc/temp02.dbf' SIZE 30G AUTOEXTEND off;

7、创建业务表空间、profile、业务用户等

附：rsp模板：

存储类型	block_size=8k	block_size=16k
FS	📎cc.rsp.txt	📎rb.rsp.txt
ASM	📎cc.rsp.txt	📎rb.rsp.txt