ORACLE實例分享：DB

　　修改數據庫名
查看oracle實例名
shutdown immediate
lsnrctl stop $ORACLE_SID#windows下關閉使用oracle服務
修改 /etc/oratab 的$ORACLE_SID#windows沒有
修改用戶環境變量bashprofile  #windows沒有
linux下$ORACLE_HOME/dbs 修改有關$ORACLE_SID的文件名
  windows下$ORACLE_HOME/database 修改有關$ORACLE_SID的文件名
重新生成密碼文件啟動數據庫
liunxorapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries= force=y
windows
在一個cmd運行oracle %ORACLE_ORCL%
在另一個cmd運行
set ORACLE_HOME=E:appcswggodproductdbhome_database
set ORACLE_ORCL=orcl
orapwd file=%ORACLE_HOME%/ORACLE_HOME/dbs/orapw%ORACLE_SID% password=oracle entries= force=y
startup 
 
#選擇orcl實例
[oracle@h ~]$ sqlplus "/as SYSDBA"
SQL*Plus: Release Production on Mon Nov ::
Copyright (c) Oracle  All rights reserved

　　Connected to:
Oracle Database g Enterprise Edition Release bit Production
With the Partitioning OLAP Data Mining and Real Application Testing options
SQL>
#查看實例
SQL> select * from v$thread; 
 

　　SQL> select instance_name from v$instance;
 
INSTANCE_NAME

orcl
#關機 

　　SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SQL>
#關閉監聽
[oracle@h ~]$ lsnrctl stop orcl
LSNRCTL for Linux: Version Production on NOV ::
Copyright (c) Oracle  All rights reserved
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
The command completed successfully
#修改/etc/oratab
[oracle@h ~]$ su root
Password:
[root@h oracle]# vi /etc/oratab
#orcl 修改為bitc
bitc:/app/oracle/product//dbhome_:N
test:/app/oracle/product//dbhome_:N
"/etc/oratab" L C written
[root@h oracle]#
#修改bash_profile並生效
[root@h oracle]# su oracle
[oracle@h ~]$ pwd
/home/oracle
[oracle@h ~]$ vi bash_profile
# bash_profile
# Get the aliases and functions
if [ f ~/bashrc ]; then
        ~/bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product//dbhome_
export ORACLE_SID=bitc
export PATH=$ORACLE_HOME/bin::$PATH
"bash_profile" L C written                            
[oracle@h ~]$ bash_profile
[oracle@h ~]$ echo $ORACLE_SID
bitc
#修改配置文件名
[oracle@h ~]$ cd $ORACLE_HOME/dbs
[oracle@h dbs]$ ls al
total
drwxrxrx  oracle oinstall Nov :
drwxrxrx oracle oinstall Nov :
rwrw  oracle oinstall Nov : hc_DBUAdat
rwrw  oracle oinstall Nov : hc_orcldat
rwrw  oracle oinstall Nov : hc_testdat
rwrr  oracle oinstall May   initora
rwr  oracle oinstall Nov  : initorclora
rwr  oracle oinstall   Nov  : lkORCL
rwr  oracle oinstall   Nov : lkTEST
rwr  oracle oinstall Nov : orapworcl
rwr  oracle oinstall Nov : orapwtest
rwr  oracle oinstall Nov : spfileorclora
rwr  oracle oinstall Nov  : spfileorclorabak
rwr  oracle oinstall Nov : spfiletestora
[oracle@h dbs]$ chmod *
[oracle@h dbs]$ mv hc_orcldat hc_bitcdat
[oracle@h dbs]$ mv lkORCL lkBITC
[oracle@h dbs]$ mv orapworcl orapwbitc
[oracle@h dbs]$ mv spfileorclora spfilebitcora
[oracle@h dbs]$
[oracle@h dbs]$ ls
hc_bitcdat   initora      lkTEST     spfilebitcora
hc_DBUAdat  initorclora  orapwbitc  spfileorclorabak
hc_testdat   lkBITC        orapwtest  spfiletestora
#生成密碼文件
[oracle@h dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries= force=y
#啟動數據庫
[oracle@h dbs]$ echo $ORACLE_SID
bitc
[oracle@h dbs]$ sqlplus "/as SYSDBA"
SQL*Plus: Release Production on Mon Nov ::
Copyright (c) Oracle  All rights reserved
Connected to an idle instance
SQL> startup
ORA: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started
Total System Global Area  bytes
Fixed Size                  bytes
Variable Size             bytes
Database Buffers          bytes
Redo Buffers                bytes
Database mounted
Database opened
SQL> exit
數據庫更名後配置靜態監聽
[oracle@h dbs]$ netmgr
#配置監聽文件
 

　　配置遠程namespace
 
驗證 
 
#修改靜態監聽參數

　　SQL> show parameter listener;
NAME                                 TYPE        VALUE

listener_networks                    string
local_listener                       string      BITC
remote_listener                      string
SQL> alter system set local_listener="BITC";
System altered
SQL> alter system register;
System altered
SQL> exit
Disconnected from Oracle Database g Enterprise Edition Release bit Production
With the Partitioning OLAP Data Mining and Real Application Testing options
#啟動實例監聽
[oracle@h dbs]$ lsnrctl start BITC
LSNRCTL for Linux: Version Production on NOV ::
Copyright (c) Oracle  All rights reserved
TNS: Listener using listener name orcl has already been started
[oracle@h dbs]$ lsnrctl status BITC
LSNRCTL for Linux: Version Production on NOV ::
Copyright (c) Oracle  All rights reserved
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))
STATUS of the LISTENER

