『AskWan』

oracle asm configure error

Sun, 28 Mar 2010 13:50:16 +0000

when i configure the oracle asm lib for my linux system (CentOS 4.7 x86)

[root@ra ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [FAILED]

the i check the system log message ,found that :

Mar 28 21:32:23 ra modprobe: FATAL: Module oracleasm not found. 
Mar 28 21:32:23 ra oracleasm: Unable failed

then ,i checked the asmlib version matched my linux server kernel or not ,yes i belive
the files were right version,

# uname -a
Linux ra 2.6.9-78.ELsmp #1 SMP Fri Jul 25 00:04:28 EDT 2008 i686 i686 i386 GNU/Linux

and the files i have downloaded:

oracleasm-2.6.9-78.EL-2.0.5-1.el4.i686.rpm
oracleasmlib-2.0.4-1.el4.i386.rpm
oracleasm-support-2.1.3-1.el4.i386.rpm

but what cause this problem ?

after check the grub.conf ,i found i have made a mistake , the system running kernel is 2.6.9-78.ELsmp , but what i have download is 2.6.9-78.EL ,when i installed the right asm lib version ,then things seems all right!

[root@ra ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]: 
Default group to own the driver interface [dba]: 
Start Oracle ASM library driver on boot (y/n) [y]: 
Scan for Oracle ASM disks on boot (y/n) [y]: 
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

Concluding: you must download the right oracle asm lib version that match your system kernel 100%!

---END---
Tags - oracle , asm , configure

create a standby database using active database duplication in oracle 11g

Fri, 26 Mar 2010 10:01:32 +0000

active database duplication is a new feature in oracle 11g,it can create a standby database for data guard without any preexisting backups to start the database duplication process.
this is an example noted by me to test this feature on the same host.

env and plans:
IP:192.168.0.120
DB: Oracle 11g 11.2.0.1.0 for linux 64bit
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

primary database:
instance name: ora11gr2
db_unique_name: ora11gr2
net service name: ora11gr2
oracle data file,online redo log file and controlfile are located in : /u01/app/oracle/oradata/ora11gr2
alert file located in: /u01/app/oracle/admin/ora11gr2
archied log file are located in:/u01/app/oracle/archive/ora11gr2

standby database:
instance name: standby
db_unique_name: standby
net service name :standby
oracle data file,online redo log file and controlfile are located in : /u01/app/oracle/oradata/standby
alert file located in: /u01/app/oracle/admin/standby
archied log file are located in:/u01/app/oracle/archive/standby

preparatory work:
1)install oracle11g software and create a database ora11gr2 in 192.168.0.120.
2)create dir for standby

$mkdir /u01/app/oracle/oradata/standby
$mkdir /u01/app/oracle/admin/standby
$mkdir /u01/app/oracle/admin/standby/{a,dp}dump
$mkdir /u01/app/oracle/admin/standby/pfile
$mkdir /u01/app/oracle/archive/standby

3)alter primary database ora11gr2 to archive log mode
4)alter primary database to force logging mode.
5)modified initial startup file in primary database

LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(ora11gr2,standby)'
LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/archive/ora11gr2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11gr2'
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
DB_FILE_NAME_CONVERT='ora11gr2','standby'
LOG_FILE_NAME_CONVERT='ora11gr2','standby'
FAL_CLIENT='ora11gr2'
FAL_SERVER='standby'

STEP 1:configuration listener.ora in $ORACLE_HOME/network/admin

SID_LIST_LISTENER =
        (SID_LIST =
         (SID_DESC =
                 (GLOBAL_DBNAME = ora11gr2)
                  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
                  (SID_NAME =ora11gr2)
                )
        (SID_DESC =
                (GLOBAL_DBNAME = test1)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
                (SID_NAME =test1)
        )
)

then restart listener!

STEP2:configuration tnsnames.ora in $ORACLE_HOME/network/admin

ORA11GR2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2.localdomain)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STANDBY)
    )
  )

STEP3 :configuration init.ora(initstandby.ora) file for duplicate database

*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_name='ora11gr2'
*.remote_login_passwordfile='EXCLUSIVE'

STEP4:Create password file and related directory for duplicate database

$cd $ORACLE_HOME/dbs/
$ orapwd file=orapwstandby password=oracledb entries=5
$ mkdir /u01/app/oracle/admin/standby
$ mkdir /u01/app/oracle/admin/standby/{a,dp}dump
$ mkdir /u01/app/oracle/admin/standby/pfile

STEP5:start auxiliary database instance in nomount

$ export ORACLE_SID=standby
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 24 14:00:02 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE instance shut down.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstandby.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
SQL> exit

STEP6: duplicate database from primary database

$ export ORACLE_SID=ora11gr2
$ rman target sys/oracledb@ora11gr2 auxiliary sys/oracledb@STANDBY

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 26 17:50:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=19734995)
connected to auxiliary database: ORA11GR2 (not mounted)

RMAN> duplicate target database for standby
2> dorecover from active database nofilenamecheck
3> spfile set DB_UNIQUE_NAME 'standby'
4> set FAL_CLIENT 'standby'
5> set FAL_SERVER 'ora11gr2'
6> set log_archive_dest_1 'LOCATION=/u01/app/oracle/archive/standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
7> set CONTROL_FILES '/u01/app/oracle/oradata/standby/control01.ctl',
8> '/u01/app/oracle/oradata/standby/control02.ctl',
9> '/u01/app/oracle/oradata/standby/control03.ctl';

Starting Duplicate Db at 26-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwora11gr2' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstandby'   targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileora11gr2.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestandby.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestandby.ora''";
}
executing Memory Script

