Peoplesoft Oracle VM (PSOVM) templates are really nice. That allows customers to test Peoplesoft Application in an easy way. Easy and quick deployment.
Moreover, assuming you have space enough to host the VMs and a spare server to host the Oracle VM Server, the cost is nearly zero.
=> That's the perfect tool for the business team, the latest PSOVM HCM9.1 is coming within the Features Pack 1 (Dec-2010). All the new functionalities are included and there's also the optional installation of the 30-days trial Cobol license.
=> That's the perfect tool for the development team, the latest PSOVM HCM9.1 is coming within the latest Peopletools version (8.51.02 - the patch 07 is already released, but except bugs fixes, it should not make big differences), and it’s also coming within the binaries for AppDesigner and DataMover. Developpers can now test everything they want without much configuration and installation efforts.
=> That's (almost) the perfect tool for the Peoplesoft DBA, in addition of the latest Peopletools version, the latest PSOVM HCM9.1 is coming within one of the latest Oracle Database version. Unfortunately, the Oracle database is not the last version, it is 11gR1 (11.1.0.7). Some new features of 11gR2 would be great to have, to name only two : segment creation deferrable (do not occupied space when table is empty) and few on Active Data Guard (ASH, STANDBY_MAX_DATA_DELAY).
But 11gR1 is good enough to test the Active Data Guard that Peoplesoft customers are looking for a while. A really nice feature to get a reporting database whilst the redolog continue to be applied (it’s known as real-time query). And that will be the subject here.
Moreover, assuming you have space enough to host the VMs and a spare server to host the Oracle VM Server, the cost is nearly zero.
=> That's the perfect tool for the business team, the latest PSOVM HCM9.1 is coming within the Features Pack 1 (Dec-2010). All the new functionalities are included and there's also the optional installation of the 30-days trial Cobol license.
=> That's the perfect tool for the development team, the latest PSOVM HCM9.1 is coming within the latest Peopletools version (8.51.02 - the patch 07 is already released, but except bugs fixes, it should not make big differences), and it’s also coming within the binaries for AppDesigner and DataMover. Developpers can now test everything they want without much configuration and installation efforts.
=> That's (almost) the perfect tool for the Peoplesoft DBA, in addition of the latest Peopletools version, the latest PSOVM HCM9.1 is coming within one of the latest Oracle Database version. Unfortunately, the Oracle database is not the last version, it is 11gR1 (11.1.0.7). Some new features of 11gR2 would be great to have, to name only two : segment creation deferrable (do not occupied space when table is empty) and few on Active Data Guard (ASH, STANDBY_MAX_DATA_DELAY).
But 11gR1 is good enough to test the Active Data Guard that Peoplesoft customers are looking for a while. A really nice feature to get a reporting database whilst the redolog continue to be applied (it’s known as real-time query). And that will be the subject here.
So, the exercise will be to have a database that end users will be able to query without over-loading the primary database, but having the latest data. First of all, create a Physical Standby Database.
Since primary and secondary databases will come from the same templates, we are sure about the same OS (and version) (OEL5.2) and the same database version (11.1.0.7).
Since primary and secondary databases will come from the same templates, we are sure about the same OS (and version) (OEL5.2) and the same database version (11.1.0.7).
1. The configuration
1.1 Install a working PSOVM environment as shown here
=> 1 database server, the primary site (psovmhcmdb), hosting the primary database (db name H91TMPLT, unique name H91TMPLT).
=> 1 App/Batch/PIA server
1.1 Install a working PSOVM environment as shown here
=> 1 database server, the primary site (psovmhcmdb), hosting the primary database (db name H91TMPLT, unique name H91TMPLT).
=> 1 App/Batch/PIA server
1.2 Install an additional database server, the secondary site (psovmhcmdb2) hosting the secondary database (db name H91TMPLT, unique name H91STBY). This one is deployed as a normal db server (see step 1.1), to make it simpler, the database name is named as the primary one, H91TMPLT. The PSOVM deployment scripts will create all the necessary folders and files.
1.3 Here below a schema of the target configuration we are currently looking for at this stage.
Note 1 : the DB_NAME and DB_UNIQUE_NAME of the database on primary site is H91TMPLT
Note 2 : the DB_NAME of the database on secondary site is H91TMPLT, DB_UNIQUE_NAME is H91STBY
Note 1 : the DB_NAME and DB_UNIQUE_NAME of the database on primary site is H91TMPLT Note 2 : the DB_NAME of the database on secondary site is H91TMPLT, DB_UNIQUE_NAME is H91STBY
2. First settings on primary db (primary site – psovmhcmdb)
It is assumed the primary database server has been successfully deployed and database name is H91TMPLT.
2.1 Archive log mode
The delivered database in PSOVM Database Server is not in archivelog mode, it has to be changed :
[oracle@psovmhcmdb .ssh]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb .ssh]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 04:49:35 2011 It is assumed the primary database server has been successfully deployed and database name is H91TMPLT.
2.1 Archive log mode
The delivered database in PSOVM Database Server is not in archivelog mode, it has to be changed :
[oracle@psovmhcmdb .ssh]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb .ssh]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 288104448 bytes ORACLE instance started.
Fixed Size 2159032 bytes
Variable Size 96472648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4923392 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
2.2 spfile
The delivered database in PSOVM Database Server does not use a spfile but a pfile, let’s make it
SQL> show parameter spfile
NAME TYPE VALUE The delivered database in PSOVM Database Server does not use a spfile but a pfile, let’s make it
SQL> show parameter spfile
------------ ------------ ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> startup force
ORACLE instance started.
Total System Global Area 288104448 bytes
Fixed Size 2159032 bytes
Variable Size 96472648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4923392 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------ ------------ ------------------------------
spfile string /u01/app/oracle/product/11.1.0/db_1/dbs/spfileH91TMPLT.ora
2.3 Create the directory of archive log files
We will create a dedicated folder for the archive log files onto the primary database server, first, have a look where it should go.
[oracle@psovmhcmdb oradata]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/xvda2 3177204 1037312 2139892 33% /
/dev/xvda1 31077 8277 21196 29% /boot
tmpfs 2097152 0 2097152 0% /dev/shm
/dev/xvdb1 9851308 4480692 4870196 48% /u01
/dev/xvdc1 41286796 20908248 18281264 54% /opt/oracle/psft/ptdb <—18Gb free seems good for us
[root@psovmhcmdb ~]# cd /opt/oracle/psft/ptdb We will create a dedicated folder for the archive log files onto the primary database server, first, have a look where it should go.
[oracle@psovmhcmdb oradata]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/xvda2 3177204 1037312 2139892 33% /
/dev/xvda1 31077 8277 21196 29% /boot
tmpfs 2097152 0 2097152 0% /dev/shm
/dev/xvdb1 9851308 4480692 4870196 48% /u01
/dev/xvdc1 41286796 20908248 18281264 54% /opt/oracle/psft/ptdb <—18Gb free seems good for us
[root@psovmhcmdb ptdb]# mkdir -p arch/H91TMPLT
[root@psovmhcmdb ptdb]# chown -R oracle:dba arch
[root@psovmhcmdb ptdb]# su - oracle
[oracle@psovmhcmdb ~]$ cd /u01/app/oracle/oradata
[oracle@psovmhcmdb oradata]$ ln -s /opt/oracle/psft/ptdb/arch/H91TMPLT H91TMPLT_arch
[oracle@psovmhcmdb oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Jan 23 06:18 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 05:00 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
......
Let’s make the database using it
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 288104448 bytes
Fixed Size 2159032 bytes
Variable Size 96472648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4923392 bytes
Database mounted.
SQL> alter system set log_archive_dest='/u01/app/oracle/oradata/H91TMPLT_arch';
System altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/H91TMPLT_arch
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>
2.4 Force logging
The primary database must be turned on to force logging :
SQL> alter database force logging;
Database altered.
SQL>
3. Remove db file on secondary site - psovmhcmdb2
It is assumed the secondary database server has been successfully deployed and database name is H91TMPLT. When the secondary database server has been deployed, it created the database, we don’t need that one, all the datafiles must be removed.
3.1 Check for the datafiles
[oracle@psovmhcmdb2 ~]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 05:10:50 2011 It is assumed the secondary database server has been successfully deployed and database name is H91TMPLT. When the secondary database server has been deployed, it created the database, we don’t need that one, all the datafiles must be removed.
3.1 Check for the datafiles
[oracle@psovmhcmdb2 ~]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 ~]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select distinct substr(name,1,instr(name,'/',-1)) from v$datafile
2 union
3 select distinct substr(name,1,instr(name,'/',-1)) from v$tempfile
4 union
5 select distinct substr(member,1,instr(member,'/',-1)) from v$logfile;
SUBSTR(NAME,1,INSTR(NAME,'/',-1))
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/H91TMPLT/
3.2 Shutdown the secondary database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psovmhcmdb2 ~]$
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psovmhcmdb2 ~]$
3.3 Remove the db file
[oracle@psovmhcmdb2 ~]$ rm -f /u01/app/oracle/oradata/H91TMPLT/*
[oracle@psovmhcmdb2 ~]$ rm -f /u01/app/oracle/oradata/H91TMPLT/*
4. Copy the db file of primary to secondary site - psovmhcmdb2
4.1 Check the dbf of primary database and create the script to copy all the datafiles
SQL> set lines 200 pages 0 head off feed off trims on
SQL> spool scp.sh
SQL> select 'gzip -c '||name||' | ssh psovmhcmdb2 "gunzip -c > '||name||'"' from v$datafile;
...
gzip -c /u01/app/oracle/oradata/H91TMPLT/waapp.dbf | ssh psovmhcmdb2 "gunzip -c > /u01/app/oracle/oradata/H91TMPLT/waapp.dbf"
SQL> spool off
4.1 Check the dbf of primary database and create the script to copy all the datafiles
SQL> set lines 200 pages 0 head off feed off trims on
SQL> spool scp.sh
SQL> select 'gzip -c '||name||' | ssh psovmhcmdb2 "gunzip -c > '||name||'"' from v$datafile;
...
gzip -c /u01/app/oracle/oradata/H91TMPLT/waapp.dbf | ssh psovmhcmdb2 "gunzip -c > /u01/app/oracle/oradata/H91TMPLT/waapp.dbf"
SQL> spool off
4.2 Shutdown the primary db
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psovmhcmdb ~]$
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psovmhcmdb ~]$
4.3 Copy the dbf from primary to secondary site (execute the script create earlier at step 4.1). Good enough, the name of the two databases on both sites are the same, consequently the path directories are the same, not change required. (note: the script will work if a ssh-keygen has been configured between the servers)
[oracle@psovmhcmdb ~]$ chmod +x scp.sh
[oracle@psovmhcmdb ~]$ ./scp.sh
[oracle@psovmhcmdb ~]$ chmod +x scp.sh
[oracle@psovmhcmdb ~]$ ./scp.sh
5. Open the primary db on the primary site - psovmhcmdb
5.1 create pfile from spfile
[oracle@psovmhcmdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 06:43:33 2011 5.1 create pfile from spfile
[oracle@psovmhcmdb ~]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
SQL> quit
Disconnected
[oracle@psovmhcmdb ~]$
5.2 Set the init parameters required for the physical standby settings (in bold the changes made)
[oracle@psovmhcmdb ~]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb dbs]$ vi initH91TMPLT.ora
H91TMPLT.__db_cache_size=184549376
H91TMPLT.__java_pool_size=4194304
H91TMPLT.__large_pool_size=4194304
H91TMPLT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
H91TMPLT.__pga_aggregate_target=96468992
H91TMPLT.__sga_target=289406976
H91TMPLT.__shared_io_pool_size=0
H91TMPLT.__shared_pool_size=88080384
H91TMPLT.__streams_pool_size=0
*._unnest_subquery=false
*.compatible='11.1.0.7.0'
*.control_files='/u01/app/oracle/oradata/H91TMPLT/control01.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_file_multiblock_read_count=16
*.DB_FILES=256
*.DB_NAME='H91TMPLT'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=XDB)'
*.job_queue_processes=10
#*.log_archive_dest='/u01/app/oracle/oradata/H91TMPLT_arch'
*.NLS_LENGTH_SEMANTICS='CHAR'
*.OPEN_CURSORS=1000
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='NONE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='PSUNDOTS'
#####
log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/H91TMPLT_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=H91TMPLT'
log_archive_dest_2='SERVICE=H91STBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=H91STBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
DB_UNIQUE_NAME=H91TMPLT
LOG_ARCHIVE_CONFIG='DG_CONFIG=(H91TMPLT,H91STBY)'
standby_file_management=auto
[oracle@psovmhcmdb ~]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb dbs]$ vi initH91TMPLT.ora
H91TMPLT.__db_cache_size=184549376
H91TMPLT.__java_pool_size=4194304
H91TMPLT.__large_pool_size=4194304
H91TMPLT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
H91TMPLT.__pga_aggregate_target=96468992
H91TMPLT.__sga_target=289406976
H91TMPLT.__shared_io_pool_size=0
H91TMPLT.__shared_pool_size=88080384
H91TMPLT.__streams_pool_size=0
*._unnest_subquery=false
*.compatible='11.1.0.7.0'
*.control_files='/u01/app/oracle/oradata/H91TMPLT/control01.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_file_multiblock_read_count=16
*.DB_FILES=256
*.DB_NAME='H91TMPLT'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=XDB)'
*.job_queue_processes=10
#*.log_archive_dest='/u01/app/oracle/oradata/H91TMPLT_arch'
*.NLS_LENGTH_SEMANTICS='CHAR'
*.OPEN_CURSORS=1000
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='NONE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='PSUNDOTS'
#####
log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/H91TMPLT_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=H91TMPLT'
log_archive_dest_2='SERVICE=H91STBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=H91STBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
DB_UNIQUE_NAME=H91TMPLT
LOG_ARCHIVE_CONFIG='DG_CONFIG=(H91TMPLT,H91STBY)'
standby_file_management=auto
5.3 recreate spfile from pfile
[oracle@psovmhcmdb dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 06:51:06 2011 [oracle@psovmhcmdb dbs]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
5.4 Open the primary database
SQL> startup mount
ORACLE instance started.
Total System Global Area 288104448 bytes SQL> startup mount
ORACLE instance started.
Fixed Size 2159032 bytes
Variable Size 96472648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4923392 bytes
Database mounted.
SQL>
5.5 Create standby controlfile and pfile for secondary db
SQL> alter database create standby controlfile as '/tmp/H91STBY.ctl';
Database altered. SQL> alter database create standby controlfile as '/tmp/H91STBY.ctl';
SQL> alter database open;
Database altered.
SQL> create pfile='/tmp/initH91STBY.ora' from spfile;
File created.
5.6 Set more parameters on primary database
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter system set FAL_SERVER=H91STBY;
System altered.
SQL> alter system set FAL_CLIENT=H91TMPLT;
System altered.
SQL>
6. tnsnames.ora
Set the primary and secondary databases in tnsnames.ora on both servers, primary and secondary sites, as following (assuming the servers name have been properly added into /etc/hosts) :
[oracle@psovmhcmdb dbs]$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:
H91TMPLT = Set the primary and secondary databases in tnsnames.ora on both servers, primary and secondary sites, as following (assuming the servers name have been properly added into /etc/hosts) :
[oracle@psovmhcmdb dbs]$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = psovmhcmdb.phoenix.nga)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = H91TMPLT.us.oracle.com )
(SID = H91TMPLT )
)
)
H91STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = psovmhcmdb2.phoenix.nga)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = H91TMPLT.us.oracle.com )
(SID = H91TMPLT )
)
)
7. Mount the secondary db on secondary site - psovmhcmdb2
7.1 Copy the standby controlfile and pfile created at step 5.5 onto the secondary site
[oracle@psovmhcmdb2 H91TMPLT]$ pwd
/u01/app/oracle/oradata/H91TMPLT
[oracle@psovmhcmdb2 H91TMPLT]$ scp root@psovmhcmdb:/tmp/H91STBY.ctl control01.ctl
[oracle@psovmhcmdb2 H91TMPLT]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb2 dbs]$ scp root@psovmhcmdb:/tmp/initH91STBY.ora initH91TMPLT.ora
[oracle@psovmhcmdb2 dbs]$
7.1 Copy the standby controlfile and pfile created at step 5.5 onto the secondary site
[oracle@psovmhcmdb2 H91TMPLT]$ pwd
/u01/app/oracle/oradata/H91TMPLT
[oracle@psovmhcmdb2 H91TMPLT]$ scp root@psovmhcmdb:/tmp/H91STBY.ctl control01.ctl
[oracle@psovmhcmdb2 H91TMPLT]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb2 dbs]$ scp root@psovmhcmdb:/tmp/initH91STBY.ora initH91TMPLT.ora
[oracle@psovmhcmdb2 dbs]$
7.2 Set the init parameters of the secondary database
[oracle@psovmhcmdb2 dbs]$ vi initH91TMPLT.ora
H91TMPLT.__db_cache_size=184549376
H91TMPLT.__java_pool_size=4194304
H91TMPLT.__large_pool_size=4194304
H91TMPLT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
H91TMPLT.__pga_aggregate_target=96468992
H91TMPLT.__sga_target=289406976
H91TMPLT.__shared_io_pool_size=0
H91TMPLT.__shared_pool_size=88080384
H91TMPLT.__streams_pool_size=0
*._unnest_subquery=false
*.compatible='11.1.0.7.0'
*.control_files='/u01/app/oracle/oradata/H91TMPLT/control01.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_file_multiblock_read_count=16
*.DB_FILES=256
*.DB_NAME='H91TMPLT'
*.DB_UNIQUE_NAME='H91STBY'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(H91TMPLT,H91STBY)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/H91TMPLT_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=H91STBY'
*.log_archive_dest_2='SERVICE=H91STBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=H91TMPLT'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.NLS_LENGTH_SEMANTICS='CHAR'
*.OPEN_CURSORS=1000
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='NONE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='PSUNDOTS'
standby_file_management=auto
fal_server=H91TMPLT
fal_client=H91STBY
[oracle@psovmhcmdb2 dbs]$ vi initH91TMPLT.ora
H91TMPLT.__db_cache_size=184549376
H91TMPLT.__java_pool_size=4194304
H91TMPLT.__large_pool_size=4194304
H91TMPLT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
H91TMPLT.__pga_aggregate_target=96468992
H91TMPLT.__sga_target=289406976
H91TMPLT.__shared_io_pool_size=0
H91TMPLT.__shared_pool_size=88080384
H91TMPLT.__streams_pool_size=0
*._unnest_subquery=false
*.compatible='11.1.0.7.0'
*.control_files='/u01/app/oracle/oradata/H91TMPLT/control01.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_file_multiblock_read_count=16
*.DB_FILES=256
*.DB_NAME='H91TMPLT'
*.DB_UNIQUE_NAME='H91STBY'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(H91TMPLT,H91STBY)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/H91TMPLT_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=H91STBY'
*.log_archive_dest_2='SERVICE=H91STBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=H91TMPLT'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.NLS_LENGTH_SEMANTICS='CHAR'
*.OPEN_CURSORS=1000
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='NONE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='PSUNDOTS'
standby_file_management=auto
fal_server=H91TMPLT
fal_client=H91STBY
7.3 Create the directory of archive log files on the secondary site
[root@psovmhcmdb2 ~]# mkdir -p /opt/oracle/psft/ptdb/arch/H91TMPLT
[root@psovmhcmdb2 ~]# chown -R oracle:dba /opt/oracle/psft/ptdb/arch
[root@psovmhcmdb2 ~]# su - oracle
[oracle@psovmhcmdb2 ~]$ cd /u01/app/oracle/oradata
[oracle@psovmhcmdb2 oradata]$ ln -s /opt/oracle/psft/ptdb/arch/H91TMPLT H91TMPLT_arch
[oracle@psovmhcmdb2 oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Mar 10 05:02 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:11 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
[oracle@psovmhcmdb2 oradata]$
[root@psovmhcmdb2 ~]# mkdir -p /opt/oracle/psft/ptdb/arch/H91TMPLT
[root@psovmhcmdb2 ~]# chown -R oracle:dba /opt/oracle/psft/ptdb/arch
[root@psovmhcmdb2 ~]# su - oracle
[oracle@psovmhcmdb2 ~]$ cd /u01/app/oracle/oradata
[oracle@psovmhcmdb2 oradata]$ ln -s /opt/oracle/psft/ptdb/arch/H91TMPLT H91TMPLT_arch
[oracle@psovmhcmdb2 oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Mar 10 05:02 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:11 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
[oracle@psovmhcmdb2 oradata]$
7.4 Mount the secondary database
[oracle@psovmhcmdb2 dbs]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 07:12:22 2011 [oracle@psovmhcmdb2 dbs]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 dbs]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 288104448 bytes
Fixed Size 2159032 bytes
Variable Size 96472648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4923392 bytes
Database mounted.
SQL>
8. Standby redologs
8.1 Create the standby redolog directoy on primary site - psovmhcmdb
[root@psovmhcmdb ptdb]# cd /opt/oracle/psft/ptdb/arch/H91TMPLT/../..
[root@psovmhcmdb ptdb]# mkdir -p redo/H91TMPLT
[root@psovmhcmdb ptdb]# chown -R oracle:dba redo
[root@psovmhcmdb ptdb]# su - oracle
[oracle@psovmhcmdb ~]$ cd /u01/app/oracle/oradata/
[oracle@psovmhcmdb oradata]$ ln -s /opt/oracle/psft/ptdb/redo/H91TMPLT H91TMPLT_redo
[oracle@psovmhcmdb oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Jan 23 06:18 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 05:00 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:15 H91TMPLT_redo -> /opt/oracle/psft/ptdb/redo/H91TMPLT
8.1 Create the standby redolog directoy on primary site - psovmhcmdb
[root@psovmhcmdb ptdb]# cd /opt/oracle/psft/ptdb/arch/H91TMPLT/../..
[root@psovmhcmdb ptdb]# mkdir -p redo/H91TMPLT
[root@psovmhcmdb ptdb]# chown -R oracle:dba redo
[root@psovmhcmdb ptdb]# su - oracle
[oracle@psovmhcmdb ~]$ cd /u01/app/oracle/oradata/
[oracle@psovmhcmdb oradata]$ ln -s /opt/oracle/psft/ptdb/redo/H91TMPLT H91TMPLT_redo
[oracle@psovmhcmdb oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Jan 23 06:18 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 05:00 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:15 H91TMPLT_redo -> /opt/oracle/psft/ptdb/redo/H91TMPLT
8.2 Create the standby redolog directoy on secondary site - psovmhcmdb2
[root@psovmhcmdb2 ~]# cd /opt/oracle/psft/ptdb/arch/H91TMPLT/../..
[root@psovmhcmdb2 ptdb]# mkdir -p redo/H91TMPLT
[root@psovmhcmdb2 ptdb]# chown -R oracle:dba redo
[root@psovmhcmdb2 ptdb]# su - oracle
[oracle@psovmhcmdb2 ~]$ cd /u01/app/oracle/oradata/
[oracle@psovmhcmdb2 oradata]$ ln -s /opt/oracle/psft/ptdb/redo/H91TMPLT H91TMPLT_redo
[oracle@psovmhcmdb2 oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Mar 10 05:02 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:11 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:25 H91TMPLT_redo -> /opt/oracle/psft/ptdb/redo/H91TMPLT
[root@psovmhcmdb2 ~]# cd /opt/oracle/psft/ptdb/arch/H91TMPLT/../..
[root@psovmhcmdb2 ptdb]# mkdir -p redo/H91TMPLT
[root@psovmhcmdb2 ptdb]# chown -R oracle:dba redo
[root@psovmhcmdb2 ptdb]# su - oracle
[oracle@psovmhcmdb2 ~]$ cd /u01/app/oracle/oradata/
[oracle@psovmhcmdb2 oradata]$ ln -s /opt/oracle/psft/ptdb/redo/H91TMPLT H91TMPLT_redo
[oracle@psovmhcmdb2 oradata]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 38 Mar 10 05:02 H91TMPLT -> /opt/oracle/psft/ptdb/oradata/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:11 H91TMPLT_arch -> /opt/oracle/psft/ptdb/arch/H91TMPLT
lrwxrwxrwx 1 oracle oinstall 35 Mar 10 07:25 H91TMPLT_redo -> /opt/oracle/psft/ptdb/redo/H91TMPLT
8.3 Create the standby redolog on primary db
[oracle@psovmhcmdb oradata]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 07:17:07 2011 [oracle@psovmhcmdb oradata]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb oradata]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT GROUP#, BYTES FROM V$LOG;
GROUP# BYTES
---------- ----------
1 104857600
2 104857600
3 104857600
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
no rows selected
SQL> ALTER DATABASE ADD STANDBY LOGFILE
2 ('/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo01.log') size 100m,
3 ('/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo02.log') size 100m,
3 ('/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo03.log') size 100m
SQL> /
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
4 104857600
5 104857600
6 104857600
SQL>
8.4 Create the standby redolog on secondary db
[oracle@psovmhcmdb2 oradata]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 07:25:47 2011 [oracle@psovmhcmdb2 oradata]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb2 oradata]$ sqlplus / as sysdba
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER DATABASE ADD STANDBY LOGFILE
2 '/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo01.log' size 100m,
3 '/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo02.log' size 100m,
4* '/u01/app/oracle/oradata/H91TMPLT_redo/stb_redo03.log' size 100m
SQL> /
Database altered.
9. Activate the standby mechanism on secondary db
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL>
10. First test
10.1 Force a switch of logfile on primary db
SQL> ALTER SYSTEM SWITCH LOGFILE;
10.2 Check if archivelog files have been applied on secondary db
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
no rows selected 10.1 Force a switch of logfile on primary db
SQL> ALTER SYSTEM SWITCH LOGFILE;
10.2 Check if archivelog files have been applied on secondary db
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
10.3 Error issued
The archivelog files have not been applied onto the secondary database, looking into the alert.log file of the primary database, few errors are issued :
2011-03-10 07:44:34.979000 -08:00
Errors in file /u01/app/oracle/diag/rdbms/h91tmplt/H91TMPLT/trace/H91TMPLT_arc2_30195.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Errors in file /u01/app/oracle/diag/rdbms/h91tmplt/H91TMPLT/trace/H91TMPLT_arc2_30195.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC2]: Heartbeat failed to connect to standby 'H91STBY'. Error is 16191.
The archivelog files have not been applied onto the secondary database, looking into the alert.log file of the primary database, few errors are issued :
2011-03-10 07:44:34.979000 -08:00
Errors in file /u01/app/oracle/diag/rdbms/h91tmplt/H91TMPLT/trace/H91TMPLT_arc2_30195.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Errors in file /u01/app/oracle/diag/rdbms/h91tmplt/H91TMPLT/trace/H91TMPLT_arc2_30195.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC2]: Heartbeat failed to connect to standby 'H91STBY'. Error is 16191.
=> a password file is required, and by default, the delivered database in PSOVM does not have one.
11. Password files
11.1 Change remote_login_passwordfile to exclusive on secondary db
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
System altered. 11.1 Change remote_login_passwordfile to exclusive on secondary db
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
11.2 Create password file for secondary db, take care, the password file must be named within the DB_UNIQUE_NAME if the database, not within the DB_NAME. So, here on the secondary database DB_UNIQUE_NAME=H91STBY
[oracle@psovmhcmdb2 oradata]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb2 dbs]$ orapwd file=orapwH91STBY password=oracle entries=5
[oracle@psovmhcmdb2 oradata]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb2 dbs]$ orapwd file=orapwH91STBY password=oracle entries=5
11.3 Bounce the secondary db and activate the standby mechanism
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted. SQL> shutdown immediate
ORA-01109: database not open
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 288104448 bytes
Fixed Size 2159032 bytes
Variable Size 109055560 bytes
Database Buffers 171966464 bytes
Redo Buffers 4923392 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
11.4 Change remote_login_passwordfile to exclusive on primary db
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
System altered.
SQL> quit
11.5 Create password file for primary db
[oracle@psovmhcmdb oracle]$ cd $ORACLE_HOME/dbs
[oracle@psovmhcmdb dbs]$ ls
ab_+ASM.dat hc_H91TMPLT.dat initdw.ora init.ora lk+ASM lkORCL orapworcl spfileH91TMPLT.ora
hc_+ASM.dat hc_orcl.dat initH91TMPLT.ora initorcl.ora lkH91TMPLT orapw+ASM spfile+ASM.ora
[oracle@psovmhcmdb dbs]$ orapwd file=orapwH91TMPLT password=oracle entries=5
11.6 Bounce the primary db
[oracle@psovmhcmdb dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 10 07:52:36 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 288104448 bytes
Fixed Size 2159032 bytes
Variable Size 100666952 bytes
Database Buffers 180355072 bytes
Redo Buffers 4923392 bytes
Database mounted.
Database opened.
12. Second test
12.1 Force a switch of logfile on primary db
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered. 12.1 Force a switch of logfile on primary db
SQL> ALTER SYSTEM SWITCH LOGFILE;
12.2 Check if archivelog files have been applied on secondary db
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
---------- ------------------ ------------------
11 10-MAR-11 10-MAR-11
12 10-MAR-11 10-MAR-11
13 10-MAR-11 10-MAR-11
14 10-MAR-11 10-MAR-11
15 10-MAR-11 10-MAR-11
16 10-MAR-11 10-MAR-11
17 10-MAR-11 10-MAR-11
18 10-MAR-11 10-MAR-11
19 10-MAR-11 10-MAR-11
20 10-MAR-11 10-MAR-11
21 10-MAR-11 10-MAR-11
22 10-MAR-11 10-MAR-11
23 10-MAR-11 10-MAR-11
24 10-MAR-11 10-MAR-11
25 10-MAR-11 10-MAR-11
26 10-MAR-11 10-MAR-11
27 10-MAR-11 10-MAR-11
28 10-MAR-11 10-MAR-11
29 10-MAR-11 10-MAR-11
30 10-MAR-11 10-MAR-11
31 10-MAR-11 10-MAR-11
32 10-MAR-11 10-MAR-11
22 rows selected.
SQL>
=> success, all the created archivelog files have been shipped to the secondary database.
We have now a fully working physical standby, that's good but the target is to be able to query against that database, that's not possible in that current state, so next step is to enable the Active Data Guard. To be continued.
Read my next articles :
Nicolas.
Documentation, find out more :
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#g88234
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#g88234
No comments:
Post a Comment