ORACLE实例RENAME,DB_LINK同步(Materialized View,Snapshot)51CTO博客 - 牛牛娱乐

ORACLE实例RENAME,DB_LINK同步(Materialized View,Snapshot)51CTO博客

2019年03月10日12时35分19秒 | 作者: 紫雪 | 标签: 实例,同步,数据库 | 浏览: 337

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 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
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 Copyright (c) 1991, 2009, Oracle.  All rights reserved. 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-rr.  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 Copyright (c) 1982, 2009, Oracle.  All rights reserved. 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:

验证:

#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 Copyright (c) 1991, 2009, Oracle.  All rights reserved. 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 Copyright (c) 1991, 2009, Oracle.  All rights reserved. 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 #-在TEST上 #-按ROWID建同步 SQL> create materialized view stu_mv refresh force start with sysdate next
  2   sysdate+1/1440 with rowid as select * from u01.stu@CONN_BITC;

 
Materialized view created
#-在BITC上 SQL> insert into stu values(1,TOM);
 
1 row inserted
 
SQL> commit;
 
Commit complete
#-在TEST上 SQL> select * from stu_mv;
 
                                     ID NAME
-
                                      1 TOM
#-4.快照同步
#有主键#
#-在BITC上

SQL> show user
User is "u01"
 
SQL> create table test1(id int primary key,name varchar(30));
 
Table created
 
SQL> create snapshot log on test1;
 
Materialized view log created
 
SQL> insert all into test1 values(1,egg) into test1 values(2,apple)
  2  select * from dual;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> #-在TEST上 SQL> create snapshot sn_test1 as select * from u01.test1@CONN_BITC;
 
Materialized view created
 
SQL> alter snapshot sn_test1 refresh fast start with sysdate next sysdate+1/1440
  2   with primary key;
 
Materialized view altered
 #具有主键,仿制是增量的
SQL> select * from sn_test1;
 
                                     ID NAME
-
                                      1 egg
                                      2 apple
 
SQL> #-在BITC上 SQL> insert into test1 values(3,fish);
 
1 row inserted
 
SQL> commit;
 
Commit complete #-在TEST上 SQL> select * from sn_test1;
ID NAME
-
1 egg
2 apple SQL> /
 
                                     ID NAME
-
                                      1 egg
                                      2 apple
 
SQL> /
 
                                     ID NAME
-
                                      1 egg
                                      2 apple
                                      3 fish
#无主键# #-在BITC上 SQL> create table test2(id int,name varchar(30));
 
Table created
 
SQL> insert into test2 values(1,beef);
 
1 row inserted
 
SQL> commit;
 
Commit complete#-在TEST上
SQL> create snapshot sn_test2 refresh complete start with sysdate
  2  next sysdate+1/1440 with rowid as select * from u01.test2@conn_bitc;
 
Materialized view created
#-complete,无增量改写
SQL> select * from sn_test2;
 
                                     ID NAME
-
                                      1 beef
 
#snapshot与备份差异#
热备份 热备份是在数据库运转的情况下,选用archivelog mode办法备份数据库的办法。所以,假如你有昨天夜里的一个冷备份并且又有今日的热备份文件,在发作问题时,就可以运用这些材料康复更多的信息。热备份要求数据库在Archivelog办法下操作,并需求很多的档案空间。一旦数据库运转在archivelog状况下,就可以做备份了。热备份的指令文件由三部分组成: 1.数据文件一个表空间一个表空间的备份: (1)设置表空间为备份状况;
(2)备份表空间的数据文件;
(3)回复表空间为正常状况。 2.备份归档log文件: (1)暂时中止归档进程;
(2)log下那些在archive rede log方针目录中的文件;
(3)从头发动archive进程;
(4)备份归档的redo log文件。
3.用alter database bachup controlfile指令来备份操控文件: 热备份的长处是: 1.可在表空间或数据库文件级备份,备份的时刻短。
2.备份时数据库仍可运用。
3.可到达秒级康复(康复到某一时刻点上)。
4.可对简直一切数据库实体做康复。
5.康复是快速的,在大多数情况下爱数据库仍作业时康复。 热备份的缺乏是: 1.不能犯错,不然后果严重;
2. 若热备份不成功,所得成果不行用于时刻点的康复;
3. 因难于保护,所以要特别细心当心,不允许“以失利告终”。

5.大局数据库名,数据库效劳名
#-1.数据库名DB_NAME 办法一:select name from v$database; 办法二:show parameter db 办法三:检查参数文件 #-2.数据库实例名 实例名也被写入参数文件中,该参数为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主机衔接串的时分,就需求指定实例名。当然8i今后版别的网络组件要求运用的是效劳名SERVICE_NAME。 #-3.数据库域名 数据库域名在存在于参数文件中,他的参数是db_domain.查询数据库域名 办法一:select value from v$parameter where name = db_domain; 办法二:show parameter domain 办法三:在参数文件中查询 #-4.数据库效劳名 从oracle9i版别开端,引入了一个新的参数,即数据库效劳名。参数名是SERVICE_NAME。 假如数据库有域名,则数据库效劳名就是大局数据库名;不然,数据库效劳名与数据库名相同。查询数据库效劳名 办法一:select value from v$parameter where name = service_name; 办法二:show parameter service_name #-5.大局数据库名=数据库名+数据库域名
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章