Database Administrator's blog: oracle12c

Showing posts with label oracle12c. Show all posts

Tuesday, July 17, 2018

Configure Oracle 12c Physical standby database

--===================
[oracle@bach1 ~]$
vi $ORACLE_HOME/sqlplus/admin/glogin.sql

and put there following line

SET SQLPROMPT "_user'@'_connect_identifier'> '"
[oracle@bach1 ~]$ export ORACLE_SID=cdb12c

--================

[oracle@bach1 ~]$ sqlplus / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@cdb12c>

[oracle@bach1 ~]$ sqlplus / as sysdba

SQL> SELECT log_mode, open_mode, force_logging
2 FROM v$database;

LOG_MODE OPEN_MODE FORCE_LOGGING
------------ -------------------- ---------------------------------------
ARCHIVELOG READ WRITE NO

SQL> ALTER DATABASE FORCE LOGGING;

LOG_MODE OPEN_MODE FORCE_LOGGING
------------ -------------------- ---------------------------------------
ARCHIVELOG READ WRITE YES

SYS@cdb12c> col name for a30
SYS@cdb12c> col value for a60
SYS@cdb12c>
SYS@cdb12c>
SYS@cdb12c> SELECT name, value FROM v$parameter WHERE name LIKE 'db_recovery%';

NAME VALUE
------------------------------ ------------------------------------------------------------
db_recovery_file_dest /fra/fast_recovery_area/cdb12c
db_recovery_file_dest_size 80530636800

SYS@cdb12c>
SYS@cdb12c> SELECT log_mode, open_mode FROM v$database;

LOG_MODE OPEN_MODE
------------ --------------------
ARCHIVELOG READ WRITE

SYS@cdb12c> set linesize 300
SYS@cdb12c> column REDOLOG_FILE_NAME format a50
SYS@cdb12c> SELECT
2 a.GROUP#,
3 a.THREAD#,
4 a.SEQUENCE#,
5 a.ARCHIVED,
6 a.STATUS,
7 b.MEMBER AS REDOLOG_FILE_NAME,
8 (a.BYTES/1024/1024) AS SIZE_MB
9 FROM v$log a
10 JOIN v$logfile b ON a.Group#=b.Group#
11 ORDER BY a.GROUP# ASC;

GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 145 YES ACTIVE /data/oradata/cdb12c/redo01.log 200
2 1 146 NO CURRENT /data/oradata/cdb12c/redo02.log 200
3 1 144 YES ACTIVE /data/oradata/cdb12c/redo03.log 200

SYS@cdb12c>

SYS@cdb12c> SELECT group#, type FROM v$logfile;

GROUP# TYPE
---------- -------
3 ONLINE
2 ONLINE
1 ONLINE

SYS@cdb12c>
SYS@cdb12c>

SYS@cdb12c> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200m;

Database altered.

SYS@cdb12c> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200m;

Database altered.

SYS@cdb12c> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200m;

Database altered.

SYS@cdb12c> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200m;

Database altered.

SYS@cdb12c>

SYS@cdb12c> SELECT group#, type FROM v$logfile;

GROUP# TYPE
---------- -------
3 ONLINE
2 ONLINE
1 ONLINE
4 STANDBY
5 STANDBY
6 STANDBY
7 STANDBY

7 rows selected.

SYS@cdb12c>

SYS@cdb12c> set pagesize 500
SYS@cdb12c> set linesize 500
SYS@cdb12c> col name for a40
SYS@cdb12c> col value for a60
SYS@cdb12c>
SYS@cdb12c> SELECT name, value
2 FROM v$parameter
3 WHERE name IN
4 ( 'db_name',
5 'db_unique_name',
6 'log_archive_format',
7 'remote_login_passwordfile' )
8 ORDER BY NAME;

NAME VALUE
---------------------------------------- ------------------------------------------------------------
db_name cdb12c
db_unique_name cdb12c
log_archive_format %t_%s_%r.dbf
remote_login_passwordfile EXCLUSIVE

