Database Administrator Blogs : datagurad

Showing posts with label datagurad. Show all posts

Register standby database using SRVCTL Utility

Error:

[oracle@Test2 home]$ srvctl stop database -d colldb

PRCD-1120 : The resource for database colldb could not be found.

PRCR-1001 : Resource ora.colldb.db does not exist

[oracle@Test2 home]$ srvctl config database -d colldb

PRCD-1120 : The resource for database colldb could not be found.

PRCR-1001 : Resource ora.colldb.db does not exist

Solution

[oracle@Test2 home]$ srvctl add database -d colldb -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfile$ORACLE_SID

[oracle@Test2 home]$ srvctl config database -d colldb

Database unique name: colldb

Database name:

Oracle home: /u/oracle/server/database203

Oracle user: oracle

Spfile: /u/oracle/server/database203/dbs/spfilecolldb

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Database instance: colldb

Disk Groups:

Services:

Oracle Local Listener Vs Remote Listener

The local_listener init.ora parameter specifies a network name that resolves to an address or address list of Oracle Net local listeners and it is used used ONLY when you have multiple listeners that are running on the same machine as this instance.

The LOCAL_LISTENER parameter is a component of the cross instance registration technology for Oracle RAC (Real Application Clusters).Cross instance registration needs the local listener to help manage connections across all database instances for load balancing and fail over. The local listener is particularly meaningful for the RAC configuration featured by multiple listeners on cluster nodes.
If your Oracle configuration is not RAC enabled, you may as well remove the LOCAL_LISTENER parameter from your spfile in case for default port 1521 .

SQL> show parameter local_listener

NAME TYPE VALUE
------------ -----------
local_listener string

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.2.210)(PORT=1521))';
System altered.

SQL> alter system register;
System altered.

To dynamically update the LOCAL_LISTENER parameter, use the SQL statement ALTER SYSTEM SET:

ALTER SYSTEM SET LOCAL_LISTENER='LISTENER_PRIM' scope=both sid='*' ;

If you set the parameter to null with the statement that follows, then the default local address of TCP/IP, port 1521 is assumed.

ALTER SYSTEM SET LOCAL_LISTENER=''

example : in local host tnsnames.ora file

LISTENER_PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521)))

Suppose we have 2-node cluster: host1 and host2, with VIP address host1-vip and host2-vip respectively.One RAC database (orcl) running on this cluster; instace 1 (orcl1) on host1, and instance 2 (orcl2) on host2. we have listener_host1 running on host1, and listener_host2 running on host2.
listener_host1 is considered local listener for orcl1 instance, while listener_host2 is considered remote listener for that same orcl1 instance.similarly, listener_host2 is considered local listener for orcl2 instance, and considered as remote listener for orcl1.

Errors Reported while Creating New Standby using RMAN

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/27/2013 17:00:50
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name +DATA/colldb/datafile/users.259.832173861 conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATA/colldb/datafile/undotbs1.258.832173861 conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATA/colldb/datafile/sysaux.257.832173861 conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATA/colldb/datafile/system.256.832173859 conflicts with a file used by the target database

Resulution: Use NOFILECHECK IN RMAN SCRIPT;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 22:11:28
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 11/26/2013 22:11:28
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Resolution: Check both password file

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 22:18:24
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925

Resolution : Check audit file location; if not avialble create as like primary /u/oracle/server

Data guard Broker Configuration

: Data Guard Broker :

The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. The following describes some of the operations the broker automates and simplifies

I.) Adding additional new or existing (physical, snapshot, logical, RAC or non-RAC) standby databases to an existing Data Guard configuration, for a total of one primary database, and from 1 to 30 standby databases(in Oracle 11g) in the same configuration.

II.) Managing an entire Data Guard configuration, including all databases, redo transport services, and log apply services, through a client connection to any database in the configuration.

III.) Managing the protection mode for the broker configuration.

IV.) Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.

V.) Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.

VI.) Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the redo apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.

Step by Step How to Create Dataguard Broker Configuration

1. Set the parameter dg_broker_config_file1 and dg_broker_config_file2 on all instances
of primary and standby.

Default location of broker configuration file is $ORACLE_HOME/dbs or $ORACLE_HOME/database.

