control file management

Using control file : oracle bring database to the mount stage 

what is the information stored in control file # ?

Metadata of the database if the stores in the control file #

Metadata # structure of the database, : physical location of the database  . db_name, db_unique_name, database scn_number, backup information etc. 

If the control file is corrupted, we cannot start the database, database cannot be mounted 

#### 

oracle recommends us to have 3 control files / in three different locations / 

All the control files have same data ## 

if we mutliplexing control file , if one control file is controlfile is corrupted, we can restore using another control file : 

we store control files using control_files parameter  ### 

SQL> show parameter control_files

NAME     TYPE VALUE

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

control_files     string /u01/app/oracle/oradata/mig23p/control01.ctl,/u01/app/oracle/mig23p/control02.ctl

Procedure to multiplex control files: 

SQL> select name,value,issys_modifiable from v$parameter where name='control_files';

NAME

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

VALUE

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

ISSYS_MOD

---------

control_files

/u01/app/oracle/oradata/mig23p/control01.ctl, 

/u01/app/oracle/mig23p/control02.ctl

FALSE

Nature of parameter is static ## In order to make it permanent, I need to bounce the database ### Here I want to Multiplex the 3rd control file in the same location # 

SQL> alter system set control_files='/u01/app/oracle/oradata/mig23p/control01.ctl','/u01/app/oracle/mig23p/control02.ctl','/u01/app/oracle/mig23p/control03.ctl' scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

And also 3rd control file doesn't exist physically.
Go to the controlfile location and create new controlfile using existing control file 
[oracle@atl mig23p]$ cd /u01/app/oracle/oradata/mig23p/
[oracle@atl mig23p]$ ls -ltr control02.ctl

-rw-r----- 1 oracle oinstall 9748480 May  2 20:05 control02.ctl

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ cp control02.ctl control03.ctl

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ 

[oracle@atl mig23p]$ ls -ltr

total 19072

-rw-r----- 1 oracle oinstall 9748480 May  2 20:05 control02.ctl

-rw-r----- 1 oracle oinstall 9748480 May  2 20:08 control03.ctl

SQL> alter database mount;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME   OPEN_MODE

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

OVAL009P  MOUNTED

SQL> alter database open;

Database altered.

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files   string /u01/app/oracle/oradata/mig23p/control01.ctl, /u01/app/oracle/mig23p/control02.ctl, /u01/app/oracle/mig23p/control03.ctl

What are the operations performed in mount stage #?

we can open database 

we can convert database from noarchivelog mode to archivelog mode

we can enable supplemental logging 

we can enable flashback for database 

we can restore / recover the database 

### Important — >   CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. This parameter is dynamic. we can change this parameter when database is up and running 

SQL> show parameter control_file

NAME         TYPE             VALUE

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

control_file_record_keep_time     integer 7

--Nikhil Tatineni--

--Oracle Database -

GoldenGate Checkpoint Table

Checkpoint table

Replicat maintains the position of trail in the checkpoint table on Oracle database. we can say that replicat add  a entry into the checkpoint table when  replicat applied last successful transaction applied on database. On operating system,  replicat process maintains a checkpoint file in “dirchk” directory. If the checkpoint file is corrupted, we can use information from checkpoint table and we can start replicat from where it is abended or stopped. Replicat consistently track and update information from the checkpoint file and checkpoint table and process transactions on database. 

Specifying a default checkpoint table in the GLOBALS file

Oracle always recommends,  to create checkpoint table in goldengate user schema 

Steps to create checkpoint table

Step1: 

GGSCI (nt4567) 25> dblogin userid ggsuser, PASSWORD XXXXXXXXX

Successfully logged into database.

Step2: 

GGSCI (nt4567) 26> add checkpointtable ggsuser.ckptab

Successfully created checkpoint table ggsuser.ckptab

--Note # when checkpoint table is corrupted, replicat fails on database. you can recreate checkpoint table. when we restart abended replicat, abended replicat uses checkpoint file and process transactions on database. There is no impact on database -

--Nikhil Tatineni--
--Oracle Database --

Oracle Parameter file and server parameter file

There are 2 types of parameter file’s

using parameter file  oracle bring database to no mount stage !!

export ORACLE_SID=dbname

sqlplus / as sysdba

sql> startup nomount;

Oracle will read the parameter file in ORACLE_HOME/dbs location  ###

pfile is textfile

spfile is binary file ( server parameter file )  

if spfile don’t exist, then oracle uses pfile to bring database to nomount stage ##

##if spfile exists , then it will take spfile and bring database to nomount stage ### 

###

pfile    initdatabase.ora

spfile   spfiledatabase.ora 

cd $ORACLE_HOME/dbs

in oracle_home/dbs location : oracle saves all parameter files in dbs location## 

