ORACLE 如何单机转RAC

生产库停监听 
[oracle@primary backup1]$ lsnrctl stop 
生产库启停数据库 
shutdown immediate 
Startup mount 
 
生产库全库备份： 
rmanbackup.sh 
export ORACLE_BASE=/oracle/app 
export ORACLE_HOME=/oracle/app/product/11.2.0/db_1 
export ORACLE_SID=test 
rman target / log=/backup1/rman.log << EOF  
run{ 
backup database format '/backup1/fulldb_test_%U.bak'; 
} 
exit 
EOF 
 
将生产库控制文件及备份片拷贝至目标端asm中 
scp fulldb_test_01uighoh_1_1.bak 192.168.142.150:/backup 
scp fulldb_test_02uighoo_1_1.bak 192.168.142.150:/backup 
scp control01.ctl 192.168.142.150:/tmp 
ASMCMD> mkdir test 
ASMCMD> cp /tmp/control01.ctl +data/test 
ASMCMD> cp control01.ctl control02.ctl 
 
建立adump路径，使用更改过的pfile将数据库启动至mount状态（一二节点都要创建哦） 
[oracle@rac1 tmp]$ export ORACLE_SID=test 
SQL> startup mount pfile='/tmp/pfile.ora'; 
 
restore数据文件 
vi rmanrestore.sh 
export ORACLE_BASE=/oracle/app 
export ORACLE_HOME=/oracle/app/product/11.2.0/db_1 
export ORACLE_SID=test 
rman target / log=/backup/rman.log << EOF  
run{ 
CATALOG BACKUPPIECE '/backup/fulldb_test_08uhucij_1_1.bak'; 
CATALOG BACKUPPIECE '/backup/fulldb_test_08uhucij_1_1.bak'; 
set newname for datafile 1 to '+data/test/system01.dbf'; 
set newname for datafile 2 to '+data/test/sysaux01.dbf'; 
set newname for datafile 3 to '+data/test/undotbs01.dbf'; 
set newname for datafile 4 to '+data/test/users01.dbf'; 
restore database;      
switch datafile all; 
} 
exit 
EOF 
 
chmod 775 rmanrestore.sh  
恢复数据文件，确保所有数据文件均恢复出来 
关库 
 
更改参数文件 
*.audit_file_dest='/oracle/app/admin/test/adump' 
*.audit_trail='db' 
*.compatible='11.2.0.4.0' 
*.cluster_database=true 
*.control_files='+data/test/control01.ctl','+data/test/control02.ctl' 
*.db_block_size=8192 
*.db_domain='' 
*.db_name='test' 
*.diagnostic_dest='/oracle/app' 
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)' 
*.job_queue_processes=0 
*.log_archive_dest_state_2='ENABLE' 
*.memory_target=490140544 
*.open_cursors=300 
*.processes=150 
*.remote_login_passwordfile='EXCLUSIVE' 
*.standby_file_management='AUTO' 
*.log_archive_dest_1='location=+data/test' 
test1.instance_number=1 
test2.instance_number=2 
*.log_archive_dest_1='location=+data' 
*.remote_listener='rac-scan:1521' 
test2.thread=2 
test1.thread=1 
test2.undo_tablespace='UNDOTBS2' 
test1.undo_tablespace='UNDOTBS1' 
 
使用新的参数文件将数据库启动至mount状态 
[oracle@rac1 tmp]$ export ORACLE_SID=test1 
[oracle@rac1 tmp]$ sqlplus / as sysdba 
SQL> startup mount pfile='/tmp/pfiletest.ora'; 
 
创建一节点日志组 
alter database add logfile  thread 1   group 10 '+DATA' size 50M; 
alter database add logfile  thread 1   group 11 '+DATA' size 50M; 
alter database add logfile  thread 1   group 12 '+DATA' size 50M; 
select GROUP#,THREAD#,bytes/1024/1024,status from v$log; 
 