Alias                     orcl
Version                   TNSLSNR for Linux: Version Production
Start Date                NOV ::#啟動日期 
Uptime                    days hr min sec#正常運行時間
Trace Level               off                                         #跟蹤級別
Security                  ON: Local OS Authentication#安全性
SNMP                      OFF                              
Listener Parameter File   /app/oracle/product//dbhome_/network/admin/listenerora#監聽程序參數文件
Listener Log File         /app/oracle/diag/tnslsnr/h/orcl/alert/logxml#監聽程序日志文件
Listening Endpoints Summary #監聽端點摘要
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))
Services Summary                     #服務摘要
Service "bitc" has instance(s)
  Instance "bitc" status UNKNOWN has handler(s) for this service
Service "orcl" has instance(s)
  Instance "bitc" status READY has handler(s) for this service
Service "orclXDB" has instance(s)
  Instance "bitc" status READY has handler(s) for this service
The command completed successfully
 

　　
##
動態監聽默認多個實例只能共享這個端口
在netmgr中配置namespace所有實例的主機名和端口必須完全一致否則一實例可能堵塞其他實例監聽
注在位oracle配置位oracle的遠程listener可能因為版本問題失敗

　　
Materialized View同步
#在TEST上
select * from dba_db_links;
 

　　#host 已變成BITC不是ORCLdb_link失效

　　SQL> alter public database link conn_orcl connect to u identified by abc;
alter public database link conn_orcl connect to u identified by abc
ORA: user names cannot be changed in ALTER DATABASE LINK command
SQL> drop public database link conn_orcl;
Database link dropped
SQL> create public database link CONN_BITC connect to u identified by abc using BITC;
 
Database link created
#在BITC上

　　SQL> conn system/manager
Connected to Oracle Database g Enterprise Edition Release
Connected as system
 
SQL> grant dba to u;
 
Grant succeeded
 
SQL> conn  u/abc
Connected to Oracle Database g Enterprise Edition Release
Connected as u
SQL> create table t(id int primary keyname varchar());
 
Table created
 
SQL> create materialized view log on t;
 
Materialized view log created
 #在TEST上建立物化視圖由於ORCL數據未錄入
#t_mv無數據
SQL>  select * from Ut@CONN_BITC;
 
                                     ID NAME

#按主鍵建同步
 
SQL> create materialized view t_mv refresh fast start with sysdate next sysdate+/
     with primary key as select * from ut@conn_bitc;
 
Materialized view created
SQL> select * from t_mv;
ID NAME

#在BITC上插入數據提交
SQL> insert into t values(chal);
 
row inserted
 
SQL> commit;
 
Commit complete
#在TEST上每一分鐘同步t_mv未到同步時間數據未同步
SQL> select * from t_mv;
 
                                     ID NAME

#在TEST上經過每一分鐘t_mv到同步時間數據由ORCL同步到TEST
 
SQL> select * from t_mv;
 
                                     ID NAME

                                      chal
 

　　
##
#在BITC上

　　
SQL> show user
User is "u"
SQL> create table stu (id intname varchar());
 
Table created
#log日志建立必須有主鍵
SQL> create materialized view log on stu;
 
create materialized view log on stu
 
ORA: table STU does not contain a primary key constraint
#在TEST上
#按ROWID建同步
SQL> create materialized view stu_mv refresh force start with sysdate next
     sysdate+/ with rowid as select * from ustu@CONN_BITC;
 
Materialized view created
#在BITC上
SQL> insert into stu values(TOM);
 
row inserted
 
SQL> commit;
 
Commit complete
#在TEST上
SQL> select * from stu_mv;
 
                                     ID NAME

                                      TOM

　　
#快照同步
#有主鍵#
#在BITC上

　　SQL> show user
User is "u"
 
SQL> create table test(id int primary keyname varchar());
 
Table created
 
SQL> create snapshot log on test;
 
Materialized view log created
 
