ORACLE-DBA: 2015

Clone RAC Prod DB for RAC Test DB while Prod DB is all alive(with RMAN BACKUP/RESTORE, no Duplicate Database).

##################################################

RMAN Simulation for tape:

CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/u01/app/oracle/backup/)' format '%U.bck';

CONFIGURE DEFAULT DEVICE TYPE TO 'sbt';

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT to 'control_file_%F.bck';

***without tape manager or storage, you can make rman sbt tests on local disk with this parameter

##################################################

We will clone the Prod Database for Test DB on same host while production is alive

Prod Rac Database: source

Test Database: target

1-backup the prod database

run{

backup database plus archivelog;

}

2-create pfile for test database

create pfile='/tmp/pf_Target.ora' from spfile;

3-edit pfile parameters

--change all "source1" with "target1"

--change to *.cluster_database=false

--create directory for audit;

mkdir -p /u01/app/oracle/admin/target/adump

--change controlfile path:

*.control_files='+DATA/TARGET/CONTROLFILE/controlfile.ctl'

--comment redo log parameters

#*.db_create_online_log_dest_1='+REDO1'

#*.db_create_online_log_dest_2='+REDO2'

--do not change db_name='source'

#################################

the most important part is add the following line, otherwise you can not mount the database cos another "source" instance is running

*.db_unique_name='target'

#################################

--comment the following line:

#source1.instance_number=1

4-create spfile from pfile

export ORACLE_SID=target1

sqlplus / as sysdba

SQL> create spfile from pfile='/tmp/pf_Target.ora ';

5-start database mount nomount mode

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 994050048 bytes

Fixed Size 2931712 bytes

Variable Size 356516864 bytes

Database Buffers 629145600 bytes

Redo Buffers

6-restore controlfile from last auto backup

run {

allocate channel ch00 device type 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u01/app/oracle/backup)";

restore controlfile from 'control_file_c-2990360168-20150218-00.bck';

release channel ch00;

}

7-alter database mount;

SQL> alter database mount;

8-restore database different location;

run

{

set newname for database to '+DATA/TARGET/%b' ;

SQL "alter database rename file''+REDO1/SOURCE/ONLINELOG/group_3.258.871999547'' to ''+REDO1'' ";

SQL "alter database rename file''+REDO2/SOURCE/ONLINELOG/group_3.258.871999549'' to ''+REDO2'' ";

SQL "alter database rename file''+REDO1/SOURCE/ONLINELOG/group_2.257.871999543'' to ''+REDO1'' ";

SQL "alter database rename file''+REDO2/SOURCE/ONLINELOG/group_2.257.871999545'' to ''+REDO2'' ";

SQL "alter database rename file''+REDO1/SOURCE/ONLINELOG/group_1.259.871999535'' to ''+REDO1'' ";

SQL "alter database rename file''+REDO2/SOURCE/ONLINELOG/group_1.259.871999537'' to ''+REDO2'' ";

restore database ;

switch datafile all;

switch tempfile all;

}

9- recover database;

RMAN> recover database;

10- open with resetlogs

after this command, you must see the new redolog are created to the new path

RMAN> alter database open resetlogs;

SQL> shu immediate;

SQL> startup;

#####change DB name with NID utility#####

11- change the db_name parameter on pfile

*.db_name='target'

12- shutdown and startup mount

SQL> shu immediate;

SQL> startup mount;

13- issue nid utility (db will be autiomatically shut down)

nid target=sys/20300251 dbname=target setname=yes

14- create spfile from latest pfile with new db_name parameter

SQL> create spfile from pfile='/tmp/pf_Target.ora';

15- startup database and you will see this line in the alert log

SQL> startup;

Global Name changed to TARGET

#####add database to the RAC system##############

16- change parameter on pfile for RAC database

*.cluster_database=true

target1.instance_number=1

17- shutdown database, create spfile and open

SQL> shu immediate;

