12C DG Broker ,FSFO -Testing

Tags

,

1) Set DG_BROKER_START parameter on both databases.

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

SQL> sho parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE
2) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME:

SID_LIST_LISTENER_TESTCDB=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME = TESTCDB_DGMGRL)
(SID_NAME=TESTCDB)
(ORACLE_HOME=/apps/oracle/product/12.1.0/DB_1)
)
)

SID_LIST_LISTENER_TESTSTBY=
(SID_LIST=
(SID_DESC=
(SID_NAME=TESTSTBY)
(ORACLE_HOME=/apps/oracle/product/12.1.0/DB_1)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTSTBY_DGMGRL)
(ORACLE_HOME = /apps/oracle/product/12.1.0/DB_1/)
(SID_NAME = TESTSTBY)
)
)

3) [oracle@VM3 admin]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 – 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/xxxxxxx
Connected as SYSDG.
DGMGRL> create configuration ‘TESTBROKER’ as primary database is ‘TESTCDB’ connect identifier is ‘TESTCDB’;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

DGMGDL> exit

SQL> alter system set LOG_ARCHIVE_DEST_2=” scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@VM3 admin]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 – 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/xxxxxxx
Connected as SYSDG.
DGMGRL> create configuration ‘TESTBROKER’ as primary database is ‘TESTCDB’ connect identifier is ‘TESTCDB’;
Configuration “TESTBROKER” created with primary database “TESTCDB”
DGMGRL> add database ‘TESTSTBY’ as connect identifier is TESTSTBY maintained as Phyiscal;
add database ‘TESTSTBY’ as connect identifier is TESTSTBY maintained as Phyiscal;
^
Syntax error before or at “Phyiscal”
DGMGRL> add database ‘TESTSTBY’ as connect identifier is TESTSTBY maintained as Physical;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.
SQL> alter system set LOG_ARCHIVE_DEST_2=” scope=both;

System altered.

DGMGRL> add database ‘TESTSTBY’ as connect identifier is TESTSTBY maintained as Physical;
Database “TESTSTBY” added
DGMGRL> show configuration;

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTCDB – Primary database
TESTSTBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTCDB – Primary database
TESTSTBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to ‘TESTSTBY’
Performing switchover NOW, please wait…
Operation requires a connection to instance “TESTSTBY” on database “TESTSTBY”
Connecting to instance “TESTSTBY”…
Connected as SYSDBA.
New primary database “TESTSTBY” is opening…
Operation requires startup of instance “TESTCDB” on database “TESTCDB”
Starting instance “TESTCDB”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “TESTSTBY”
DGMGRL> show configuration

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTSTBY – Primary database
TESTCDB – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

=============FASTSTARTFAILOVER===================

DGMGRL> show configuration

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTCDB – Primary database
TESTSTBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database ‘TESTCDB’ set property FastStartFailoverTarget= ‘TESTSTBY’;
Property “faststartfailovertarget” updated
DGMGRL> edit database ‘TESTSTBY’ set property FastStartFailoverTarget= ‘TESTCDB’;
Property “faststartfailovertarget” updated
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold =60;
Property “faststartfailoverthreshold” updated

Start Observer:
DGMGRL> start observer
Observer started

Start observer in background
nohup dgmgrl -silent sys/xxxxx@testcdb “start observer” &