Starting backup at 26-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Finished backup at 26-MAR-10

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestandby.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set  FAL_CLIENT =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
''ora11gr2'' comment=
'''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
''LOCATION=/u01/app/oracle/archive/standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
''/u01/app/oracle/oradata/standby/control01.ctl'', ''/u01/app/oracle/oradata/standby/control02.ctl'', ''/u01/app/oracle/oradata/standby/control03.ctl'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''standby'' comment= '''' scope=spfile

sql statement: alter system set  FAL_CLIENT =  ''standby'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''ora11gr2'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u01/app/oracle/archive/standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''/u01/app/oracle/oradata/standby/control01.ctl'', ''/u01/app/oracle/oradata/standby/control02.ctl'', ''/u01/app/oracle/oradata/standby/control03.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/standby/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/standby/control02.ctl' from
'/u01/app/oracle/oradata/standby/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/standby/control03.ctl' from
'/u01/app/oracle/oradata/standby/control01.ctl';
}
executing Memory Script

Starting backup at 26-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ora11gr2.f tag=TAG20100326T175218 RECID=1 STAMP=714678740
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 26-MAR-10

Starting restore at 26-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 26-MAR-10

Starting restore at 26-MAR-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 26-MAR-10

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:
{
   set newname for tempfile  1 to
"/u01/app/oracle/oradata/standby/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
"/u01/app/oracle/oradata/standby/system01.dbf";
   set newname for datafile  2 to
"/u01/app/oracle/oradata/standby/sysaux01.dbf";
   set newname for datafile  3 to
"/u01/app/oracle/oradata/standby/undotbs01.dbf";
   set newname for datafile  4 to
"/u01/app/oracle/oradata/standby/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"/u01/app/oracle/oradata/standby/system01.dbf"   datafile
2 auxiliary format
"/u01/app/oracle/oradata/standby/sysaux01.dbf"   datafile
3 auxiliary format
"/u01/app/oracle/oradata/standby/undotbs01.dbf"   datafile
4 auxiliary format
"/u01/app/oracle/oradata/standby/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/standby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 26-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11gr2/system01.dbf

output file name=/u01/app/oracle/oradata/standby/system01.dbf tag=TAG20100326T175235
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
output file name=/u01/app/oracle/oradata/standby/sysaux01.dbf tag=TAG20100326T175235
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
output file name=/u01/app/oracle/oradata/standby/undotbs01.dbf tag=TAG20100326T175235
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11gr2/users01.dbf
output file name=/u01/app/oracle/oradata/standby/users01.dbf tag=TAG20100326T175235
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-MAR-10

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/archive/ora11gr2/1_7_703163476.dbf" auxiliary format
"/u01/app/oracle/archive/standby/1_7_703163476.dbf"   archivelog like
"/u01/app/oracle/archive/ora11gr2/1_8_703163476.dbf" auxiliary format
"/u01/app/oracle/archive/standby/1_8_703163476.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/archive/standby/1_7_703163476.dbf";
   catalog clone archivelog  "/u01/app/oracle/archive/standby/1_8_703163476.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 26-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=3 STAMP=714678767
output file name=/u01/app/oracle/archive/standby/1_7_703163476.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=4 STAMP=714678926
output file name=/u01/app/oracle/archive/standby/1_8_703163476.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 26-MAR-10

cataloged archived log
archived log file name=/u01/app/oracle/archive/standby/1_7_703163476.dbf RECID=1 STAMP=714678929

cataloged archived log
archived log file name=/u01/app/oracle/archive/standby/1_8_703163476.dbf RECID=2 STAMP=714678929

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=714678929 file name=/u01/app/oracle/oradata/standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=714678929 file name=/u01/app/oracle/oradata/standby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=714678929 file name=/u01/app/oracle/oradata/standby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=714678929 file name=/u01/app/oracle/oradata/standby/users01.dbf

contents of Memory Script:
{
   set until scn  1015076;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-MAR-10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/archive/standby/1_7_703163476.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/archive/standby/1_8_703163476.dbf
archived log file name=/u01/app/oracle/archive/standby/1_7_703163476.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/archive/standby/1_8_703163476.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 26-MAR-10
Finished Duplicate Db at 26-MAR-10

STEP 7: simply check if it work or not
in the primary databse

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
11

switch to standby database

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
11

---END---
Tags - standby , database , active , duplication , oracle , 11g

create a duplicate database using active database duplication in oracle 11g

Wed, 24 Mar 2010 06:51:19 +0000

active database duplication is a new feature in oracle 11g,it can duplicate a database without any preexisting backups to start the database duplication process.

this simple note is written by me to test this feature in the same host.

env:
IP:192.168.0.120
DB: Oracle 11g 11.2.0.1.0 for linux 64bit
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Target Database instance name: ora11gr2
Duplicate database instance name: test1

STEP 1:configuration listener.ora in $ORACLE_HOME/network/admin

SID_LIST_LISTENER =
        (SID_LIST =
                (SID_DESC =
                        (GLOBAL_DBNAME = ora11gr2)
                        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
                        (SID_NAME =ora11gr2)
                )
        (SID_DESC =
                (GLOBAL_DBNAME = test1)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
                (SID_NAME =test1)
        )
)

STEP2:configuration tnsnames.ora in $ORACLE_HOME/network/admin

ORA11GR2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2.localdomain)
    )
  )

test1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test1)
    )
  )

STEP3 :configuration init.ora file for duplicate database

*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/test1/control01.ctl','/u01/app/oracle/oradata/test1/control02.ctl','/u01/app/oracle/oradata/test1/control01.ctl'
*.db_block_size=8192
*.db_name='test1'
*.remote_login_passwordfile='EXCLUSIVE'
DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ora11gr2','/u01/app/oracle/oradata/test1')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ora11gr2','/u01/app/oracle/oradata/test1')

STEP4:Create password file and related directory for duplicate database

$cd $ORACLE_HOME/dbs/
$ orapwd file=orapwtest1 password=oracledb entries=5
$ mkdir /u01/app/oracle/admin/test1
$ mkdir /u01/app/oracle/admin/test1/{a,dp}dump
$ mkdir /u01/app/oracle/admin/test1/pfile

