Answer: Moving data files from one ASM diskgroup to another diskgroup involves these steps:
- Step 1: Get the data file name: select. file_name. from.
- Step 2: Identify the target diskgroup to migrate to: select. name. from.
- Step 3: Take the old data file offline: alter database datafile. '+MYDB_OLDDATA/mysid/app_data.nnn'
Here are the steps to change the location of control file for a single-instance database by a parameter file:
- Stop Database Service.
- Copy Control File to New Location.
- Create PFILE from Current SPFILE.
- Modify Control File Location in PFILE.
- Restore SPFILE from Modified PFILE.
- Startup NOMOUNT to Check New Setting.
While the database is shutdown (in mount stage)
- Shutdown and mount the database.
- Ensure you have enough space in the ASM diskgroup to copy the datafile.
- Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.
- Update the controlfile with the new location of the datafile.
DBA_DATA_FILES describes database files. The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.
Renaming or moving tempfile in oracle(ASM/Non-ASM)1) sqplus to the database and make sure the Tempfiles that you want to rename or change the location. SQL> select name from v$tempfile; 2) create a pfile before making the changes. 3) shutdown the database.
Rename and/or move datafile(s) with the database shutdown
- If the database is up, shut it down.
- Copy the datafile to the new name/location at the operating system level.
- Mount the database.
- Rename the file inside Oracle.
- Open the database.
- Query v$dbfile to confirm that the changes made were correct.
Most Oracle databases store files in a file system, which is a data structure built inside a contiguous disk address space. All operating systems have file managers that allocate and deallocate disk space into files within a file system. A file system enables disk space to be allocated to many files.
- Shut down the database. SHUTDOWN.
- Copy the redo log files to the new location. Operating system files, such as redo log members, must be copied using the appropriate operating system commands.
- Startup the database, mount, but do not open it.
- Rename the redo log members.
- Open the database for normal operation.
Answer: To see the controlfile contents, use these steps: SQL> alter database backup controlfile to trace; We can now view the controlfile contents (as a "create database" statement) by looking for the latest file in the user_dump_dest directory.
How to change the oracle database name using nid utility
- Mount the database. SQL> STARTUP MOUNT ORACLE instance started.
- Run the NID utility. SYNTAX – nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME.
- change the db_name parameter in the parameter file.
- Rename the spfile to new db name.
Switches all data files within the specified tablespace, as with SWITCH DATAFILE TO COPY (see Example 3-55). Switches the specified active database files to image copies. This subclause updates the names for data files and temp files for which you have issued a SET NEWNAME command.
Temporarily Changing the Destination Using SQL*PlusIf you are automatically archiving, you can use the following command to override the destination specified by the LOG_ARCHIVE_DEST. This command does not change the value in the initialization parameter file. This change is only valid until you restart the instance.
Are you talking about the automatic extension of data files? In that case the command you need will be similar to this: alter database datafile 'filename' autoextend on next 512K maxsize 11G; See the section Enabling and Disabling Automatic Extension for a Data File of the Oracle 12.1 Database Administrator's Guide.
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
To move the datafile associated with the USERS tablespace, first take the tablespace offline and move the file at the OS level. Next, use the alter tablespace command to rename the file in the database to reflect the new location. Finally, the tablespace is brought back online.
There are 2 methods we can use to do this. One is to use a combination of "alter table X move tablespace Y" and "alter index X rebuild tablespace Y" -- this works in Oracle8i release 8.1 and up ONLY. Attached is a script called moveall.
Here are the high-level steps that will be performed.
- Create an ASM Disk Group using the ASM Filter Driver.
- Create a tablespace in this diskgroup and populate it with some random data.
- Check the space allocated at the database and array level.
- Drop the tablespace and run ASM rebalance.
- Check the space again.
The ALTER TABLE… MOVE PARTITION command moves a partition or subpartition from its current tablespace to a different tablespace. The ALTER TABLE… MOVE PARTITION command can move partitions (or subpartitions) of a LIST, RANGE or HASH partitioned (or subpartitioned) table.
SALIH MOVE TABLESPACE NEW_TABLESPACE_NAME; You can move lots of tables to the new tablespace with using generate move scripts. select 'ALTER TABLE '||owner||'. '||table_name||' move tablespace '||'NEW_TBS_NAME;' from dba_tables where tablespace_name='PMDB_DAT1';
Moving oracle tables with lob columns to another tablespace
- Move table.
- Rebuild indexes (and move them to the new tablespace if you wish so)
- Move the lob segment.
- Enable row movement.
- Shrink the table and lob segment.
To alter the SYSAUX tablespace, you must have
the SYSDBA system privilege. If you have ALTER TABLESPACE system privilege, then you can perform any ALTER TABLESPACE operation.
ALTER TABLESPACE
- Take the tablespace online or offline.
- Begin or end a backup.
- Make the tablespace read only or read write.
Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.
An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
DBID And DBNAME
- Backup the database.
- Mount the database after a clean shutdown.
- Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege.
- Shutdown the database.
- Modify the DB_NAME parameter in the initialization parameter file.
- Create a new password file.
Setting a file in a tablespace to autoextend is a fairly easy task as long as the user performing the actions has DBA or SYSDBA privileges: SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01. dbf' autoextend on maxsize 2000M; Database altered.
LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database. The file must exist on the standby database and must be writable or the recovery process will halt with an error.
Check status of tablespace offline or online in OracleCheck the tablespace status is offline or online. SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES; Take the tablespace offline.
If you want to rename a data file on a standby database, you need to do it manually:
- Cancel the Data Guard recovery (set dg_broker_start to false and restart the database in mount status if you use data guard).
- Set the standby file management to manual alter system set standby_file_management=manual.
DB_FILE_NAME_CONVERT is useful for creating a duplicate database for recovery purposes. When the standby database is updated, this parameter converts the datafile name on the primary database to the datafile name on the standby database.
Steps for Creating New Control Files
- Make a list of all datafiles and redo log files of the database.
- Shut down the database.
- Back up all datafiles and redo log files of the database.
- Start up a new instance, but do not mount or open the database: STARTUP NOMOUNT.
There is no way to rename a system generated ASM filename. Once a user-created-alias is added in the diskgroup, then you can use “rename alias” to rename the filename within ASM.
To rename a column in oracle we have to use rename column statement. You have to use rename column statement along with alter table statement. The RENAME COLUMN statement allows us to rename an existing column in an existing table in any schema (except the schema SYS).
