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
——— —————————— —————- ——————–
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
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
—————- ——————–
PRIMARY TO STANDBY
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME
TYPE VALUE
———————————— ———– ——————————
standby_file_management string AUTO
———————————— ———– ——————————
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
———————————— ———– ——————————
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
——— —————————— —————- ——————–
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
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
—————- ——————–
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
———- ———
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
———————————— ———– ——————————
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>
———————————— ———– ——————————
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
——————————————————————————–
/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’
[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
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
——————————————————————————–
/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
———- ——————————
6 EXAMPLE
SQL> select name from v$datafile where ts#=6;
NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
——————————————————————————–
/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
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
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’
[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.
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.
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
——————————————————————————–
/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 :
MOS ID : 733796.1
ORACLE 12c
NEW FEATURE AVAILABLE
Online Move (12c)
Oracle 12c includes the command,
which performs an online move of a datafile.
SQL> ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';
Database altered.
RMAN
RMAN can be used to move files with less downtime by copying them
in advance of the move, then recovering them as part of the move itself. First,
log in to RMAN and list the current files.RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name DB11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/DB11G/system01.dbf
2 1150 SYSAUX *** /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3 444 UNDOTBS1 *** /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4 120 USERS *** /u01/app/oracle/oradata/DB11G/users01.dbf
5 345 EXAMPLE *** /u01/app/oracle/oradata/DB11G/example01.dbf
8 3277 SOE *** /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 370 TEMP 32767 /u01/app/oracle/oradata/DB11G/temp01.dbf
RMAN>
Copy the file(s) to the new location.
RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/DB11G/soe.dbf';
Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.
RMAN> SQL 'ALTER TABLESPACE soe OFFLINE';
Switch to the new datafile copy(s) and recover the tablespace.
RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE soe;
Turn the tablespace online again.
RMAN> SQL 'ALTER TABLESPACE soe ONLINE';
Remove the old datafile(s).
RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf';
Listing the current files shows the move is complete.
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name DB11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/DB11G/system01.dbf
2 1150 SYSAUX *** /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3 444 UNDOTBS1 *** /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4 120 USERS *** /u01/app/oracle/oradata/DB11G/users01.dbf
5 345 EXAMPLE *** /u01/app/oracle/oradata/DB11G/example01.dbf
8 3277 SOE *** /u01/app/oracle/oradata/DB11G/soe.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 370 TEMP 32767 /u01/app/oracle/oradata/DB11G/temp01.dbf
RMAN>
Moving the SYSTEM tablespace is possible using a similar method, but the database must be shutdown and mounted before the switch and recover can be done.
Recreating the Controlfile
For largescale rearrangements it may be easier to manipulate the
controlfile contents manually by backing up the controlfile to trace.
SQL> CONN sys/password
AS SYSDBA
Connected.
SQL> ALTER DATABASE
BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>
user_dump_dest directory contains commands and instructions
for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all
datafiles and logfiles on one step.This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.
No comments:
Post a Comment