STEP5:start auxiliary database instance in nomount

$ export ORACLE_SID=test1
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 24 14:00:02 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE instance shut down.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittest1.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
SQL> exit

STEP6: duplicate database from target database

$ export ORACLE_SID=ora11gr2
$ rman target sys/oracledb@ora11gr2 auxiliary sys/oracledb@test1

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 24 14:01:00 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=19734995)
connected to auxiliary database: TEST1 (not mounted)

RMAN> duplicate target database to test1 from active database ;

Starting Duplicate Db at 24-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
''ORA11GR2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
''TEST1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/test1/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORA11GR2'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

Starting backup at 24-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ora11gr2.f tag=TAG20100324T140119 RECID=3 STAMP=714492079
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-MAR-10

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
"/u01/app/oracle/oradata/test1/system01.dbf";
   set newname for datafile  2 to
"/u01/app/oracle/oradata/test1/sysaux01.dbf";
   set newname for datafile  3 to
"/u01/app/oracle/oradata/test1/undotbs01.dbf";
   set newname for datafile  4 to
"/u01/app/oracle/oradata/test1/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"/u01/app/oracle/oradata/test1/system01.dbf"   datafile
2 auxiliary format
"/u01/app/oracle/oradata/test1/sysaux01.dbf"   datafile
3 auxiliary format
"/u01/app/oracle/oradata/test1/undotbs01.dbf"   datafile
4 auxiliary format
"/u01/app/oracle/oradata/test1/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 24-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11gr2/system01.dbf
output file name=/u01/app/oracle/oradata/test1/system01.dbf tag=TAG20100324T140125
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:27
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
output file name=/u01/app/oracle/oradata/test1/sysaux01.dbf tag=TAG20100324T140125
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
output file name=/u01/app/oracle/oradata/test1/undotbs01.dbf tag=TAG20100324T140125
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11gr2/users01.dbf
output file name=/u01/app/oracle/oradata/test1/users01.dbf tag=TAG20100324T140125
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-MAR-10

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/flash_recovery_area/ORA11GR2/archivelog/2010_03_24/o1_mf_1_6_5tmbtgxl_.arc" auxiliary format
"/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 24-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=6 RECID=2 STAMP=714492240
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 24-MAR-10

cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf RECID=2 STAMP=714492246

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=714492246 file name=/u01/app/oracle/oradata/test1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=714492246 file name=/u01/app/oracle/oradata/test1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=714492246 file name=/u01/app/oracle/oradata/test1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=714492246 file name=/u01/app/oracle/oradata/test1/users01.dbf

contents of Memory Script:
{
   set until scn  1026657;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_6_703163476.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-MAR-10

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
''TEST1'' comment=
''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

sql statement: alter system set  db_name =  ''TEST1'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/test1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/test1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/test1/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
  '/u01/app/oracle/oradata/test1/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   set newname for tempfile  1 to
"/u01/app/oracle/oradata/test1/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test1/sysaux01.dbf",
"/u01/app/oracle/oradata/test1/undotbs01.dbf",
"/u01/app/oracle/oradata/test1/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/test1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/test1/sysaux01.dbf RECID=1 STAMP=714492269
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/test1/undotbs01.dbf RECID=2 STAMP=714492269
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/test1/users01.dbf RECID=3 STAMP=714492269

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=714492269 file name=/u01/app/oracle/oradata/test1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=714492269 file name=/u01/app/oracle/oradata/test1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=714492269 file name=/u01/app/oracle/oradata/test1/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 24-MAR-10

RMAN> exit

---END---
Tags - duplicate , database , active , duplicationoracle , 11g

Use rman to create a duplicate database on the some host

Tue, 23 Mar 2010 08:35:21 +0000

As we all known! it's a common task to create a duplicate database from the target production database as a DBA.this is an example that i have noted the processes to create a duplicate database step by step,and duplicate database from source database to another host is the same .

IP:192.168.0.21
DB: Oracle 10g R2 for linux 64bit
Source database instance name : dg1
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
Duplicate database instance name :dg2

step 1.backup the source database use rman :

$ rman target /
RMAN> backup full database plus archivelog ;

Starting backup at 23-MAR-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=714411579
input archive log thread=1 sequence=3 recid=2 stamp=714411579
input archive log thread=1 sequence=4 recid=3 stamp=714411668
input archive log thread=1 sequence=5 recid=4 stamp=714412284
channel ORA_DISK_1: starting piece 1 at 23-MAR-10
channel ORA_DISK_1: finished piece 1 at 23-MAR-10
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/01l9a47t_1_1 tag=TAG20100323T155125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 23-MAR-10

Starting backup at 23-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/oradata/dg1/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/dg1/sysaux01.dbf
input datafile fno=00005 name=/u01/oracle/oradata/dg1/example01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/dg1/undotbs01.dbf
input datafile fno=00004 name=/u01/oracle/oradata/dg1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-MAR-10
channel ORA_DISK_1: finished piece 1 at 23-MAR-10
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/02l9a486_1_1 tag=TAG20100323T155134 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAR-10
channel ORA_DISK_1: finished piece 1 at 23-MAR-10
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/03l9a49u_1_1 tag=TAG20100323T155134 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAR-10

Starting backup at 23-MAR-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=5 stamp=714412353
channel ORA_DISK_1: starting piece 1 at 23-MAR-10
channel ORA_DISK_1: finished piece 1 at 23-MAR-10
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/04l9a4a1_1_1 tag=TAG20100323T155233 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-MAR-10

step 2: configuration listener.ora in $ORACLE_HOME/network/admin/
add below as:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_NAME = dg1)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (SID_NAME = dg1)
    )
   (SID_DESC =
      (GLOBAL_NAME = dg2)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (SID_NAME = dg2)
    )
  )

