11.2.0.4的安装与配置 (RHEL 6.5)(5)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = 
    (GLOBAL_DBNAME = bdcadb.99bill.com)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
    (SID_NAME = bdcadb)
    )
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
      (PROGRAM=extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = bdcataf.99bill.com)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
    (SID_NAME = bdcadb)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

2. 开启归档模式
$ mkdir -p /u02/archive/bdcadb
$ sqlplus / as sysdba
alter system set log_archive_format = 'Arc_%t_%s_%r.arc' scope = spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u02/archive/bdcadb/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME='bdcadb';
alter system set log_archive_config = 'DG_CONFIG=( bdcadb,bdcadg)';
shutdown immediate

startup mount
alter database archive log;
alter database open;

3. TNSNAMES设置
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora

BDCADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdcadb.99bill.com)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bdcadb.99bill.com)
    )
  )

BDCADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdcadg.99bill.com)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bdcadb.99bill.com)
    )
  )
 
BDCATAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdcadb.99bill.com)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bdcataf.99bill.com)
    )
  )
LISTENER_BDCADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = bdcadb.99bill.com)(PORT = 1530))
然后拷贝到备库上面
4. 修改supplement的参数
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

5. 设置online redo logfile
每个节点创建4组logfile，每组logfile有2个member，每个member 512M

增加1个 group
alter database add logfile  group 5 ('/u02/oradata/bdcadb/redo05_1.log','/u02/oradata/bdcadb/redo05_2.log') size 512M;

使用以下命令查询status为INACTIVE状态的logfile并删除，如果不是，可以切换几次，并使用checkpoint命令状态改为INACTIVE
alter system checkpoint;
online redo logfile物理不会自动删除，需要进入ASM手工删除
select group#, thread#, bytes, members, archived, status from v$log;

删除group1,2,3
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

增加3个 group
alter database add logfile  group 1 ('/u02/oradata/bdcadb/redo01_1.log','/u02/oradata/bdcadb/redo01_2.log') size 512M;
alter database add logfile  group 2 ('/u02/oradata/bdcadb/redo02_1.log','/u02/oradata/bdcadb/redo02_2.log') size 512M;
alter database add logfile  group 3 ('/u02/oradata/bdcadb/redo03_1.log','/u02/oradata/bdcadb/redo03_2.log') size 512M;

6. 增加一个controlfile
默认只有2个controlfile，需要手工增加一个

alter system set control_files = '/u02/oradata/bdcadb/control01.ctl','/u02/oradata/bdcadb/control02.ctl','/u02/oradata/bdcadb/control03.ctl' scope = spfile

关闭INSTANCE，把controlfile拷贝一份
cp control01.ctl control03.ctl

启动数据库

7. profile改为unlimited
alter profile DEFAULT limit
  failed_login_attempts unlimited
  password_life_time unlimited
  password_lock_time unlimited
  password_grace_time unlimited;

8. TEMP的datafile增加
alter tablespace temp add tempfile '/u02/oradata/bdcadb/temp02.dbf' size 10000M autoextend on ;

9. 其他初始化参数
$ mkdir -p /u02/app/oracle
alter system set diagnostic_dest = '/u02/app/oracle';
alter system set utl_file_dir = '/tmp' scope = spfile;
alter system set processes = 4000 scope = spfile;
alter system set db_writer_processes = 4 scope = spfile;
alter system set open_cursors = 1000 scope = spfile;
alter system set undo_retention = 86400;
alter system set memory_target=0;
alter system set sga_max_size = 48G scope = spfile;
alter system set sga_target = 48G scope = spfile;
alter system set pga_aggregate_target = 12G;
alter system set db_file_multiblock_read_count = 16;
alter system set db_files = 2048 scope = spfile;
alter system set optimizer_index_caching = 90;
alter system set optimizer_index_cost_adj = 15;
alter system set log_archive_max_processes = 6;
alter system set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' scope = spfile;
alter system set standby_file_management = auto;

设置adump
$ mkdir -p /u02/app/oracle/admin/bdcadb/adump
alter system set audit_file_dest = '/u02/app/oracle/admin/bdcadb/adump' scope = spfile;