Step By Step Guide On Creating Physical Standby Using RMAN Duplicate In ASM Filesystem For ASM Primary

Pre-Requisite
a) Database (PRIMARY) is up and running and the datafiles, controlfiles residing in ASM.
b) ASM instance and diskgroups configured in STANDBY server.
c) Network connectivity between PRIMARY and STANDBY server

PROCEDURE
1.Enable force logging in PRIMARY.
2.Create SRL(standby redo logs) in PRIMARY.
3.Backup the PRIMARY database.
4.Make proper changes in the parameter file of PRIMARY.
5.Create the parameter file for STANDBY
6.Copy the files (RMAN Backup, init.ora) to STANDBY
7.Establish the connectivity between PRIMARY and STANDBY.
8.Start the STANDBY instance and use RMAN duplicate to create standby database
9. Create SRL on standby.
10.Start the MRP process,
11. Verify whether the log are shipped and applied properly @the standby

Consider two databases of names PRIMARY= PRIMA  and STANDBY= MYSTD
1. Enable Forced Logging on PRIMARY
SQL> ALTER DATABASE FORCE LOGGING;
2. Configure Standby Redo Log on PRIMARY
a. Check the log files and sizes,
SQL>SELECT GROUP#,BYTES FROM V$LOG;
b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;


c. Verify the standby redo log file groups were created(do this after the creation of stanby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Use RMAN to backup PRIMARY database with archivelog & controlfile.

RMAN>backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN>backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';

4. Make the necessary changes to PRIMARY  .

DB_NAME=pri
DB_UNIQUE_NAME=pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
LOG_ARCHIVE_DEST_2= 'SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYSTD'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=MYSTD
FAL_CLIENT=PRIMA
DB_FILE_NAME_CONVERT='MYSTD','PRIMA'
LOG_FILE_NAME_CONVERT='MYSTD','PRIMA'
STANDBY_FILE_MANAGEMENT=AUTO


5. Create the parameter file for standby,

a. CREATE PFILE='<specify any location>' from spfile; (@primary,)
b. Make the necessary changes, for example,

DB_NAME=PRIMA
DB_UNIQUE_NAME=MYSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
CONTROL_FILES='+DATA','+FRA'
DB_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMA
FAL_CLIENT=MYSTD

Note: If the ASM Disk groups names are different between PRIMARY and STANDBY do the necessary changes in STANDBY with the parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n

 

6. Copy the files (RMAN Backup, init.ora) to STANDBY
a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical locationExample:scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*
b) Copy the init.ora (step 5) to STANDBY
c) Recreate the password file in standby using orapwd utility


7. Establish the connectivity between primary and standby.

In PRIMARY (TNSNAMES.ORA)

MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)

In STANDBY (TNSNAMES.ORA)
PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)

NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY

 

8. Start the STANDBY instance and use RMAN duplicate to create standby database

NOTE: Make sure the ASM instance also running.
$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’<specify the newly created parameter location>’
SQL>startup nomount 

 

NOTE: Connect to catalog if your primary database has catalog database.

 

$RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd>
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}

9. Create SRL(Standby Redo logs) on standby,

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

 

 

For Example,

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
10. Start the MRP process in PRIMARY

In PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In STANDBY
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Verify whether the log are shipped and applied properly in the standby

a. execute on PRIMARY database

SQL> ALTER SYSTEM SWITCH LOGFILE;

b. execute on STANDBY database

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

c. Issue more log switches at PRIMARY

SQL> ALTER SYSTEM SWITCH LOGFILE;

d. Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

---

Recompiles/Invalidates all PL/SQL code inside Oracle Db

from sys user we can below script for Recompiles/Invalidates all PL/SQL Code , this will very help full ,

Instance has to be started in upgrade mode and Oracle built-in PLSQL code has to be revalidated. As "startup upgrade" hangs too, we have no option other than using "alter database open migrate" command which achieves the same target databse state as follows:


# sqlplus "/as sysdba"
shutdown immediate
startup mount
alter database open migrate;
@$ORACLE_HOME/admin/utlirp.sql   ---- this script invalidates all PL/SQL code
@$ORACLE_HOME/admin/utlrp.sql    ---- this script recompiles everything again
shutdown immediate
startup

