Database Administrator's blog: March 2018

Below are the steps for enabling the oracle Enterprise Manager Express 12c

[oracle@bach1 ~]$
[oracle@bach1 ~]$ . oraenv
ORACLE_SID = [cdb12c] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@bach1 ~]$
[oracle@bach1 ~]$
[oracle@bach1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 11 16:47:11 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select name, cdb, con_id from v$database;

NAME CDB CON_ID
--------- --- ----------
CDB12C YES 0

SQL>
SQL>
SQL> select instance_name, status, con_id from v$instance;

INSTANCE_NAME STATUS CON_ID
---------------- ------------ ----------
cdb12c OPEN 0

SQL>
SQL> show parameter dispatchers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=cdb12c
XDB)
max_dispatchers integer
SQL>
SQL>

>> Execute the DBMS_XDB.setHTTPSPort procedure to set the HTTPS port 5500 and the DBMS_XDB.setHTTPPort procedure to set the HTTP port 5510 for EM Express

SQL>
SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

PL/SQL procedure successfully completed.

SQL> exec DBMS_XDB_CONFIG.SETHTTPPORT(5510);

PL/SQL procedure successfully completed.

SQL>
SQL> select name, con_id from v$database;

NAME CON_ID
--------- ----------
CDB12C 0

SQL>
SQL>
SQL>

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPSPORT
------------
5500

SQL>
SQL>
SQL>
SQL> set pages 500
SQL> set lines 500
SQL>
SQL> select NAME, OPEN_MODE from v$pdbs;

NAME OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
PDB3 READ WRITE
PDB4 READ WRITE
PDB5 READ WRITE
PDB6 READ WRITE
PDB7 READ WRITE
PDB8 READ WRITE
PDB9 READ WRITE
PDB10 READ WRITE

11 rows selected.

SQL>

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>

>> Set global port enable

SQL>
SQL> exec dbms_xdb_config.setglobalportenabled(TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Login to EM

1) Host Files Configure

<IP-address> <fully-qualified-machine-name> <machine-name>

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.31.10.xxx bach1.xxx.com bach1

2) Add the below following lines into "/etc/sysctl.conf"

[root@bach1 ~]# vi /etc/sysctl.conf

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

3) Add the following lines to the "/etc/security/limits.conf" file.

[root@bach1 ~]# vi /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 4096

oracle hard nofile 65536

oracle soft stack 10240

4) patch Requirement

>Login as "root" user

binutils-2.20.51.0.2-5.11.el6 (x86_64)

compat-libcap1-1.10-1 (x86_64)

compat-libstdc++-33-3.2.3-69.el6 (x86_64)

compat-libstdc++-33-3.2.3-69.el6.i686

gcc-4.4.4-13.el6 (x86_64)

gcc-c++-4.4.4-13.el6 (x86_64)

glibc-2.12-1.7.el6 (i686)

glibc-2.12-1.7.el6 (x86_64)

glibc-devel-2.12-1.7.el6 (x86_64)

glibc-devel-2.12-1.7.el6.i686

ksh

libgcc-4.4.4-13.el6 (i686)

libgcc-4.4.4-13.el6 (x86_64)

libstdc++-4.4.4-13.el6 (x86_64)

libstdc++-4.4.4-13.el6.i686

libstdc++-devel-4.4.4-13.el6 (x86_64)

libstdc++-devel-4.4.4-13.el6.i686

libaio-0.3.107-10.el6 (x86_64)

libaio-0.3.107-10.el6.i686

libaio-devel-0.3.107-10.el6 (x86_64)

libaio-devel-0.3.107-10.el6.i686

make-3.81-19.el6

sysstat-9.0.4-11.el6 (x86_64)

numactl-devel-2 (x86_64)

sysstat-9(x86_64)

compat-libstdc++-33.i686

compat-libcap

unixODBC-2.2.14-11.el6 (x86_64) or later

unixODBC-2.2.14-11.el6.i686 or later

unixODBC-devel-2.2.14-11.el6 (x86_64) or later

unixODBC-devel-2.2.14-11.el6.i686 or later

elfutils-libelf-devel-0.97

zlib-devel.i686

libaio-devel.x86_64 0:0.3.109-13.el7

5) Create required Groups and user.

-Enter the following commands to create the oinstall and dba groups (from "root" user):

