有dba权限,存储过程中引用视图v$session提示表不存在

需要显示授权
grand select on sys.V_$SESSION to xxx;
说明:不能直接授权给v$session
原因:v$session是公共同义词,不能被授权
(可以通过select * from dba_objects where object_name=’V$SESSION’;查询出来)
note:11gr2版本存在该问题,其他版本暂时未知

发表在 Oracle | 评论关闭

数据文件位置调整

1、关闭数据库
shutdown immediate;
2、移动数据文件
mv /u01/ora10g/oradata/ggtarget /u02/ora10g/oradata
3、数据库至于nomount状态
startup nomount;
4、创建pfile
create pfile from spfile;
5、修改pfile中control_files
6、使用pfile把数据库至于mount状态
shutdown immediate;
startup mount pfile=/u01/ora10g/product/10.2.0/db_1/dbs/initggtarge.ora;
7、修改文件位置

--查询select name from v$datafile;
alter database rename file '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf';
alter database rename file '/u01/ora10g/oradata/ggtarge/undotbs01.dbf' to '/u02/ora10g/oradata/ggtarge/undotbs01.dbf';
alter database rename file '/u01/ora10g/oradata/ggtarge/sysaux01.dbf' to '/u02/ora10g/oradata/ggtarge/sysaux01.dbf';
alter database rename file '/u01/ora10g/oradata/ggtarge/users01.dbf' to '/u02/ora10g/oradata/ggtarge/users01.dbf';
 --查询select member from v$logfile;
alter database rename file '/u01/ora10g/oradata/ggtarge/redo01.log' to '/u02/ora10g/oradata/ggtarge/redo01.log';
alter database rename file '/u01/ora10g/oradata/ggtarge/redo02.log' to '/u02/ora10g/oradata/ggtarge/redo02.log';
--查询select name from v$tempfile;
alter database rename file '/u01/ora10g/oradata/ggtarge/temp01.dbf' to '/u02/ora10g/oradata/ggtarge/temp01.dbf';

8、创建spfile
create spfile to pfile;
9、重启数据库使用spfile启动
shutdown immediate;
startup;

发表在 Oracle备份恢复 | 评论关闭

ssh等效连接配置

1、配置SSH(两台机器上均需执行)
A、以ORACLE用户登陆

mkdir ~/.ssh
chmod 700 ~/.ssh

B、使用SSH-KEYGEN生成SSH协议的RSA密钥

ssh-keygen -t rsa

2、添加密钥到授权密钥文件(任意一台机器上执行)
以下三条命令在11.1.1.2上执行

ssh 11.1.1.2 cat /home/oracle/.ssh/id_rsa.pub>>authorized_keys
ssh 11.1.1.3 cat /home/oracle/.ssh/id_rsa.pub>>authorized_keys
scp authorized_keys 11.1.1.3:/home/oracle/.ssh/

chmod 600 ~/.ssh/authorized_keys (两台机器均需要执行)

3、测试(两台机器上均执行)

ssh 11.1.1.2 date
ssh 11.1.1.3 date

如果不要求输入密码就出现系统日期,说明SSH配置成功(第一次可能需要输入yes)

发表在 Linux | 评论关闭