SQL> insert all into test values(egg) into test values(apple)
    select * from dual;
 
rows inserted
 
SQL> commit;
 
Commit complete
 
SQL>
#在TEST上
SQL> create snapshot sn_test as select * from utest@CONN_BITC;
 
Materialized view created
 
SQL> alter snapshot sn_test refresh fast start with sysdate next sysdate+/
     with primary key;
 
Materialized view altered
 #擁有主鍵復制是增量的
SQL> select * from sn_test;
 
                                     ID NAME

                                      egg
                                      apple
 
SQL>
#在BITC上
SQL> insert into test values(fish);
 
row inserted
 
SQL> commit;
 
Commit complete
#在TEST上
SQL> select * from sn_test;
ID NAME

egg
apple
SQL> /
 
                                     ID NAME

                                      egg
                                      apple
 
SQL> /
 
                                     ID NAME

                                      egg
                                      apple
                                      fish

　　#無主鍵#
#在BITC上
SQL> create table test(id intname varchar());
 
Table created
 
SQL> insert into test values(beef);
 
row inserted
 
SQL> commit;
 
Commit complete
#在TEST上

　　SQL> create snapshot sn_test refresh complete start with sysdate
    next sysdate+/ with rowid as select * from utest@conn_bitc;
 
Materialized view created
#complete無增量刷新

　　SQL> select * from sn_test;
 
                                     ID NAME

                                      beef 

#snapshot與備份區別#
熱備份
熱備份是在數據庫運行的情況下采用archivelog mode方式備份數據庫的方法所以如果你有昨天夜裡的一個冷備份而且又有今天的熱備份文件在發生問題時就可以利用這些資料恢復更多的信息熱備份要求數據庫在Archivelog方式下操作並需要大量的檔案空間一旦數據庫運行在archivelog狀態下就可以做備份了熱備份的命令文件由三部分組成
數據文件一個表空間一個表空間的備份
()設置表空間為備份狀態
()備份表空間的數據文件
()回復表空間為正常狀態
備份歸檔log文件
()臨時停止歸檔進程
()log下那些在archive rede log目標目錄中的文件
()重新啟動archive進程
()備份歸檔的redo log文件
用alter database bachup controlfile命令來備份控制文件
熱備份的優點是
可在表空間或數據庫文件級備份備份的時間短
備份時數據庫仍可使用
可達到秒級恢復(恢復到某一時間點上)
可對幾乎所有數據庫實體做恢復
恢復是快速的在大多數情況下愛數據庫仍工作時恢復
熱備份的不足是
不能出錯否則後果嚴重
若熱備份不成功所得結果不可用於時間點的恢復
因難於維護所以要特別仔細小心不允許“以失敗告終”

　　全局數據庫名數據庫服務名
#數據庫名DB_NAME
方法一:select name from v$database;
方法二show parameter db
方法三查看參數文件
#數據庫實例名
實例名也被寫入參數文件中該參數為instance_name在winnt平台中實例名同時也被寫入注冊表
在一般情況下數據庫名和實例名是一對一的關系但如果在oracle並行服務器架構(即oracle實時應用集群)中數據庫名和實例名是一對多的關系(HA)
方法一select instance_name from v$instance;
方法二show parameter instance
方法三在參數文件中查詢
數據庫實例名與ORACLE_SID
雖然兩者都表是oracle實例但兩者是有區別的instance_name是oracle數據庫參數而ORACLE_SID是操作系統的環境變量ORACLD_SID用於與操作系統交互也就是說從操作系統的角度訪問實例名必須通過ORACLE_SID在winnt不台ORACLE_SID還需存在於注冊表中
 
但ORACLE_SID必須與instance_name的值一致否則你將會收到一個錯誤在unix平台是“ORACLE not available”在winnt平台是“TNS:協議適配器錯誤”數據庫實例名與網絡連接
數據庫實例名除了與操作系統交互外還用於網絡連接的oracle服務器標識當你配置oracle主機連接串的時候就需要指定實例名當然i以後版本的網絡組件要求使用的是服務名SERVICE_NAME
#數據庫域名
數據庫域名在存在於參數文件中他的參數是db_domain查詢數據庫域名
方法一select value from v$parameter where name = db_domain;
方法二show parameter domain
方法三在參數文件中查詢
#數據庫服務名
從oraclei版本開始引入了一個新的參數即數據庫服務名參數名是SERVICE_NAME
如果數據庫有域名則數據庫服務名就是全局數據庫名否則數據庫服務名與數據庫名相同查詢數據庫服務名
方法一select value from v$parameter where name = service_name;
方法二show parameter service_name
#全局數據庫名=數據庫名+數據庫域名

From:http://tw.wingwit.com/Article/program/Oracle/201311/19017.html