SYS@cdb12c>

SYS@cdb12c> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG ='DG_CONFIG=(cdb12c,cdr12c)' SCOPE=BOTH;

System altered.

SYS@cdb12c> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb12c' SCOPE=BOTH;

System altered.

SYS@cdb12c> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=cdr12c ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdr12c' SCOPE=BOTH;

System altered.

SYS@cdb12c> show parameter LOG_ARCHIVE_CONFIG

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(cdb12c,cdr12c)
SYS@cdb12c>
SYS@cdb12c> show parameter LOG_ARCHIVE_DEST_1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=cdb12
c
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SYS@cdb12c>
SYS@cdb12c> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=cdr12c ASYNC VALID_FOR
=(ONLINE_LOGFILES,PRIMARY_ROLE
) DB_UNIQUE_NAME=cdr12c
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SYS@cdb12c>

SYS@cdb12c> ALTER SYSTEM SET FAL_SERVER = cdr12c SCOPE=BOTH;

System altered.

SYS@cdb12c> ALTER SYSTEM SET DB_FILE_NAME_CONVERT = '/cdr12c/','/cdb12c/' SCOPE=SPFILE;

System altered.

SYS@cdb12c> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT = '/cdr12c/','/cdb12c/' SCOPE=SPFILE;

System altered.

SYS@cdb12c> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO SCOPE=BOTH;

System altered.

SYS@cdb12c> STARTUP FORCE;
ORACLE instance started.

Total System Global Area 4.1339E+10 bytes
Fixed Size 19420336 bytes
Variable Size 5905582928 bytes
Database Buffers 3.5299E+10 bytes
Redo Buffers 114794496 bytes
Database mounted.
Database opened.
SYS@cdb12c>
SYS@cdb12c> show parameter FAL_SERVER

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string CDR12C
SYS@cdb12c>
SYS@cdb12c> show parameter DB_FILE_NAME_CONVERT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /cdr12c/, /cdb12c/
pdb_file_name_convert string
SYS@cdb12c> show parameter LOG_FILE_NAME_CONVERT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string /cdr12c/, /cdb12c/
SYS@cdb12c>
SYS@cdb12c> show parameter STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SYS@cdb12c>

SYS@cdb12c> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/cdr12c.ctl';

Database altered.

SYS@cdb12c>

SYS@cdb12c> CREATE PFILE='/tmp/initCDR12C.ora' FROM SPFILE;

File created.

oracle@bach1 tmp]$ vi initCDR12C.ora
*.audit_file_dest='/u01/app/oracle/admin/cdr12c/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/data/oradata/cdr12c/control01.ctl','/fra/fast_recovery_area/cdr12c/control02.ctl'
*.db_block_size=8192
*.db_domain='nblbd.com'
*.db_file_name_convert='/cdb12c/','/cdr12c/'
*.db_name='cdr12c'
*.db_recovery_file_dest='/fra/fast_recovery_area/cdb12c'
*.db_recovery_file_dest_size=76800m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdb12cXDB)'
*.enable_pluggable_database=true
*.fal_server='CDB12C'
#*.local_listener='LISTENER_CDB12C'
*.log_archive_config='DG_CONFIG=(cdb12c,cdr12c)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdr12c'
*.log_archive_dest_2='SERVICE=cdb12c ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb12c'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/cdb12c/','/cdr12c/'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=13124m
*.processes=1280
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=30371m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

--===Standby server ==
[oracle@bachdbdr ~]$ mkdir -p /u01/app/oracle/admin/cdr12c/adump
[oracle@bachdbdr ~]$
[oracle@bachdbdr ~]$
[oracle@bachdbdr ~]$ cd /u01/app/oracle/admin/cdr12c/adump
[oracle@bachdbdr adump]$
--====End=============

[oracle@bach1 tmp]$ scp initCDR12C.ora oracle@172.31.200.39:$ORACLE_HOME/dbs
oracle@172.31.200.39's password:
initCDR12C.ora 100% 1169 491.2KB/s 00:00
[oracle@bach1 tmp]$

