1. Logon as the oracle user.
su - oracle
2. Setup the OE schema in the database.
a. By default the TWO_TASK is set to the PDB. Unset the TWO_TASK to connect to the CDB.
[oracle@source ~]$ echo $TWO_TASK pdb1 [oracle@source ~]$ unset TWO_TASK
b. Logon to the database (CDB) as sysdba.
[oracle@source ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 31 23:25:18 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options CDB$ROOT@ORCL>
c. Reset the passwords for system and sys as they will expire in 7 days.
CDB$ROOT@ORCL> alter user system identified by oracle; User altered.
CDB$ROOT@ORCL> alter user sys identified by oracle; User altered.
d. Connect to the PDB.
CDB$ROOT@ORCL> ALTER SESSION SET container = pdb1;
e. 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;
f. Drop the following table. This is part of a nested table and is not supported by the replication.
SQL> drop table OE.CATEGORIES_TAB;
g. Logout of the database.
SQL> exit
3. By default the listener is not setup to automatically register services. Setup the listener so that it automatically supports services.
a. Stop the listener service.
lsnrctl stop LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-JUL-2014 23:49:54 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) The command completed successfully
b. Rename the current listener.ora file.
cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin mv listener.ora _listener.ora
c. Start the listener again.
lsnrctl start LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-JUL-2014 23:50:05 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.1.0 - Production Log messages written to /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source-int)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 31-JUL-2014 23:50:05 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source-int)(PORT=1521))) The listener supports no services The command completed successfully
d. Check the listener services.
lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-JUL-2014 23:50:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: source, pid: 3347>
(ADDRESS=(PROTOCOL=tcp)(HOST=source-int)(PORT=55291))
Service "pdb1" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
4. Setup the correct TNS alias. Edit /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
vi /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
a. Add the following to the end of the file.
TTORCL_SRC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = source-int)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
TTORCL_TRG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = target-int)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
b. Test if the TNS alias is working.
tnsping ttorcl_src TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 31-JUL-2014 23:56:35 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = source-int)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1))) OK (0 msec)
c. Test the connection to the PDB.
sqlplus oe/oe@ttorcl_src SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 31 23:57:41 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Last Successful login time: Thu Jul 31 2014 23:50:31 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options PDB1@ORCL> exit
5. Bring the database in archivelog mode. This is a requirement for Dbvisit Replicate.
a. By default the TWO_TASK is set to the PDB. Unset the TWO_TASK to connect to the CDB.
[oracle@source ~]$ unset TWO_TASK
b. Shutdown the database.
sqlplus / as sysdba CDB$ROOT@ORCL> shutdown immediate;
c. Start up the database in no mount mode.
CDB$ROOT@ORCL> startup mount; ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted.
d. Turn on archivelog mode.
CDB$ROOT@ORCL> alter database archivelog; Database altered.
e. Open the database.
CDB$ROOT@ORCL> alter database open; Database altered.
f. Check the archivelog mode.
CDB$ROOT@ORCL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 104 Next log sequence to archive 106 Current log sequence 106
g. Logout of the database.
SQL> exit
6. 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.
su -
change directory to /usr/local/bin
cd /usr/local/bin
b. Download the 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 the script 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 the script to the oracle crontab.
su - oracle
crontab -e
e. Add the following line to crontab and then save and exit (:wq) This runs the script every 15 minutes and deletes all archives older than 4.8 hours (0.2 days).
*/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.
Add Comment