October 15, 2004
关于PUSH_SUBQ提示的说明
作者:eygle
出处:http://blog.eygle.com
我那个例子的意思是说:
PUSH_SUBQ 本质上是个CBO的hints(当然RBO也提不上hints)
由于PUSH_SUBQ 的引入就是为了来解决unnesting的某些不足
所以在不同的版本上,这个hints发挥的作用也有所不同了.
8i上这个提示的作用更接近本原:
|
$ sqlplus "/ as sysdba"
SQL*Plus: Release 8.1.5.0.0 - Production on Sun Sep 12 20:51:21 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> connect scott/tiger
Connected.
SQL> create table dept1 as select * from dept;
Table created.
SQL> set linesize 120
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER ---------------'最初push_subq的使命是为了消除/提高这个filter的效率的'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
6 1 TABLE ACCESS (FULL) OF 'DEPT1'
Statistics
----------------------------------------------------------
0 recursive calls
52 db block gets
21 consistent gets
0 physical reads
0 redo size
2715 bytes sent via SQL*Net to client
751 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
|
用于push_subq是个CBO hints，这里我们可以看到COST的出现:
|
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=122)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=122)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=2 Bytes=44)
3 2 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)--------'这里消除了之前的filter'
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=21 Bytes=2100)
Statistics
----------------------------------------------------------
204 recursive calls
85 db block gets
38 consistent gets
0 physical reads
0 redo size
2706 bytes sent via SQL*Net to client
768 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed |
再看CBO下:
正常情况下,没什么好说的:
|
SQL> set autotrace traceonly
SQL> exec dbms_stats.gather_schema_stats('SCOTT')
PL/SQL procedure successfully completed.
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
1 0 FILTER
2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
39 recursive calls
68 db block gets
21 consistent gets
0 physical reads
0 redo size
2708 bytes sent via SQL*Net to client
751 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
|
在CBO下push_subq发挥了同样的作用:
|
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
1 0 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
3 2 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)---'注意这里'
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
Statistics
----------------------------------------------------------
0 recursive calls
84 db block gets
11 consistent gets
0 physical reads
0 redo size
2709 bytes sent via SQL*Net to client
768 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed |
而在Oracle9i之中:
|
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Sep 12 21:42:57 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> connect scott/tiger
Connected.
SQL> set linesize 120
SQL> set autotrace traceonly
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
6 1 TABLE ACCESS (FULL) OF 'DEPT1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
1164 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9676)
1 0 HASH JOIN (SEMI) (Cost=8 Card=82 Bytes=9676)
2 1 HASH JOIN (Cost=5 Card=82 Bytes=8938)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
4 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=82 Bytes=738)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> exec dbms_stats.gather_schema_stats('scott')
PL/SQL procedure successfully completed.
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
1 0 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
2 1 HASH JOIN (Cost=5 Card=11 Bytes=572)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)
Statistics
----------------------------------------------------------
172 recursive calls
0 db block gets
60 consistent gets
0 physical reads
0 redo size
1196 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
1 0 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
2 1 HASH JOIN (Cost=5 Card=11 Bytes=572)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1196 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> |
这个变化可能来自于CBO的更加优化
Posted by eygle at 9:07 PM | Comments (0)
Dataguard配置Step by Step
作者:eygle
出处:http://blog.eygle.com
1.主节点备份并生成备用数据库控制文件
设置主节点为force Logging模式(为了双向切换,建议备用节点也设置为force logging模式)
ALTER DATABASE FORCE LOGGING;
设置主节点为归档模式
登陆主节点,进行数据库备份，并生成备用数据库控制文件
Last login: Mon Aug 9 16:46:47 2004 from 172.16.32.65 [root@standby root]# su - oracle
[oracle@standby oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 10:16:18 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/opt/oracle/oradata/primary/system01.dbf
/opt/oracle/oradata/primary/undotbs01.dbf
/opt/oracle/oradata/primary/users01.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@standby oracle]$ ls
admin dictionary.ora jre oradata oraInventory oui product soft[oracle@standby oracle]$ tar -cvf oradata.tar oradata
oradata/
oradata/primary/
oradata/primary/archive/
oradata/primary/control01.ctl
oradata/primary/control02.ctl
oradata/primary/control03.ctl
oradata/primary/redo01.log
oradata/primary/redo02.log
oradata/primary/redo03.log
oradata/primary/system01.dbf
oradata/primary/undotbs01.dbf
oradata/primary/temp01.dbf
oradata/primary/users01.dbf[oracle@standby oracle]$ ls -l *.tar
-rw-r--r-- 1 oracle dba 576512000 Aug 16 10:22 oradata.tar
[oracle@standby oracle]$ id
uid=800(oracle) gid=800(dba) groups=800(dba)
[oracle@standby oracle]$ hostname
standby
[oracle@standby oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 10:27:54 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/primary/archive
Oldest online log sequence 88
Next log sequence to archive 90
Current log sequence 90
SQL> alter database create standby controlfile as '/opt/oracle/stdcotrl.ctl';Database altered.
SQL> !
ls[oracle@standby oracle]$ ls
admin dictionary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl
2.从主节点创建pfile文件
SQL> create pfile from spfile;
File created.SQL> !
[oracle@standby oracle]$ cd $ORACLE_HOME/dbs
[oracle@standby dbs]$ ls
initdw.ora init.ora initprimary.ora lkPRIMARY orapwprimary spfileprimary.ora sqlnet.log
3.登陆备用节点,ftp获得数据库文件、备用控制文件及参数文件
Last login: Mon Aug 16 08:47:58 2004 from 172.16.32.65
[root@eygle root]# su - oracle
[oracle@eygle oracle]$ ls
admin doc jre oradata oraInventory oui product
[oracle@eygle oracle]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 5154852 3360600 1532396 69% /
/dev/sda7 101089 25744 70126 27% /home
/dev/sda5 4127076 2686152 1231280 69% /opt
none 515296 0 515296 0% /dev/shm
/dev/sda2 4127108 2218172 1699288 57% /usr
/dev/sda6 2063504 107744 1850940 6% /var
[oracle@eygle oracle]$ ftp 172.16.33.58
Connected to 172.16.33.58 (172.16.33.58).
220 (vsFTPd 1.2.0)
Name (172.16.33.58:root): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> ls
227 Entering Passive Mode (172,16,33,58,222,252)
150 Here comes the directory listing.
drwxr-xr-x 3 800 800 4096 Jun 30 07:02 admin
-rw-r--r-- 1 800 800 5422222 Jul 13 11:58 dictionary.ora
-rw-r--r-- 1 800 800 1165 Aug 16 02:51 initprimary.ora
drwxrwxr-x 4 800 800 4096 Jun 30 06:29 jre
drwxrwxr-x 12 800 800 4096 Jun 30 06:44 oraInventory
drwxr-xr-x 3 800 800 4096 Jul 01 06:15 oradata
-rw-r--r-- 1 800 800 576512000 Aug 16 02:22 oradata.tar
drwxrwxr-x 6 800 800 4096 Jun 30 06:29 oui
drwxr-xr-x 3 800 800 4096 Jun 30 05:18 product
drwxr-xr-x 6 800 800 4096 Jun 30 04:24 soft
-rw-r----- 1 800 800 1662976 Aug 16 02:37 stdcotrl.ctl
226 Directory send OK.
ftp> bin
200 Switching to Binary mode.
ftp> mget oradata.tar
mget oradata.tar? y
227 Entering Passive Mode (172,16,33,58,238,132)
150 Opening BINARY mode data connection for oradata.tar (576512000 bytes).
226 File send OK.
576512000 bytes received in 49.2 secs (1.1e+04 Kbytes/sec)
ftp> mget *.ctl
mget stdcotrl.ctl? y
227 Entering Passive Mode (172,16,33,58,73,35)
150 Opening BINARY mode data connection for stdcotrl.ctl (1662976 bytes).
226 File send OK.
1662976 bytes received in 0.14 secs (1.2e+04 Kbytes/sec)
ftp> mget initprimary.ora
mget initprimary.ora? y
227 Entering Passive Mode (172,16,33,58,194,239)
150 Opening BINARY mode data connection for initprimary.ora (1165 bytes).
226 File send OK.
1165 bytes received in 0.000325 secs (3.5e+03 Kbytes/sec)
ftp> bye
221 Goodbye.[oracle@eygle oracle]$ ls
admin doc initprimary.ora jre oradata oradata.tar oraInventory oui product stdcotrl.ctl
[oracle@eygle oracle]$ mv initprimary.ora $ORACLE_HOME/dbs
[oracle@eygle oracle]$ cd $ORACLE_HOME/dbs
[oracle@eygle dbs]$ ls
a.sql initdw.ora init.ora initprimary.ora initrac1.ora initrac2.ora initrac.ora orapw
orapwrac1 orapwrac2 spfilerac.ora
解包数据文件
[oracle@eygle oracle]$ ls
admin doc jre oradata oradata.tar oraInventory oui product stdcotrl.ctl
[oracle@eygle oracle]$ tar -xvf oradata.tar
oradata/
oradata/primary/
oradata/primary/archive/
oradata/primary/control01.ctl
oradata/primary/control02.ctl
oradata/primary/control03.ctl
oradata/primary/redo01.log
oradata/primary/redo02.log
oradata/primary/redo03.log
oradata/primary/system01.dbf
oradata/primary/undotbs01.dbf
oradata/primary/temp01.dbf
oradata/primary/users01.dbf
修改initprimary.ora文件
修改控制文件名称及路径(如果和原配置不同)，增加几个参数,修改后如下:
[oracle@eygle dbs]$ cat initprimary.ora创建必要的目录
*.aq_tm_processes=1
*.background_dump_dest='/opt/oracle/admin/primary/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/opt/oracle/oradata/primary/stdcotrl.ctl'
*.core_dump_dest='/opt/oracle/admin/primary/cdump'
...
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/archive'
*.log_archive_dest_2='' *.log_archive_format='%t_%s.dbf'
*.log_archive_start=true
...
*.user_dump_dest='/opt/oracle/admin/primary/udump'
*.utl_file_dir='/opt/oracle'
*.standby_archive_dest='/opt/oracle/oradata/primary/stdarch'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.standby_file_management='AUTO'
[oracle@eygle oracle]$ cd $ORACLE_BASE/admin
[oracle@eygle admin]$ mkdir primary
[oracle@eygle admin]$ ls
primary rac
[oracle@eygle admin]$ cd primary/
[oracle@eygle primary]$ ls
[oracle@eygle primary]$ mkdir bdump cdump udump
4.配置主节点监听器及tnsnames.ora文件
配置后如下:
[oracle@standby oracle]$ cd /opt/oracle/product/9.2.0/network/admin/
[oracle@standby admin]$ cat listener.ora
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = primary)
)
)[oracle@standby admin]$ cat tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)[oracle@standby admin]$ lsnrctl start
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:31
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 16-AUG-2004 10:46:31
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "primary" has 1 instance(s).
Instance "primary", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
5.配置备用数据库监听器及tnsnames.ora文件
配置后文件如下:
[oracle@eygle admin]$ cd $ORACLE_HOME/network/admin
[oracle@eygle admin]$ cat listener.ora
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = eygle)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = primary)
)
)
[oracle@eygle admin]$ cat tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
[oracle@eygle admin]$
6.在主备节点用tnsping测试网络连通性
[oracle@standby admin]$ tnsping standby
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:50
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (10 msec)
[oracle@standby admin]$ tnsping primaryTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:55
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (0 msec)[oracle@eygle admin]$ tnsping primary
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:01
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (50 msec)
[oracle@eygle admin]$ tnsping standbyTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:06
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (10 msec)
7.启动备用数据库
[oracle@eygle primary]$ hostname
eygle
[oracle@eygle primary]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 11:09:40 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
8.在主节点设置归档路径
SQL> alter system set log_archive_dest_2='service=standby mandatory reopen=60';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
在备用节点观察日志
[oracle@eygle bdump]$ tail -f alert_primary.log
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 90
Datafile 1: '/opt/oracle/oradata/primary/system01.dbf'
Starting datafile 2 recovery in thread 1 sequence 90
Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf'
Starting datafile 3 recovery in thread 1 sequence 90
Datafile 3: '/opt/oracle/oradata/primary/users01.dbf'
Media Recovery Waiting for thread 1 seq# 90
Mon Aug 16 11:10:50 2004
Completed: alter database recover managed standby database di
Mon Aug 16 11:13:34 2004
Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_90.dbf
Media Recovery Waiting for thread 1 seq# 91
Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_91.dbf
Media Recovery Waiting for thread 1 seq# 92
Mon Aug 16 12:09:38 2004
Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_92.dbf
9.在主节点进行同样的配置，以便切换后继续日志传递
[oracle@standby oracle]$ ls
admin dictionary.ora initprimary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl[oracle@standby oracle]$ cd oradata
[oracle@standby oradata]$ ls
primary
[oracle@standby oradata]$ cd primary/
[oracle@standby primary]$ ls
archive control02.ctl redo01.log redo03.log temp01.dbf
users01.dbfcontrol01.ctl control03.ctl redo02.log system01.dbf
undotbs01.dbf
[oracle@standby primary]$ mkdir stdarch
[oracle@standby primary]$ exit
exitSQL> alter system set standby_archive_dest='/opt/oracle/oradata/primary/stdarch';
System altered.
10.停止主数据库,启用备用数据库
SQL> alter database commit to switchover to physical standby;
Database altered.SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
在备用模式启用主数据
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.
打开备用数据库
[oracle@eygle oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 12:11:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - ProductionSQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;System altered.
在主库上观察日志应用情况
[oracle@standby bdump]$ tail -f alert_primary.log11.进行数据修改
Starting datafile 2 recovery in thread 1 sequence 93
Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf'
Starting datafile 3 recovery in thread 1 sequence 93
Datafile 3: '/opt/oracle/oradata/primary/users01.dbf'
Media Recovery Log /opt/oracle/product/9.2.0/dbs/arch1_93.dbf
Mon Aug 16 15:08:43 2004
Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_94.dbf
Media Recovery Waiting for thread 1 seq# 95
Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_95.dbf
Media Recovery Waiting for thread 1 seq# 96
SQL> create table t as select * from dba_users; Table created.SQL> alter system switch logfile;
System altered.
在从库上以read only打开数据库，执行查询
SQL> select username from t;
select username from t
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> select username from t;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYSSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.SQL>
12.把数据库切换回到主节点
在主节点
SQL> alter database commit to switchover to physical standby;Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
statORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytesSQL> alter database mount standby database;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
在备用节点
SQL> alter database commit to switchover to primary;Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
完成自由切换
-The End-
Posted by eygle at 5:11 PM | Comments (21)
Oracle HowTo:在非归档模式下如何更改数据文件位置
作者:eygle
出处:http://blog.eygle.com
如果数据库处于非归档模式下，可以通过如下步骤更改文件路径:1.关闭数据库
2.系统级进行文件复制
3.启动数据库到mount状态
3.通过SQL修改数据文件位置
4.打开数据库
以下是实际操作的步骤示范：
1.数据库处于非归档模式
SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/conner/archive Oldest online log sequence 150 Current log sequence 153 |
2.需要移动test.dbf文件
SQL> select name from v$datafile; NAME ------------------------------------------------------------ /opt/oracle/oradata/conner/system01.dbf /opt/oracle/oradata/conner/undotbs01.dbf /opt/oracle/oradata/conner/users01.dbf /opt/oracle/test.dbf |
3.关闭数据库
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
4.复制文件到新的位置
SQL> ! cp /opt/oracle/test.dbf /opt/oracle/oradata/conner/test.dbf |
5.启动数据库到mount状态
SQL> startup mount; ORACLE instance started. Total System Global Area 101782828 bytes Fixed Size 451884 bytes Variable Size 37748736 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. SQL> select name from v$datafile; NAME ----------------------------------------------------------- /opt/oracle/oradata/conner/system01.dbf /opt/oracle/oradata/conner/undotbs01.dbf /opt/oracle/oradata/conner/users01.dbf /opt/oracle/test.dbf |
6.修改文件位置
SQL> alter database rename file '/opt/oracle/test.dbf' to '/opt/oracle/oradata/conner/test.dbf'; Database altered. SQL> alter database open; Database altered. SQL> select name from v$datafile; NAME ---------------------------------------------------------------- /opt/oracle/oradata/conner/system01.dbf /opt/oracle/oradata/conner/undotbs01.dbf /opt/oracle/oradata/conner/users01.dbf /opt/oracle/oradata/conner/test.dbf SQL> |
Posted by eygle at 4:04 PM | Comments (4)
如何启动或关闭数据库的归档(ARCHIVELOG)模式
作者:eygle
出处:http://blog.eygle.com
Oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog)归档模式可以提高Oracle数据库的可恢复性，生产数据库都应该运行在此模式下，归档模式应该和相应的备份策略相结合，只有归档模式没有相应的备份策略只会带来麻烦。
本文简单介绍如何启用和关闭数据库的归档模式。
1.shutdown normal或shutdown immediate关闭数据库
[oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 15 15:48:36 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
2.启动数据库到mount状态
SQL> startup mount; ORACLE instance started. Total System Global Area 101782828 bytes Fixed Size 451884 bytes Variable Size 37748736 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. |
3.启用或停止归档模式
如果要启用归档模式，此处使用
alter database archivelog 命令。
SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/conner/archive Oldest online log sequence 148 Next log sequence to archive 151 Current log sequence 151 |
如果需要停止归档模式，此处使用：
alter database noarchivelog 命令。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 101782828 bytes Fixed Size 451884 bytes Variable Size 37748736 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/conner/archive Oldest online log sequence 149 Current log sequence 152 |
4.修改相应的初始化参数
Oracle10g之前，你还需要修改初始化参数使数据库处于自动归档模式。
在pfile/spfile中设置如下参数：
log_archive_start = true
重启数据库此参数生效，此时数据库处于自动归档模式。
也可以在数据库启动过程中，手工执行：
archive log start
使数据库启用自动归档，但是重启后数据库仍然处于手工归档模式。
从Oracle10g开始，log_archive_start参数已经废除，请参考：Oracle10g已经废弃log_archive_start参数.
Posted by eygle at 4:00 PM | Comments (0)