[oracle@bach1 ~]$ scp $ORACLE_HOME/dbs/orapwcdb12c oracle@172.31.200.39:$ORACLE_HOME/dbs/orapwcdr12c
oracle@172.31.200.39's password:
orapwcdb12c 100% 3584 1.4MB/s 00:00
[oracle@bach1 ~]$

--====directories create ===
[oracle@bachdbdr cdr12c]$ mkdir -p /u01/app/oracle/admin/cdr12c/adump
[oracle@bachdbdr cdr12c]$ mkdir -p /u01/app/oracle/admin/cdr12c/dpdump
[oracle@bachdbdr cdr12c]$ mkdir -p /u01/app/oracle/admin/cdr12c/pfile
[oracle@bachdbdr cdr12c]$ mkdir -p /u01/app/oracle/admin/cdr12c/xdb_wallet

[oracle@bachdbdr ~]$ mkdir -p /fra/fast_recovery_area/cdr12c
[oracle@bachdbdr ~]$ mkdir -p /data/oradata/cdr12c
[oracle@bachdbdr ~]$ mkdir -p /data/oradata/cdr12c/pdbseed
[oracle@bachdbdr ~]$ mkdir -p /data/oradata/cdr12c/PORA12C2

[oracle@bach1 admin]$ scp tnsnames.ora oracle@172.31.200.39:$ORACLE_HOME/network/admin
oracle@172.31.200.39's password:
tnsnames.ora 100% 1585 624.5KB/s 00:00
[oracle@bach1 admin]$
[oracle@bach1 admin]$ scp listener.ora oracle@172.31.200.39:$ORACLE_HOME/network/admin
oracle@172.31.200.39's password:
listener.ora 100% 345 178.0KB/s 00:00
[oracle@bach1 admin]$
[oracle@bach1 admin]$

oracle@bachdbdr admin]$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb12c.nblbd.com)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = cdb12c)
)
(SID_DESC =
(GLOBAL_DBNAME = cdr12c.nblbd.com)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = cdr12c)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bachdbdr.nblbd.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@bach1 ~]$ tnsping cdr12c

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 09-MAY-2018 02:00:58

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bach1.nblbd.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdr12c.nblbd.com)))
OK (0 msec)
[oracle@bach1 ~]$
[oracle@bach1 ~]$ tnsping cdb12c

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 09-MAY-2018 02:01:05

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bach1.nblbd.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb12c.nblbd.com)))
OK (0 msec)
[oracle@bach1 ~]$

[oracle@bachdbdr ~]$ tnsping cdr12c

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-MAY-2018 17:57:52

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bach1.nblbd.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdr12c.nblbd.com)))
OK (0 msec)
[oracle@bachdbdr ~]$ tnsping cdb12c

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-MAY-2018 17:58:00

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bach1.nblbd.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb12c.nblbd.com)))
OK (0 msec)
[oracle@bachdbdr ~]$

