修改数据库名
查看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
#全局数据库名=数据库名+数据库域名
