My DBA Reference: October 2017

Tuesday, October 10, 2017

Apply psu and one off patches for single instance database with ASM

First make sure you have lastest version of OPatch in both oracle and grid homes.

Opatch Grid home owned by oracle:dba

Opatch db home owned by oracle:dba

Patch folder unzipped by oracle

opatch auto to be run by root

ocm.rsp file to be generated by oracle user in both grid and db home

cd $ORACLE_HOME

cd OPatch/ocm/bin

./emo*

Create a response file using above

download patch and unzip to the staging folder

in my case

/export/home/oracle/oracle_software/25476126

First apply for grid home

export ORACLE_HOME=/opt/app/crs/11.2.0.4

./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/crs/11.2.0.4 -ocmrf /opt/app/crs/11.2.0.4/OPatch/ocm/bin/ocm.rsp

Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/oracle/product/11.2.0/11.2.0.4/ -ocmrf /opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/ocm/bin/ocm.rsp

***********************************************************************

Apply one off patches

First apply from grid home

export ORACLE_HOME=/opt/app/crs/11.2.0.4

cd /opt/app/crs/11.2.0.4/OPatch
./opatch apply -oh /opt/app/crs/11.2.0.4 -local /export/home/oracle/oracle_software/10194190

Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

cd /export/home/oracle/oracle_software/10194190

/opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/opatch apply

********************************************************************************

NOTE

if response file creation fails with motif error in solaris

please install below

pkg install pkg://solaris/library/motif

Friday, October 6, 2017

cleaning all asm disks and headers and recreating diskgroup in solaris

Identify the disks for the diskgroup you are trying to format everything from

In my case i am cleanup data diskgroup and the raw devices are
/dev/rdsk/c0d3s0
/dev/rdsk/c0d4s0
..
..
...
..

So i am running the following command to cleanup disks

dd if=/dev/zero of=/dev/rdsk/c0d3s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d4s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d5s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d6s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d7s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d8s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d9s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d10s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d11s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d12s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d13s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d14s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d15s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d16s0 bs=1048576 count=50

Recreating diskgroup in solaris

Login as root

# format

select disk

y
p
6
default
default
0
0
1
default
default
0
0

0
default
default
1

10920c/65532c (depending on disk)
label
y
q

After its done

View permissions using

ls -lhL /dev/rdsk/c0d*s0

grant permission to oracle user on the disk

chown oracle:dba /dev/rdsk/c0d3s0
chown oracle:dba /dev/rdsk/c0d4s0

chmod 660 /dev/rdsk/c0d3s0
chmod 660 /dev/rdsk/c0d4s0

Once you do this

you should be able to see diskgroup in asmca and proceed creating diskgroup

To improve rebalance speed

alter diskgroup data rebalance power 4;

11g standby database using dataguard

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Primary and standby :

tnsnames.ora on both servers

testDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

Edit or change parameters from db

PRIMARY
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';

--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/

scp orapwtest oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/

Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump

Standby:

startup nomount

Primary :

rman target sys/password@test auxiliary sys/password@testDR

duplicate target database for standby from active database dorecover;

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Verify Dataguard configuration is syncing by using below queries

Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

************************************************************************************
Steps to activate standby:

STOP MRP
alter database recover managed standby database cancel;

Change Redolog entries in control files before activating standby

on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;

If you get any errors regards to redolog.. Fix the issue

select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;

alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';

Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;

Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

10g standby database using dataguard

Primary and standby :

tnsnames.ora on both servers

testDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

Edit or change parameters from db

PRIMARY
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';

--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/dbs/

scp orapwtest oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/dbs/

Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump

Primary:

alter database create standby controlfile as'/export/home/oracle/test_stby.ctl' reuse;

scp /export/home/oracle/test_stby.ctl oracle@10.0.1.2:/export/home/oracle/

Run incremental/ full backup

Once completed. Make sure the backup location is accessible by standby database.

Standby:

startup nomount

rman target=sys/password@test auxiliary=/

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

alter database recover managed standby database disconnect;

Verify Dataguard configuration is syncing by using below queries

Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

************************************************************************************
Steps to activate standby:

STOP MRP
alter database recover managed standby database cancel;

Change Redolog entries in control files before activating standby

on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;

If you get any errors regards to redolog.. Fix the issue

select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;

alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';

Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;

Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

10g oracle database refresh or clone to same name

test_OLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

test_NEW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

On Source:

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

create pfile from spfile;

scp orapwtest oracle@10.120.1.2:/opt/app/oracle/product/10.2.0/dbs/

scp inittest.ora oracle@10.120.1.2:/opt/app/oracle/product/10.2.0/dbs/

Rman > CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;

Take a fullbackup.

On TARGET

Edit pfile

*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump

Make sure the backup from source db is accessible in the same location to standby

If not create directories and copy backup and archive logs to the target

scp -r /oracle/oradata1/backup/test oracle@10.120.1.2:/oracle/oradata1/backup/

Copy the archive logs from source to target

scp -r /oracle/oradata1/archive/test oracle@10.120.1.2:/oracle/oradata1/archive/

Target:

startup nomount

rman target sys/Password@test_old auxiliary /

DUPLICATE TARGET DATABASE TO test;

11g oracle database refresh or clone to different name

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Source and Target :

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

testx =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testx)
)
)

Source:

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp inittest.ora oracle@10.120.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/inittestx.ora

scp orapwtest oracle@10.120.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/orapwtestx

Target:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+REDO'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.control_files='+DATA/testx/controlfile/control01.ctl','+REDO/testx/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/testx/adump'
*.diagnostic_dest='/opt/app/oracle'

create directories as per pfile

mkdir -p /opt/app/oracle/admin/testx/adump

Target:

startup nomount

Source :

rman target sys/password@test auxiliary sys/password@testx

duplicate target database to testx from active database;

11g oracle database refresh or clone to same name

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Source and Target :

test_old =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

test_new =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

Source:

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp inittest.ora oracle@10.120.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/inittest.ora

scp orapwtest oracle@10.120.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/orapwtest

Target:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+REDO'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.diagnostic_dest='/opt/app/oracle'

create directories as per pfile

mkdir -p /opt/app/oracle/admin/test/adump

Target:

startup nomount

Source :

rman target sys/password@test_old auxiliary sys/password@test_new

duplicate target database to test from active database;

My DBA Reference