当前位置：首页 > news >正文

ORACLE实例RENAME，DB_LINK同步(Materialized View,Snapshot）

news 来源：原创 2024/4/29 10:34:36

1.修改数据库名

1.查看oracle实例名
2.shutdown immediate
3.lsnrctl stop $ORACLE_SID#----------windows下关闭使用oracle服务
4.修改 /etc/oratab 的$ORACLE_SID#------------windows没有
5.修改用户环境变量.bashprofile #------------windows没有
6.linux下$ORACLE_HOME/dbs 修改有关$ORACLE_SID的文件名
windows下$ORACLE_HOME/database 修改有关$ORACLE_SID的文件名
7.重新生成密码文件，启动数据库
liunx：orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y
windows：
在一个cmd运行oracle %ORACLE_ORCL%
在另一个cmd运行
set ORACLE_HOME=E:\app\cswggod\product\11.2.0\dbhome_1\database
set ORACLE_ORCL=orcl
orapwd file=%ORACLE_HOME%/ORACLE_HOME/dbs/orapw%ORACLE_SID% password=oracle entries=5 force=y
startup

#-------------------1.选择orcl实例

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 12 03:07:58 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

#-------------2.查看实例

SQL> select * from v$thread;

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl
#-----------------3.关机

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

#-------------4.关闭监听

[oracle@h1 ~]$ lsnrctl stop orcl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 03:20:11

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1522)))
The command completed successfully

#--------------5.修改/etc/oratab

[oracle@h1 ~]$ su root
Password:
[root@h1 oracle]# vi /etc/oratab

#------------orcl 修改为bitc

bitc:/app/oracle/product/11.2.0/dbhome_2:N
test:/app/oracle/product/11.2.0/dbhome_2:N
"/etc/oratab" 24L, 764C written
[root@h1 oracle]#

#-----------------6.修改.bash_profile并生效

[root@h1 oracle]# su oracle

[oracle@h1 ~]$ pwd
/home/oracle

[oracle@h1 ~]$ 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/11.2.0/dbhome_2
export ORACLE_SID=bitc
export PATH=$ORACLE_HOME/bin:.:$PATH

".bash_profile" 16L, 323C written
[oracle@h1 ~]$ . .bash_profile

[oracle@h1 ~]$ echo $ORACLE_SID
bitc

#---------------------7.修改配置文件名

[oracle@h1 ~]$ cd $ORACLE_HOME/dbs
[oracle@h1 dbs]$ ls -al
total 56
drwxr-xr-x. 2 oracle oinstall 4096 Nov 12 03:18 .
drwxr-xr-x. 76 oracle oinstall 4096 Nov 10 00:52 ..
-rw-rw----. 1 oracle oinstall 1544 Nov 10 00:40 hc_DBUA0.dat
-rw-rw----. 1 oracle oinstall 1544 Nov 12 03:18 hc_orcl.dat
-rw-rw----. 1 oracle oinstall 1544 Nov 12 03:03 hc_test.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1906 Nov 9 23:35 initorcl.ora
-rw-r-----. 1 oracle oinstall 24 Nov 3 07:13 lkORCL
-rw-r-----. 1 oracle oinstall 24 Nov 10 00:45 lkTEST
-rw-r-----. 1 oracle oinstall 1536 Nov 10 06:40 orapworcl
-rw-r-----. 1 oracle oinstall 1536 Nov 10 04:23 orapwtest
-rw-r-----. 1 oracle oinstall 2560 Nov 12 03:04 spfileorcl.ora
-rw-r-----. 1 oracle oinstall 2560 Nov 9 23:05 spfileorcl.ora.bak
-rw-r-----. 1 oracle oinstall 3584 Nov 12 03:03 spfiletest.ora

[oracle@h1 dbs]$ chmod 777 *

[oracle@h1 dbs]$ mv hc_orcl.dat hc_bitc.dat
[oracle@h1 dbs]$ mv lkORCL lkBITC
[oracle@h1 dbs]$ mv orapworcl orapwbitc
[oracle@h1 dbs]$ mv spfileorcl.ora spfilebitc.ora
[oracle@h1 dbs]$

