月归档：三月 2012

Configuring an active standby pair with one subscriber

发表于 2012 年 3 月 6 日由惜分飞

Step 1: Create the DSNs for the master and the subscriber databases

[master1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[master2]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master2
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[subscriber1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate/subscriber1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

Step 2: Create a table in one of the master databases

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CREATE TABLE tab (a NUMBER NOT NULL,
       >    b CHAR(18),
       >    PRIMARY KEY (a));

Step 3: Define the active standby pair

Command>  CREATE ACTIVE STANDBY PAIR master1, master2
       >    SUBSCRIBER subscriber1;

Step 4: Start the replication agent on a master database

Command>  CALL ttRepStart;

Step 5: Set the state of a master database to ‘ACTIVE’

Command>  CALL ttRepStateSet('ACTIVE');

Step 6. Create a user on the active database

Command> CREATE USER terry IDENTIFIED BY terry;

User created.

Command>  GRANT admin TO terry;

Step 7: Duplicate the active database to the standby database

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master1 -host xifenfei -uid terry -pwd terry  "dsn=master2"

Step 8: Start the replication agent on the standby database

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 9. Duplicate the standby database to the subscriber

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master2 -host xifenfei -uid terry -pwd terry  "dsn=subscriber1"

Step 10: Start the replication agent on the subscriber

[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 11: Insert data into the table on the active database

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> INSERT INTO tab VALUES (1,'Hello');
1 row inserted.
Command> commit;

Setp 12:Verify that the insert is replicated to master2 and subscriber1

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.
Command> exit
Disconnecting...
Done.
[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.

Configuring an active standby pair with one subscriber

发表在 TimesTen | 3 条评论

tempfile真正文件号

发表于 2012 年 3 月 5 日由惜分飞

1.发现问题
这里看到文件号为201，但是查询了v$datafile和v$tempfile视图都没有文件号为201

SQL> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;

USERN SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
----- --------- ---------- ---------- ---------- ----------
SYS   SORT             201     260745        650          1

SEGFILE#  NUMBER  File number of initial extent 

SEGRFNO#   NUMBER  Relative file number of initial extent 

SQL> SELECT FILE#,RFILE# FROM V$DATAFILE;

     FILE#     RFILE#
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11

     FILE#     RFILE#
---------- ----------
        13         13
        14         14

13 rows selected.

SQL> SELECT FILE#,RFILE# FROM V$TEMPFILE;

     FILE#     RFILE#
---------- ----------
         1          1

2.查看v$sort_usage的原始表

SELECT USERNAME,
       USERNAME,
       KTSSOSES,
       KTSSOSNO,
       PREV_SQL_ADDR,
       PREV_HASH_VALUE,
       PREV_SQL_ID,
       KTSSOTSN,
       DECODE(KTSSOCNT, 0, 'PERMANENT', 1, 'TEMPORARY'),
       DECODE(KTSSOSEGT,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              'UNDEFINED'),
       KTSSOFNO,
       KTSSOBNO,
       KTSSOEXTS,
       KTSSOBLKS,
       KTSSORFNO
  FROM X$KTSSO, V$SESSION
 WHERE KTSSOSES = V$SESSION.SADDR
   AND KTSSOSNO = V$SESSION.SERIAL#
   and inst_id = USERENV('Instance')

这里没有发现有用信息，只是知道X$KTSSO.KTSSOFNO是v$sort_usage.SEGFILE#，通过v$sort_usage视图是查询临时表空用来排序的数据文件使用情况。所以把问题定位在v$tempfile视图中，检查它为什么没有显示文件号为201的文件

3.查看v$tempfile视图

SELECT TF.TFNUM,
       TO_NUMBER(TF.TFCRC_SCN),
       TO_DATE(TF.TFCRC_TIM,
               'MM/DD/RR HH24:MI:SS',
               'NLS_CALENDAR=Gregorian'),
       TF.TFTSN,
       TF.TFRFN,
       DECODE(BITAND(TF.TFSTA, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       DECODE(BITAND(TF.TFSTA, 12),
              0,
              'DISABLED',
              4,
              'READ ONLY',
              12,
              'READ WRITE',
              'UNKNOWN'),
       FH.FHTMPFSZ * TF.TFBSZ,
       FH.FHTMPFSZ,
       TF.TFCSZ * TF.TFBSZ,
       TF.TFBSZ,
       FN.FNNAM
  FROM X$KCCTF TF, X$KCCFN FN, X$KCVFHTMP FH
 WHERE FN.FNFNO = TF.TFNUM
   AND FN.FNFNO = FH.HTMPXFIL
   AND TF.TFFNH = FN.FNNUM
   AND TF.TFDUP != 0
   AND BITAND(TF.TFSTA, 32) <> 32
   AND FN.FNTYP = 7
   AND FN.FNNAM IS NOT NULL
   and inst_id = USERENV('Instance')

从这里可以看出v$tempfile.file#出自X$KCCTF.TFNUM

4.继续查看X$KCCTF表

SQL> desc X$KCCTF
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 TFNUM                                              NUMBER
 TFAFN                                              NUMBER
 TFCSZ                                              NUMBER
 TFBSZ                                              NUMBER
 TFSTA                                              NUMBER
 TFCRC_SCN                                          VARCHAR2(16)
 TFCRC_TIM                                          VARCHAR2(20)
 TFFNH                                              NUMBER
 TFFNT                                              NUMBER
 TFDUP                                              NUMBER
 TFTSN                                              NUMBER
 TFTSI                                              NUMBER
 TFRFN                                              NUMBER
 TFPFT                                              NUMBER
 TFMSZ                                              NUMBER
 TFNSZ                                              NUMBER

这里发现一列TFAFN，初步怀疑这个才是真正的绝对文件号。

5.证明X$KCCTF.TFAFN是绝对文件号

SQL> select TFAFN,TFNUM from X$KCCTF;

     TFAFN      TFNUM
---------- ----------
       201          1

如果证明X$KCCTF.TFAFN才是真正的文件号，而TFNUM是临时文件的文件号

6.temp file绝对文件号结论
再进一步可以知道，实际上，为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files+file#。

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
db_files                             integer     200

发表在 Oracle | 4 条评论

记录oracle 9i for linux安装过程中几个错误

发表于 2012 年 3 月 4 日由惜分飞

最近因为一个测试环境需要oracle 9i，我在Oracle linux 4.8上安装，竟然遇到几个问题记录下：
1.运行runInstaller提示如下错误

[oracle@xifenfei Disk1]$ ./runInstaller 
[oracle@xifenfei Disk1]$ Initializing Java Virtual Machine from /tmp/OraInstall2012-03-04_09-32-16PM/jre/bin/java. Please wait...
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2012-03-04_09-32-16PM/jre/lib/i386/libjava.so: 
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

解决方法：打上p3006854_9204_LINUX.zip补丁

[root@xifenfei tmp]$ unzip p3006854_9204_LINUX.zip 
Archive:  p3006854_9204_LINUX.zip
   creating: 3006854/
  inflating: 3006854/rhel3_pre_install.sh  
  inflating: 3006854/README.txt 
[root@xifenfei 3006854]# cd 3006854
[root@xifenfei 3006854]# chmod +x rhel3_pre_install.sh 
[root@xifenfei 3006854]# ./rhel3_pre_install.sh 
Applying patch...
Ensuring permissions are correctly set...
Done.
Patch successfully applied

2.在Linking Oracle9i到63%时入到如下错误

解决办法：在/usr/bin目录下有gcc 和 gcc32两个文件，执行 mv gcc gcc296 和 mv gcc32 gcc,完成之后，retry安装界面

[root@xifenfei tmp]# cd /usr/bin
[root@xifenfei bin]# ls gcc*
gcc  gcc32  gcc4
[root@xifenfei bin]# mv gcc gcc296
[root@xifenfei bin]# mv gcc32 gcc

3.执行netca/dbca报如下错误

[oracle@xifenfei Disk1]$ dbca
/u01/oracle/jre/1.1.8/bin/../lib/i686/green_threads/libzip.so: symbol errno, 
version GLIBC_2.0 not defined in file libc.so.6 with link time reference (libzip.so)
Unable to initialize threads: cannot find class java/lang/Thread
Could not create Java VM
[oracle@xifenfei Disk1]$ netca
/u01/oracle/jre/1.1.8/bin/../lib/i686/native_threads/libzip.so: symbol errno, 
version GLIBC_2.0 not defined in file libc.so.6 with link time reference (libzip.so)
Unable to initialize threads: cannot find class java/lang/Thread
Could not create Java VM

解决办法:在环境变量中加上LD_ASSUME_KERNEL=2.4.1

vi .bash_profile --加上下面语句
export LD_ASSUME_KERNEL=2.4.1
source ~/.bash_profile

发表在 Oracle安装升级 | 2 条评论

17813235971

QQ 咨询

月归档：三月 2012

Configuring an active standby pair with one subscriber

tempfile真正文件号

记录oracle 9i for linux安装过程中几个错误

文章分类

最近发表

月归档：三月 2012

Configuring an active standby pair with one subscriber

tempfile真正文件号

记录oracle 9i for linux安装过程中几个错误

标签云

文章分类

最近发表