Enable Fast start failover:
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL> show configuration;

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTCDB – Primary database
TESTSTBY – (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Test the FSFO:

Abort Primary

SQL> shutdown abort
ORACLE instance shut down.
Standby Alert log:

RFS[5]: Possible network disconnect with primary database
Sat Oct 25 16:55:32 2014
Attempting Fast-Start Failover because the threshold of 60 seconds has elapsed.
Sat Oct 25 16:55:32 2014
Data Guard Broker: Beginning failover
Sat Oct 25 16:55:32 2014
ALTER DATABASE FAILOVER TO TESTSTBY
Sat Oct 25 16:55:32 2014
Terminal Recovery requested in process 3575
Terminal Recovery: Stopping real time apply
Sat Oct 25 16:55:32 2014
MRP0: Background Media Recovery cancelled with status 16037
Sat Oct 25 16:55:32 2014
Errors in file /apps/oracle/diag/rdbms/teststby/TESTSTBY/trace/TESTSTBY_mrp0_3577.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2065014
Sat Oct 25 16:55:32 2014
MRP0: Background Media Recovery process shutdown (TESTSTBY)
Sat Oct 25 16:55:33 2014
Terminal Recovery: Stopped real time apply
Sat Oct 25 16:55:33 2014
Attempt to do a Terminal Recovery (TESTSTBY)
Sat Oct 25 16:55:33 2014
Media Recovery Start: Managed Standby Recovery (TESTSTBY)
Sat Oct 25 16:55:33 2014
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is ’10/25/2014 16:55:33′
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 44 redo required

Sat Oct 25 16:55:33 2014
Terminal Recovery:

Sat Oct 25 16:55:33 2014
Recovery of Online Redo Log: Thread 1 Group 5 Seq 44 Reading mem 0
Mem# 0: /apps/oracle/ORADATA/TESTSTBY/onlinelog/o1_mf_5_b45mthjw_.log
Mem# 1: /apps/oracle/fast_recovery_area/TESTSTBY/onlinelog/o1_mf_5_b45mtj39_.log
Sat Oct 25 16:55:34 2014
Incomplete Recovery applied until change 2065015 time 10/25/2014 16:54:31
Sat Oct 25 16:55:34 2014
Media Recovery Complete (TESTSTBY)
Terminal Recovery: successful completion
Forcing ARSCN to IRSCN for TR 0:2065015
Attempt to set limbo arscn 0:2065015 irscn 0:2065015
Resetting standby activation ID 2602731760 (0x9b2288f0)
Sat Oct 25 16:55:34 2014
ALTER DATABASE SWITCHOVER TO PRIMARY (TESTSTBY)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /apps/oracle/diag/rdbms/teststby/TESTSTBY/trace/TESTSTBY_rsm0_3575.trc
Standby terminal recovery start SCN: 2065014
RESETLOGS after incomplete recovery UNTIL CHANGE 2065015
Online logfile pre-clearing operation disabled by switchover
Online log /apps/oracle/ORADATA/TESTSTBY/onlinelog/o1_mf_1_b45mssyc_.log: Thread 1 Group 1 was previously cleared
Online log /apps/oracle/fast_recovery_area/TESTSTBY/onlinelog/o1_mf_1_b45mstcm_.log: Thread 1 Group 1 was previously cleared
Online log /apps/oracle/ORADATA/TESTSTBY/onlinelog/o1_mf_2_b45mszjn_.log: Thread 1 Group 2 was previously cleared
Online log /apps/oracle/fast_recovery_area/TESTSTBY/onlinelog/o1_mf_2_b45mt09w_.log: Thread 1 Group 2 was previously cleared
Online log /apps/oracle/ORADATA/TESTSTBY/onlinelog/o1_mf_3_b45mt4y7_.log: Thread 1 Group 3 was previously cleared
Online log /apps/oracle/fast_recovery_area/TESTSTBY/onlinelog/o1_mf_3_b45mt5qd_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2065013
Sat Oct 25 16:55:34 2014
Setting recovery target incarnation to 3
Sat Oct 25 16:55:35 2014
Switchover: Complete – Database mounted as primary
Completed: ALTER DATABASE FAILOVER TO TESTSTBY
ALTER DATABASE OPEN
Data Guard Broker initializing…
Sat Oct 25 16:55:35 2014
Observer log:

DGMGRL> start observer
Observer started
16:55:32.21 Saturday, October 25, 2014
Initiating Fast-Start Failover to database “TESTSTBY”…
Performing failover NOW, please wait…
Failover succeeded, new primary is “TESTSTBY”
16:56:28.28 Saturday, October 25, 2014
DGMGRL> show configuration

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTSTBY – Primary database
Warning: ORA-16829: fast-start failover configuration is lagging

TESTCDB – (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

======================Reinstate old Primary========
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
—————————————-
2065013
Old primary:
SQL*Plus: Release 12.1.0.1.0 Production on Sat Oct 25 17:08:18 2014

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 367439872 bytes
Fixed Size 2288488 bytes
Variable Size 255853720 bytes
Database Buffers 104857600 bytes
Redo Buffers 4440064 bytes
Database mounted.

Observer log: FSFO automatically reinstate

17:09:34.11 Saturday, October 25, 2014
Initiating reinstatement for database “TESTCDB”…
Reinstating database “TESTCDB”, please wait…
Reinstatement of database “TESTCDB” succeeded
17:10:19.75 Saturday, October 25, 2014

Old Primary alert log:
FLASHBACK DATABASE TO SCN 2065013
Sat Oct 25 17:09:40 2014
RFS[1]: Assigned to RFS process (PID:32732)
RFS[1]: Database mount ID mismatch [0x9b2bfd89:0x9b2b6c29] (2603351433:260331421
7)
RFS[1]: Not using real application clusters
Sat Oct 25 17:09:40 2014
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Sat Oct 25 17:09:42 2014
Serial Media Recovery started
Sat Oct 25 17:09:44 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 44 Reading mem 0
Mem# 0: /apps/oracle/ORADATA/TESTCDB/onlinelog/o1_mf_1_9vc5pl1w_.log
Mem# 1: /apps/oracle/fast_recovery_area/TESTCDB/onlinelog/o1_mf_1_9vc5plmr_.lo
g
Sat Oct 25 17:09:47 2014
Incomplete Recovery applied until change 2065014 time 10/25/2014 16:54:31
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 2065013
alter database convert to physical standby
Sat Oct 25 17:09:47 2014
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (TESTCDB)
Clearing standby activation ID 2602731760 (0x9b2288f0)
The primary database controlfile was created using the
‘MAXLOGFILES 16′ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 52428800;
Offline data file 5 marked as online during convert to standby or switchover to
standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to
standby.
Restore of backup may be required if the file is not physically accessible.
Sat Oct 25 17:09:47 2014
Waiting for all non-current ORLs to be archived…
Sat Oct 25 17:09:47 2014
All non-current ORLs have been archived.
Clearing online redo logfile 1 /apps/oracle/ORADATA/TESTCDB/onlinelog/o1_mf_1_9v
c5pl1w_.log

Clearing online log 1 of thread 1 sequence number 44
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /apps/oracle/ORADATA/TESTCDB/onlinelog/o1_mf_2_9v
c5poxp_.log

Clearing online log 2 of thread 1 sequence number 42
Sat Oct 25 17:09:51 2014
FLASHBACK DATABASE TO SCN 2065013
ORA-1155 signalled during: FLASHBACK DATABASE TO SCN 2065013

Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /apps/oracle/ORADATA/TESTCDB/onlinelog/o1_mf_3_9v
c5pslk_.log

Clearing online log 3 of thread 1 sequence number 43
Clearing online redo logfile 3 complete
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value “AUTO”.
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Starting background process TMON
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standby
Completed: alter database convert to physical standby
New configuration status:
DGMGRL> show configuration

Configuration – TESTBROKER

Protection Mode: MaxPerformance
Databases:
TESTSTBY – Primary database
TESTCDB – (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS
DGMGRL> show database ‘TESTSTBY’

Database – TESTSTBY

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
TESTSTBY

Database Status:
SUCCESS

DGMGRL> show database ‘TESTCDB’;

Database – TESTCDB

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 479.00 KByte/s
Real Time Query: OFF
Instance(s):
TESTCDB

Database Status:
SUCCESS

Reference:

http://gavinsoorma.com/2010/03/11g-data-guard-broker-dgmgrl-configuration-quick-steps/

http://gavinsoorma.com/2009/06/10g-data-guard-broker-fast-start-failover/

http://www.databasejournal.com/features/oracle/article.php/3840571/Performing-Database-Failover-with-Oracle-11g-Data-Guard.htm

Failover Database

Tags

,

http://docs.oracle.com/cd/E18283_01/server.112/e17022/role_management.htm#i1026491

http://taliphakanozturken.wordpress.com/2011/07/09/physical-standby-database-switchover-failover-operations/

https://blogs.oracle.com/bcndatabase/entry/reinstate_a_failed_over_data

http://gavinsoorma.com/2009/07/flashback-database-after-a-data-guard-failover/

After Test-Failover, make NEW Primary Standby again

http://uhesse.com/2012/01/16/after-test-failover-make-new-primary-standby-again/

CONVERTING NON-ASM INSTANCE TO ASM INSTANCE  

Tags

Database Name: DEVDBA

Asm disk group: +TEST_DG’

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

RESTORE THE CONTROLFILE TO DISK GROUP:

[oracle@VM1  admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Oct 3 09:20:31 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVDB (not mounted)

RMAN>  restore controlfile to ‘+TEST_DG’ from ‘/u02/oradata/11.2.0.3/DEVDB/control01.ctl’;

Starting restore at 03-OCT-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=193 device type=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 03-OCT-13

RMAN> exit

Recovery Manager complete.

[oracle@VM1  admin]$ s

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:25:18 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

SWITCH CONTROLFILE TO DISKGROUP LOCATION:

SQL>  alter system set control_files=’+TEST_DG/DEVDB/CONTROLFILE/current.270.827832049′ scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

SQL> select name from v$controlfile;

NAME

——————————————————————————–

+TEST_DG/devdb/controlfile/current.270.827832049

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

MOVE DATAFILE TO ASM:

[oracle@VM1  admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Oct 3 09:28:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVDB (DBID=732555631, not open)

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

RMAN> backup as copy database format ‘+TEST_DG’;

Starting backup at 03-OCT-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=233 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=241 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=249 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u02/oradata/DEVDB/system.dbf

channel ORA_DISK_2: starting datafile copy

input datafile file number=00002 name=/u02/oradata/DEVDB/sysaux.dbf

channel ORA_DISK_3: starting datafile copy

input datafile file number=00003 name=/u02/oradata/DEVDB/undotbs1.dbf

channel ORA_DISK_4: starting datafile copy

copying current control file

output file name=+TEST_DG/devdb/controlfile/backup.274.827832515 tag=TAG20131003T092833 RECID=1 STAMP=827832514

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_4: starting full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_4: starting piece 1 at 03-OCT-13

channel ORA_DISK_4: finished piece 1 at 03-OCT-13

piece handle=+TEST_DG/devdb/backupset/2013_10_03/nnsnf0_tag20131003t092833_0.275.827832515 tag=TAG20131003T092833 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01

output file name=+TEST_DG/devdb/datafile/sysaux.272.827832515 tag=TAG20131003T092833 RECID=2 STAMP=827832517

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:04

output file name=+TEST_DG/devdb/datafile/undotbs1.273.827832515 tag=TAG20131003T092833 RECID=3 STAMP=827832518

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:04

output file name=+TEST_DG/devdb/datafile/system.271.827832513 tag=TAG20131003T092833 RECID=4 STAMP=827832530

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:27

Finished backup at 03-OCT-13

RMAN> switch database to copy ;

datafile 1 switched to datafile copy “+TEST_DG/devdb/datafile/system.271.827832513″

datafile 2 switched to datafile copy “+TEST_DG/devdb/datafile/sysaux.272.827832515″

datafile 3 switched to datafile copy “+TEST_DG/devdb/datafile/undotbs1.273.827832515″

RMAN> ALTER DATABASE OPEN;

database opened

RMAN> exit

Recovery Manager complete.

[oracle@VM1  admin]$ s

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:29:26 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

+TEST_DG/devdb/datafile/system.271.827832513

+TEST_DG/devdb/datafile/sysaux.272.827832515

+TEST_DG/devdb/datafile/undotbs1.273.827832515

MOVE REDOLOGS TO ASM:

<Note: Make sure the log groups which you want to drop shouldn’t be in CURRENT/ACTIVE state, In case they are switch logfile and check the status>

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 CURRENT

2          1 INACTIVE

3          1 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 (‘+TEST_DG’) size 300M;

Database altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 CURRENT

2          1 INACTIVE

3          1 UNUSED

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 (‘+TEST_DG’) size 300M;

Database altered.

SQL>  select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 CURRENT

2          1 UNUSED

3          1 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 ACTIVE

2          1 CURRENT

3          1 UNUSED

SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 ACTIVE

2          1 ACTIVE

3          1 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 CURRENT

2          1 INACTIVE

3          1 INACTIVE

SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 INACTIVE

2          1 CURRENT

3          1 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 (‘+TEST_DG’) size 300M;

Database altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

GROUP#    MEMBERS STATUS

———- ———- —————-

1          1 UNUSED

2          1 CURRENT

3          1 INACTIVE

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

MODIFY THE PFILE POINTING TO SPFILE:

[oracle@VM1  dbs] vi initDEVDB.ora

Add one parameter  .db_create_file_dest=+TEST_DG

SQL> startup pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora’;

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

Database opened.

SQL> create spfile=’+TEST_DG’ from pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora’;

File created.

SQL> exit

ASMCMD> find –type parameterfile +TEST_DG *

+TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001

##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs

[oracle@VM1  dbs] vi initDEVDB.ora

Spfile=’ +TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001’

SQL> startup pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora’;

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

Database opened.

MOVING THE TEMP FILE:

SQL> select name, bytes from v$tempfile;

NAME

——————————————————————————–

BYTES

———-

/u02/oradata/DEVDB/temp01.dbf

104857600

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select name from v$tempfile;

NAME

——————————————————————————–

+TEST_DG/devdb/tempfile/temp.281.827833353

+TEST_DG/devdb/tempfile/temp1.280.827833319

SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME

——————————————————————————–

+TEST_DG/devdb/tempfile/temp.281.827833353

Ref: http://orasteps.wordpress.com/2013/04/23/migration-of-non-asm-11g-database-to-asm/

Follow

Get every new post delivered to your Inbox.