[oracle@h1 dbs]$ ls
hc_bitc.dat init.ora lkTEST spfilebitc.ora
hc_DBUA0.dat initorcl.ora orapwbitc spfileorcl.ora.bak
hc_test.dat lkBITC orapwtest spfiletest.ora

#-----------------8.生成密码文件

[oracle@h1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y

#---------------9.启动数据库

[oracle@h1 dbs]$ echo $ORACLE_SID
bitc
[oracle@h1 dbs]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 12 03:35:11 2012

Connected to an idle instance.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 501059584 bytes
Fixed Size                  2214736 bytes
Variable Size             318768304 bytes
Database Buffers          176160768 bytes
Redo Buffers                3915776 bytes
Database mounted.
Database opened.
SQL> exit

2.数据库更名后，配置静态监听

[oracle@h1 dbs]$ netmgr

#-------------------1.配置监听文件

配置远程namespace：
p_w_picpath

验证：

#----------------2.修改静态监听参数

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 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

#----------------------------3.启动实例监听
[oracle@h1 dbs]$ lsnrctl start BITC

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 05:01:08

TNS-01106: Listener using listener name orcl has already been started
[oracle@h1 dbs]$ lsnrctl status BITC

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 05:01:16

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     orcl
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                12-NOV-2012 04:48:54 #启动日期
Uptime                    0 days 0 hr. 12 min. 22 sec #正常运行时间
Trace Level               off                                         #跟踪级别
Security                  ON: Local OS Authentication #安全性
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora #监听程序参数文件
Listener Log File         /app/oracle/diag/tnslsnr/h1/orcl/alert/log.xml #监听程序日志文件
Listening Endpoints Summary... #监听端点摘要
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1522)))
Services Summary...                     #服务摘要
Service "bitc" has 1 instance(s).
Instance "bitc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "bitc", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "bitc", status READY, has 1 handler(s) for this service...
The command completed successfully

#-----------------------------------------------#

动态监听默认1521，多个实例只能共享这个端口；
在netmgr中配置namespace，所有实例的主机名和端口必须完全一致，否则一实例可能堵塞其他实例监听
注在64位oracle配置32位oracle的远程listener可能因为版本问题失败

3.Materialized View同步

#---------------------------------------在TEST上
select * from dba_db_links;

#---------------------host 已变成BITC，不是ORCL，db_link失效

SQL> alter public database link conn_orcl connect to u01 identified by abc;
alter public database link conn_orcl connect to u01 identified by abc
ORA-32598: 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 u01 identified by abc using 'BITC';

Database link created

#---------------------------------------在BITC上

SQL> conn system/manager
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system

SQL> grant dba to u01;

Grant succeeded

SQL> conn u01/abc
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as u01

SQL> create table t1(id int primary key,name varchar(30));

Table created

SQL> create materialized view log on t1;

Materialized view log created
#---------------------------------------在TEST上,建立物化视图，由于ORCL数据未录入，

#------------t1_mv无数据

SQL> select * from U01.t1@CONN_BITC;

ID NAME
--------------------------------------- ------------------------------

#-----------------按主键建同步

SQL> create materialized view t1_mv refresh fast start with sysdate next sysdate+1/1440
2 with primary key as select * from u01.t1@conn_bitc;

Materialized view created

SQL> select * from t1_mv;
ID NAME
--------------------------------------- ------------------------------

#---------------------------------------在BITC上，插入数据提交

SQL> insert into t1 values(1,'chal');

1 row inserted

SQL> commit;

Commit complete

#---------------------------------------在TEST上,每一分钟同步，t1_mv未到同步时间，数据未同步

SQL> select * from t1_mv;

ID NAME
--------------------------------------- ------------------------------

#---------------------------------------在TEST上,经过每一分钟，t1_mv到同步时间，数据由ORCL同步到TEST

SQL> select * from t1_mv;

ID NAME
--------------------------------------- ------------------------------
1 chal

#--------------------------------------------#

#---------------------------------------在BITC上

SQL> show user
User is "u01"

SQL> create table stu (id int,name varchar(30));

Table created

#------------------log日志建立必须有主键

SQL> create materialized view log on stu;

create materialized view log on stu

ORA-12014: table 'STU' does not contain a primary key constraint