06 - Configure Oracle
- Arjen Visser
- Jaimes Booth (Unlicensed)
1. Once the VM has been restarted, logon as the oracle user.
su - oracle
It is now possible to connect externally with Putty or a terminal emulator to the VM (source) with the following command:
ssh -p10022 oracle@192.168.1.197
This uses port forwarding which was setup when Virtual Box was configured (02 - Set up NAT network in Virtual Box).
192.168.1.197 is the IP address of the host machine (laptop). Use ifconfig or ipconfig to identify what the local IP address is of your host machine.
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.
echo $TWO_TASK pdb1 unset TWO_TASK
b. Logon to the database (CDB) as sysdba.
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. Setup the correct DataPump directory which will be used later in the workshop.
SQL> create directory TTORCL_TRG_DATA_PUMP_DIR as '/u01/app/oracle/admin/orcl/dpdump/';
h. 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 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 so that this file is no longer used. We want the default Listener configuration.
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 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. It may take several minutes for the services to start and appear.
lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 - Production
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 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 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.
unset TWO_TASK
Unsetting of TWO_TASK was already done in a previous step so it is not strictly needed.
b. Shutdown the database.
sqlplus / as sysdba
CDB$ROOT@ORCL> shutdown immediate
c. Start up the database in no mount mode.
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.
alter database archivelog;
Database altered.
e. Open the database.
alter database open;
Database altered.
f. Check the archivelog mode.
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.
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. The password is oracle
su -
b. Create an archive management script named del_arch.sh that will manage the archives.
vi /usr/local/bin/del_arch.sh
c. Paste the following contents and save and exit the script (:wq!).
#!/bin/bash
#
# set -x
###########################################
# Function to echo usage
###########################################
usage ()
{
echo
echo "Usage: del_arch <dbsid> <retention_in_days>"
echo example: del_arch testdb 0.5
echo
echo Default retention is 2 days
}
###########################################
# Function to setup parameter variables
###########################################
setup_parameters ()
{
system_hostname=`uname -n`
program_name=`basename $0`
dba_top=/u01/oracle/log
log=${dba_top}/rman_${db}-`date +%d%m%Y%H%M`.log
[ -d "${dba_top}" ] || mkdir ${dba_top}
}
###########################################
# Function to change database environments
###########################################
change_db ()
{
export ORAENV_ASK=NO
export ORACLE_SID=$1
. /usr/local/bin/oraenv >> /dev/null
}
###########################################
# Function to Add text to logfile
###########################################
addLog()
{
echo "`date +%d-%h-%Y:%H:%M:%S` : $1" >> ${log}
}
###################################
# Function to purge archive logs #
###################################
run_delete ()
{
change_db ${db}
addLog "Start Delete with rentention ${retention}..."
echo "Start Delete with rentention ${retention}..."
rman append msglog=${log} <<!EOF
connect target /
run {
change archivelog all validate;
delete noprompt archivelog until time 'SYSDATE-${retention}';
}
!EOF
addLog "End Delete..."
}
##################
##### Main Program
##################
db=$1
PATH=$PATH:/usr/local/bin/:.
export TMP=/u03/tmp
retention=$2
if [ -z "$retention" ]
then
retention=2
fi
if [ -z "$db" ]
then
usage
exit 2
else
setup_parameters
change_db ${db}
run_delete
fi
d. Make the script executable and change ownership to oracle.
chmod 750 /usr/local/bin/del_arch.sh
chown oracle:dba /usr/local/bin/del_arch.sh
e. As user oracle, add the script to the oracle crontab.
su - oracle
crontab -e
f. 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.