step 3:configuration tnsnames.ora in $ORACLE_HOME/network/admin/
add below as:

DG1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dg1)
    )
  )
DG2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dg2)
    )
  )

step 4:create an init.ora file for the new duplicate database "dg2"
as dg1 init.ora file is :

then we create a new init.ora file in $ORACLE_HOME/dbs/ refers to this file as follow:

dg2.__db_cache_size=134217728
dg2.__java_pool_size=4194304
dg2.__large_pool_size=4194304
dg2.__shared_pool_size=67108864
dg2.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/dg2/adump'
*.background_dump_dest='/u01/oracle/admin/dg2/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/dg2/control01.ctl','/u01/oracle/oradata/dg2/control02.ctl','/u01/oracle/oradata/dg2/control03.c
tl'
*.core_dump_dest='/u01/oracle/admin/dg2/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg2'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oracle/archive_dg2'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/dg2/udump'
*.db_file_name_convert = ('/u01/oracle/oradata/dg1',
'/u01/oracle/oradata/dg2')
*.log_file_name_convert = ('/u01/oracle/oradata/dg1',
'/u01/oracle/oradata/dg2')

step 5: create corresponding file and dir for the duplicate database

create dir for duplicate database

$mkdir /u01/oracle/oradata/dg2
$mkdir /u01/oracle/admin/dg2/{a,b,c,dp,u}dump
$mkdir /u01/oracle/admin/dg2/pfile
$mkdir /u01/oracle/archive_dg2

create password file for duplicate database

$cd $ORACLE_HOME/dbs/
$ orapwd file=orapwdg2 password=oracledb entries=5

step 6:start a new auxiliary database(duplicate database)

$ export ORACLE_SID=dg2
$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 23 16:15:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/init.ora
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers

SQL>exit

step 7:use RMAN to connect to source database and auxiliary database

$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 23 16:19:02 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DG1 (DBID=1694483490)

RMAN>
RMAN> connect auxiliary sys/oracledb@dg2

connected to auxiliary database: DG2 (not mounted)

step 8:use RMAN duplicate the target database

RMAN> duplicate target database to dg2 ;

Starting Duplicate Db at 23-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
   set until scn  561138;
   set newname for datafile  1 to
"/u01/oracle/oradata/dg2/system01.dbf";
   set newname for datafile  2 to
"/u01/oracle/oradata/dg2/undotbs01.dbf";
   set newname for datafile  3 to
"/u01/oracle/oradata/dg2/sysaux01.dbf";
   set newname for datafile  4 to
"/u01/oracle/oradata/dg2/users01.dbf";
   set newname for datafile  5 to
"/u01/oracle/oradata/dg2/example01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-MAR-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/dg2/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/dg2/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/dg2/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/dg2/users01.dbf
restoring datafile 00005 to /u01/oracle/oradata/dg2/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/product/10.2.0/db_1/dbs/02l9a486_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/oracle/product/10.2.0/db_1/dbs/02l9a486_1_1 tag=TAG20100323T155134
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:47
Finished restore at 23-MAR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DG2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( '/u01/oracle/oradata/dg2/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/oracle/oradata/dg2/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/oracle/oradata/dg2/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
  '/u01/oracle/oradata/dg2/system01.dbf'
CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714414353 filename=/u01/oracle/oradata/dg2/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714414353 filename=/u01/oracle/oradata/dg2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714414353 filename=/u01/oracle/oradata/dg2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714414353 filename=/u01/oracle/oradata/dg2/example01.dbf

contents of Memory Script:
{
   set until scn  561138;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/oracle/archive/1_6_713115876.dbf
archive log filename=/u01/oracle/archive/1_6_713115876.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-MAR-10

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     213909504 bytes

Fixed Size                     2019672 bytes
Variable Size                 75501224 bytes
Database Buffers             134217728 bytes
Redo Buffers                   2170880 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DG2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( '/u01/oracle/oradata/dg2/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/oracle/oradata/dg2/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/oracle/oradata/dg2/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
  '/u01/oracle/oradata/dg2/system01.dbf'
CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   set newname for tempfile  1 to
"/u01/oracle/oradata/dg2/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/oracle/oradata/dg2/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/oracle/oradata/dg2/sysaux01.dbf";
   catalog clone datafilecopy  "/u01/oracle/oradata/dg2/users01.dbf";
   catalog clone datafilecopy  "/u01/oracle/oradata/dg2/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/oracle/oradata/dg2/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/oracle/oradata/dg2/undotbs01.dbf recid=1 stamp=714414365

cataloged datafile copy
datafile copy filename=/u01/oracle/oradata/dg2/sysaux01.dbf recid=2 stamp=714414365

cataloged datafile copy
datafile copy filename=/u01/oracle/oradata/dg2/users01.dbf recid=3 stamp=714414365

cataloged datafile copy
datafile copy filename=/u01/oracle/oradata/dg2/example01.dbf recid=4 stamp=714414365

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714414365 filename=/u01/oracle/oradata/dg2/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714414365 filename=/u01/oracle/oradata/dg2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714414365 filename=/u01/oracle/oradata/dg2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714414365 filename=/u01/oracle/oradata/dg2/example01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 23-MAR-10
RMAN>

now,creating a duplicate database from source database on the some host have done!
---END---

Tags - oracle , rman , duplicate , database , target

ORA-12528

Mon, 22 Mar 2010 08:32:14 +0000

today,i create a duplicate database from a source databse

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAR-2010 16:23:38
…………
Instance "test1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

when connect to instance test,

"ORA-12528: TNS:listener: all appropriate instances are blocking new connections"

read metalink 419440.1,found that from all oracle 9i to oracle 11g have this problem !
what cause of this problem is, although instance starts, but did not register to the listener.
Instance through the PMON process to listen on the register, while the PMON process requires
the instance start in the MOUNT status!
then editor $ORACLE_HOME/network/admin/listener.ora
add :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = test1)
      (ORACLE_HOME =  /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = test1)
    )
  )

