Renaming tablespaces in Oracle

Renaming tablespaces in Oracle

Prior to Oracle 10g, there was no easy way to rename the tablespaces. One had to create a new tablespace, move all the objects from the old tablespace to the new tablespace and then drop the old tablespace along with its data files. This becomes a very tedious process and requires double the storage till the drop is done.

In 10g, Oracle introduced a new feature of renaming tablespace just like renaming a table. First, we will check for the existing tablespace name in the database by issuing the following query (make sure you are connected to the database with correct privileges):

SQL>SELECT name FROM v$tablespace WHERE tablespace_name = ‘TEST_TBS’;

NAME
—————
TEST_TBS

If you want to rename/delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active: (Check what all are the users using that tablespace and then unassign all the quotas)

SQL> alter user <username> quota 0 on <tablespace_name>;

Now we will rename it by issuing the following command.

SQL> ALTER TABLESPACE TEST_TBS RENAME TO TEST01_DATA;

Tablespace altered.

If you execute the first query again, it won’t return any record as tablespace is renamed to new name.

Few things to keep in mind when renaming the tablespaces:

• Rename tablespace is not supported for SYSTEM and SYSAUX tablespaces. Attempt to rename these tablespaces will result into an error.
• Database compatibility parameter (COMPATIBLE) in init.ora should be set to at least 10.0.0.0
• To rename a tablespace, the tablespace and its data files should be online.
• Temporary tablespace can be renamed as well.

When tablespace is being renamed, database updates the references in control file, data dictionary and online data file headers as they all reference the tablespace. One can verify that by querying the user_tables table to see the change in tablespace name for the objects which belong to older tablespace.

　

How to kill oracle data pump job

Oracle: How to kill data pump jobs (Below example is for import (impdp) the same is applicable for export (expdp) )

When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too

Following sql will give you the list of data pump jobs

select * from dba_datapump_jobs;

If you want to kill your impdp or expdp

1) Make sure that your impdp/expdp command prompt window is active

2) Press Control-C , It will pause the job. Don’t press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background

3) Type Kill_Job

ex: Import>kill_job

Are you sure you wish to stop this job (y/n): y

　

If by mistake, you closed the window and your import/export job is still running,

1) Get the name of the job using

select * from dba_datapump_jobs

2) Open a new command prompt window. If you want to kill your import job type

impdp username/password@database attach=name_of_the_job   (Get the name_of_the_job using the above query)

3) Once you are attached to job, Type Kill_Job

ex: Import>kill_job

Are you sure you wish to stop this job (y/n): y

And your job is killed, it will no longer show in dba_datapump_jobs

Activating the Standby Database – Switchover Method

Activating the Standby Database – Switchover Method

The main advantage of a graceful switchover is that it avoids the resetlogs operation. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Setjob_queue_processes value to 0 in both (PRIMARY and STANDBY) (PROD & DR)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE

———————————— ———– ————-

job_queue_processes integer 10

SQL> ALTERSYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In PRIMARY database check the database role. (PROD server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

——— —————- ——- ——————– ———– ———- ——————–

DBNAMEPRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In STANDBY database check the database role. (DR server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

——— —————- ——- ——————– ———– ———- ——————–

DBNAME PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the PRIMARY database. (PROD server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the PRIMARY database in RESTRICTED mode. (PROD server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the PRIMARY database. (PROD server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (PROD & DR)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.

9. Initiate the switchover on the PRIMARY database. (PROD)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

10. Once the step above has completed, log on to the STANDBY database and issues the following command. (DR)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

11. Immediately return to the FORMER PRIMARY database and issue a shutdown and mount the NEW STANDBY database. (PROD server)

SQL> shutdown immediate
SQL> startup mount;

12. On the NEW PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (DR server)

SQL> alter database open;

Database opened.

13. Verify the new STANDBY’S status. (PROD server)

SQL> select name, database_role from v$database;

NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY

14. Setjob_queue_processes value to 10 in both (PRIMARY and STANDBY) (PROD & DR)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE

———————————— ———– ————-

job_queue_processes integer 0

SQL> ALTERSYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;

15. Put the NEW STANDBY/FORMER PRIMARY database into managed recovery mode. (PROD)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

16. Test the communications for archive by performing a log switch. (DR server)

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Now check whether these logs are applying in the NEW STANDBY and also check the listener status.

17. On the NEW PRIMARY database instance, create the temporary tablespace. (DR server)

SQL> Create temporary tablespace temp tempfile ‘/u01/dbname/oradata/temp01.dbf’ size 5120m;

Tablespace created.

18. On the NEW PRIMARY database instance, take a backup if possible. (DR server)

 

Switch back to Orginal Primary Database – Switchover Method

1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Setjob_queue_processes value to 0 in both (PRIMARY and STANDBY) (PROD & DR)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE

———————————— ———– ————-

job_queue_processes integer 10

SQL> ALTERSYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In New PRIMARY database check the database role. (DR server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

——— —————- ——- ——————– ———– ———- ——————–

DBNAMEPRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In New STANDBY database check the database role. (PROD server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

——— —————- ——- ——————– ———– ———- ——————–

DBNAME PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the New PRIMARY database. (DR server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the New PRIMARY database in RESTRICTED mode. (DR server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the New PRIMARY database. (DR server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (PROD & DR)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.

9. Initiate the switchover on the New PRIMARY database. (DR)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

10. Once the step above has completed, log on to the New STANDBY database/Orginal Primary and issues the following command. (PROD)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

11. Immediately return to the FORMER PRIMARY database/Orginal STANDBY and issue a shutdown and mount the Orginal STANDBY database. (DR server)

SQL> shutdown immediate
SQL> startup mount;

12. On the Orginal PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (PROD server)

SQL> alter database open;

Database opened.

13. Verify the orginal STANDBY’S status. (DR server)

SQL> select name, database_role from v$database;

NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY

14. Setjob_queue_processes value to 10 in both (PRIMARY and STANDBY) (PROD & DR)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE

———————————— ———– ————-

job_queue_processes integer 0

SQL> ALTERSYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;

15. Put the Orginal STANDBY/FORMER PRIMARY database into managed recovery mode. (DR)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

16. Test the communications for archive by performing a log switch. (PROD server)

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Now check whether these logs are applying in the Orginal STANDBY and also check the listener status.

17. On the Orginal PRIMARY database instance, take a backup if possible. (PROD server)

 

Script to find out the RMAN DB backup status

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

To get the current status of the Running RMAN JOB

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) “% COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

Query to display the session details with the OS pid

— Show user info including os pid

set linesize 300

col “SID/SERIAL” format a10

col username format a15

col osuser format a15

col program format a40

col status format a15

select s.sid || ‘,’|| s.serial#, s.username, s.osuser, p.spid, s.program, s.status from v$session s,v$process p Where s.paddr = p.addr order by to_number(p.spid);