[root@bach1 ~]# groupadd oinstall

[root@bach1 ~]# groupadd dba

[root@bach1 ~]# groupadd oper

-Complete the following actions to create oracle user (from root user):

# useradd -g oinstall -G dba,oper oracle

# passwd oracle

[root@bach1 ~]# chown oracle:oinstall /home/oracle

[oracle@bach1 ~]$ chmod -R 775 .bash_profile [Login as "oracle" user]

6) Create required Directories.

-Login as "root" user

[root@bach1 ~]# mkdir -p /u01/app/oracle/product/12.2.0.1/dbhome_1

[root@bach1 ~]# chown -R oracle:oinstall /u01

[root@bach1 ~]# chmod -R 775 /u01

7) Create oracle user environment.

-Login as "oracle" user

$ vi .bash_profile

ORACLE_HOSTNAME=bach1.xxxx.com; export ORACLE_HOSTNAME

ORACLE_UNQNAME=cdb12c; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1; export ORACLE_HOME

ORACLE_SID=cdb12c; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

[oracle@bach1 ~]$ . ./.bash_profile

[root@bach1 ~]# chown -R oracle:oinstall /fra

[root@bach1 ~]# chmod -R 775 /fra

[root@bach1 ~]# chown -R oracle:oinstall /data

[root@bach1 ~]# chmod -R 775 /data

[root@bach1 ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.

[root@bach1 ~]# /u01/app/oracle/product/12.2.0.1/dbhome_1/root.sh

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/12.2.0.1/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :

yes

Installing Oracle Trace File Analyzer (TFA).

Log File: /u01/app/oracle/product/12.2.0.1/dbhome_1/install/root_bach1.nblbd.com_2018-03-11_10-34-46-579462926.log

Finished installing Oracle Trace File Analyzer (TFA)

[root@bach1 ~]#

tnsnames.ora

BACPS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bach1.xxxx.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb3.xxxx.com)

)

BEFTN =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bach1.xxxx.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb4.xxxx.com)

)

[oracle@bach1 dump]$ sqlplus sys/****@bacps as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 11 13:20:49 2018

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL> select tablespace_name,con_id from cdb_tablespaces order by 2;

TABLESPACE_NAME CON_ID

------------------------------ ----------

SYSTEM 5

SYSAUX 5

UNDOTBS1 5

TEMP 5

USERS 5

SQL>

Tablespaces

create tablespace nblbacpsdata datafile '/data/oradata/cdb12c/pdb3/nblbacpsdata01.dbf' size 1g autoextend on;

create tablespace nblbacpsindex datafile '/data/oradata/cdb12c/pdb3/nblbacpsindex01.dbf' size 1g autoextend on;

create user nblbacps identified by nblbacps default tablespace nblbacpsdata temporary tablespace temp quota unlimited on nblbacpsdata quota unlimited on nblbacpsindex;

grant dba to nblbacps;

create directory dir_bacps as '/soft/dump';

grant read,execute on directory dir_bacps to nblbacps;

IMPDP

[oracle@bach1 dump]$ impdp nblbacps/****@bacps directory=dir_bacps dumpfile=nblbacps_11mar18.dmp logfile=imp_new_11mar18.log

Import: Release 12.2.0.1.0 - Production on Sun Mar 11 13:34:37 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "NBLBACPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "NBLBACPS"."SYS_IMPORT_FULL_01": nblbacps/********@bacps directory=dir_bacps dumpfile=nblbacps_11mar18.dmp logfile=imp_new_11mar18.log

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"NBLBACPS" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

EXPDP

oracle@bach1 dump]$ expdp nblbacps/nblbacps@bacps directory=dir_bacps dumpfile=bacps_12c.dmp logfile=bacps_12c.log

Export: Release 12.2.0.1.0 - Production on Sun Mar 11 13:37:54 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "NBLBACPS"."SYS_EXPORT_SCHEMA_01": nblbacps/********@bacps directory=dir_bacps dumpfile=bacps_12c.dmp logfile=bacps_12c.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/USER

Database Administrator's blog

Pages

Tuesday, March 13, 2018

Enable Oracle Enterprise Manager Express 12c

create 12c pluggable database using dbca

Oracle12c database Installation on Red Hat Enterprise Linux Server release 7.4 (Maipo)

Sunday, March 4, 2018

Audit Vault Agent Start