[admind@tnc63 dbs]$ pwd

/u01/app/admind/product/12.1.0/dbhome_1/dbs

[admind@tnc63 dbs]$ ls -ltr

-rw-r--r-- 1 admind oinstall 1083 Apr 15 20:39 inittalent9.ora

-rw-r----- 1 admind oinstall 3584 Apr 21 21:23 spfiletalent9.ora

[admind@tnc63 dbs]$ cat inittalent9.ora

talent9.__data_transfer_cache_size=0

talent9.__db_cache_size=301989888

talent9.__java_pool_size=4194304

talent9.__large_pool_size=8388608

talent9.__oracle_base='/u01/app/admind'#ORACLE_BASE set from environment

talent9.__pga_aggregate_target=213909504

talent9.__sga_target=520093696

talent9.__shared_io_pool_size=8388608

talent9.__shared_pool_size=188743680

talent9.__streams_pool_size=0

*.audit_file_dest='/u01/app/admind/admin/talent9/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/admind/oradata/TALENT9/controlfile/o1_mf_cg6q60gm_.ctl','/u01/app/admind/fast_recovery_area/TALENT9/controlfile/o1_mf_cg6q60jr_.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/admind/oradata'

*.db_domain=''

*.db_name='talent9'

*.db_recovery_file_dest='/u01/app/admind/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/u01/app/admind'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=talent9XDB)'

*.memory_target=697m

*.open_cursors=300

*.processes=600

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_retention=1500

*.undo_tablespace='UNDOTBS1'

sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=‘undo_retention’;

NAME,              VALUE ,   ISSYS_MOD

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

undo_retention      1500       IMMEDIATE

Immediate / True  : Then the parameter is dynamic

False                     : Then the parameter is static 

sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=’cursor_sharing’;

sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=’open_cursors’;

NAME,              VALUE ,   ISSYS_MOD

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

open_cursors      300       IMMEDIATE

If it is a dynamic parameter #

sql> alter system set open_cursors=500 scope=both;

scope=both 

# Value is updated to spfile

# Value is updated in memory 

It is static parameter

# scope=spfile # value is updated only in the spfile ####

sql> alter system set parameter_name=value scope=spfile;

In order to update in memory we need to bounce the database #

sql> shut immediate;

sql> startup;

Assignment #  change following parameters 

processes=600 to 900

open_cursors=500 

undo_retention=1800

db_recovery_file_dest_size=5000m;

--Useful scripts # 

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

COLUMN name  FORMAT A30

COLUMN value FORMAT A70

COLUMN ses_mod FORMAT a10

COLUMN sys_mod FORMAT a10

COLUMN ins_mod FORMAT a10

SELECT p.name,

       p.type,

       p.value,

       p.isses_modifiable as SES_MOD,

       p.issys_modifiable as SYS_MOD,

       p.isinstance_modifiable as INS_MOD

FROM   v$parameter p

ORDER BY p.name

/

---Nikhil Tatineni--

---Oracle In memory-- 

Sample Manager GoldenGate Extract, Pump, Replicat Process

