ora-nnnnn 错误记录

发表于 2011 年 1 月 13 日 由 惜分飞

一、ORA-00257解决
原因：空间不足错误
解决：检查数据文件和日志文件的存储空间，发现问题，进行添加相应数据文件或者对归档日志重新设置归档路径
注意：在10g及其以上版本中，可能是闪回日志文件（系统默认2g）不足
解决方案一：关闭闪回日志的功能
alter database flashback off;
解决方案二：增大闪回日志文件
alter system set DB_RECOVERY_FILE_DEST_SIZE=10g
解决方案三：修改闪回日志文件到一个大目录中
ALTER SYSTEM SET db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’

二、ORA-16038: 日志 3 序列号 5035 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 3 线程 1:
……REDO03.LOG
当启动数据库时，有类此上述错误
原因：日志组3的数据文件损坏或丢失
解决：
1、查看alter文件
2、查询v$log视图，确定给日志文件是否归档
3、如果已经归档
用CLEAR命令重建该日志文件
alter database clear logfile group 3;
4、如果没有归档需要先归档
alter database clear unarchived logfile group 3;
5、启动数据库
alter database open;

三、ORA-12162: TNS:net service name is incorrectly specified
新装数据库，使用sqlplus连接，出现上述错误
原因：环境变量ORACLE_SID没有设置
解决vi .profile
export ORACLE_SID=orcl

四、TNS-12555: TNS: permission denied error solution
现象：安装11g r2 的grid中
TNS-12555: TNS: permission denied
TNS-12560: TNS: protocol adapter error
TNS-00525: Insufficient privilege for operation
Linux Error: 1: Operation not permitted
解决：
chmod 777 / var / tmp / .oracle

五、TNS-12547: TNS:lost contact
现象：TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 104: Connection reset by peer
解决：在/etc/hosts中添加
127.0.0.1 localhost

发表在 ORA-xxxxx | 评论关闭

查询oracle中主外键列

发表于 2011 年 1 月 12 日 由 惜分飞

一、查询表主键列

select column_name
  from dba_cons_columns
 where constraint_name in (select constraint_name
                             from dba_constraints
                            where table_name = upper('tablename')
                              and constraint_type = 'P');

二、查询表外键列

select column_name
  from dba_cons_columns
 where constraint_name in (select constraint_name
                             from dba_constraints
                            where table_name = upper('tablename')
                              and constraint_type = 'R');

三、查询表中列

select column_name
  from dba_tab_columns
 where table_name = upper('tablename');

四、查询表之间的主外键关系

select b.table_name  主键表名,
       b.column_name 主键列名,
       a.table_name  外键表名,
       a.column_name 外键列名
  from (select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.constraint_name = b.constraint_name) a,
       (select distinct a.r_constraint_name, b.table_name, b.column_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.r_constraint_name = b.constraint_name) b
 where a.r_constraint_name = b.r_constraint_name;
发表在 Oracle | 评论关闭

goldengate通用配置

发表于 2011 年 1 月 10 日 由 惜分飞

一、理论
source

ADD EXTRACT <ext>, TRANLOG, BEGIN <time>, [, THREADS]
ADD EXTTRAIL <local_trail>, EXTRACT <ext>

EDIT PARAMS <ext>
-- Identify the Extract group:
EXTRACT <ext>
-- Specify database login information as needed for the database:
[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]
-- Specify the local trail that this Extract writes to:
EXTTRAIL <local_trail>
-- Specify tables to be captured:
TABLE <owner>.<table>;

ADD EXTRACT <pump_1>, EXTTRAILSOURCE <local_trail>, BEGIN <time>
ADD RMTTRAIL <remote_trail_1>, EXTRACT <pump_1>

EDIT PARAMS <pump_1>
-- Identify the data pump group:
EXTRACT <pump_1>
-- Specify database login information as needed for the database:
[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]
-- Specify the name or IP address of the first target system:
RMTHOST <target_1>, MGRPORT <portnumber>
-- Specify the remote trail on the first target system:
RMTTRAIL <remote_trail_1>
-- Allow mapping, filtering, conversion or pass data through as-is:
[PASSTHRU | NOPASSTHRU]
-- Specify tables to be captured:
TABLE <owner>.<table>;

target

edit params ./GLOBALS
CHECKPOINTTABLE <owner>.<tablename>

ADD REPLICAT <rep_1>, EXTTRAIL <remote_trail_1>, BEGIN <time> checkpointtable <owner>.<tablename>

edit params <rep_1>
-- Identify the Replicat group:
REPLICAT <rep_1>
-- State whether or not source and target definitions are identical:
SOURCEDEFS <full_pathname> | ASSUMETARGETDEFS
-- Specify database login information as needed for the database:
[TARGETDB <dsn_3>,] [USERID <user id>[, PASSWORD <pw>]]
-- Specify error handling rules:
REPERROR (<error>, <response>)
-- Specify tables for delivery:
MAP <owner>.<table>, TARGET <owner>.<table>[, DEF <template name>];

二、配置实例
source

add extract extl,tranlog,begin now
ADD EXTTRAIL /u01/ogg/dirdat/rl, EXTRACT EXTL
ADD TRANDATA chf.objce_t

edit params extl
extract extl
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg,password xifenfei
exttrail /u01/ogg/dirdat/rl
dynamicresolution
gettruncates
table chf.objce_t;

ADD EXTRACT pump1, EXTTRAILSOURCE /u01/ogg/dirdat/rl, BEGIN now
add rmttrail /u01/ogg/dirdat/rl extract pump1

edit params pump1
extract pump1
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg, password xifenfei
rmthost 192.168.1.111, mgrport 7809
rmttrail /u01/ogg/dirdat/rl
PASSTHRU
gettruncates
table chf.objce_t;

target

edit params ./GLOBALS
CHECKPOINTTABLE ogg.chkpoint

ADD CHECKPOINTTABLE ogg.chkpoint

add replicat repl exttrail /u01/ogg/dirdat/rl,begin now,checkpointtable ogg.chkpoint 

edit repl
replicat repl    
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")                           
userid ogg,password xifenfei                    
assumetargetdefs                          
reperror default,discard                      
discardfile /tmp/repsz.dsc,append,megabytes 100   
gettruncates                                                      
map chf.objce_t, target chf.objce_t;
发表在 GoldenGate | 评论关闭