then restart listener,and then

Instance "test1", status UNKNOWN, has 1 handler(s) for this service...l

Tags - oracle , ora-12528

Streaming Replication in postgresql 9.0

Thu, 18 Mar 2010 06:46:16 +0000

Streaming Replication (SR) is an important new feature in postgresql 9.0 ,it can provides the capability to continuously ship and apply the WAL XLOG records to some number of standby servers in order to keep them current.
and,this new feature has included in PostgreSQL 9.0 Alpha4.

now, i write this article to test and experience this new feature !

env:
OS version: debian lenny 5.0 x86_64
Postgresql version : 9.0 alpha4
IP: 192.168.0.156

my planning is below

Master:
====================================
installded : /usr/local/pgsql_master
DATA=/usr/local/pgsql_master/data
port:5432
Archive_dest:/usr/local/pgsql_master/data/archive_master

Standby:
=====================================
installded : /usr/local/pgsql_standby
DATA=/usr/local/pgsql_standby/data
port:5433
Archive_dest:/usr/local/pgsql_standby/data/archive_standby

step 1 : download PostgreSQL 9.0 Alpha4 and install postgresql for both master and standby

step 2 : configuration master postgresql config file
$vim /usr/local/pgsql_master/data/postgresql.conf

listen_addresses = '192.168.0.156'
port = 5432
archive_mode = on
archive_command = 'cp "%p" /usr/local/pgsql_master/data/archive_master/"%f"'
max_wal_senders = 5

step 3:configuration authentication
$vim /usr/local/pgsql_master/data/pg_hba.conf

host all all 192.168.0.156/32 trust

step 4:start up master

$/usr/local/pgsql_master/bin/pg_ctl -D /usr/local/pgsql_master/data/ -l /usr/local/pgsql_master/data/logfile start

step 5:make a base backup for standby from master

$/usr/local/pgsql_master/bin/psql -p5432 -c "SELECT pg_start_backup('base backup')"
pg_start_backup
-----------------
0/1000020
(1 row)