删除掉原库状态为inactive的日志组 
只将原库状态为CURRENT的日志组拷贝至目标端asm中 
alter database drop logfile group 1; 
alter database drop logfile group 3; 
scp redo02.log 192.168.142.150:/backup/ 
ASMCMD> cp /backup/redo02.log +data/test 
 
rename日志文件 
alter database rename file '/oracle/oradata/test/redo02.log' to '+DATA/test/redo02.log'; 
 
recover database; 
alter database open; 
 
增加二节点日志组 
alter database add logfile  thread 2   group 13 '+DATA' size 50M; 
alter database add logfile  thread 2   group 14 '+DATA' size 50M; 
alter database add logfile  thread 2   group 15 '+DATA' size 50M; 
select GROUP#,THREAD#,bytes/1024/1024,status from v$log; 
 
删除原有日志组 
alter database drop logfile group 2; 
 
添加二节点undo 
create undo tablespace undotbs2 datafile '+DATA/test/undotbs02.dbf' size 10M autoextend on; 
 
激活二节点undo 
alter database enable thread 2; 
 
刷新rac组件 
spool on  
spool catclust.txt 
@?/rdbms/admin/catclust.sql 
spool off 
select comp_name,version,status from dba_registry 
 
修改参数文件 
create spfile='+data/test/spfiletest.ora' from pfile='/tmp/pfiletest.ora'; 
cd $ORACLE_HOME/dbs 
vi inittest1.ora 
SPFILE='+DATA/test/spfiletest.ora' 
 
cd $ORACLE_HOME/dbs 
vi inittest2.ora 
SPFILE='+DATA/test/spfiletest.ora' 
 
关闭数据库，重启双节点实例 
 
将新的实例添加至集群服务中 
[oracle@rac1 ~]$ srvctl add database -d test -o /oracle/app/product/11.2.0/db_1  
[oracle@rac1 ~]$ srvctl add instance -d test -i test1 -n rac1 
[oracle@rac1 ~]$ srvctl add instance -d test -i test2 -n rac2 
 
数据库参数优化 
修改db_files 的值 
alter system set db_files=1024 scope=spfile sid=’*’; 
 
密码过期时间，从11g开始，oracle对数据库所有密码默认过期时间180天： 
SQL> alter profile default limit  PASSWORD_LIFE_TIME unlimited; 
 
密码登陆错误次数，对于输入错误密码导致数据库账号被锁定： 
SQL> alter profile default limit  FAILED_LOGIN_ATTEMPTS unlimited; 
 
密码大小写敏感，该参数默认值是TRUE，因此，默认情况下密码大小写是敏感的 
SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false sid='*'; 
 
密码错误延迟登录，11G引入了延迟密码验证，在输入错误的密码后，后续如果还是采用错误的密码登陆，将会导致密码延迟验证，从第三次开始，后续的每次登陆导致密码延迟1秒左右 
而且会导致失败登陆延长，可以通过如下事件来屏蔽密码的延迟验证 
SQL> ALTER SYSTEM SET event='28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE=SPFILE SID='*'; 
 
Oracle的审计从11g开始，默认为开启，建议关闭： 
SQL> alter system set audit_trail=none scope=spfile sid='*'; 
 
关闭Resource Manager该特性为11g新特性，用来给特定的资源组分配指定的CPU配额，容易引起等待事件：RESMGR:cpu quantum，导致数据库响应慢。CPU耗尽 
ALTER SYSTEM SET "_resource_manager_always_on"=FALSE SCOPE=SPFILE SID='*'; 
alter system set "_resource_manager_always_off"=true scope=spfile SID='*'; 
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');  
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN',''); 
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');  
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN',''); 
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');  
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN',''); 
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN',''); 
 
ALTER SYSTEM SET deferred_segment_creation=FALSE SCOPE=SPFILE SID='*'; 
 
ALTER SYSTEM SET parallel_force_local=TRUE SCOPE=BOTH; 
 
alter system  set "_serial_direct_read"=never scope=spfile sid='*'; 
 
alter system set "_external_scn_rejection_threshold_hours"=1 scope=spfile sid='*'; 
alter system set "_external_scn_logging_threshold_seconds"=600 scope=spfile sid='*';