SQL> create spfile from pfile='/tmp/pf_Target.ora';

18- startup and add database to cluster

srvctl add database -d target -o /u01/app/oracle/product/12.1.0/dbhome_1

srvctl add instance -d target -i target1 -n node1

19- shutdown database

SQL> shu immediate;

20- open and control database with srvctl

srvctl start database -d target

srvctl stop database -d target

srvctl start database -d target

Goldengate Multiple Schema Replication between

Oracle 11gR2 and Oracle 12cR1

include DDL replication

Source Host:

OS: Red Hat Enterprise Linux Server release 5.10 (Tikanga) x86_64

DB Name: source à 11gR2 (11.2.0.4.0) reside on ASM

Host Name: rac1.localdomain

Target Host:

OS: Red Hat Enterprise Linux Server release 6.6 (Santiago) x86_64

DB Name: target à 12cR1 (12.1.0.2.0) reside on ASM

Host Name: node1.localdomain

Goldengate Version: 12.1.2.1.0

Main Configuration Steps

1- Install Goldengate Software on both source and target systems

2- Export the schemas which will be replicated and import them to the target database

3- Prepare the source database

4- Prepare the target database

5- Configure extract, pump and replicat process

6- Start extract and replication proccess

7- Control DML and DDL operations

1. Install Goldengate Software on both source and target systems

Source:

[oracle@rac1 ~]$ cd /media/sf_oracle_setups/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@rac1 Disk1]$ sh runInstaller

[oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

[oracle@rac1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25

Operating system character set identified as UTF-8.

GGSCI (rac1.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (rac1.localdomain) 2>

Target:

[oracle@node1 goldengate]$ cd /media/sf_12c/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@node1 Disk1]$ sh runInstaller

[oracle@node1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (node1.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (node1.localdomain) 2>

2. Export the schemas which will be replicated and import them to the target database

Source:

create or replace directory pump_dir as '/u01/app/oracle/pump';

expdp userid='"/ as sysdba"' schemas=hr,scott dumpfile=scott_hr.dmp directory='pump_dir'

Target:

create or replace directory pump_dir as '/u01/app/oracle/pump';

create tablespace target_tbs datafile '+DATA' size 500m;

impdp userid='"/ as sysdba"' dumpfile=scott_hr.dmp directory='pump_dir' remap_tablespace=example:target_tbs;

grant connect, resource, unlimited tablespace to hr, scott;

***impdp can give some errors about granting roles if you dont create the roles first manually.

3. Prepare the source database

a. Switch database archivelog

[oracle@rac1 ~]$ srvctl stop database -d source

[oracle@rac1 ~]$ srvctl start database -d source -o mount

SQL> alter database archivelog;

SQL> alter database open;

b. Add supplemental log and disable recyclebin

SQL> alter database add supplemental log data;

SQL> alter system set recyclebin=off scope=spfile;

SQL> purge dba_recyclebin;

c. Create goldengate tablespace and user

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

d. Create the objects and roles for ddl replication

[oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

SQL> @marker_setup.sql

--enter gg_user for goldengate schema

SQL> @ddl_setup.sql

--enter gg_user for goldengate schema

SQL> @role_setup.sql

--enter gg_user for goldengate schema

SQL> GRANT GGS_GGSUSER_ROLE TO gg_user;

SQL> @ddl_enable.sql

SQL> alter system set enable_goldengate_replication=true;

e. Add info about Goldengate DDL schema

[oracle@rac1 gg]$ ./ggsci

GGSCI (rac1.localdomain) 1> edit params ./GLOBALS

GGSCHEMA gg_user

GGSCI (rac1.localdomain) 2>

f. Create trail and discard(only on target) directories

Source:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

Target:

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/discard

g. Check manager process and port on host side

GGSCI (rac1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (rac1.localdomain) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (rac1.localdomain) 3> start manager

Manager started.

GGSCI (rac1.localdomain) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

h. Add primary key information to the logfiles for replicated schemas

GGSCI (rac1.localdomain) 1> dblogin userid gg_user;

Password:

Successfully logged into database.

GGSCI (rac1.localdomain as gg_user@source1) 2> add schematrandata hr

2015-01-14 17:24:04 INFO OGG-01788 SCHEMATRANDATA has been added on schema hr.

2015-01-14 17:24:04 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hr.

GGSCI (rac1.localdomain as gg_user@source1) 3> add schematrandata scott

2015-01-14 17:24:28 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.

2015-01-14 17:24:28 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.

i. Add ASM entry to the tnsnames.ora for redo logs

ASM1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.198)(PORT = 1521))

(CONNECT_DATA =

(INSTANCE_NAME = +ASM1)

(SERVER = dedicated)

(SERVICE_NAME = +ASM)

)

4. Prepare the target database

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

SQL> alter system set enable_goldengate_replication=TRUE;

5. Configure extract, pump and replicat process

Source:

GGSCI (rac1.localdomain) 1> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI (rac1.localdomain) 2> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1

EXTTRAIL added.

GGSCI (rac1.localdomain) 3> edit params ext1

extract ext1

userid gg_user password oracle

tranlogoptions asmuser sys@asm1 asmpassword 123456

exttrail /u01/app/oracle/product/gg/dirdat/tr

ddl include mapped

map hr.*;

map scott.*;

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 5> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr, begin now

EXTRACT added

GGSCI (rac1.localdomain) 7> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1

RMTTRAIL added.

GGSCI (rac1.localdomain) 6> edit params pump1

extract pump1

userid gg_user, password oracle

rmthost 192.168.150.241, mgrport 7809

rmttrail /u01/app/oracle/product/gg/dirdat/tr

passthru

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:31:46

EXTRACT STOPPED PUMP1 00:00:00 00:09:15

Target:

GGSCI (node1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (node1.localdomain) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (node1.localdomain) 6> edit params ./GLOBALS

checkpointtable gg_user.checkpoint

GGSCI (node1.localdomain) 1> dblogin userid gg_user

Password:

Successfully logged into database.

GGSCI (node1.localdomain as gg_user@target1) 2> add checkpointtable gg_user.checkpoint

Successfully created checkpoint table gg_user.checkpoint.

GSCI (node1.localdomain as gg_user@target1) 3> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now

REPLICAT added.

GGSCI (node1.localdomain as gg_user@target1) 4> edit params rep1

replicat rep1

assumetargetdefs

SETENV (ORACLE_SID=target1)

SETENV (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)

userid gg_user, password oracle

discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10

DDL

map hr.*, target hr.*;

map scott.*, target scott.*;

GGSCI (node1.localdomain as gg_user@target1) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:11:44

6. Start extract and replication proccess.

Source:

GGSCI (rac1.localdomain) 1> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (rac1.localdomain) 2> start extract pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (rac1.localdomain) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:00

EXTRACT RUNNING PUMP1 00:00:00 00:00:08

Target:

GGSCI (node1.localdomain) 11> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (node1.localdomain) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:08

7. Control DML and DDL operations

Source:

[oracle@rac1 ~]$ sqlplus hr/hr

SQL> create table emre_test (idx number);

SQL> insert into emre_test values (1);

SQL> insert into emre_test values (2);

SQL> commit;

exit;

[oracle@rac1 ~]$ sqlplus scott/scott

SQL> create table emre_test2 (names varchar2(30));

SQL> insert into emre_test2 values ('emre');

SQL> insert into emre_test2 values ('emir');

SQL> commit;

Target:

[oracle@node1 tr]$ sqlplus hr/hr

SQL> select * from emre_test;

IDX

----------

[oracle@node1 tr]$ sqlplus scott/scott

SQL> select * from emre_test2;

NAMES

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

emre

emir

ORACLE-DBA

Menu

18 Şubat 2015 Çarşamba

15 Ocak 2015 Perşembe