Skip to main content

Posts

Showing posts from 2017

MySQL NDB Cluster Installation for Data, SQL and Management Node

Management node: It manages the other nodes within the NDB Cluster, provides configuration data, start and stop nodes, running backups. As it manages the configuration of the other nodes, it should be started first, before any other node. Use ndb_mgmd to start it.
It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status.
Data node: It stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you need four data nodes. One replica is good for data storage, but no redundancy. For redundancy and high availability 2 more nodes are recommended.To start data node use ndbd or ndbmtd. Tables are normally stored completely in memory, that's why it can be refer as in-memory database. Some data can be stored on disk.
SQL node: It accesses the cluster data. It is a traditional MySQL server that uses the NDBCLUSTER…

Oracle 12c Golden Gate Integrated Configuration

Enable minimal supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
Configure source MGR
GGSCI > edit param mgr
PORT 7809
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3    
Configure Target MGR
GGSCI >  edit param mgr
Port 7909
DynamicPortList 20100-20199
PurgeOldExtracts ./dirdat/pe*, UseCheckPoints, MinKeepHours 2
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
Configure Wallet at Source, so you don't need to provide password in
GGSCI>  Create Wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI> Add CredentialStore                                     
Credential store created in ./dircrd/.
GGSCI> Alter CredentialStore Add User <User>@<ServiceName> Password <pwd> Alias <alias>
Credential store in ./dircrd/ altered.
GGSC…

Oracle Daily SQL

-- crsctl for Oracle RAC

./crsctl start cluster
./crsctl check crs

- Make column to CLOB

ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME LONG);
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME CLOB);
RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>;

- Rename column, change data type, drop column

ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO COLUMN_NAME1;
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME VARCHAR2(10));
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

-- Create Primary Key

ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN1,COLUMN2,COLUMN3,COLUMN4) USING INDEX;


-- Create Unique Index, Rename Index

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1,COLUMN2,COLUMN3,COLUMN4);


ALTER INDEX <INDEX_NAME> RENAME TO <NEW_INDEX>;

-- Privilege and Role management

SELECT * FROM ROLE_SYS_PRIVS;

-- Create synonym

create synonym <SYNONYM_NAME>for  <SCHEMA_NAME>.<TABLE_NAME>;

--  Add Data file

select FILE_NAME || '    ' || TABLESPA…

Troubleshoot Goldengate using Logdump

Troubleshoot Golden Gate :-

01. Get error detail from ggserr.log
02. Identify your file using following command
     ggsci> INFO REPLICAT <REP_NAME> DETAIL
     Last two digits of the file name represents the Sequence Number. for example:
        “/u01/oracle/goldengate/dirdat/se000007”
03. Find out position such as op pos=00000000000000006637, tx pos=00000000000000002467     
04. Use logdump uitility on GGSCI command
    use rlwrap so that you can scroll and search
    $ rlwrap ./logdump
    GHDR ON
    DETAIL ON
    DETAIL DATA
05.  Logdump>OPEN /u01/ogg-bd/dirdat/<File_Name>
       Logdump 12 >pos 6637
       Logdump 37 >POS 0
Reading forward from RBA 0
Logdump 38 >FILTER INCLUDE FILENAME ORCL.SOE.CUSTOMERS
Logdump 39 >N
    Here is the way how LOGSCN appear in GG Dump file
        TokenID x4c 'L' LOGCSN    Info x00  Length    8   
        3131 3637 3235 3433  | 11672543

Options to fix the issue:-

Reset RBA using Logdump
SKIPTRANSACTION Param…

Oracle Golden Gate Basic Classic

Create environment
01. Source & target - Create GG tablespace.
02. Source & target - Create the GoldenGate Schema Owner
03. Grant privilege including DBA to GG schema owner
04. Add schema owner to global parameter file ./GLOBALS
05. Execute role set up script to create role GGS_GGSUSER_ROLE
06. Grant role GGS_GGSUSER_ROLE to GG user at both Source and target
Configure GG  Extract
01. Source and Target - Configure Manager Parameters
$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
02. Source, create parameter file for Extract - ex1,
EXTRACT ex1
USERID <id>, PASSWORD <pwd>
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE <Schema>.*;
03. Source, configure Data Pump Parameters
EXTRACT dp1
USERID <id>, PASSWORD <pwd>
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE <schema>.*;
Target - Create check point table
$ ggsci
GGSCI 1> DBLOGIN USERID <id>, PASSWORD <pwd>
GGSCI 2>…

Oralce Pluggable Database

Pluggable database administration:-

Open all pluggable databases:
Connect to container database using oraenv
$. oraenv=DB_Name
$sqlplus "/as sysdba"
SQL> alter pluggable database all open;
Get pluggable database status:
SQL> select con_id, dbid, name, open_mode from v$pdbs;
Switch to Puggable database from container :
SQL> alter session set container = <Puggable_DB>;
Get status of Pluggable database
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
Get file location for pluggable database:
SQL>select file_name from dba_data_files;
Create table space for pluggable datatbase:
SQL> alter session set container=<Pluggable_DB>;
Session altered.
SQL> CREATE TABLESPACE TB_Name LOGGING DATAFILE '/<Path>/<File_Name>.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Set new t…