$cp -frp /usr/local/pgsql_master/data/* /usr/local/pgsql_standby/data/

$/usr/local/pgsql_master/bin/psql -p5432 -c "SELECT pg_stop_backup()"
pg_stop_backup
----------------
0/10000E0
(1 row)

step 6: configuration standby postgresql config file

listen_addresses = '192.168.0.156'
port = 5433
archive_mode = on
archive_command = 'cp "%p" /usr/local/pgsql_standby/data/archive_standby/"%f"'
recovery_connections = on

$rm -fr /usr/local/pgsql_standby/data/postmaster.pid
$rm -fr /usr/local/pgsql_standby/data/pg_xlog/0000000*
$rm -fr /usr/local/pgsql_standby/data/pg_xlog/archive_status/*
$mkdir /usr/local/pgsql_standby/data/archive_standby

step 7: configuration recovery config file for standby
vim recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.156 port=5432 user=postgres'
trigger_file = '/usr/local/pgsql_master/data/archive_master/failover.trigger'
restore_command = 'cp -i /usr/local/pgsql_master/data/archive_master/%f "%p"

step 8: startup standby

step 9 : check process

$ ps -ef|grep postgres:
postgres 13951 13949  0 14:30 ?        00:00:00 postgres: writer process
postgres 13952 13949  0 14:30 ?        00:00:00 postgres: wal writer process
postgres 13953 13949  0 14:30 ?        00:00:00 postgres: autovacuum launcher process
postgres 13954 13949  0 14:30 ?        00:00:00 postgres: archiver process
postgres 13955 13949  0 14:30 ?        00:00:00 postgres: stats collector process
postgres 13965 13964  0 14:30 ?        00:00:00 postgres: startup process   recovering 000000010000000000000003
postgres 13968 13964  0 14:30 ?        00:00:00 postgres: writer process
postgres 13969 13964  0 14:30 ?        00:00:00 postgres: stats collector process
postgres 13974 13964  0 14:30 ?        00:00:00 postgres: wal receiver process   streaming 0/3000088
postgres 13975 13949  0 14:30 ?        00:00:00 postgres: wal sender process postgres 192.168.0.156(6138) streaming 0/3000088

step 10 : insert some data to master and then check on standby for test

./psql -p5432
psql (9.0alpha4)
Type "help" for help.

postgres=# create table test(id int not null primary key,name varchar(20));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

postgres=# insert into test values(1,'askwan');
INSERT 0 1
postgres=# insert into test values(2,'wanzhihua');
INSERT 0 1
postgres=# select * from test;
id |   name
----+-----------
  1 | askwan
  2 | wanzhihua
(2 rows)

to standby :

./psql -p5433
psql (9.0alpha4)
Type "help" for help.

postgres=# select * from test;
id |   name
----+-----------
  1 | askwan
  2 | wanzhihua
(2 rows)

if you want to insert data to standby,error message output :

postgres=# insert into test values ( 3,'error message');
ERROR: transaction is read-only

step 11 : if some error happend on master ,than you can do a fail over
simply create a file name "failover.trigger" on standby
path "/usr/local/pgsql_master/data/archive_master/"
when master server normal ,you want to use that master again ,
than Repeat the operations from step 5th to step 11th.

Summary:
write later !! FYA.

Tags - streaming , replication , postgresql , stream , standby , master , sr

Oracle Block Change Tracking

Tue, 16 Mar 2010 09:27:52 +0000

Oracle Block Change Tracking is a new feature in oracle 10g ,it can improve RMAN incremental backup proformance by recording changed blocks in each datafile use a change tracking file.If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.
Identify current database enable block change tracking feature or not:

SQL> select filename,status,bytes from v$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS BYTES
---------- ----------

DISABLED

Enabling and Disabling Change Tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19773: must specify change tracking file name

ENABLE BCT:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
  2  USING FILE '/opt/oracle/oracle/flash_recovery_area/CAPITALV/rman_change_track.f' REUSE;

Database altered.
SQL> select filename,status,bytes from v$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS          BYTES
---------- ----------
/opt/oracle/oracle/flash_recovery_area/CAPITALV/rman_change_track.f
ENABLED      11599872

DISABLE BCT:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> select filename,status,bytes from v$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS BYTES
---------- ----------

DISABLED

Moving the Change Tracking File：
step 1 :

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
FILENAME
--------------------------------------------------------------------------------
/opt/oracle/oracle/flash_recovery_area/CAPITALV/rman_change_track.f

   step 2 :
   shutdown database

   step 3:

cp /opt/oracle/oracle/flash_recovery_area/CAPITALV/rman_change_track.f /tmp/

step 4 :
mount database and do :

alter database rename file '/opt/oracle/oracle/flash_recovery_area/CAPITALV/rman_change_track.f' to '/tmp/rman_change_track.f'

step 5 : open database
alter database open

referrence ：《Oracle® Database Backup and Recovery Basics》

Tags - oracle , block , change , tracking , bct

configuration oracle data guard for testing

Thu, 11 Mar 2010 06:39:41 +0000

OS: CentOS 5.4 x86_64
Oracle: Oracle 10.2.0.1 64bit for linux

题外话：有些东西长时间不接触，而理解又不深刻的话，慢慢就淡忘了。。。
这个笔记是边参考文档oracle 10g《Data Guard Concepts and Administration》，边验证记录下的
仅供参考

单机环境配置oracle 10g dataguard (physical standby)
IP：192.168.0.21
预期规划如下：

主库：
db_name=dg1
sid=dg1
db_unique_name=primary
network_service_name=primary_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/dg1
归档日志路径=/u01/oracle/oradata/dg1/archive
警告跟踪等日志文件路径=/u01/oracle/admin/dg1/

从库：
dg_name=dg1
sid=standby
db_unique_name=standby
network_service_name=standby_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/standby
归档日志路径=/u01/oracle/oradata/standby/archive
警告跟踪等日志文件路径=/u01/oracle/admin/standby

1 准备工作

      安装完oracle软件后，创建主库

1.1 登录主库，将主库置于归档模式下：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/dg1/archive
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

打开force logging

SQL>alter database force logging;

SQL>select dbid,log_mode,force_logging from v$database;

DBID LOG_MODE FOR
---------- ------------ ---
1694483490 ARCHIVELOG YES

1.2 建立密码文件

$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwdg1 password=askwan.com entries=8
$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwstandby password=askwan.com entries=8

1.3 为从库建立相应目录

$ mkdir /u01/oracle/oradata/standby
$ mkdir /u01/oracle/admin/standby/
$ mkdir /u01/oracle/admin/standby/{a,b,c,dp,u}dump
$ mkdir /u01/oracle/admin/standby/pfile

1.4 配置standby redo log

SQL> alter database add standby logfile
        group 4 ('/u01/oracle/oradata/dg1/standby_redo04.log') size 50m,
        group 5 ('/u01/oracle/oradata/dg1/standby_redo05.log') size 50m,
        group 6 ('/u01/oracle/oradata/dg1/standby_redo06.log') size 50m,
        group 7 ('/u01/oracle/oradata/dg1/standby_redo07.log') size 50m;

Database altered.

2 配置监听LISTENER和网络服务名NETWORK SERVICE NAME

2.1 配置 listener.ora

#Network Configuration File: #/u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = dg1)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
    (SID_DESC =
      (SID_NAME = standby)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

2.2 配置网络服务器名

# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = primary)
    )
  )

STANDBY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

2.3 启动监听

$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2010 11:10:27

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.21)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                10-MAR-2010 11:10:27
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1" has 1 instance(s).
  Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3 配置主库初始化参数文件

SQL> create pfile from spfile;

File created.

在/u01/oracle/product/10.2.0/db_1/dbs路径下编辑主库刚生成的pfile文件initdg1.ora

3.1 配置主库参数文件initdg1.ora
未编辑前：

编辑后：

dg1.__db_cache_size=134217728
dg1.__java_pool_size=4194304
dg1.__large_pool_size=4194304
dg1.__shared_pool_size=67108864
dg1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/dg1/adump'
*.background_dump_dest='/u01/oracle/admin/dg1/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/dg1/control01.ctl','/u01/oracle/oradata/dg1/control02.ctl','/u01/oracle/oradata/dg1/control03.c
tl'
*.core_dump_dest='/u01/oracle/admin/dg1/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/dg1/udump'
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='standby','dg1'
LOG_FILE_NAME_CONVERT='standby','dg1'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/dg1/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=standby_21
FAL_CLIENT=primary_21

3.2 配置从库初始化参数文件initstandby.ora
从库文件则依据主库文件修改，修改后：

standby.__db_cache_size=134217728
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=67108864
standby.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/standby/adump'
*.background_dump_dest='/u01/oracle/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/standby/control01.ctl','/u01/oracle/oradata/standby/control02.ctl','/u01/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/standby/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='dg1','standby'
LOG_FILE_NAME_CONVERT='dg1','standby'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/standby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_CLIENT=standby_21
FAL_SERVER=primary_21

4 备份主库数据文件到从库

SQL> alter database begin backup;

Database altered.

$ cp -frp /u01/oracle/oradata/dg1/* /u01/oracle/oradata/standby/
$ rm -fr /u01/oracle/oradata/standby/control0*.ctl

建立从库控制文件

SQL> alter database create standby controlfile as '/u01/oracle/oradata/standby/control01.ctl';

Database altered.

SQL> alter database end backup;

Database altered.

$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control02.ctl

$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control03.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.

5 数据库启动
5.1 从库打开redo apply

$ export ORACLE_SID=standby

SQL>startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora' nomount
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.

SQL>alter database mount standby database

SQL> alter database recover managed standby database disconnect from session;

主库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
4

从库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
4

切换操作不做了

参考文档:《Data Guard Concepts and Administration》
Tags - oracle , dataguard

a simple oracle rman backup script

Fri, 05 Mar 2010 08:17:22 +0000

Note:this backup script is suitable for db size < 200G,
and for backup file safety ,may be you should use an independent disk to store backup files

1 configuration target database to archive log mode

2 configuration or check log_archive_dest and db_recovery_file_dest or log_archive_dest_n

3 write a rman script

oracle@dbtest:~$ vim backup.rman

run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/orabak/ctfile_%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/opt/oracle/orabak/DBFULL_%U';
BACKUP DATABASE SKIP INACCESSIBLE PLUS ARCHIVELOG FILESPERSET 20 DELETE ALL INPUT;
DELETE OBSOLETE;
RELEASE CHANNEL C1;
}

4 write backup shell script

oracle@dbtest:~$ vim backup.sh

#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=capitalv
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH
rman TARGET / log='/opt/oracle/orabak/rman.log' cmdfile='/opt/oracle/backup.rman'

5 set cronjob for user oracle

30 02 * * 0,3 bash /opt/oracle/backup.sh

Tags - oracle , rman , backup

how to create a rman catalog

Tue, 02 Mar 2010 04:07:06 +0000

SQL> create tablespace rmantest datafile '/u01/oracle/oradata/askwan/rmantest01.dbf' size 100M autoextend on ;

Tablespace created.

SQL> create user rman identified by rman;

User created.

SQL> alter user rman default tablespace rmantest temporary tablespace temp;

User altered.

SQL> alter user rman quota unlimited on rmantest;

User altered.

SQL> grant recovery_catalog_owner,connect,resource to rman;

Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@oracle ~]$ rman catalog rman/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 2 12:10:44 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

RMAN>
RMAN> create catalog tablespace rmantest;

recovery catalog created

create rman catalog done!
Tags - rman , catalog

log_archive_dest and db_recovery_file_dest

Tue, 02 Mar 2010 03:45:34 +0000

默认安装情况下，oracle归档路径可能只有一个，即$ORACLE_HOME/flash_recovery_area这个位置：

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/oracle/product/10.
                                                 2.0/db_1/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

而其他归档日志参数默认值都为空：
SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0

使用参数 log_archive_dest 修改归档日志路径
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
alter system set log_archive_dest='/opt/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

报db_recovery_file_dest参数不能和LOG_ARCHIVE_DEST还有 LOG_ARCHIVE_DEST_N同时使用，先将
db_recovery_file_dest参数置空：

SQL> alter system set db_recovery_file_dest='' scope=both;

System altered.

SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;

System altered.

归档日志参数的格式：
SQL> show parameter log_archive_format;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

SQL> alter system switch logfile;

System altered.

查看归档日志：
SQL> host ls -al /opt/oracle/archive/
total 1156
drwxr-xr-x  2 oracle oinstall    4096 2010-03-02 11:28 .
drwxrwxr-x 25 oracle oinstall    4096 2010-03-02 11:14 ..
-rw-r-----  1 oracle oinstall 1170432 2010-03-02 11:28 1_34_708277817.dbf

oracle建议归档日志使用log_archive_dest_n代替log_archive_dest,故一般将log_archive_dest和db_recovery_file_dest置空，然后设置log_archive_dest_n .

configuration oracle 10g archive log mode

Fri, 15 Jan 2010 07:18:45 +0000

VERSION:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Check current database archive log mode

SQL> select dbid,name,log_mode from v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
2406511032 CAPITALV  NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Current log sequence           34

shutdown database clear

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup database to mount status:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             142607832 bytes
Database Buffers          134217728 bytes
Redo Buffers                6303744 bytes
Database mounted.

open database archive log mode :

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            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34

stop archive log mode :

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             142607832 bytes
Database Buffers          134217728 bytes
Redo Buffers                6303744 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             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Current log sequence           34

Tags - oracle , archive , archivelog , mode

oracle 10g em and isqlplus unrecognizable chinese characters on debian lenny

Mon, 28 Dec 2009 04:26:27 +0000

OS:Debian Lenny 503 amd64
Oracle 10g R2

emctl stop dbconsole
isqlplusctl stop

Quotation

cd $ORACLE_HOME/jdk/jre/lib
mv font.properties font.properties_bak20091229
cp -frp font.properties.zh_CN.Redhat font.properties

vim font.properties
in the last line :
change

Quotation

filename.-misc-zysong18030-medium-r-normal--*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_CN/TrueType/zysong.ttf

Quotation

filename.-misc-zysong18030-medium-r-normal--*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_CN/TrueType/simhei.ttf

simhei.ttf copied from windows (C:\WINDOWS\Fonts )

Quotation

cp -f font.properties $ORACLE_HOME/jre/1.4.2/lib
cp -f font.properties $ORACLE_HOME/javavm/lib/ojvmfonts

Quotation

rm -fr $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zhs/*.gif
rm -fr $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/cabo/images/cache/*.gif

emctl start dbconsole
isqlplusctl start

-------EOF--------

Postgresql Warm Standby Testing

Thu, 03 Dec 2009 03:24:12 +0000

OS: Debian5.0  lenny x86_64
Postgresql:8.3.8
Master :192.168.0.121
Warm  Standby:192.168.0.122

1.  install postgresql on both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/
#./configure –prefix=/usr/local/pgsql
#make
#make install
#mkdir /usr/local/pgsql/data
#chown postgres /usr/local/pgsql/data
#su - postgres
$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

2. Config passwordless ssh authentication use user postgres
Please refer to http://www.petefreitag.com/item/532.cfm

3. Install pg_standby on both both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/contrib/pg_standby
#make
#make install

4. Config postgresql

In master server :

Quotation

archive_mode = on
archive_command = 'rsync -arv %p postgres@192.168.0.122:/wal_archives/%f'
archive_timeout = 1200

in warm standby server :

#mkdir /wal_archives
#chown postgres /wal_archives/

start master server :

pg_ctl -D /usr/local/pgsql/data/ start -l /usr/local/pgsql/data/logfile

5.  Dump all production data to master server  .

You can check the warm standby server ‘s /wal_archives directory , whether there is archive logs  from master server located on .

6.  Login to master  server as postgres user.
Create a script to do a base backup:

Quotation

#!/bin/bash
PGDATA=/usr/local/pgsql/data
psql -c "CHECKPOINT;"
psql -c "SELECT pg_start_backup('BASEBACKUP');"
tar -C /usr/local/pgsql/ -zcf - data |ssh 192.168.0.122 "tar -C /usr/local/pgsql/ -zxf -"
psql -c "select pg_stop_backup();"
echo "BASEBACKUP compete!"

7. Login to warm standby server as postgresql user.
Create a script to do some clean and configuration

Quotation

#!/bin/bash
PG_HOME=/usr/local/pgsql
PGDATA=/usr/local/pgsql/data
trigger_file=/tmp/pgsql.trigger
pg_standby=$PG_HOME/bin/pg_standby
PG_ARCHIVES=/wal_archives
  rm -f $PGDATA/recovery.*
  rm -f $PGDATA/logfile
  rm -f $PGDATA/postmaster.pid
  rm -f $PGDATA/pg_xlog/0*
  rm -f $PGDATA/pg_xlog/archive_status/0*
  sed -i '/^archive_/s/^/#/g' $PGDATA/postgresql.conf
  echo "restore_command = '$pg_standby -l -d -s 2 -t $trigger_file $PG_ARCHIVES %f %p %r 2>>/tmp/standby.log'" > $PGDATA/recovery.co
nf
  chown postgres.postgres $PGDATA/recovery.conf
  echo "Init completed! now start warm standby server."

Note:PostgreSQL 8.4 provides the recovery_end_command option.so,in recovery.conf add recovery_end_command=’rm –f /tmp/pgsql.trigger ’

pg_standby supports creation of a "warm standby" database server. It is designed to be a production-ready program, as well as a customizable template should you require specific modifications.
More about pg_standby,refer to http://www.postgresql.org/docs/current/static/pgstandby.html
http://www.enterprisedb.com/docs/en/8.4/pg/pgstandby.html

8. Start warm standby server

pg_ctl -D /usr/local/pgsql/data start -l /usr/local/pgsql/data/logfile

9. check warm standby postgresql logfile:

Quotation

LOG:  database system was interrupted; last known up at 2009-12-03 11:44:37 CST
LOG:  starting archive recovery
LOG:  restore_command = '/usr/local/pgsql/bin/pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /wal_archives %f %p %r 2>>/tmp/standby.log
'
LOG:  restored log file "000000010000000000000014.00000020.backup" from archive
LOG:  restored log file "000000010000000000000014" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 0/14000068
FATAL:  the database system is starting up
LOG:  restored log file "000000010000000000000015" from archive
LOG:  restored log file "000000010000000000000016" from archive
LOG:  restored log file "000000010000000000000017" from archive
LOG:  restored log file "000000010000000000000018" from archive
LOG:  restored log file "000000010000000000000019" from archive
LOG:  restored log file "00000001000000000000001A" from archive
LOG:  restored log file "00000001000000000000001B" from archive
LOG:  restored log file "00000001000000000000001C" from archive
LOG:  restored log file "00000001000000000000001D" from archive
LOG:  restored log file "00000001000000000000001E" from archive
LOG:  restored log file "00000001000000000000001F" from archive
LOG:  restored log file "000000010000000000000020" from archive
LOG:  restored log file "000000010000000000000021" from archive
……

Check standby logfile:

Quotation

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 00000001.history
WAL file path           : /wal_archives/00000001.history
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014.00000020.backup
WAL file path           : /wal_archives/000000010000000000000014.00000020.backup
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014.00000020.backup" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014
WAL file path           : /wal_archives/000000010000000000000014
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000015
WAL file path           : /wal_archives/000000010000000000000015
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000015" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000016
WAL file path           : /wal_archives/000000010000000000000016
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds

Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000016" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
running restore         : OK
......
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
......

10. failover
In warm standby server
touch /tmp/ pgsql.trigger
Tags - postgresql , warm , standby , pitr

use pgFouine analyse postgresql slow queries

Wed, 25 Nov 2009 01:55:55 +0000

"pgFouine is a PostgreSQL log analyzer used to generate detailed reports from a PostgreSQL log file. pgFouine can help you to determine which queries you should optimize to speed up your PostgreSQL based application."

you can use syslog or stderr

for example :use syslog

1. edit your /etc/syslog.conf

Quotation

local0.* -/var/log/pgsql

Quotation

*.info;mail.none;authpriv.none;cron.none;local0.none /var/log/messages

2. edit postgresl.conf

Quotation

log_destination = 'syslog'
silent_mode = on

log_min_duration_statement = 0
#set to 0 log all
#set to -1 ,disable query logging
#set to X(positive integral) ,means to log queries slower than X milliseconds:

log_duration = off
log_statement = 'all'

3.download pgFouine
http://pgfouine.projects.postgresql.org/index.html

than restart syslogd and postgresql

4. analyse log
./pgfouine.php -file /var/log/postgresql/pgsql > askwan.com.html

use stderr please refer to http://gkoenig.wordpress.com/2009/02/19/pgfouine-dive-into-postgres-log/
Tags - use , pgfouine , analyse , postgresql , slow , queries