askMLabs

Written By askMLabs on Monday, February 20, 2012 | 2:45 PM

In this article we will see how to rename a datafile in a physical standby setup. Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Some structural and configuration changes at primary database requires manual intervention at a physical standby database.

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

1. Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.

On Primary :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     524
Next log sequence to archive   526
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

On Standby :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     524
Next log sequence to archive   0
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

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

SEQUENCE# APPLIED
---------- ---------
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.

SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace example rename datafile '/home/oracle/app/oracle/oradata/orcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf

SQL>

4. Verify the same tablespace on standby database.

SQL> select ts#,name from v$tablespace where name='EXAMPLE';

TS# NAME
---------- ------------------------------
6 EXAMPLE

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

5. Stop recovery on standby database and shut it down.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

6. Rename the datafile on standby database.

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             343935672 bytes
Database Buffers          104857600 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database rename file '/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf

7. Keep standby database in recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Reference :

Renaming a Datafile in the Primary Database

MOS ID : 733796.1

Hope It Helps

SRI

Written By askMLabs on Thursday, February 16, 2012 | 2:08 PM

In this article, we will discuss the steps to use expdp on physical standby database. There may be some situations where you don’t want your production database loaded with the expdp operation. The other option you have in a standby setup is that you can use your standby database for export operations. But in a physical standby database, the database is in recovery mode and if we want to open the database for expdp operation, we can open the physical standby database in read only mode. Expdp operation needs to create and maintain a master table which needs requires the physical standby database opened in read write mode.

So we have an option in expdp to export standby database. We can use expdp parameter NETWORK_LINK from any non standby database to export database.

Verify primary and Standby Databases

Cancel recovery and Open standby database in Read only Mode

Create a physical directory on primary server to keep export dump files

Create DB Link, Oracle Directory and test it.

Execute expdp on primary server.

1. Verify primary and Standby Databases :

On Primary :

On Standby :

SQL> select name,user,database_role,switchover_status from v$database;

NAME      USER                           DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      SYS                            PHYSICAL STANDBY NOT ALLOWED

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     522
Next log sequence to archive   0
Current log sequence           524
SQL>

2. Cancel recovery and Open standby database in Read only Mode :

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL>

3. Create a physical directory on primary server to keep export dump files :

[oracle@dgaskmpri01 ~]$ mkdir /tmp/askm
[oracle@dgaskmpri01 ~]$ chmod 777 /tmp/askm
[oracle@dgaskmpri01 ~]$

4. Create DB Link, Oracle Directory and test it :

[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 04:25:18 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn system/xxxxx
Connected.
SQL> create database link db_sbyorcl connect to system identified by oracle using 'sbyorcl';

Database link created.

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
orcl

SQL> select db_unique_name from v$database@db_sbyorcl;

DB_UNIQUE_NAME
------------------------------
sbyorcl

SQL> create directory sby_dump_dir as '/tmp/askm';

Directory created.

SQL>

5. Execute expdp on primary server :

[oracle@dgaskmpri01 askm]$ expdp system/oracle full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 04:50:05 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE

.....
.....
....

ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('OE',0,1,'11.02.00.00.00',newblock)
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_CUBE_EXP
ORA-16000: database open for read-only access
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"OE"]
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_METADATA", line 5466

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

----- PL/SQL Call Stack -----
object      line object
handle    number name
0x34f7b2c0     19208 package body SYS.KUPW$WORKER
0x34f7b2c0      8385 package body SYS.KUPW$WORKER
0x34f7b2c0      2935 package body SYS.KUPW$WORKER
0x34f7b2c0      9054 package body SYS.KUPW$WORKER
0x34f7b2c0      1688 package body SYS.KUPW$WORKER
0x34d28650         2 anonymous block

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 04:55:56

[oracle@dgaskmpri01 askm]$ expdp system/oracle attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 05:03:32 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B2533E2386512016E040A8C022010D27
Start Time: Tuesday, 22 November, 2011 5:03:43
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/askm/sby_db.dmp
bytes written: 950,272

Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OE
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 36
Worker Parallelism: 1

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@dgaskmpri01 askm]$ clear
[oracle@dgaskmpri01 askm]$

I got error while exporting the database. This is identified as bug in 10g and 11gR1. As per the oracle documentation, it is rectified in 11gR2, but I am still getting this issue on 11gR2. I need to further investigate it. Comments and Feedbacks are accepted pls Smile

Reference:

How to use Data Pump Export (expdp) to export from Physical Standby Database [ID 1356592.1]

Thanks

SRI

askMLabs

Renaming A Datafile In A Physical Standby Environment

Written By askMLabs on Monday, February 20, 2012 | 2:45 PM

Using expdp To Export From Physical Standby Database

Written By askMLabs on Thursday, February 16, 2012 | 2:08 PM

Popular post

Labels

Blog Archive

Certifications

Total Pageviews

About askMLabs

askMLabs

Renaming A Datafile In A Physical Standby Environment

Written By askMLabs on Monday, February 20, 2012 | 2:45 PM

Using expdp To Export From Physical Standby Database

Written By askMLabs on Thursday, February 16, 2012 | 2:08 PM

Popular post

Labels

Blog Archive

Subscribe To

Certifications

Total Pageviews

About askMLabs

Written By askMLabs on Monday, February 20, 2012 | 2:45 PM

Written By askMLabs on Thursday, February 16, 2012 | 2:08 PM