-- Sample Manager process # 
-- Manager runs on default port number 7809 # 
PORT 7809
-- Lag Time reporting #
LAGINFOMINUTES 0
LAGREPORTMINUTES 5
LAGCRITICALSECONDS 30
-- Any process got abended, manager will auto restart the goldengate processes #
AUTORESTART EXTRACT E*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440
AUTORESTART EXTRACT P*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440
AUTORESTART REPLICAT R*, RETRIES 5, WAITMINUTES 2, RESETMINUTES 1440
-- House Keeping jobs, purging used trails using check point  
PURGEOLDEXTRACTS /local_trail_directory/*, USECHECKPOINTS, MINKEEPHOURS 1, FREQUENCYMINUTES 30

NAMING CONVENTION should be followed in any environment we are Implementing GoldenGate#
E starts with Extract 
P starts with Pump
R starts with Replicat 

GoldenGate Extract process should not exceed 8 Characters
Before adding Any goldengate for any table
we have to make sure, we need to enable supplemental logging for table ##

-- Sample Extract Process
-- GGSCI> add extract ework, tranlog, begin now
-- GGSCI> add exttrail /gg_trail/local/da, extract ework, megabytes 500
extract ework
userid ggsuser, password oracle
exttrail /gg_trail/local/da
discardfile /gg_home/dsc/ework.dsc, megabytes 100, append
discardrollover at 06:00 on saturday
TRANLOGOPTIONS INCLUDEREGIONID
table hr.emp;

Collect the enough Information to configure GoldenGate pump process # 
rmthost      # happy.world.com
port number  # 7809
local Trail  # /gg_trail/local
remote trail # /gg_trail/remote 
table hr.emp

--add extract pwork, exttrailsource /gg_trail/local/da, begin now
--add rmttrail /gg_trail/remote/da, extract pwork, megabytes 500
extract pwork
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /gg_home/dsc/pwork.dsc, megabytes 100, append
discardrollover at 06:00 on saturday
rmthost zhappy.world.com, mgrport 7809
rmttrail  /gg_trail/remote/da
table hr.emp;


On Target we have to configure replicat ## 
rmttrail # /gg_trail/remote/
checkpoint table information # ckptab

GGSCI 5> dblogin userid ggsuser, password oracle
Successfully logged into database.
GGSCI 6> add checkpointtable ggsuser.ckptab 

—Sample Replicat PROCESS 
--add replicat rwork,exttrail /gg_trail/remote/da,checkpointtable ggsuser.ckptab
replicat rwork
assumetargetdefs
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /gg_home/dsc/rwork.dsc, megabytes 100, append
discardrollover at 21:00
reperror (default, discard)
map hr.emp, target scott.emp;

--Nikhil Tatineni--
--GoldenGate -- 

EM express for oracle 12c

If you want to configure EM Express Diagnostics Pack and for the SQL Monitor and SQL Tuning Advisor features, you will need the Tuning Pack.

control_management_pack_access parameter=DIAGNOSTIC

starting EM express for NON CDB

[oracle@z1p12c admin]$ lsnrctl status 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAY-2016 00:23:53

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=z1p12c.ffdc.sbc.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     listener

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                27-MAY-2016 00:16:12

Uptime                    0 days 0 hr. 7 min. 41 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/z1p12c/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=z1p12c.ffdc.sbc.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "mldb69" has 1 instance(s).

  Instance "mldb69", status READY, has 1 handler(s) for this service...

The command completed successfully

Execute  DBMS_XDB.setHTTPSPort procedure to set the HTTPS port for Enterprise Manager Express

Manually set up EM Express, simply configure the HTTPS or HTTP port by logging into the database and setting the port

[oracle@z1p12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 27 00:32:57 2016

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

Connected to:

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

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL>  select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()

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

    0

SQL> exec dbms_xdb_config.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport() from dual;

DBMS_XDB.GETHTTPPORT()

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

  8080

SQL> alter system register;

System altered.

SQL> alter system set shared_servers=5 scope=both;

System altered.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()

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

8080

SQL> show parameter dispatcher

NAME     TYPE VALUE

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

dispatchers     string (PROTOCOL=TCP) (SERVICE=mldb69XDB)

max_dispatchers      integer

SQL> !

[oracle@z1p12c admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAY-2016 00:34:44

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=z1p12c.ffdc.sbc.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     listener

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                27-MAY-2016 00:16:12

Uptime                    0 days 0 hr. 18 min. 32 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/z1p12c/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=z1p12c.ffdc.sbc.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=z1p12c.ffdc.sbc.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=z1p12c.ffdc.sbc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/mldb69/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "mldb69" has 2 instance(s).

  Instance "mldb69", status READY, has 1 handler(s) for this service...

Service "mldb69XDB" has 1 instance(s).

  Instance "mldb69", status READY, has 1 handler(s) for this service...

The command completed successfully

connect to EM express for database as follows

http://192.168.1.75:8080/em/login

Grant EM Express to User #

SQL> grant EM_EXPRESS_BASIC to < User >;

--Nikhil Tatineni--

Knowledge on 12c

12c: Upgrading Oracle Multitenant databasehttp://www.oracleinmemory.com/2016/02/12c-upgrading-oracle-multitenant.html12c: New Notation for Oracle Patching Numberhttp://www.oracleinmemory.com/2016/02/12c-new-notation-for-patching-number.html12c: In Memory Column Store
http://www.oracleinmemory.com/2015/11/12c-in-memory-column-store.html12c: Heat Map and Automatic Data Optimization (ADO).http://www.oracleinmemory.com/2015/11/12c-heat-map-and-automatic-data.html12c: In-database archivinghttp://www.oracleinmemory.com/2015/11/12c-in-database-archiving.html12c: parameter: temp_undo_enabledhttp://www.oracleinmemory.com/2015/12/12c-parameter-tempundoenabled.html12c: Trigger to open all pdbs after cdb reboothttp://www.oracleinmemory.com/2016/02/12c-trigger-to-open-all-pdbs-after-cdb.html12c: Clone pluggable database using existing Pluggable databasehttp://www.oracleinmemory.com/2016/02/12c-clone-pluggable-databases-using.html12c: open & close Pluggable databaseshttp://www.oracleinmemory.com/2016/02/12c-open-close-pluggable-databases.html12c: Rename PDB’S from CDB Root http://www.oracleinmemory.com/2016/02/12c-rename-pdbs-from-cdb-root.html12c: Creating Service on Pluggable Database using DBMS_SERVICEhttp://www.oracleinmemory.com/2016/02/12c-creating-service-on-pdb.html12c: Upgrading OPatch Version http://www.oracleinmemory.com/2016/02/12c-upgrading-opatch-version.html

Classic Extract to Integrated Extract 11g/12c

Converting classic Extract to Integrated Extract # 

Prereq's # 

we need additional privilege's to enable the integrated capture on database 

SQL> exec dbms_goldengate_auth.grant_admin_privilege('ggsuser');

PL/SQL procedure successfully completed.

Set "streams_pool_size" parameter for Integrated capture 

Before Upgrading the extract into Integrated mode make sure prereq's completed on database #  

If we are using Classic Extract, if we want to upgrade to Integrated follow the steps as follows

ggsci> dblogin userid ggsuser password oracle
ggsci> stop extract ext_int

ggsci> register extract ext_int database 

ggsci> alter extract ext_int, upgrade Integrated tranlog

ggsci> start extract ext_int 

you can see following message in report file, when you start extract in Integrated mode 
ggsci > view report ext_int

2016-10-13 09:14:46  INFO    OGG-02248  Logmining server DDL filtering enabled.

2016-10-13 09:14:47  INFO    OGG-02068  Integrated capture successfully attached to logmining server OGG$CAP_EEMP u

sing OGGCapture API.

2016-10-13 09:14:47  INFO    OGG-02089  Source redo compatibility version is: 11.2.0.4.0.

2016-10-13 09:14:47  INFO    OGG-02086  Integrated Dictionary will be used.

Links Related to GoldenGate #

GoldenGate Daily Patrol scripts on Servers

http://www.databaseinmemory.com/2016/02/goldengate-daily-patrol-scripts-on.html

Migrating Oracle database using Goldengate

http://www.databaseinmemory.com/2015/11/migrating-oracle-database-using.html

Query’s to monitor Integrated Extract#

http://www.databaseinmemory.com/2016/12/querys-used-to-monitor-goldengate.html

Keygen # GoldenGate Password Encryption

http://www.databaseinmemory.com/2016/10/media-recovery-terminated-with-error.html

Remove DDL replication # GoldenGate

http://www.databaseinmemory.com/2016/11/remove-ddl-replication-goldengate.html

What is lag in OGG ?

http://www.databaseinmemory.com/2015/10/what-is-lag-in-ogg.html

GoldenGate Extract : Bounded Recovery

http://www.databaseinmemory.com/2015/10/extract-bounded-recovery.html

--Nikhil Tatineni--

--Oracle In Memory--

GoldenGate subdirectories Usage 12c/11g

# GoldenGate subdirectories and Usage # 

GGSCI (z1p12c.ffdc.sbc.com) 3> create subdirs

Creating subdirectories under current directory /home/goldengate_home

Parameter files                /home/goldengate_home/dirprm: created

Report files                   /home/goldengate_home/dirrpt: created

Checkpoint files               /home/goldengate_home/dirchk: created

Process status files           /home/goldengate_home/dirpcs: created

SQL script files               /home/goldengate_home/dirsql: created

Database definitions files     /home/goldengate_home/dirdef: created

Extract data files             /home/goldengate_home/dirdat: created

Temporary files                /home/goldengate_home/dirtmp: created

Credential store files         /home/goldengate_home/dircrd: created

Masterkey wallet files         /home/goldengate_home/dirwlt: created

Dump files                     /home/goldengate_home/dirdmp: created

dirprm # Goldengate use dirprm directory to store goldengate parameter files and also we can also store prm that to create definitions for tables in goldengate

dirrpt # Goldengate process creates report files in dirprm directory. when ever goldengate process is stopped, started it writes process information to the report files 

dirchk # checkpoint files are created when ever we create goldengate process. GoldenGate updates the timestamp and scn of the database to the checkpoint file. GoldenGate process any transaction it updates the checkpoint file. If the goldengate process is abended, gg process uses checkpoint file  to start from where it is abended 

dirpcs #  when goldengate process  is up and running file is created with extension ( .pce , .pcr, .pcm ) 

dirsql # stores the sql scripts 

dirdef # definition files are created by defgen utility to store source and target definitions. All the definition files are written in ASCII format

dirdat # goldengate stores the local trail and remote trail created by pump and extract process 

dirtmp # it stores large transactions in the dirtmp directory 

discrd # used to store credential for encrypted user’s id and also store password ( I see this is new in 12c ) 

dirwlt # Wallet that stores master key 

dirdmp # It stores trace and dump files created on dirdmp directory 

--Nikhil Tatineni--

--12c Oracle GoldenGate--