Create Temporary Tablespace In Standby Database [UPDATED]
Temporary file addition is not handle automatically as data file added in Dataguard Environment.You have to manually add the temp file in Standby database and managed it manually for sync both Primary and standby.Parameter standby_file_management=AUTO has no impact on tempfile on Standby. It managed manually.
Create Temporary Tablespace In Standby Database
4. Standby: Check files present in TEMP tablespace.Tempfile will NOT automatically be created in the physical standbyselect name from v$tempfile;Note: standby_file_management=AUTO has no impact on tempfile management.
A. You can switchover the Primary to Standby database.Add the new tempfile to the new primary site manually after the switch-over is complete.alter tablespace temp add tempfile '+DATA' size 50M;Note: it will maintain the same file structure at both primary and standby.
B. Tempfile is added to the Primary and the standby site is OPEN READ ONLY mode.you can add the new temp file manually to the Standby if standby is in READONLY mode.alter tablespace temp add tempfile '+DATA' size 20M;
Note: You cannot add temp file during database mount state. If you tried you got the following error:SQL> alter tablespace TEMP add tempfile 'C:\ORacle18c\oradata\xe\temp02.dbf' size 200m;alter tablespace TEMP add tempfile 'C:\ORacle18c\oradata\xe\temp02.dbf' size 200m*ERROR at line 1:ORA-01109: database not open
By default, tablespaces are created with auto-extend enabled, and no maximum size. Because of these default settings, tablespaces can grow to consume all allocated storage. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.
To create a temporary tablespace on the instance store, use the Amazon RDS procedure rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace. The create_inst_store_tmp_tblspace procedure has the following parameters.
When you run rdsadmin_util.create_inst_store_tmp_tblspace, the newly created temporary tablespace is not automatically set as the default temporary tablespace. To set it as the default, see Setting the default temporary tablespace.
When you create a temporary tablespace on a primary DB instance, the read replica doesn't create tempfiles. Assume that an empty temporary tablespace exists on your read replica for either of the following reasons:
You can add a tempfile to the empty temporary tablespace, and store the tempfile in the instance store. To create a tempfile in the instance store, use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_inst_store_tempfile. You can use this procedure only on a read replica. The procedure has the following parameters.
You can't drop an existing temporary tablespace on a read replica. You can change the tempfile storage on a read replica from Amazon EBS to the instance store, or from the instance store to Amazon EBS. To achieve these goals, do the following:
With Amazon RDS, you can store external table files in directory objects. You can create a directory object, or you can use one that is predefined in the Oracle database, such as the DATA_PUMP_DIR directory. For information about creating directory objects, see Creating and dropping directories in the main data storage space. You can query the ALL_DIRECTORIES view to list the directory objects for your Amazon RDS Oracle DB instance.
Suppose that you want to move data that is in an Amazon RDS Oracle DB instance into an external data file. In this case, you can populate the external data file by creating an external table and selecting the data from the table in the database. For example, the following SQL statement creates the orders_xt external table by querying the orders table in the database.
By default, Oracle tablespaces are created with auto-extend enabled and no maximum size. Because of these default settings, tablespaces can grow too large in some cases. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.
To resize the temporary space in a read replica for an Oracle DB instance, use either the rdsadmin.rdsadmin_util.resize_temp_tablespace or the rdsadmin.rdsadmin_util.resize_tempfile Amazon RDS procedure.
With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).
Through initialization parameters, you specify the file system directory to be used for a particular type of file. The database then ensures that a unique file, an Oracle managed file, is created and deleted when no longer needed.
Existing databases are able to operate as they always have. New files can be created as managed files while old ones are administered in the old way. Thus, a database can have a mixture of Oracle managed and unmanaged files.
Defines the location of the default file system directory or Oracle ASM disk group where the database creates data files or temp files when no file specification is given in the create operation. Also used as the default location for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n are not specified.
Defines the location of the Fast Recovery Area, which is the default file system directory or Oracle ASM disk group where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default location for redo log and control files or multiplexed copies of redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n are not specified. When this parameter is specified, the DB_RECOVERY_FILE_DEST_SIZE initialization parameter must also be specified.
The file system directories specified by these parameters must already exist; the database does not create them. The directory must also have permissions to allow the database to create the files in it.
The default location is used whenever a location is not explicitly specified for the operation creating the file. The database creates the file name, and a file thus created is an Oracle managed file.
One or more control files as specified in the DB_CREATE_ONLINE_LOG_DEST_n initialization parameter. The file in the first directory is the primary control file. When DB_CREATE_ONLINE_LOG_DEST_n is specified, the database does not create a control file in DB_CREATE_FILE_DEST or in DB_RECOVERY_FILE_DEST (the Fast Recovery Area).
If no value is specified for DB_CREATE_ONLINE_LOG_DEST_n, but values are set for both the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST, then the database creates one control file in each location. The location specified in DB_CREATE_FILE_DEST is the primary control file.
If the database creates an Oracle managed control file, and if there is a server parameter file, then the database creates a CONTROL_FILES initialization parameter entry in the server parameter file. If there is no server parameter file, then you must manually include a CONTROL_FILES initialization parameter entry in the text initialization parameter file.
If the DB_CREATE_ONLINE_LOG_DEST_n parameter is not set, but both the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters are set, then the database creates one Oracle managed log file member in each of those locations. The log file in the DB_CREATE_FILE_DEST destination is the first member.
If DB_CREATE_FILE_DEST is not set, then the database creates one SYSTEM and one SYSAUX tablespace data file whose names and sizes are operating system dependent. Any SYSTEM or SYSAUX tablespace data file created in this manner is not an Oracle managed file.
By default, Oracle managed data files, including those for the SYSTEM and SYSAUX tablespaces, are 100MB and autoextensible. When autoextension is required, the database extends the data file by its existing size or 100 MB, whichever is smaller. You can also explicitly specify the autoextensible unit using the NEXT parameter of the STORAGE clause when you specify the data file (in a CREATE or ALTER TABLESPACE operation).
Optionally, you can create an Oracle managed data file for the SYSTEM or SYSAUX tablespace and override default attributes. This is done by including the DATAFILE clause, omitting a file name, but specifying overriding attributes. When a file name is not supplied and the DB_CREATE_FILE_DEST parameter is set, an Oracle managed data file for the SYSTEM or SYSAUX tablespace is created in the DB_CREATE_FILE_DEST directory with the specified attributes being overridden. However, if a file name is not supplied and the DB_CREATE_FILE_DEST parameter is not set, then the CREATE DATABASE statement fails.
The UNDO TABLESPACE clause itself is optional in the CREATE DATABASE statement. If it is not supplied, and automatic undo management mode is enabled (the default), then a default undo tablespace named SYS_UNDOTS is created and a 20 MB data file that is autoextensible is allocated as follows:
If automatic undo management mode is enabled (the default), then an undo tablespace data file in directory /u01/app/oracle/oradata that is 20 MB and autoextensible up to an unlimited size. An undo tablespace named SYS_UNDOTS is created.
This database configuration is not recommended for a production database. The example illustrates how a very low-end database or simple test database can easily be created. To better protect this database from failures, at least one more control file should be created and the redo log should be multiplexed.
When certain conditions are met, the following SQL statements can create data files for tablespaces using Oracle Managed Files: CREATE TABLESPACE, CREATE UNDO TABLESPACE, and ALTER TABLESPACE ... ADD DATAFILE.