In case if you want broker configuration files in non default location, set these parameters.

In case of RAC database, set broker configuration file location to shared location
and same value on all the instances.

ALTER SYSTEM SET dg_broker_config_file1 = '\U01\oradata\dr1mfg.dat' scope=both sid='*';

ALTER SYSTEM SET dg_broker_config_file2 = '\U01\oradata\dr2mfg.dat' scope=both sid='*';

OR in case of ASM file system use:

ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/mfg/dr1mfg.dat' scope=both sid='*';

ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/mfg/dr2mfg.dat' scope=both sid='*';

If you want the broker configuration files in default location,
then you can ignore this step- 1

2. Enable broker on both primary and standby:

SQL>ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';

3. Connect to DGMGRL on primary: (from instance one in case of RAC)

$dgmgrl

DGMGRL for Linux: Version 10.2.0.3.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> CONNECT sys/<password>;

Connected.

4. Create configuration on primary:

DGMGRL> CREATE CONFIGURATION 'COLDB_CONF' AS PRIMARY DATABASE IS
'colldb_pm' CONNECT IDENTIFIER IS colldb_pm;

Configuration "COLDB_CONF" created with primary database "COLLDB_PM"

5. Add standby in the configuration:

DGMGRL> ADD DATABASE 'colldb_st' AS CONNECT IDENTIFIER IS colldb_st MAINTAINED
AS PHYSICAL;

Database "COLLDB_ST" added

6. Enable Configuration:

DGMGRL> ENABLE CONFIGURATION;

Enabled.

Use the SHOW command to verify that the configuration and databases were successfully
enabled and brought online:

DGMGRL> SHOW CONFIGURATION;

Configuration

Name: PRODCONF

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

PROD10G - Primary database

STDBY10G - Physical standby database

Current status for "PRODCONF"

SUCCESS

IF ANYONE WANT TO RECONFIGURE AGAIN THE USE BELOW STEPS.

1. DGMGRL> CONNECT SYS/MANAGER

2. DGMGRL> DISABLE CONFIGURATION;

Disabled.

3. DGMGRL> remove CONFIGURATION;

Removed configuration

4. Continue Step 3

Data-Guard Set up using ASM On Oracle 11GR2

:: Data-Guard Set up using ASM On Oracle 11GR2

Oracle Data Guard is one of the most effective and comprehensive data availability, data protection and disaster recovery solutions available today for enterprise data.Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.Data Guard maintains these standby databases as transitional consistent copies of the production database. These standby databases can be located at remote disaster recovery sites thousands of miles away from the production data center, or they may be located in the same city, same campus, or even in the same building. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage, and preventing any data loss.

Available as a feature of the Enterprise Edition of the Oracle Database, Data Guard can be used in combination with other Oracle High Availability (HA) solutions such as Real Application Clusters (RAC), Oracle Flashback and Oracle Recovery Manager (RMAN), to provide a very high level of data protection and data availability that is unprecedented in the industry.

Step By Step Guide to Configure 11gR2 dataguard Primary Database Side Set Up

SQL> select force_logging from v$database;

FOR

---

SQL> alter database force logging;

Database altered.

SQL> alter database add standby logfile '+DATA' SIZE 50M;

Database altered.

SQL> alter database add standby logfile '+DATA' SIZE 50M;

Database altered.

SQL> alter database add standby logfile '+DATA' SIZE 50M;

Database altered.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> alter system set log_archive_config='dg_config=(colldb_pm,colldb_st)';

System altered.

SQL> alter system set log_archive_dest_2='service=colldb_st async valid_for=(online_logfile,primary_role) db_unique_name=colldb_st';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=colldb_st

2 ;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET log_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';

Database altered.

SQL> CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;

File created.

SQL>

SQL> alter system set FAL_SERVER=colldb_st;

System altered.

SQL> alter system set FAL_CLIENT=colldb_pm;

System altered.

SQL>

Stand by Database Set up:

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

Database Name :- COLLDB

Primary db_unique_name :- COLLDB_PM

Standby db_unique_name :- COLLDB_ST

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

*On standby* Add listener entry Listener file will be found in GRID_HOME/network/admin

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME= /u/oracle/server/database203/)