oracle@bachdbdr dbs]$ mv initCDR12C.ora initcdr12c.ora
[oracle@bachdbdr dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 8 18:03:55 2018

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

Connected to an idle instance.

SQL>
SQL> create spfile from pfile;

File created.

SQL>

[oracle@bachdbdr dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 8 18:40:29 2018

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initcdr12c.ora';
ORACLE instance started.

Total System Global Area 3.1877E+10 bytes
Fixed Size 19428376 bytes
Variable Size 4496296936 bytes
Database Buffers 2.7313E+10 bytes
Redo Buffers 47677440 bytes
SQL>
SQL>

[oracle@bach1 ~]$ rman

Recovery Manager: Release 12.2.0.1.0 - Production on Wed May 9 02:37:29 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

RMAN> CONNECT TARGET sys/Nblcdb12c@cdb12c

connected to target database: CDB12C (DBID=4169099588)

RMAN> CONNECT AUXILIARY sys/Nblcdb12c@cdr12c

connected to auxiliary database (not started)

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

--===

SELECT Operations.id, Operations.title, DefOperations.title AS defOpTitle, Operations.isFromUser
FROM Operations LEFT JOIN DefOperations ON Operations.defOpID = DefOperations.id;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION NODELAY;

alter database recover managed standby database cancel

select group#, status from v$standby_log;
select status, error from v$archive_dest where dest_id = 2;

SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;

--==============command===

SQL> col member for a40
SQL> select * from v$logfile;

select * from v$logfile where type='STANDBY';

--====drop Standby Log file ==

select group#, thread#, status, bytes/1024/1024 from v$standby_log;

select group#, thread#, status, bytes/1024/1024 from v$log;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;

alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;

--====Add Standby Log file ==

alter database add standby logfile thread 1 GROUP 4 '/data/oradata/cdb12c/redo_std04a.rdo' size 200M reuse;

alter database add standby logfile thread 1 GROUP 5 '/data/oradata/cdb12c/redo_std05a.rdo' size 200M reuse;
alter database add standby logfile thread 1 GROUP 6 '/data/oradata/cdb12c/redo_std06a.rdo' size 200M reuse;
alter database add standby logfile thread 1 GROUP 7 '/data/oradata/cdb12c/redo_std07a.rdo' size 200M reuse;

alter database add standby logfile thread 1 GROUP 4 '/data/oradata/cdr12c/redo_std04a.rdo' size 200M reuse;
alter database add standby logfile thread 1 GROUP 5 '/data/oradata/cdr12c/redo_std05a.rdo' size 200M reuse;
alter database add standby logfile thread 1 GROUP 6 '/data/oradata/cdr12c/redo_std06a.rdo' size 200M reuse;
alter database add standby logfile thread 1 GROUP 7 '/data/oradata/cdr12c/redo_std07a.rdo' size 200M reuse;

alter system set standby_file_management=AUTO scope=both sid='*';

EDIT DATABASE testfo SET PROPERTY TransportDisconnectedThreshold='120';

Primary:
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;

PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;

edit database "cdr12c" set state="APPLY-ON";

--================Dataguard broker====

SYS@cdb12c> show parameter dg_broker_start

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SYS@cdb12c>
SYS@cdb12c> alter system set dg_broker_start=true;

System altered.

SYS@cdb12c>
SYS@cdb12c> show parameter dg_broker_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SYS@cdb12c>

SYS@cdr12c> show parameter dg_broker_start

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SYS@cdr12c>
SYS@cdr12c>
SYS@cdr12c> alter system set dg_broker_start=true;

System altered.

SYS@cdr12c>
SYS@cdr12c> show parameter dg_broker_start

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SYS@cdr12c>
SYS@cdr12c>

SYS@cdb12c> show parameter log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=cdr12c ASYNC VALID_FOR
=(ONLINE_LOGFILES,PRIMARY_ROLE
) DB_UNIQUE_NAME=cdr12c
log_archive_dest_20 string

SYS@cdb12c> alter system set log_archive_dest_2='';

System altered.

SYS@cdb12c> show parameter log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string

SYS@cdr12c> show parameter log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=cdb12c ASYNC VALID_FOR
=(ONLINE_LOGFILES,PRIMARY_ROLE
) DB_UNIQUE_NAME=cdb12c
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28 string
log_archive_dest_29 string
SYS@cdr12c>
SYS@cdr12c> alter system set log_archive_dest_2='';

System altered.

SYS@cdr12c> show parameter log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string

[oracle@bach1 ~]$ dgmgrl sys@cdb12c
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon May 21 23:58:41 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb12c"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> create configuration cdb as primary database is cdb12c connect identifier is cdb12c;
Configuration "cdb" created with primary database "cdb12c"
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> add database cdr12c as connect identifier is cdr12c maintained as physical;
Database "cdr12c" added
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>

Tuesday, March 13, 2018

Enable Oracle Enterprise Manager Express 12c

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

create 12c pluggable database using dbca

Below are the step by step process of creating 12c pluggable database using dbca.

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

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