Script to Generate Sequence creation DDL for all primary keys using the current values

This is best achieved by a PL/SQL procedure that generates the required DDL.

Please create below procedure, which can generate DDLs given a schema name:

CREATE OR REPLACE procedure GenerateSeqDDL(schemaowner in varchar2)
IS
  TYPE RecTyp IS RECORD (
tabname VARCHAR2(30),
colname VARCHAR2(30));

  TYPE TabTyp IS TABLE OF RecTyp INDEX BY BINARY_INTEGER;
  sqtab   TabTyp;
  i BINARY_INTEGER;
  m number;

  cursor c1 is
select cons.table_name, col.column_name, cons.owner
from dba_constraints cons
inner join dba_cons_columns col on cons.owner = col.owner and cons.constraint_name = col.constraint_name
inner join dba_tab_cols cols on col.owner = cols.owner and cols.table_name = col.table_name and col.column_name = cols.column_name
where constraint_type = 'P' and cons.owner=schemaowner and cols.data_type = 'NUMBER';
BEGIN

  i := 1;

  FOR rec in c1
  LOOP
sqtab(i).tabname := rec.table_name;
sqtab(i).colname := rec.column_name;
dbms_output.put_line(rec.table_name);
i := i+1;
  END LOOP;

      FOR i2 IN 1..sqtab.count LOOP
execute immediate 'select max(' || sqtab(i2).colname ||') +1 from ' || schemaowner || '.' || sqtab(i2).tabname into m;
DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE ' || sqtab(i2).tabname || '_PK_SEQ START WITH ' || m || ' MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;');
END LOOP;

ENd;
/

Migrate MySQL database to another Server

Make the backup of the database

mysqldump -u root -p --opt [DB_name] > /tmp/[database_name].sql


Where DB_name is the database you want to move.

Copy the database to the new server

You can use rsync, scp or ftp, I will show you how to do it with scp

scp /tmp/[database_name].sql user@newserver.com:/tmp/

Create the database in the new server

 

mysql -u root -pcreate database [database_name];

grant all privileges on [database_name].* to "some-user"@"hostname" identified by "some-strong-password";


flush privileges;

exit

Import the backup

mysql -u root -p [database_name] < /tmp/[database_name].sql

Iptables. open a port more than one IP

Sometimes you need to open a port on your server, you want it to be recheable only from specific IP address, you can use Iptables for this:

iptables -I INPUT -p tcp -s 10.1.1.2 --dport 22 -j ACCEPT

In that case, you are opening ssh port only to IP 10.1.1.2, if you need to open DNS for your internal network.

iptables -I INPUT -p udp -s 10.1.0.0/16 --dport 53 -j ACCEPT

Once you have them added and opened for those IPs, you need to close the door for the rest of IPs

iptables -I INPUT -p tcp -s 0.0.0.0/0 --dport 22 -j DROP iptables -I INPUT -p udp -s 0.0.0.0/0 --dport 53 -j DROP

---

Types Of Privileges In MySQL

Types of privileges in MySQL There are 4 types of privileges. i). Global privileges like *.* (all hosts connecting to Mysql db server) Ex: GRANT SELECT, INSERT ON *.* TO ‘someuser’@'somehost’; ii). Database privileges like .* Ex: GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@'somehost’; iii). Table privileges like SELECT, INSERT, UPDATE, DELETE Ex: GRANT SELECT, INSERT ON mydb.mytbl TO ‘someuser’@'somehost’; iv). Column privileges like Ex: GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@'somehost’;

System MAC address

Each network interface on your computer is identified by a series of letters and numbers down as a MAC address. This string is useful for identifying a computer:

Windows system

Open the Windows Run dialog by holding down the Windows Key and pressing ‘r’.
Type cmd in the Run dialog box and press Enter.
Type getmac at the command prompt. Your MAC address will be displayed.

Linux system

Open a terminal window.
Type ifconfig at the command prompt. Your MAC address will be displayed beside the label HWaddr.

 Mac System

Open the Terminal application.
Type ifconfig at the command prompt. Your MAC address will be displayed beside the label ether.