MSSQL - Change tempdb mdf and ldf file location

sp_helpdb tempdb




USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\tempdb_mssql_8.ndf');
GO
USE master;
GO

###########################ALSO  ##########################################

We cant change recovery mode from simple to full in temp db





USE [master]
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL
GO

--Sizing the data and log files and autogrowth property

USE [master]

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 20480KB )--<< pre sizing data, auto growth

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'templog', SIZE = 20480KB , FILEGROWTH = 20480KB )--<< pre sizing log, auto growth

GO

expdp Master Table error ORA-31633:

ERROR :

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.FULL_EXPDP_SMRTESTENV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object

oracle@s08:~> sh /u02/scripts/expdp_SMRTESTENV_full.sh

Export: Release 12.1.0.2.0 - Production on Mon Mar 23 11:48:20 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.FULL_EXPDP_SMRTESTENV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;  2    3    4

OWNER_NAME                               JOB_NAME                                 OPERATION                      JOB_MODE             STATE                          ATTACHED_SESSIONS
---------------------------------------- ---------------------------------------- ------------------------------ -------------------- ------------------------------ -----------------
SYS                                      FULL_EXPDP_SMRTESTENV                    EXPORT                         FULL                 NOT RUNNING                           0



SQL> drop table sys.FULL_EXPDP_SMRTESTENV;

Table dropped.

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;  2    3    4

no rows selected

SQL>


oracle@s08:/u01/app/oracle/diag/rdbms/smrtestenv/SMRTESTENV/trace> sh /u02/scripts/expdp_SMRTESTENV_full.sh

Export: Release 12.1.0.2.0 - Production on Mon Mar 23 12:09:13 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."FULL_EXPDP_SMRTESTENV":  "sys/********@SMRTESTENV AS SYSDBA" dumpfile=expdpSMRTESTENV-2020-03-23_12-09-13.dmp directory=EXP_SMRTESTENV logfile=expdpSMRTESTENV-2020-03-23_12-09-13.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_SMRTESTENV parallel=2
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.858 GB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Database installation using DBCA Silent installation with custom Template on 12C

oracle@s8:/u01/app/oracle/product/12.1.0/dbhome_1/bin> ./dbca -silent -createDatabase -templateName Teamcenter_Oracle.dbt -gdbName SMRTESTENV -sid SMRTESTENV -sysPassword system123 -systemPassword system123 -emConfiguration NONE -characterSet AL32UTF8
Creating and starting Oracle instance
2% complete
3% complete
4% complete
11% complete
Creating database files
12% complete
13% complete
14% complete
22% complete
Creating data dictionary views
25% complete
32% complete
33% complete
34% complete
35% complete
36% complete
37% complete
38% complete
39% complete
40% complete
41% complete
42% complete
43% complete
44% complete
45% complete
52% complete
55% complete
56% complete
58% complete
Completing Database Creation
62% complete
65% complete
68% complete
69% complete
70% complete
80% complete
90% complete
92% complete
Running Custom Scripts
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SMRTESTENV/SMRTESTE.log" for further details.
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/SMRTESTENV.
Database Information:
Global Database Name:SMRTESTENV
System Identifier(SID):SMRTESTENV

oracle@sgdcpl08:/u01/app/oracle/product/12.1.0/dbhome_1/bin>

Check Template Teamcenter_Oracle.dbt on link