Generate DDL for any objects in Oracle 9i

To generate the ddl of any objects in oracle 9i, the following script can be used.

1. Login as sysdba

2. Run the below query:

select dbms_metadata.get_ddl('TABLE',u.object_name) from DBA_objects u where u.object_type='TABLE' and u.owner='Schema1'

This will get the ddl for all the tables in a particular schema -schema1.

3. In case, if you get any errors because of the dbms metadata as shown below:

 ERROR:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_XMLGEN" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

4.To fix the error, recreate the dbms_xmlgen package with the following steps.

Recreate the DBMS_XMLGEN package as follows.
From SQL*Plus connected to the database as AS SYSDBA, enter:
SQL> @?/rdbms/admin/dbmsxml.sql
SQL> @?/rdbms/admin/prvtxml.plb
SQL> alter package SYS.DBMS_XMLGEN compile body;
SQL> alter package SYS.DBMS_METADATA compile body;This will fix the dbms_metadata issues and the ddl can be generated.

Applying Oracle Patches

Applying Patches on Oracle Database:

1.Download the CPU patch from Metalink.

2.Change the owner of the patch file to oracle user.
# chown –R oracle: install p14841437_112020_SOLARIS64.zip

3.Set the PATH variable to locate the opatch utility.

$ export PATH=$PATH: $ORACLE_HOME/OPatch

4.unzip the patch and go the unzipped directory
$unzip pp14841437_112020_SOLARIS64.zip

------------------------------------------------------------------------

1. Login to the database server using Oracle ID and create the backup folder

2. To create backup folder where the backup file can be created.
example:  $ cd /oradata/dba/backup

3. Issue the tar command to backup the Oracle home
example: $tar –cvf orahome.tar /u01/app/oracle/product/11.2.0/dbhome_1

4. Issue the tar command to backup the Oracle OraInventory
example:  $tar –cvf Inventory.tar /u01/app/OraInventory

To backup the Grid home follow the steps below:

1. Login to the database server using Grid ID
2. Go to the folder where the backup file can be created.
example:  $ cd /oradata/dba/backup

3. Issue the tar command to backup the Grid Home
example:  $ tar –cvf gridhome.tar /u01/app/11.2.0/grid

------------------------------------------------------------------------

1 - Shut down the db instances,ASM instance and Nodeapps services on APPDBSVR01.

1.a. Login with oracle id
srvctl stop instance -d APPDB -i APPDB1

1.b. Login with grid id

srvctl stop asm –n APPDBSVR01
srvctl stop nodeapps –n APPDBSVR01

If the asm/nodeapps cannot be shut down, then force option can be used.
i.e.
srvctl stop asm -f -n APPDBSVR01
srvctl stop nodeapps -f -n APPDBSVR01

2 - Apply the patch to the RAC home
APPDBSVR01-> cd 14841437
APPDBSVR01-> opatch apply

3. Verify Patches are applied
$opatch lsinventory -detail -oh $ORACLE_HOME              
4 - Start the Nodeapps,ASM instance and db instances on APPDBSVR01,
4.a. Login with grid id
srvctl start nodeapps –n APPDBSVR01
srvctl start asm -n APPDBSVR01
srvctl start instance -d APPDB -i APPDB1

5. On the second node, the post installation steps can be done.

6. Post Installation Steps

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus.
Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT

Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for anyerrors:

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log

Recompile all invalid PL/SQL packages
SQL> @?/rdbms/admin/utlrp.sql


Meanwhile, the below might be helpful if you encounter any issues in bringinng up the cluster.

Check the resources status:

Login with grid id:
 $crsctl stat res -t

The output will show you whether cluster and related resources are online or offline.

$cd $ORA_CRS_HOME
$crsctl check crs


If any resource is offline or the below error is encountered while starting up the nodeapps, then crsctl can be used to start the cluster.$ srvctl start nodeapps -n appdbsvr01
PRCR-1070 : Failed to check if resource ora.gsd is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.net1.network is registered
Cannot communicate with crsd
PRCR-1035 : Failed to look up CRS resource appdbsvr01 for ora.cluster_vip.type
PRCR-1068 : Failed to query resources
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.ons is registered
Cannot communicate with crsd

For the above error, execute the below command.
$crsctl start cluster

This will start the resources and bring up the asm.

ORA-00257: archiver error

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

Recently, there's an issue at the development database saying, user cannot connect although sysdba is able to connect. The following error is logged in the alert log file.

When the archiver state is queried from v$database, it says - stopped.

The reason for the error is that the mountpoint of the archived logs is 100%. So we need to backup the archive logs to a different location and free-up the disk usage.

With this the archiver starts to run.

XServer - XTerm

Xterm:

In case, if you need a gui based tool (unlike putty) for any installation in solaris/aix servers, the software mobaxterm can be used.

It’s similar to putty (a portable executable file) but with x11 server.
While using it, just need to make sure the display is set to the locahost server (i.e. the windows server on which you’re running the mobaxterm), with that you’re all set to go with gui based installation.

Below outlines the purpose of MobaXterm:

MobaXterm is an enhanced terminal for Windows with an X11 server, several network tools for remote computing and all the essential Unix commands packaged into a single portable exe file. MobaXterm has been designed to suit the needs of computer users, sysadmins, developers and webmasters by providing:
- a multitab terminal with embedded Unix commands (ls, cd, cat, sed, grep, awk, rsync, wget, ...)
- an embedded X11 server for easily exporting your Unix/Linux display
- a session manager with several network utilities: SSH, RDP, VNC, SFTP, FTP, Telnet, Rlogin, FTP, SFTP and XDMCP

The downloads,documentation can be found at: http://mobaxterm.mobatek.net/

Check DB Size - Oracle

select (select sum(bytes)/1024/1024 from dba_data_files)+
(select sum(bytes)/1024/1024 from dba_temp_files) "Size in MB" from dual;

MS SQL Memory Setting

To set the memory of MS SQL Server, use the following formula.The default is unlimited in the SQL server during installation.

2GB-Base memory requirement for Enterprise version + (Total Server memory / 2)
 *Example 2GB + (16 / 2) = 10 GB.

This is to prevent other services and process such as backup, antivirus and OS operations from reaching a memory limit when the SQL server attempts to use the maximum memory for optimal performance.
Thank you.

More information at this link.
http://msdn.microsoft.com/en-us/library/ms143506(v=SQL.100).aspx

Datapump

DataPump:

If you need to export the dump using sys account, then the following is the command:

$ expdp 'userid="/ as sysdba"' full=y directory=dpump_dir dumpfile=datapump.dmp

where the directory is an oracle virtual directory pointing to the physical file location.

Directory needs to be created first using the below command using sysdba account:

$ create directory dpump_dir as '/u01/oracle10g/datapump/export/';

After creating the directory, necessary access needs to be given, if the import/export is going to be done using a different account:

$ grant read,write on directory dpump_dir to appuser;

Datapump export/import using different versions:

If the datapump needs to be exported from say Oracle 11g version to Oracle 10g version, then the version parameter can be used to do the data export.
i.e. In oracle 11g database, the following command can be used to export the dump:

$ expdp 'userid="/ as sysdba"' full=y directory=dpump_dir dumpfile=datapump.dmp version=10.2

In oracle 10g database, the import can be done without any errors:

$ impdp 'userid="/ as sysdba"' full=y directory=dpump_dir dumpfile=datapump.dmp

Change Schema -SQL Server 2005

ALTER SCHEMA NewSchema TRANSFER OldSchema.Table1