Migrate database from one RMAN catalog to another RMAN catalog
Tuesday, June 14, 2016
Moving from one RMAN catalog to another RMAN catalog
Excerpt from Chennai:
In a nut shell It is nothing but importing a CATALOG from one database to another, or in other words, "moving" or "migrating" CATALOG.
We can see GGDB1 is also registered to this CATALOG.
Wednesday, July 30, 2014
Migrate database from one RMAN catalog to another RMAN catalog
IMPORT CATALOG
is a new feature of RMAN in Oracle 11g. We can move or merge schemas of
different RECOVERY CATALOG of different databases into a centralized
repository.
In a nut shell It is nothing but importing a CATALOG from one database to another, or in other words, "moving" or "migrating" CATALOG.
SOURCE DATABASE - GGDB1
TARGET DATABASE - GGDB2
ORACLE VERSION - 11.2.0.3.0
OS VERSION - Linux 6.0 X86_64
The below scenario describes the steps to move a CATALOG from GGDB1 (Source) to GGDB2 (Target).
In GGDB1 - The catalog is registered.
In GGDB2 - Check the CATALOG for the registered database.
In GGDB2 - Connect to the destination database that will receive the new CATALOG data.
Issue the IMPORT CATALOG command specifying the owner of the source catalog and Check the registered database in the catalog in GGDB2 (Target database).
We can see GGDB1 is also registered to this CATALOG.
In GGDB1 - Check the source catalog (GGDB1) for the registered database.,
On the Source Catalog, the database will be automatically Unregistered after the IMPORT CATALOG.
If you need to retain the catalog on the source side even after the import then a keyword should be added to the IMPORT CATALOG command.
Connect to the RMAN prompt and issue the IMPORT CATALOG command with the keyword NO UNREGISTER.,
Check whether the source database (GGDB1) is registered to the New target catalog database (GGDB2).
Now check on the Source (GGDB1) side whether the imported database exists.
You can clearly see that the database is not Unregistered from the Source side.
Thursday, May 12, 2016
Change unknown SYSMAN password on #EM12c
Change unknown SYSMAN password on #EM12c
When I normally start work on a new EM 12c environment, I would request to have a userid created; however, I don’t have a userid in this environment and I need access EM 12c as SYSMAN. Without knowing the password for SYSMAN, how can I access the EM 12c interface? The short answer is that I can change the SYSMAN password from the OS where EM 12c is running.Note:
Before changing the SYSMAN password for EM 12c, make sure to understand the following:
- SYSMAN is used by the OMS to login to the OMR to store and query all activity
- SYSMAN password has to be changed at both the OMS and OMR to EM 12c to work correctly
- Do not modify the SYSMAN or any other repository user at the OMR level (not recommended)
Tip: Make sure you know what the SYS password is for the OMR. It will be needed to reset SYSMAN.
1. Stop all OMS processes
cd <oms home>/bin
emctl stop oms
Image 1:
2. Change the SYSMAN password
cd <oms home>/bin
emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password> -new_pwd <new sysman password>
In Image 2, notice that I didn’t pass the password for SYS or SYSMAN on the command line. EMCTL will ask you to provide the password if you don’t put it on the command line.
Image 2:
3. Stop the Admin Server on the primary OMS and restart OMS
cd <oms home>/bin
emctl stop oms -all
emctl start oms
Image 3:
4. Verify that all of OMS is up and running
cd <oms home>/bin
emctl status oms -details
Image 4:
After verifying that the OMS is backup, I can now try to login to the OMS interface.
Image 5:
As we can see, I’m able to access OEM as SYSMAN now with the new SYSMAN password.
Enjoy!!
Re-creating Oracle Enterprise Manager's Console Repository
| Mandalika's scratchpad | [ Work blog @Oracle | Stock Market Notes | My Music Compositions ] |
Old Posts:
09.04
10.04
11.04
12.04
01.05
02.05
03.05
04.05
05.05
06.05
07.05
08.05
09.05
10.05
11.05
12.05
01.06
02.06
03.06
04.06
05.06
06.06
07.06
08.06
09.06
10.06
11.06
12.06
01.07
02.07
03.07
04.07
05.07
06.07
08.07
09.07
10.07
11.07
12.07
01.08
02.08
03.08
04.08
05.08
06.08
07.08
08.08
09.08
10.08
11.08
12.08
01.09
02.09
03.09
04.09
05.09
06.09
07.09
08.09
09.09
10.09
11.09
12.09
01.10
02.10
03.10
04.10
05.10
06.10
07.10
08.10
09.10
10.10
11.10
12.10
01.11
02.11
03.11
04.11
05.11
07.11
08.11
09.11
10.11
11.11
12.11
01.12
02.12
03.12
04.12
05.12
06.12
07.12
08.12
09.12
10.12
11.12
12.12
01.13
02.13
03.13
04.13
05.13
06.13
07.13
08.13
09.13
10.13
11.13
12.13
01.14
02.14
03.14
04.14
05.14
06.14
07.14
09.14
10.14
11.14
12.14
01.15
02.15
03.15
04.15
06.15
09.15
12.15
01.16
03.16
04.16
05.16
Thursday, August 28, 2008
Re-creating Oracle Enterprise Manager's Console Repository
An earlier blog post, Oracle 10g: Setting up The Oracle Enterprise Manager Console, has the detailed instructions for setting up the Oracle 10g
OEM console. However if the database along with the RDBMS server was
moved (copied over) to a different host, Oracle Enterprise Manager (OEM)
refuses to start. In such cases, the error message will be similar to
the following.
One simple solution (there might be many) to fix this issue is to re-create the repository for the OEM console. The steps are as follows.
You are done. As simple as that.
_______________
% emctl start dbconsole
OC4J Configuration issue. /export/pspp/oracle/oc4j/j2ee/OC4J_DBConsole_localhost_test not found.
One simple solution (there might be many) to fix this issue is to re-create the repository for the OEM console. The steps are as follows.
- Make sure the database instance and the listener are up.
- Unlock
sysmanandsysuser accounts if not unlocked already.
eg.,% sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 27 23:42:24 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> alter user sysman identified by manager account unlock; User altered. SQL> alter user sys identified by manager account unlock; User altered. SQL> quit
- Re-create the repository.
eg.,% emca -config dbcontrol db -repos recreate STARTED EMCA at Aug 27, 2008 11:43:09 PM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: Database SID: test Listener port number: 1521 Password for SYS user: manager Password for DBSNMP user: dbsnmp Password for SYSMAN user: manager Email address for notifications (optional): Outgoing Mail (SMTP) server for notifications (optional): ----------------------------------------------------------------- You have specified the following settings Database ORACLE_HOME ................ /export/pspp/oracle Database hostname ................ matrix Listener port number ................ 1521 Database SID ................ test Email address for notifications ............... Outgoing Mail (SMTP) server for notifications ............... ----------------------------------------------------------------- Do you wish to continue? [yes(Y)/no(N)]: Y Aug 27, 2008 11:43:28 PM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /export/pspp/oracle/cfgtoollogs/emca/test/emca_2008-08-27_11-43-09-PM.log. Aug 27, 2008 11:43:31 PM oracle.sysman.emcp.EMReposConfig dropRepository INFO: Dropping the EM repository (this may take a while) ... ... ...
You are done. As simple as that.
_______________
| 2004-2016 |  |
Wednesday, April 15, 2015
Thursday, March 26, 2015
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS;
drop Tablespace UNDOTBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace
Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.
Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.
SQL>Create PFILE from SPFILE;
2)Edit pfile and set undo management to manual.
undo_management = manual
3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)
4)Mount the database using new pfile.
Startup mount pfile='fullpath to pfile'
5)Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;
6)Open the database.
sql>Alter Database Open;
7)Drop the undo segments,
sql>Drop Rollback Segment "_SYSSMU1$";
......
8)Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;
9)Add the new undo tablespace.
10) Shutdown Immediate;
11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.
12) Startup the Database.
Friday, March 20, 2015
Reclaiming Unused Space in Datafiles
http://oracle-base.com/articles/misc/reclaiming-unused-space.php
Thanks
Remember, reorganising a tablespace is a big structural change. You should *always* take backup before doing any structural changes to databases you care about.
Now truncate the "T1" table to simulate a maintenance operation.
The fact that the majority of the free space is before some of the "T2" extents means we can not shrink the size of the datafile to release the space.
Before working through any of the methods described below, recreate this situation.
If we switch off autoextend for the relevant datafile, the last four columns will look more representative.
First, create a directory object for the export and import to work with.
We can also see the size of the datafile has been reduced from 26M to 14M.
If the datafile name must remain the same do the following:
The way to move segments depends on the type of segment being moved. Here are a few examples.
The following example performs a manual reorganization where the datafile name is not retained. Remember to recreate the test environment before starting this example.
First, create a new tablespace to hold the objects.
We can also see the size of the datafile has been reduced from 26M to 13M.
Starting at the "tablespaces" screen, select the "RECLAIM_TS" tablespace by clicking the radio group button next to it, select the "Reorganize" action and click the "Go" button.
Accept the default object selection by clicking the "Next" button.
The options screen allows you to decide how the reorganization should take place. The "Method" section has two options:
The resulting screen provides an impact report. If it includes any anticipated problems, you may need to move back and alter your options. When you are happy with the impact report, click the "Next" button.
Enter the desired schedule information and click the "Next" button.
The review page includes the script that will be run by the job. If you are happy with the review information, click the "Submit Job" button.
After the job completes we can see the segments are now at the start of the tablespace map, allowing us to reduce the associated datafile size if we want. The reorganization process doesn't actually do the datafile resize for us, so the tablespace size is unchanged.
Remember, the shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible you will have blocks at the end of the datafile, which means the datafile can't be resized smaller. If this is the case you will need to use one of the methods mentioned previously.
The problem here is we can't tell what files the gaps are in without hovering over the gaps and reading the tool tip. That's not very simple when we have lots of files, segments and gaps to contend with. At this point I stop looking at the extent map and just use a script to identify all the gaps in the tablespace, or individual datafiles.
The ts_extent_map.sql script produces a list of all the free space in the tablespace or individual datafile. The combined output and separate output for each datafile is shown below.
If you are using a database version prior to 11g, reducing the size of the temporary tablespace is similar to reclaiming space from the undo tablespace. Create a new temp tablespace, move the users on to it, then drop the old temp tablespace.
Thanks
Reclaiming Unused Space in Datafiles
There are a number of scenarios that can lead to unused space in datafiles. The two most common I see are:- A lack of housekeeping/maintenance means that one or more tables have grown excessively. After the data is pruned the datafiles contain unused space that needs to be reclaimed.
- One or more segments (tables, partitions or indexes) have been moved to another tablespace leaving empty areas in the datafiles that previously held them.
- Setup Test Environment
- Identify Tablespaces with Free Space
- Export/Import
- Manual Tablespace Reorganization
- Enterprise Manager Tablespace Reorganization
- Shrink?
- Tablespaces with Multiple Datafiles
- Undo Tablespace
- Temp Tablespace
Remember, reorganising a tablespace is a big structural change. You should *always* take backup before doing any structural changes to databases you care about.
Setup Test Environment
Before we can look at the solutions we need to create a test environment so we can clearly see the problem. Each of the solutions presented require that this setup is done first.
CONN / AS SYSDBA
-- Create a tablespace and user for the test.
DROP USER reclaim_user CASCADE;
DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE reclaim_ts DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER reclaim_user IDENTIFIED BY reclaim_user DEFAULT TABLESPACE reclaim_ts QUOTA UNLIMITED ON reclaim_ts;
GRANT CREATE SESSION, CREATE TABLE TO reclaim_user;
-- Create and populate two tables in the test schema.
CONN reclaim_user/reclaim_user
CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE t2 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t2_pk PRIMARY KEY (id)
);
INSERT /*+append*/ INTO t1If we switch off autoextend for the relevant datafile, the last four columns will look more representati
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;
INSERT /*+append*/ INTO t2
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 't1');
EXEC DBMS_STATS.gather_table_stats(USER, 't2');
We can see both table segments are made up of multiple extents, each extent being made up of multiple blocks.Enterprise Manager gives us a nice image of the contents of the tablespace by doing the following:CONN / AS SYSDBA COLUMN segment_name FORMAT A30 SELECT segment_type, segment_name, COUNT(*) FROM dba_extents WHERE owner = 'RECLAIM_USER' GROUP BY segment_type, segment_name ORDER BY segment_type, segment_name; SEGMENT_TYPE SEGMENT_NAME COUNT(*) ------------------ ------------------------------ ---------- INDEX T1_PK 3 INDEX T2_PK 3 TABLE T1 27 TABLE T2 27 SQL> SELECT table_name, num_rows, blocks FROM dba_tables WHERE owner = 'RECLAIM_USER'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- T1 10000 1461 T2 10000 1461 SQL>
- Click on the "Server" tab.
- Click the "Tablespaces" link.
- Select the "RECLAIM_TS" tablespace by clicking the radio button.
- Select the "Show Tablespace Contents" action and click the "Go" button.
- On the resulting page, expand the "Extent Map" section.
Now truncate the "T1" table to simulate a maintenance operation.
What we can see now is the "T1" table has a single extent (yellow) and there is lots of free space (green) in the centre of the tablespace.CONN reclaim_user/reclaim_user TRUNCATE TABLE t1;
The fact that the majority of the free space is before some of the "T2" extents means we can not shrink the size of the datafile to release the space.
So this represents the starting point of our problem. We have free space we need to release from the datafile associated with our tablespace.CONN / AS SYSDBA COLUMN name FORMAT A50 SELECT name, bytes/1024/1024 AS size_mb FROM v$datafile WHERE name LIKE '%reclaim%'; NAME SIZE_MB -------------------------------------------------- ---------- /u01/app/oracle/oradata/DB11G/reclaim01.dbf 26 SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 24M; ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 24M * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value SQL>
Before working through any of the methods described below, recreate this situation.
Identify Tablespaces with Free Space
You can easily identify tablespaces with lots of free space using the ts_free_space.sql script.For tablespaces with autoextend enabled, the script calculates the maximum sizes and percentages based on maximum size the datafiles can grow to, but the "FREE_MB" column is based on the current file size, so use that figure for tablespaces with datafiles set to autoextend.SQL> @ts_free_space.sql TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT ------------------------------ ---------- ---------- ----------- ----------- ---------- ----------- EXAMPLE 345 35 345 35 10 XXXXXXXXX- RECLAIM_TS 25 12 32767 32754 99 ---------- SYSAUX 580 33 580 33 5 XXXXXXXXX- SYSTEM 720 7 720 7 0 XXXXXXXXXX UNDOTBS1 70 41 70 41 58 XXXX------ USERS 20 15 20 15 75 XXX------- 6 rows selected. SQL>
If we switch off autoextend for the relevant datafile, the last four columns will look more representative.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' AUTOEXTEND OFF; Database altered. SQL> @ts_free_space.sql TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT ------------------------------ ---------- ---------- ----------- ----------- ---------- ----------- EXAMPLE 345 35 345 35 10 XXXXXXXXX- RECLAIM_TS 25 12 25 12 48 XXXXX----- SYSAUX 580 33 580 33 5 XXXXXXXXX- SYSTEM 720 7 720 7 0 XXXXXXXXXX UNDOTBS1 70 40 70 40 57 XXXX------ USERS 20 15 20 15 75 XXX------- 6 rows selected. SQL>
Export/Import
The export/import process looks like this:- Export the schema objects that are stored in the specific tablespace/datafile you want to resize.
- Drop the exported objects.
- Resize the datafiles.
- Import the objects.
- Perform any required maintenance, like grants etc.
First, create a directory object for the export and import to work with.
Export the objects in question. In this case we are doing the whole schema, but you may be able to get away with a subset of the objects if your schema is spread across several tablespaces.CONN / AS SYSDBA CREATE DIRECTORY temp_dir AS '/tmp'; GRANT READ, WRITE ON DIRECTORY temp_dir TO reclaim_user;
Drop the original objects and reduce the size of the datafile.expdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=expdpRECLAIM_USER.log
Import the objects back into the schema.CONN / AS SYSDBA DROP TABLE reclaim_user.t1; DROP TABLE reclaim_user.t2; PURGE DBA_RECYCLEBIN; ALTER TABLESPACE reclaim_ts COALESCE; ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' RESIZE 5M;
The tablespace map shows we have removed the large section of free space in the middle of the datafile associated with our tablespace.impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log
We can also see the size of the datafile has been reduced from 26M to 14M.
In this example I truncated the first table, so my table segments did not contain much free space internally. If you have segments with lots of internal free space to clean up in addition to the unused space in the datafile, you may want to include the TRANFORM parameter in your import command. Using "TRANSFORM=SEGMENT_ATTRIBUTES:N" setting tells the import command to forget the physical attributes (including the storage clause) of the table when recreating it.CONN / AS SYSDBA COLUMN name FORMAT A50 SELECT name, bytes/1024/1024 AS size_mb FROM v$datafile WHERE name LIKE '%reclaim%'; NAME SIZE_MB -------------------------------------------------- ---------- /u01/app/oracle/oradata/DB11G/reclaim01.dbf 14 SQL>
impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log \
transform=segment_attributes:n
Manual Tablespace Reorganization
This method can take one of two forms. It you are happy to change the datafile name do the following:- Create a new tablespace.
- Move the segments to the new tablespace.
- Drop the original tablespace.
- Rename the new tablespace to match the original name. *
If the datafile name must remain the same do the following:
- Create a new tablespace.
- Move the segments to the new tablespace.
- Resize the original datafile.
- Move the segments back to the original tablespace.
- Drop the new tablespace.
The way to move segments depends on the type of segment being moved. Here are a few examples.
Of course, the tables and their respective indexes could be moved using the Online Table Redefinition functionality.-- Move a table segment. ALTER TABLE tab1 MOVE TABLESPACE new_ts; -- Move an index segment. ALTER INDEX ind1 REBUILD TABLESPACE new_ts; ALTER INDEX ind1 REBUILD TABLESPACE new_ts ONLINE; -- Move a table partition segment. (Remember to check for unusable indexes) ALTER TABLE tab1 MOVE PARTITION part_1 TABLESPACE new_ts NOLOGGING; -- Move an index partition segment. ALTER INDEX ind1 REBUILD PARTITION ind1_part1 TABLESPACE new_ts; -- Move LOB segments if we had them. -- ALTER TABLE tab1 MOVE LOB(lob_column_name) STORE AS (TABLESPACE new_ts);
The following example performs a manual reorganization where the datafile name is not retained. Remember to recreate the test environment before starting this example.
First, create a new tablespace to hold the objects.
Move the objects to the new tablespace.CONN / AS SYSDBA CREATE TABLESPACE reclaim_ts_temp DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M; ALTER USER reclaim_user QUOTA UNLIMITED ON reclaim_ts_temp;
Drop the original tablespace and rename the new one back to the original name.ALTER TABLE reclaim_user.t1 MOVE TABLESPACE reclaim_ts_temp; ALTER INDEX reclaim_user.t1_pk REBUILD TABLESPACE reclaim_ts_temp; ALTER TABLE reclaim_user.t2 MOVE TABLESPACE reclaim_ts_temp; ALTER INDEX reclaim_user.t2_pk REBUILD TABLESPACE reclaim_ts_temp;
Once again, the tablespace map shows we have removed the large section of free space in the middle of the datafile associated with our tablespace.DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES; ALTER TABLESPACE reclaim_ts_temp RENAME TO reclaim_ts;
We can also see the size of the datafile has been reduced from 26M to 13M.
CONN / AS SYSDBA COLUMN name FORMAT A50 SELECT name, bytes/1024/1024 AS size_mb FROM v$datafile WHERE name LIKE '%reclaim%'; NAME SIZE_MB -------------------------------------------------- ---------- /u01/app/oracle/oradata/DB11G/reclaim01.dbf 13 SQL>
Enterprise Manager Tablespace Reorganization
The manual tablespace reorganization method works well, but when you start dealing with lots of segments it can become a bit painful to script, especially if you start using the online table redefinition functionality. This is where Enterprise Manager comes to the rescue because it can perform all the hard work for you, allowing you to easily define a job to perform a tablespace reorganization.Starting at the "tablespaces" screen, select the "RECLAIM_TS" tablespace by clicking the radio group button next to it, select the "Reorganize" action and click the "Go" button.
Accept the default object selection by clicking the "Next" button.
The options screen allows you to decide how the reorganization should take place. The "Method" section has two options:
- Speed: This is an offline operation using MOVE for tables and REBUILD for indexes. Essentially this is the same as the Manual Tablespace Reorganization shown previously.
- Availability: This is an online operation, making use of the online table redefinition functionality. The online table redefinition can be done based on the primary key of the table or the ROWID of the rows.
- Use tablespace rename feature: As the name implies, this uses the first method described in the manual reorganization section.
- Use scratch tablespace: This uses the second method described in the manual reorganization section. You must provide a existing scratch tablespace name to hold all the objects during the reorganaization.
The resulting screen provides an impact report. If it includes any anticipated problems, you may need to move back and alter your options. When you are happy with the impact report, click the "Next" button.
Enter the desired schedule information and click the "Next" button.
The review page includes the script that will be run by the job. If you are happy with the review information, click the "Submit Job" button.
After the job completes we can see the segments are now at the start of the tablespace map, allowing us to reduce the associated datafile size if we want. The reorganization process doesn't actually do the datafile resize for us, so the tablespace size is unchanged.
Shrink?
If there is only a single object in the datafile, it's possible a shrink operation will actually be enough to compact the data and free up the empty blocks, allowing the datafile to be resized to a smaller size.Remember, the shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible you will have blocks at the end of the datafile, which means the datafile can't be resized smaller. If this is the case you will need to use one of the methods mentioned previously.
Tablespaces with Multiple Datafiles
So far I've conveniently side-stepped the issue of tablespaces with multiple datafiles. Why? Because it makes the tablespace extent map a little more confusing to look at. To see what I mean, perform the setup again, but this time before building the tables add an additional datafile to the tablespace.With the tables built, populated and the "T1" table truncated, the extent map will look something like this.ALTER TABLESPACE reclaim_ts ADD DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
The problem here is we can't tell what files the gaps are in without hovering over the gaps and reading the tool tip. That's not very simple when we have lots of files, segments and gaps to contend with. At this point I stop looking at the extent map and just use a script to identify all the gaps in the tablespace, or individual datafiles.
The ts_extent_map.sql script produces a list of all the free space in the tablespace or individual datafile. The combined output and separate output for each datafile is shown below.
This can help you make a judgment as to whether a tablespace reorganization is necessary.SQL> @ts_extent_map reclaim_ts all Tablespace Block Size (bytes): 8192 *** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81 *** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5 *** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5 Total Gap Blocks: 1576 Total Gap Space (MB): 12.31 SQL> @ts_extent_map reclaim_ts 8 Tablespace Block Size (bytes): 8192 *** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81 *** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5 Total Gap Blocks: 936 Total Gap Space (MB): 7.31 SQL> @ts_extent_map reclaim_ts 9 Tablespace Block Size (bytes): 8192 *** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5 Total Gap Blocks: 640 Total Gap Space (MB): 5 SQL>
Undo Tablespace
The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I've used autoextend, but you may wish to remove this if you want manual control over the datafile size.Remember, flashback operations requiring undo will not be possible because you have deleted the retained undo. You may want to consider yourCREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M; ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2; DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
UNDO_RETENTION parameter setting.Temp Tablespace
If you are using oracle 11g, you can shrink a temporary tablespace using theALTER TABLESPACE command, as shown here.If you are using a database version prior to 11g, reducing the size of the temporary tablespace is similar to reclaiming space from the undo tablespace. Create a new temp tablespace, move the users on to it, then drop the old temp tablespace.
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/DB11G/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
-- Switch all existing users to new temp tablespace.
BEGIN
FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE temp2';
END LOOP;
END;
/
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tuesday, March 17, 2015
Changing Listener and SCAN Listener Port in 11gR2 RAC
Changing Listener and SCAN Listener Port in 11gR2 RAC
Steps below will change the port from the default 1521 to 9120. The configuration is a two node 11gR2 Standard Edition RAC with role separation and the solution for Oracle Security Alert for CVE-2012-1675 applied.
1. Current listener and scan listener configurations (run as grid user)
srvctl status listener Listener LISTENER is enabled Listener LISTENER is running on node(s): db-02,db-01 srvctl config listener Name: LISTENER Network: 1, Owner: grid Home: End points: TCP:1521 $ srvctl status scan_listener SCAN Listener LISTENER_SCAN1 is enabled SCAN listener LISTENER_SCAN1 is running on node db-02 SCAN Listener LISTENER_SCAN2 is enabled SCAN listener LISTENER_SCAN2 is running on node db-01 SCAN Listener LISTENER_SCAN3 is enabled SCAN listener LISTENER_SCAN3 is running on node db-01 $ srvctl config scan_listener SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521/TCPS:2992 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521/TCPS:2992 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521/TCPS:2992The TCPS on 2992 is due to the COST setup and not part of the port change mentioned here.
2. As mentioned earlier (on the RAC installation guide) the local_listener is auto set and current configuration of local_listener is using port 1521
SQL> show parameter local NAME TYPE VALUE --------------- ------- ------------------------------------------------------------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.171)(PORT=1521))))The remote listener is restricted to TCPS on the scan vips (again this is due to COST setup.
SQL> show parameter remote
NAME TYPE VALUE
--------------- ------- -----------------------------------------------------------------------
remote_listener string (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.100.181)(PORT=2992))
(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.100.182)(PORT=2992))
(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.100.183)(PORT=2992)))
3.
The listener.ora in GI_HOME does not contain any port information.
There's endpoints_listener.ora with port information but this is for
backward compatibility for DB versions lower than 11.2 and not
applicable in this case as DB is 11.2.4. Make a note of listener status information which shows where the default port is being used. Run this is as grid user and set ORACLE_HOME variable (ie. . oraenv to +ASM*) if not the command will fail.
lsnrctl status listener # on db1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 12:55:04 Uptime 0 days 3 hr. 57 min. 32 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/db-01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.171)(PORT=1521))) <---- vip (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.170)(PORT=1521))) <---- ip Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... lsnrctl status listener # on db2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 12:54:52 Uptime 0 days 4 hr. 1 min. 31 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/db-02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.172)(PORT=1521))) <-- vip (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.173)(PORT=1521))) <-- ip Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service...Scan listener status. It's important to check the status of the scan_listener on the node it's currently active. Use the srvctl status to find out on which node the particular scan listener is currently active.
lsnrctl status listener_scan1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN1 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 12:55:06 Uptime 0 days 3 hr. 58 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-02/listener_scan1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.181)(PORT=2992)))<-- scan ip with TCPS due to COST (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.181)(PORT=1521))) <-- scan ip with TCP Services Summary... lsnrctl status listener_scan2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN2 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 12:55:04 Uptime 0 days 3 hr. 57 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-01/listener_scan2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.182)(PORT=2992))) <-- scan ip with TCPS due to COST (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.182)(PORT=1521))) <-- scan ip with TCP Services Summary... lsnrctl status listener_scan3 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN3 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 12:55:04 Uptime 0 days 3 hr. 57 min. 59 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-01/listener_scan3/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.183)(PORT=2992))) <-- scan ip with TCPS due to COST (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.183)(PORT=1521))) <-- scan ip with TCP Services Summary...
5. To change the port run srvctl as grid user.
srvctl modify listener -l LISTENER -p 9120 srvctl config listener Name: LISTENER Network: 1, Owner: grid Home: End points: TCP:9120 srvctl modify scan_listener -p TCP:9120/TCPS:2992 srvctl config scan_listener SCAN Listener LISTENER_SCAN1 exists. Port: TCP:9120/TCPS:2992 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:9120/TCPS:2992 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:9120/TCPS:2992Changes are not effective until the listeners are restarted.
srvctl stop listener srvctl start listener srvctl stop scan_listener srvctl start scan_listener6. Verify the listeners have picked up the new port.
lsnrctl status listener_scan1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN1 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 17:57:47 Uptime 0 days 0 hr. 1 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-02/listener_scan1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.181)(PORT=2992))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.181)(PORT=9120))) Services Summary... lsnrctl status listener_scan2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN2 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 17:57:47 Uptime 0 days 0 hr. 0 min. 45 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-01/listener_scan2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.182)(PORT=2992))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.182)(PORT=9120))) Services Summary... lsnrctl status listener_scan3 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN3 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 17:57:49 Uptime 0 days 0 hr. 0 min. 45 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/db-01/listener_scan3/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.100.183)(PORT=2992))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.183)(PORT=9120))) Services Summary... lsnrctl status # on db1 and db2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-JAN-2013 17:57:31 Uptime 0 days 0 hr. 1 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/db-02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.171/2)(PORT=9120))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.170/3)(PORT=9120)))6. The DB's local_listener port is still registered on earler port (only one instance shown below).
SQL> show parameter local NAME TYPE VALUE --------------- ------- ------------------------------------------------------------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.171)(PORT=1521))))Restart the database so local_listener is registered with the new port. Run as oracle user
srvctl stop database -d std11g2 srvctl start database -d std11g2 SQL> show parameter local NAME TYPE VALUE --------------- ------- ------------------------------------------------------------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.171)(PORT=9120))))7. If COST is not used (refer 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained 887522.1 note and section titled "Is it recommended to use COST feature?") then remote_listener value which is by default set as scan-name:port must also be updated with the new port. Since here COST is used (1340831.1) this step is omitted.
8. Port information on the endpoints_listener.ora would have been automatically edited by the agents and will reflect the new values set.
9. Change the port on any tnsnames.ora files used for connectivity to reflect the new port.
10. EM repository may need recreation with the new port information or manually edit config/emoms.properties and emd/targets.xml with port information.
Useful metalink notes
Changing Default Listener Port Number [ID 359277.1]
Listener port changed after 11.2 upgrade [ID 1269679.1]
Changing Listener Ports On RAC/EXADATA [ID 1473035.1]
11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1]
How To Configure Scan Listeners With A TCPS Port? [ID 1092753.1]
How to Modify SCAN Setting or SCAN Listener Port after Installation [ID 972500.1]
How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure [ID 1063571.1]
Using the TNS_ADMIN variable and changing the default port number of all Listeners in an 11.2 RAC for an 11.2, 11.1, and 10.2 Database [ID 1306927.1]
Wednesday, February 4, 2015
Super fast Database Copying/Cloning
Oracle Tips by Burleson Consulting
A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.
STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode
Wednesday, July 30, 2014
How to Modify SCAN Setting or SCAN Listener Port after Installation (Doc ID 972500.1)
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterInformation in this document applies to any platform.
Goal
This note provides steps to update 11gR2 Grid Infrastructure (CRS) Single Client Access Name (SCAN) setting or SCAN listener port setting if it's not setup properly or if it's changed after setup.
Note:
1. This procedure does not apply when GNS is being used
2. Even though clustername and SCAN name is set to the same during 'typical' installation, changing SCAN name will not affect clustername as change of clustername requires deconfigure and reconfigure of the entire cluster.
Solution
A. To update SCAN setting
1. As per documentation "Oracle Grid Infrastructure Installation Guide", Oracle strongly recommend to configure SCAN name in either DNS or GNS as /etc/hosts file can only resolve to one IP address.
SCAN IP must be in same subnet as public and VIP. If the new SCAN IP will be in different subnet, refer to note 276434.1 to change nodeapps/network resource first.
1a. If you intend to use /etc/hosts for SCAN name resolution, the same and only entry for SCAN name must exist on all nodes.
1b. If you intend to use DNS for SCAN name resolution, remove entries for SCAN name from /etc/hosts on all nodes, and make sure nslookup returns good result on all nodes, for example:
$ nslookup pay-scan.us.oracle.com
..
Name: pay-scan.us.oracle.com
Address: 10.4.0.201
Name: pay-scan.us.oracle.com
Address: 10.4.0.202
Name: pay-scan.us.oracle.com
Address: 10.4.0.203
..
Name: pay-scan.us.oracle.com
Address: 10.4.0.201
Name: pay-scan.us.oracle.com
Address: 10.4.0.202
Name: pay-scan.us.oracle.com
Address: 10.4.0.203
2. Once #1a or #1b is configured properly, execute the following to modify:
If name resolution for SCAN is being switched from local hosts file to DNS, be sure to remove SCAN name in local hosts file on all nodes prior to execute below commands.
2.1. To modify SCAN name or SCAN VIP addresses:
2.1.1. As grid user stop resources:
$ $GRID_HOME/bin/srvctl stop scan_listener
$ $GRID_HOME/bin/srvctl stop scan
$ $GRID_HOME/bin/srvctl stop scan
2.1.2. As root user modify SCAN:
# $GRID_HOME/bin/srvctl modify scan -n pay-scan.us.oracle.com
### For 11.2.0.1 only, if you intend to change SCAN name, due to bug 9603829, execute the following:
# $GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=<new SCAN name>"
### Example:
# $GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=pay-scan.us.oracle.com"
### For 11.2.0.1 only, if you intend to change SCAN name, due to bug 9603829, execute the following:
# $GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=<new SCAN name>"
### Example:
# $GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=pay-scan.us.oracle.com"
Once SCAN name is changed, update database init.ora/spfile parameter remote_listener to the new one.
2.1.3. As grid user modify and start resources:
$ $GRID_HOME/bin/srvctl modify scan_listener -u
$ $GRID_HOME/bin/srvctl start scan_listener
$ $GRID_HOME/bin/srvctl start scan_listener
2.1.4. To confirm the change
$ $GRID_HOME/bin/srvctl config scan
SCAN name: pay-scan.us.oracle.com, Network: 1/10.4.0.0/255.255.255.0/eth1
SCAN VIP name: scan1, IP: /10.4.0.201/120.0.0.201
SCAN VIP name: scan2, IP: /10.4.0.202/120.0.0.202
SCAN VIP name: scan3, IP: /10.4.0.203/120.0.0.203
$ $GRID_HOME/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
SCAN name: pay-scan.us.oracle.com, Network: 1/10.4.0.0/255.255.255.0/eth1
SCAN VIP name: scan1, IP: /10.4.0.201/120.0.0.201
SCAN VIP name: scan2, IP: /10.4.0.202/120.0.0.202
SCAN VIP name: scan3, IP: /10.4.0.203/120.0.0.203
$ $GRID_HOME/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
2.2. To change SCAN to be on second network:
By default, SCAN will be configured on first public network (ora.net1.network), however in multiple public network environment, SCAN can run on second or other network.
There's no option to modify SCAN to use non-first network, to change to second or other network, scan_listener and scan resources need to be removed and added back.
In this example, we'll move SCAN from net1 to net2
$ $GRID_HOME/bin/srvctl config network
Network exists: 1/10.1.0.0/255.255.255.128/eth3, type static
Network exists: 2/10.1.1.0/255.255.255.128/eth4, type static
Network exists: 1/10.1.0.0/255.255.255.128/eth3, type static
Network exists: 2/10.1.1.0/255.255.255.128/eth4, type static
2.2.1. As grid user stop resources and remove scan_listener:
$ $GRID_HOME/bin/srvctl stop scan_listener$ $GRID_HOME/bin/srvctl stop scan
$ $GRID_HOME/bin/srvctl remove scan_listener -f
$ $GRID_HOME/bin/srvctl remove scan_listener -f
2.2.2. As root user remove and add SCAN:
# $GRID_HOME/bin/srvctl remove scan -f
#$GRID_HOME/bin/srvctl add scan -n <scan-name> -k 2
#$GRID_HOME/bin/srvctl add scan -n <scan-name> -k 2
Once SCAN name is changed, update database init.ora/spfile parameter remote_listener to the new one.
2.2.3. As grid user add scan_listener and start resources:
$ $GRID_HOME/bin/srvctl add scan_listener-p <port>
$ $GRID_HOME/bin/srvctl start scan_listener
$ $GRID_HOME/bin/srvctl start scan_listener
B. To modify SCAN listener port
As grid user:
1. Modify SCAN listener port:
$GRID_HOME/bin/srvctl modify scan_listener -p <new-SCAN-port>
Update 11gR2 database init.ora parameter: alter system set remote_listener='<SCAN-name>:<new-port-number>' scope=both;
2. Restart SCAN listener so the new port will be effective:
$GRID_HOME/bin/srvctl stop scan_listener
$GRID_HOME/bin/srvctl start scan_listener
$GRID_HOME/bin/srvctl start scan_listener
3. Confirm the change:
$GRID_HOME/bin/srvctl config scan_listener
--
With metta
Quang Phan
Quang Phan
Subscribe to:
Posts (Atom)