Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 10 Next »

1. Logon as the oracle user

su - oracle

2. Setup the OE schema in the database

a. Logon to the database as sysdba

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 20 18:00:32 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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

b. Unlock and alter the OE schema. Run the following SQL

ALTER USER oe IDENTIFIED BY oe; 
ALTER USER oe ACCOUNT UNLOCK;
GRANT ALTER SESSION,      
CONNECT,     
CREATE MATERIALIZED VIEW,     
CREATE VIEW,     
QUERY REWRITE,     
RESOURCE TO oe;     
GRANT EXECUTE ON DBMS_LOCK TO oe;     
ALTER USER OE QUOTA UNLIMITED ON USERS;

3. Setup the correct TNS alias. Edit /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora

vi /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora

Add the following:

TTORCL_SRC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = source-int)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
TTORCL_TRG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target-int)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Test if the TNS alias is working:

$ tnsping ttorcl_src
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-JAN-2014 13:39:13
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = source-int)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) 
OK (0 msec)

4. Bring the database in archivelog mode. This is a requirement for Dbvisit Replicate

sqlplus / as sysdba
SQL> alter system set log_archive_start=TRUE scope=spfile;

System altered.

a. Shut the database down

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

b. Bring the database into mount mode and change the archivelog setting and then open the database

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area  456146944 bytes
Fixed Size		    1344840 bytes
Variable Size		  348129976 bytes
Database Buffers	  100663296 bytes
Redo Buffers		    6008832 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

c. Ensure the database is in archivelog mode

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     613
Next log sequence to archive   615
Current log sequence	       615

5. Ensure the archive log files are managed and not filling up and stopping the database. Install a script that manages the archive logs

a. Logon as root and cd to /usr/local/bin

su - 
cd /usr/local/bin

b. Download an archive management script from the dbvisit.com website named del_arch.sh

[root@source bin]# wget http://ww2.dbvisit.com/download/files/external/del_arch.sh
--2013-12-23 01:13:17--  http://ww2.dbvisit.com/download/files/external/del_arch.sh
Resolving ww2.dbvisit.com... 67.222.54.241
Connecting to ww2.dbvisit.com|67.222.54.241|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1822 (1.8K) [application/x-sh]
Saving to: `del_arch.sh'
100%[================================================================================================================>] 1,822       --.-K/s   in 0s      
2013-12-23 01:13:18 (104 MB/s) - `del_arch.sh' saved [1822/1822]

c. Make it executable and change ownership to oracle

[root@source bin]# chmod 750 del_arch.sh 
[root@source bin]# chown oracle:dba del_arch.sh 
[root@source bin]# exit

d. As user oracle, add it to the oracle crontab. Run it every 15 minutes and delete all archives older than 4.8 hours (0.2 days)

su - oracle
crontab -e

e. Add the following line to crontab and then save and exit (:wq)

*/15 * * * * /usr/local/bin/del_arch.sh orcl 0.2 > /tmp/del_arch.txt 2>&1

In a production environment a proper RMAN backup procedure would be used to backup the archive log files before removing them.

 

 

  • No labels