1、在不退出sqlplus的情況下,直接在命令行輸入conn username/password;即可完成切換。
成都創(chuàng)新互聯(lián)公司主要從事網(wǎng)站制作、成都做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)沾化,十多年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575
2、在退出sqlplus的情況下,windows下:set ORACLE_SID=db_name; linux下:export ORACLE_SID = db_name再登錄相應(yīng)的用戶即可完成切換。
3、創(chuàng)建用戶命令:create user usernamer identified by passwd;
4、給用戶賦權(quán)限:grant connect,resource,dba to username;
直接登錄相應(yīng)的用戶就可以了。
步驟:直接在命令行輸入sqlconn
username/password;即可完成切換.
解釋:每個用戶都只能默認一個數(shù)據(jù)庫,不可能出現(xiàn)多個數(shù)據(jù)庫。所以直接切換到需要的用戶下即可完成操作。
oracle dg 三大模式切換
1、最大性能模式MAXIMUM PERFORMANCE --默認模式,最大性能模式特點。
192.168.1.181
SQL?select?database_role,protection_mode,protection_level?from?v$database;
DATABASE_ROLE??PROTECTION_MODE???PROTECTION_LEVEL
----------------?--------------------?--------------------
PRIMARY?????MAXIMUM?PERFORMANCE?MAXIMUM?PERFORMANCE
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_config??????????string???dg_config=(orcl,db01)
log_archive_dest_1??????????string???location=/home/oracle/arch_orc
l?valid_for=(all_logfiles,all_
roles)?db_unique_name=orcl
log_archive_dest_2??????????string???service=db_db01?LGWR?ASYNC?val
id_for=(online_logfiles,primar
y_roles)?db_unique_name=db01
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???31
Next?log?sequence?to?archive??33
Current?log?sequence??????33
192.168.1.183
SQL?select?database_role,protection_mode,protection_level?from?v$database;
DATABASE_ROLE??PROTECTION_MODE???PROTECTION_LEVEL
----------------?--------------------?--------------------
PHYSICAL?STANDBY?MAXIMUM?PERFORMANCE?MAXIMUM?PERFORMANCE
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_config??????????string???dg_config=(db01,orcl)
log_archive_dest_1??????????string???location=/home/oracle/arch_db0
1?valid_for=(all_logfiles,all_
roles)?db_unique_name=db01
log_archive_dest_2??????????string???service=db_orcl?LGWR?ASYNC?val
id_for=(online_logfiles,primar
y_roles)?db_unique_name=orcl
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???31
Next?log?sequence?to?archive??33
Current?log?sequence??????33
192.168.1.181
SQL?alter?system?switch?logfile;
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???32
Next?log?sequence?to?archive??34
Current?log?sequence??????34
192.168.1.183
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_db01
Oldest?online?log?sequence???32
Next?log?sequence?to?archive??0
Current?log?sequence??????34
2 、最大性能模式--切換到--最大高可用 ?(默認是最大性能模式---MAXIMUM PERFORMANCE)。
192.168.1.181
SQL?select?DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL?from?v$database;?
DATABASE_ROLE??PROTECTION_MODE???PROTECTION_LEVEL
----------------?--------------------?--------------------
PRIMARY?????MAXIMUM?PERFORMANCE?MAXIMUM?PERFORMANCE
SQL?show?parameter?log_archive_dest_2
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_dest_2??????????string???service=db_db01?LGWR?ASYNC?val
id_for=(online_logfiles,primar
y_roles)?db_unique_name=db01
192.168.1.181
SQL?shutdown?immediate
192.168.1.183
SQL?alter?database?recover?managed?standby?database?cancel;
SQL?shutdown?immediate
192.168.1.181
SQL?startup?mount;
SQL?alter?database?set?standby?database?to?maximize?availability;
SQL?alter?system?set?log_archive_dest_2='service=db_db01?LGWR?SYNC?valid_for=(online_logfiles,primary_roles)?db_unique_name=db01'?scope=spfile;
192.168.1.183
SQL?startup?nomount
SQL?alter?database?mount?standby?database;
SQL?alter?system?set?log_archive_dest_2='service=db_orcl?LGWR?SYNC?valid_for=(online_logfiles,primary_roles)?db_unique_name=orcl'?scope=spfile;
SQL?shutdown?immediate
SQL?startup?nomount
SQL?alter?database?mount?standby?database;
192.168.1.181
SQL?startup
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive_dest_2
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_dest_2??????????string???service=db_db01?LGWR?SYNC?vali
d_for=(online_logfiles,primary
_roles)?db_unique_name=db01
SQL?select?database_role,protection_level,protection_mode?from?v$database;
DATABASE_ROLE??PROTECTION_LEVEL???PROTECTION_MODE
----------------?--------------------?--------------------
PRIMARY?????MAXIMUM?AVAILABILITY?MAXIMUM?AVAILABILITY
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???34
Next?log?sequence?to?archive??36
Current?log?sequence??????36
192.168.1.183
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive_dest_2
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_dest_2??????????string???service=db_orcl?LGWR?SYNC?vali
d_for=(online_logfiles,primary
_roles)?db_unique_name=orcl
SQL?select?database_role,protection_level,protection_mode?from?v$database;
DATABASE_ROLE??PROTECTION_LEVEL???PROTECTION_MODE
----------------?--------------------?--------------------
PHYSICAL?STANDBY?MAXIMUM?AVAILABILITY?MAXIMUM?AVAILABILITY
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_db01
Oldest?online?log?sequence???35
Next?log?sequence?to?archive??0
Current?log?sequence??????36
192.168.1.181
SQL?alter?system?switch?logfile;
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???35
Next?log?sequence?to?archive??37
Current?log?sequence??????37
192.168.1.183
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_db01
Oldest?online?log?sequence???36
Next?log?sequence?to?archive??0
Current?log?sequence??????37
3、最大高可用--切換到--最保護能模式,DG最大保護模式Maximum protection。
192.168.1.181
SQL?shutdown?immediate
192.168.1.183
SQL?shutdown?immediate
192.168.1.181
SQL?alter?database?set?standby?database?to?maximize?protection;
SQL?shutdown?immediate
192.168.1.183
SQL?startup?nomount
SQL?alter?database?mount?standby?database;
192.168.1.181
SQL?startup
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive_dest_2
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_dest_2??????????string???service=db_db01?LGWR?SYNC?vali
d_for=(online_logfiles,primary
_roles)?db_unique_name=db01
SQL?select?database_role,protection_level,protection_mode?from?v$database;
DATABASE_ROLE??PROTECTION_LEVEL???PROTECTION_MODE
----------------?--------------------?--------------------
PRIMARY?????MAXIMUM?PROTECTION??MAXIMUM?PROTECTION
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???37
Next?log?sequence?to?archive??39
Current?log?sequence??????39
192.168.1.183
SQL?col?dest_name?for?a25
SQL?select?dest_name,status?from?v$archive_dest_status;
DEST_NAME?????????STATUS
-------------------------?---------
LOG_ARCHIVE_DEST_1????VALID
LOG_ARCHIVE_DEST_2????VALID
SQL?show?parameter?log_archive_dest_2
NAME?????????????????TYPE????VALUE
------------------------------------?-----------?---------------------------
log_archive_dest_2??????????string???service=db_db01?LGWR?SYNC?vali
d_for=(online_logfiles,primary
_roles)?db_unique_name=db01
SQL?select?database_role,protection_level,protection_mode?from?v$database;
DATABASE_ROLE??PROTECTION_LEVEL???PROTECTION_MODE
----------------?--------------------?--------------------
PRIMARY?????MAXIMUM?PROTECTION??MAXIMUM?PROTECTION
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_db01
Oldest?online?log?sequence???37
Next?log?sequence?to?archive??0
Current?log?sequence??????39
192.168.1.181
SQL?alter?system?switch?logfile;
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_orcl
Oldest?online?log?sequence???38
Next?log?sequence?to?archive??40
Current?log?sequence??????40
192.168.1.183
SQL?archive?log?list
Database?log?mode???????Archive?Mode
Automatic?archival???????Enabled
Archive?destination??????/home/oracle/arch_db01
Oldest?online?log?sequence???37
Next?log?sequence?to?archive??0
Current?log?sequence??????40
你這個屬于主、備機切換
1.?察看主庫狀態(tài)
select?switchover_status?from?v$database;
收集主庫上的臨時表空間的情況,原因是備庫激活后臨時文件可能丟失,需要手工建上去:
col?file_name?format?a40
select?file_name,tablespace_name,bytes/1024/1024?from?dba_temp_files;
FILE_NAME????????????????????????????????TABLESPACE_NAME??????BYTES/1024/1024
----------------------------------------?--------------------?---------------
/data/oradata/alihr/temp01.dbf???????????TEMP????????????????????????????2048
2.切換主庫到standby
alter?database?commit?to?switchover?to?physical?standby;
或:
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PHYSICAL?STANDBY?WITH?SESSION?SHUTDOWN;
shutdown?immediate;
startup?nomount;
alter?database?mount?standby?database;
3.驗證要被切換的standby是否接收到switch?to?通知
SELECT?SWITCHOVER_STATUS?FROM?V$DATABASE;?
以前的狀態(tài)就是SESSIONS?ACTIVE,現(xiàn)在就變?yōu)門O?PRIMARY
4.切換物理standby到主用模式,檢查redo?log是否創(chuàng)建好
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PRIMARY;
shutdown?immediate;
startup;
5.?standby數(shù)據(jù)庫切換成主庫后,檢查是否需要、對臨時表空間增加臨時文件:
先檢查臨時文件是否丟失:
col?file_name?format?a60
select?file_name,tablespace_name?from?dba_temp_files;
把結(jié)果前面原主庫上的臨時文件進行對比,如有丟失則使用如下命令增加:
alter?tablespace?temp?add?tempfile?'/data/oradata/alihr/temp02.dbf'?size?2048M?reuse;
正常情況下,如果db_file_name_convert參數(shù)設(shè)置正確的話,11g會自動建立temp?file
6.在新的standby機器上
alter?system?set?log_archive_dest_state_2='defer';
alter?database?recover?managed?standby?database?disconnect?from?session;
7.檢查主備庫中fal參數(shù)
fal_server服務(wù)名是在standby機器的tnsnames中,指向主庫,fal_client是在主庫上的tnsnames中,指向standby。
fal參數(shù)只在standby機器上生效,所以在standby機器上fal_server指向主庫,fal_client機器指向備庫.
而主庫上的fal參數(shù)雖然不生效,但為了避免主庫切換成standby時,我們還要修改fal參數(shù),所以現(xiàn)在就設(shè)置好,
讓fal_server指向現(xiàn)在的standby數(shù)據(jù)庫,fal_client指向自己。
8.在新的主庫
alter?system?set?log_archive_dest_state_2='enable';
alter?system?archive?log?current;?
然后到備庫上檢查備庫機器上日志是否正常的傳過來了。
9.檢查和調(diào)整主備庫兩臺機器中的crontab中的數(shù)據(jù)庫備份腳本和刪除歸檔腳本。
=================oracle11g的最大保護和最大可用模式下的切換==================
如果主庫是意外宕機的,則直接把備庫切換成主庫:
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?CANCEL;
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?FINISH;
shutdown?immediate;
connect?/?as?sysdba;
startup?mount;
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PRIMARY;
shutdown?immediate;
connect?/?as?sysdba;
startup?mount;
ALTER?DATABASE?SET?STANDBY?DATABASE?TO?MAXIMIZE?AVAILABILITY;
alter?database?open;
注意:上面操作中是手工的把數(shù)據(jù)庫重新置成MAXIMIZE?AVAILABILITY,否則數(shù)據(jù)庫起來后是最大性能模式。
檢查數(shù)據(jù)庫的角色:
select?database_role?from?v$database;
原有主庫啟動后,如果不是硬盤壞,主庫上的數(shù)據(jù)還存在,則把主庫轉(zhuǎn)換成standby就可以了:
主庫上:
startup?mount;
recover?automatic?database;
ALTER?DATABASE?CONVERT?TO?PHYSICAL?STANDBY;
shutdown?immediate;
注意千萬不要把主庫打開了,否則會導(dǎo)致主庫的SCN發(fā)生變化,無法轉(zhuǎn)換成standby數(shù)據(jù)庫了。
在把原先的主庫轉(zhuǎn)化為standby時,有時可能報如下錯誤:
SQL?alter?database?recover?managed?standby?database?finish;
alter?database?recover?managed?standby?database?finish
*
ERROR?at?line?1:
ORA-00283:?recovery?session?canceled?due?to?errors
ORA-19909:?datafile?1?belongs?to?an?orphan?incarnation
ORA-01110:?data?file?1:?'/opt/oracle/oradata/oratest/system01.dbf'
這里把到rman中:
list?incarnation?of?database;
reset?database?to?incarnation?1;
recover?database;
reset?database?to?incarnation?2;
=========================================================
failover在物理standby的切換
1.檢查standby看是否使用了standby?log
2.有standby?log,執(zhí)行下面的命令
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?CANCEL;
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?FINISH;
2.1?沒有standby?log則不執(zhí)行上面的
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?FINISH?SKIP?STANDBY?LOGFILE;
3.?切換
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PRIMARY;
3.1?如果上面3步驟失敗,則
ALTER?DATABASE?ACTIVATE?STANDBY?DATABASE;
4.?重啟db
shutdown?immediate
startup
switchover的方法:
主庫和物理standby的切換
1.察看主庫狀態(tài)
SELECT?SWITCHOVER_STATUS?FROM?V$DATABASE;
2.切換
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PHYSICAL?STANDBY;
3.原主庫
shutdown?immediate;
startup?nomount
alter?database?mount?standby?database;
4.驗證要被切換的standby是否接收到switch?to?通知
SELECT?SWITCHOVER_STATUS?FROM?V$DATABASE;?
增加online?redo日志
5.切換物理standby到主
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PRIMARY;
shutdown?immediate;
startup;
6.在新的standby機器(也就是老的主庫)
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?DISCONNECT?FROM?SESSION;
7.在新的主庫
alter?system?archive?log?current;?
========active?standby?database===========
當:
alter?database?activate?standby?database;
原來的主庫只能通過閃回轉(zhuǎn)化成standby?database,但要求數(shù)據(jù)庫的flashback打開。
在新主庫上:
select?to_char(standby_became_primary_scn)?from?v$database;
在舊主庫上:
SHUTDOWN?IMMEDIATE;
STARTUP?MOUNT;
FLASHBACK?DATABASE?TO?SCN?standby_became_primary_scn;
ALTER?DATABASE?CONVERT?TO?PHYSICAL?STANDBY;
select?max(sequence#)?from?v$log_history?where?RESETLOGS_TIME=(select?max(RESETLOGS_TIME)?from?v$log_history);?
實例恢復(fù):
recover?managed?standby?database?disconnect?using?current?logfile;
=================lgwr設(shè)置====================================
alter?system?set?log_archive_dest_3?=?'location=/disk3/arch/bopscha?reopen=2?MAX_FAILURE=3';
alter?system?set?log_archive_dest_state_3?=?alternate;
alter?system?set?log_archive_dest_1?=?'location=/disk2/arch/bopscha?alternate=log_archive_dest_3?reopen=60?MAX_FAILURE=5'?;
*.log_archive_dest_2='SERVICE=DTMRT?LGWR?ASYNC??VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)?DB_UNIQUE_NAME=DTMRT';
alter?database?add?standby?logfile?group?4?('/oracle/u02/ORA10GDG/STANDBYRD01.LOG')?size?200M;
select?group#,thread#,sequence#,archived,status?from?v$standby_log;
alter?database?set?standby?database?to?maximize?{availability?|?performance?|?protection};
select?protection_mode?from?v$database;
ORA-19527:
LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/oract/','/u02/oracle/oradata/oract/'
alter?system?set?log_archive_dest_2='SERVICE=bopsteststb?lgwr?sync?affirm';
select?frequency,?duration?from?v$redo_dest_resp_histogram?where?dest_id=2?and?frequency1;
SQL?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?CANCEL;
SQL?ALTER?DATABASE?OPEN;
SQL?ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?USING?CURRENT?LOGFILE?DISCONNECT;?
alter?database?convert?to?snapshot?standby;
alter?database?convert?to?physical?standby;
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?USING?CURRENT?LOGFILE?DISCONNECT;
alter?database?commit?to?switchover?to?physical?standby;
alter?database?activate?standby?database?finish?apply;
alter?database?recover?managed?standby?database?finish;?
舊主庫轉(zhuǎn)換成standby的步驟:
在新主庫上:
select?to_char(standby_became_primary_scn)?from?v$database;
在舊主庫上:
SHUTDOWN?IMMEDIATE;
STARTUP?MOUNT;
FLASHBACK?DATABASE?TO?SCN?standby_became_primary_scn;
ALTER?DATABASE?CONVERT?TO?PHYSICAL?STANDBY;
ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PRIMARY?WITH?SESSION?SHUTDOWN;
ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?FINISH?FORCE;
本文名稱:oracle如何切換庫,oracle 切換庫
文章來源:http://chinadenli.net/article46/hesieg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供定制開發(fā)、品牌網(wǎng)站建設(shè)、營銷型網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計公司、小程序開發(fā)、動態(tài)網(wǎng)站
聲明:本網(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)