oracle数据库如何切换模式

上图为NAVICAT图形工具打开的ORACLE数据库，默认打开的库名为SYSTEM，我写程序也是默认打开的是SYSTEM库，怎么用SQL语句切换到BAR2008这个库？

推荐答案 2016-07-12

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

温馨提示：答案为网友推荐，仅供参考

当前网址：http://44.wendadaohang.com/zd/GYYDZWDG33YK6D6WGDR.html

其他回答

第1个回答推荐于2018-04-10

你新建一个连接，用户名输入的时候就写bar2008，填入相应的密码就行了。追问

用户名写BAR2008进不去，因为这个数据库不是我的，可能密码错误或者其他，我只能SYSTEM方式进去。

追答

你没有密码切换不了的。如果只有system的密码，那你只能每个表名前面加前缀BAR2008了。

另外system和bar2008这不叫库名，这叫用户名。
如果你的application所有数据都放在bar2008用户下，建议你还是要过来密码好了。

追问

执行SQL语句时，每个表名加前缀BAR2008，需要什么格式？比如我的表叫“SOFT” ，就是BAR2008_SOFT ？？？？

追答

bar2008.soft

格式就是
用户名.对象名

本回答被提问者和网友采纳

第2个回答 2014-03-12

命令行sqlplus可以用
conn BAR2008/密码@库名
切换

NAVICAT应该只是个查询工具，你要切换过去干啥呢，如果要访问直接用BAR2008.表名就可以了，或者对它建同义词访问追问

这个数据库是一个软件的数据库，不是我创建的，我用NAVICAT是要了解数据库里的详细结构等等。我自己要写个软件来操作这个数据库，但是我写的软件登陆进去时在SYSTEM下，我要的所有数据都在BAR2008下，还有这个SYSTEM和BAR2008应该叫库名吧。

追答

根据你的说法，我理解是你有2个用户，一个是system用户，可能用于你软件的用户权限认证，领一个是BAR2008，用于业务数据存储与访问。这样的话你需要同时知道两个用户的密码，或者用管理员给system用户赋权查询所有表的权限，简单点可以赋予dba的权限，及可以跨用户查询了

还有system应该是oracle自带的用户，应该具备管理员权限，你试试能不能查询ALL_TABLES这个表

追问

我现在执行SQL语句访问SYSTEM下的所有数据都可以读到，就是BAR2008下任意数据都读不到，我是菜鸟，不会用SQL语句读其他用户下的数据。所以还请帮忙写个SQL语句例子，加注释。

追答

select * from ALL_TABLES 看看

第3个回答 2014-03-13

直接在你写的软件里写明是修改哪个schema下的Table就好了，
e.g.
update BAR2008.AUTOLOGOFF set loginname='XXX' ;
select * from BAR2008.BASE_COUNTRY ;

相似回答

大家正在搜