(SID_NAME = colldb)

(GLOBAL_DBNAME= colldb)

)

(SID_DESC =

(global_dbname = colldb)

(ORACLE_HOME = /u/oracle/server/database203)

(sid_name = colldb)

)

After that tnsping each service name on both the servers

Add entries to tnsnames.ora to both nodes TNSNAMES.ORA will be found in ORACLE_HOME/network/admin on both servers

COLLDB_PM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Test1.imslab.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = colldb_pm)

)

COLLDB_ST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Test2.imslab.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = COLLDB)

)

Step 9::

Copy Password file onto standby or create password file with same password

copy password file primary to stand by

orapwd file=/u/oracle/server/database203/dbs/orapwcolldb force=y entries=5 password=sys

Step 10::
Location ORACLE_HOME/dbs/initcolldb

Create Standby Pfile

Only one parameter needed - > DB_NAME

DB_NAME=TESTMP2

Step 11::

Startup Standby

sqlplus / as sysdba

startup nomount

Stand Db creation:

From Standby database run these commands

[oracle@Test2 ~]$ rman target sys/sys@colldb_pm auxiliary sys/sys@colldb_st

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Nov 27 16:52:41 2013

connected to target database: COLLDB (DBID=3477737225)

connected to auxiliary database: COLLDB (not mounted)

RMAN>

run

{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'colldb_pm','colldb_st'

set db_unique_name='stbydv1'

set db_file_name_convert='+DATA','+DATA'

set log_file_name_convert='+DATA','+DATA'

set control_files='+DATA'

set log_archive_max_processes='5'

set fal_client='colldb_st'

set fal_server='colldb_pm'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(colldb_pm,colldb_st)'

set log_archive_dest_2='service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'

nofilenamecheck;

release channel prmy1;

release channel prmy2;

release channel prmy3;

release channel prmy4;

}

OUTPUT

RMAN> run

2> {

3> allocate channel prmy1 type disk;

4> allocate channel prmy2 type disk;

5> allocate channel prmy3 type disk;

6> allocate channel prmy4 type disk;

7> allocate auxiliary channel stby type disk;

8> duplicate target database for standby from active database

9> spfile

10> parameter_value_convert 'colldb_pm','colldb_st'

11> set db_unique_name='colldb_st'

12> set db_file_name_convert='+DATA','+DATA'

13> set log_file_name_convert='+DATA','+DATA'

14> set control_files='+DATA'

15> set log_archive_max_processes='5'

16> set fal_client='colldb_st'

17> set fal_server='colldb_pm'

18> set standby_file_management='AUTO'

19> set log_archive_config='dg_config=(colldb_pm,colldb_st)'

20> set log_archive_dest_2='service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'

21> nofilenamecheck;

22> release channel prmy1;

23> release channel prmy2;

24> release channel prmy3;

25> release channel prmy4;

26> }

allocated channel: prmy1

channel prmy1: SID=85 device type=DISK

allocated channel: prmy2

channel prmy2: SID=73 device type=DISK

allocated channel: prmy3

channel prmy3: SID=83 device type=DISK

allocated channel: prmy4

channel prmy4: SID=79 device type=DISK

allocated channel: stby

channel stby: SID=20 device type=DISK

Starting Duplicate Db at 27-NOV-13

contents of Memory Script:

{

backup as copy reuse

targetfile '/u/oracle/server/database203/dbs/orapwcolldb' auxiliary format

'/u/oracle/server/database203/dbs/orapwcolldb' targetfile

'+DATA/colldb/spfilecolldb.ora' auxiliary format

'/u/oracle/server/database203/dbs/spfilecolldb.ora' ;

sql clone "alter system set spfile= ''/u/oracle/server/database203/dbs/spfilecolldb.ora''";

}

executing Memory Script

Starting backup at 27-NOV-13

Finished backup at 27-NOV-13

sql statement: alter system set spfile= ''/u/oracle/server/database203/dbs/spfilecolldb.ora''

contents of Memory Script:

{

sql clone "alter system set db_unique_name =

''colldb_st'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''+DATA'', ''+DATA'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''+DATA'', ''+DATA'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''+DATA'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_max_processes =

5 comment=

'''' scope=spfile";

sql clone "alter system set fal_client =

''colldb_st'' comment=

'''' scope=spfile";

sql clone "alter system set fal_server =

''colldb_pm'' comment=

'''' scope=spfile";

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_config =

''dg_config=(colldb_pm,colldb_st)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_2 =

''service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_unique_name = ''colldb_st'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''+DATA'', ''+DATA'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''+DATA'', ''+DATA'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''+DATA'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

sql statement: alter system set fal_client = ''colldb_st'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''colldb_pm'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(colldb_pm,colldb_st)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1657225216 bytes

Fixed Size 1345324 bytes

Variable Size 1040189652 bytes

Database Buffers 603979776 bytes

Redo Buffers 11710464 bytes

allocated channel: stby

channel stby: SID=23 device type=DISK

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA/colldb_st/controlfile/current.258.832613087'' comment=

''Set by RMAN'' scope=spfile";

backup as copy current controlfile for standby auxiliary format '+DATA/colldb_st/controlfile/current.259.832613087';

sql clone "alter system set control_files =

''+DATA/colldb_st/controlfile/current.259.832613087'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA/colldb_st/controlfile/current.258.832613087'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 27-NOV-13

channel prmy1: starting datafile copy

copying standby control file

output file name=/u/oracle/server/database203/dbs/snapcf_colldb.f tag=TAG20131127T172446 RECID=5 STAMP=832613087

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

Finished backup at 27-NOV-13

sql statement: alter system set control_files = ''+DATA/colldb_st/controlfile/current.259.832613087'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1657225216 bytes

Fixed Size 1345324 bytes

Variable Size 1040189652 bytes

Database Buffers 603979776 bytes

Redo Buffers 11710464 bytes

allocated channel: stby

channel stby: SID=23 device type=DISK

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

set newname for tempfile 1 to

"+data";

switch clone tempfile all;

set newname for datafile 1 to

"+data";

set newname for datafile 2 to

"+data";

set newname for datafile 3 to

"+data";

set newname for datafile 4 to

"+data";

backup as copy reuse

datafile 1 auxiliary format

"+data" datafile

2 auxiliary format

"+data" datafile

3 auxiliary format

"+data" datafile

4 auxiliary format

"+data" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

Starting backup at 27-NOV-13

channel prmy1: starting datafile copy

input datafile file number=00001 name=+DATA/colldb/datafile/system.256.832173859

channel prmy2: starting datafile copy

input datafile file number=00002 name=+DATA/colldb/datafile/sysaux.257.832173861

channel prmy3: starting datafile copy

input datafile file number=00003 name=+DATA/colldb/datafile/undotbs1.258.832173861

channel prmy4: starting datafile copy

input datafile file number=00004 name=+DATA/colldb/datafile/users.259.832173861

output file name=+DATA/colldb_st/datafile/undotbs1.263.832613107 tag=TAG20131127T172500

channel prmy3: datafile copy complete, elapsed time: 00:00:15

output file name=+DATA/colldb_st/datafile/users.262.832613107 tag=TAG20131127T172500

channel prmy4: datafile copy complete, elapsed time: 00:00:15

output file name=+DATA/colldb_st/datafile/system.257.832613101 tag=TAG20131127T1

channel prmy1: datafile copy complete, elapsed time: 00:00:45

output file name=+DATA/colldb_st/datafile/sysaux.256.832613101 tag=TAG20131127T1

channel prmy2: datafile copy complete, elapsed time: 00:00:45

Finished backup at 27-NOV-13

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=832613147 file name=+DATA/colldb_st/datafile/s

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=832613147 file name=+DATA/colldb_st/datafile/s

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=832613147 file name=+DATA/colldb_st/datafile/u

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=832613147 file name=+DATA/colldb_st/datafile/u

Finished Duplicate Db at 27-NOV-13

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

RMAN>

Database Administrator Blogs

Ask Me

Register standby database using SRVCTL Utility

Oracle Local Listener Vs Remote Listener

Errors Reported while Creating New Standby using RMAN

Data guard Broker Configuration

Data-Guard Set up using ASM On Oracle 11GR2

Popular Posts

Followers

Total Pageviews

About Me